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。

一个百万级别的基本信息表A，一个百万级别的详细记录表B，A中有个身份证id，B中也有身份id；先要找出A中在B的详细记录。

可以动态传入参数，省却了动态SQL的拼写。

XML字段类型更好的解决了XML数据的操作。XQuery确实不错，但是个人对其没好感。（CSDN的开发者应该是相当的熟了！）

无法深入到数据库系统层面去了解和探究

这是一个不错的新特性。虽然索引的附加字段没有索引键值效率高，但是相对映射到数据表中效率还是提高了很多。我做过试验，在我的实验环境中会比映射到表中提高30%左右的效率。

可以动态传入参数，省却了动态SQL的拼写。

从项目平台的选择上讲，我们关心的，应该是一款产品能不能满足任务需求，而不是网上怎么说。

我个人认为就是孜孜不懈的学习