MYSQL网页设计有用的数据库反省程序(2)

“对于MySQL数据库，无论是在开发方面，还是支持方面，现在有大量强大的MySQL学习教程可以选择。每一个新手开发者可以轻松地使用MySQL数据库进行开发。程序|数据|数据库
TtitleOff



prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt+3.0LogSwitchIntheLastDay+

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt



TTitleleft"***Database:"xdbname",HowOffentheLogSwitch(Asof:"xdate")***"skip1



columnarchive_nameformatA40

column"Time"formatA25



selectto_char(TO_DATE(Time,MM/DD/RRHH24:MI:SS),DD-MON-RRRRHH24:MI:SS)"Time",

ARCHIVE_NAME

fromv$log_history

whereTO_DATE(Time,MM/DD/RRHH24:MI:SS)>sysdate-1

orderbyTO_DATE(Time,MM/DD/RRHH24:MI:SS)desc;



prompt

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt3.0Invesigation

promptStandard:

promptDuringperiodsofhighactivity,logswitchesareoccurringevery20minutes

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt

ttitleoff



prompt

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt+3.1LogBuffer-redologspacerequests:TheValue+

prompt+shouldberelativesmallpromptServeriswaitingfor+

prompt+diskspacetobeallocateforredologentries+

prompt+Spaceiscreatedbyperformingalogswitch+

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt



ttitleleft"***LogBuffers-redologspacerequests***"Skip1

selectsubstr(name,1,25)"LogBuffers",

substr(value,1,15)"VALUE(Near0?)"

fromv$sysstat

wherename=redologspacerequests

/



prompt

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt3.1Investigation

promptIfthevalueisnotnear0,increaseLOGBUFFER.

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt

ttitleoff



prompt

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt+3.2LogBuffer-logbufferspace+

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt

ttitleleft"***LogBuffers-LogBufferSpaceWaits***"Skip1

selectsid,event,seconds_in_wait,state

fromv$session_wait

whereevent=logbufferspace

/



prompt

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt3.2Invesigation

promptThereshouldbenologbufferspacewaits

promptMakingthelogbufferbiggerifitissmall

promptMovingthelogfilestofasterdiskssuchasstripeddisks

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt

ttitleoff



prompt

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt+3.3LogBuffer-RedoBufferAllocationRetries+

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt



ttitleleft"***LogBuffers-RedoBufferAllocationRetries***"Skip1

columnnameprint

selectname,value

fromv$sysstat

wherenamein(redobufferallocationretries,redoentries)

/



prompt

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt3.3Investigation

promptRedoBufferAllocationRetriesshouldbenear0

promptthenumbershouldbelessthan1%ofRedoEntries

promptIncreasethesizeoftheredologbuffer(LOGBUFFER)

promptimprovethecheckpointingorarchivingprocess

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt

ttitleoff



prompt

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt+3.4LogBuffer-LogFileSwitchCompletion+

prompt+Identifythelogfileswitchwaitsbecauseoflogswitches+

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt



ttitleleft"***LogBuffers-LogFileSwithCompletion***"Skip1

selectevent,total_waits,time_waited,average_wait

fromv$system_event

whereeventlikelogfileswitchcompletion%

/

prompt

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt3.4Investigation

promptIncreasethesizeoftheredologfiles

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt

ttitleoff



prompt

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt+3.5LogBuffer-CHECKPOINTIncomplete+

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt



ttitleleft"***LogBuffers-CheckPointIncompletion***"Skip1

selectevent,total_waits,time_waited,average_wait

fromv$system_event

whereeventlikelogfileswitch(check%

oreventlikelogfileswitch(arch%

/

prompt

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt3.5Investigation

promptcheckthefrequenceofcheckpointsandsettheappropriatevalues

promptforLOG_CHECKPOINT_INTERVALandLOG_CHECKPOINT_TIMEOUT

promptcheckthesizeandnumberofredologgroups

promptconfirmthatthearchivedeviceisnotfull

promptaddredologgroups

promptincreasethenumberofbufferstoarchiveandreducingthesizeofbuffersbysetting

promptLOG_ARCHIVE_BUFFERSandLOG_ARCHIVE_BUFFER_SIZE

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt

ttitleoff



prompt

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt+4.0SharePoolSize-GetsandMisses(LibraryCache)+

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt



column"Executions"format9,999,999,990

column"CacheMissesExecuting"format9,999,999,990

column"DataDictionaryGets"format9,999,999,999

column"GetMisses"format9,999,999,999

column"%Ratio"format999.99



ttitleleftskip1-

left"***SharedPoolSize(ExecutionMisses)***"skip1



selectsum(pins)"Executions",

sum(reloads)"CacheMissesExecuting",

(sum(reloads)/sum(pins)*100)"%Ratio"

fromv$librarycache

/



prompt

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt4.0Investigation

promptIf%Ratioisabove1%,increaseSHARE_POOL_SIZE.

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt

ttitleoff



prompt

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt+4.1SharePoolSize-GetsandMisses(DataDictionary)+

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt



ttitleleft"***************SharedPoolSize(DictionaryGets)***********"skip1

selectsum(gets)"DataDictionaryGets",

sum(getmisses)"GetMisses",

100*(sum(getmisses)/sum(gets))"Ratio"

fromv$rowcache

/



prompt

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt4.1Investigation

promptIf%Ratioisabove12%,increaseSHARED_POOL_SIZE.

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt

ttitleoff



prompt

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt+5.0CheckWhichSQListhemostcostSQL+

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt

ttitleoff



ttitleleft"***V$SQLCheck***"Skip1

columnSQL_TEXTFormatA50

selectSql_TEXT,EXECUTIONS,ROWS_PROCESSED,BUFFER_GETS/100,DISK_READS/100

FROMV$sqlV,(SELECTSUM(BUFFER_GETS)TOT_GETSFROMV$SQL)S

WHEREBUFFER_GETS>TOT_GETS*0.1

ORDERBYBUFFER_GETSDESC

/



prompt

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt5.0Invesigation

promptSelectedSQListhemostcostSQL(>10%oftotalgets)

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt

ttitleoff



prompt

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt+6.0HowMuchCPUisusedforeachsession+

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt

ttitleleft"***CPUUsage***"Skip1

columnUSERNAMEFORMATA10

columnMACHINEFORMATA15

columnOSUSERFORMATA15

columnTERMINALFORMATA15

columnPROGRAMFORMATA20

selects.sid,v.SERIAL#,v.USERNAME,v.OSUSER,v.MACHINE,v.TERMINAL,v.PROGRAM,s.value"CPUUsed"

fromv$sesstats,v$statnamen,v$sessionv

wheres.statistic#=n.statistic#andn.name=CPUusedbythissession

ands.sid=v.sid

/



prompt

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt6.0Invesigation

prompt

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt
这是无法比较的，因为基于云的数据库提供了不同的模式。关键是要通过围绕云计算产品来包装其他增值服务以适应不断变化的市场条件：这就是DBaaS。