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的拼写。 从项目平台的选择上讲，我们关心的，应该是一款产品能不能满足任务需求，而不是网上怎么说。 我个人认为就是孜孜不懈的学习
页:
[1]