社区 › MYSQL数据库开发 › MYSQL教程之有用的数据库反省程序(3)

简单生活 发表于 2015-1-16 22:48:52

MYSQL教程之有用的数据库反省程序(3)

如IBM公司最近宣布让渠道合作伙伴分销其SaaS应用程序的新计划。微软认为MySQL学习教程是销售其云计算服务的重要组成部分。然而即使有这种趋势，DBaaS仍然不同于内部数据库，解决方案提供商必须认识到这一点;否则，他们不仅仅是丢失几个客户，而是要失去的更多。程序|数据|数据库
prompt

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

prompt+7.0SortAreaSize+

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

prompt



ttitleleft"***SortArea***"Skip1



Selectsubstr(name,1,25)"SortAreaName",

substr(value,1,15)"Value"

fromv$sysstat

wherenamelikesort%

/



prompt

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

prompt7.0Invesigation

promptThelowerthevalueofthesortstodisk,

promptthebetterthesortisperforming.

promptSORT_AREA_SIZEcantbeincreaselargeenoughtoelimatesortstodisk

promptTheSortsonadatabasearelow-maintenanceitems

promptUsually,SORT_AREA_SIZEandSORT_AREA_RETAINED_SIZEshouldbesettothesamevalue

promptTheOptimalValueofSORT_AREA_SIZEandSORT_AREA_RETAINED_SIZEis1M

prompt(Forparallelquery).Thelargervaluethan1Mhavenotimprovedperformancesignificantly

promptcheckv$licenseandv$sort_usage

promptFromRevealNet:

promptINITIAL/NEXTparameteroftemporarytableshouldhaveminimumsizeSORT_AREA_SIZE+1block

promptSORT_AREA_RETAINED_SIZEsettohalftheSORT_AREA_SIZE



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

prompt

ttitleoff



prompt

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

prompt+7.1SEQUENCE_CACHE_ENTRIES

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

prompt

ttitleleft"***SEQUENCE_CACHE_ENTRIES***"Skip1

selectcount(*)"NumberofSequence",SUM(CACHE_SIZE)"CacheSizeNeeded"fromDBA_sequences

/



prompt

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

prompt7.1Invesigation

promptSEQUENCE_CACHE_ENTRIESshouldsetto1000

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

prompt

ttitleoff



prompt

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

prompt+9.0RollbackSegmentContention+

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

prompt



ttitleleft"***RollbackSegmentContention***"Skip1



column"RollbackSegName"formata20

column"Online?"formata10

column"Gets"format9,999,999,990

column"Waits"format9,999,999,990

column"%Ratio"format999.99



selectr.name"RollbackSegName",

s.status"Online?",

s.gets"Gets",

s.waits"Waits",

(waits/gets)*100"%Ratio"

fromv$rollstats,v$rollnamer

wheres.usn=r.usn

/



prompt

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

prompt9.0Investigation

promptIftheradioisabove2%,createmorerollbacksegment.

promptTheguidelineis:

promptTransactionNumber*RollbackSeg.Num

prompt<16*4

prompt>=16&<32*8

prompt>=32*Min(50,TransactionNumbers/4)

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

prompt

ttitleoff



prompt

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

prompt+9.1RollbackSegmentContention+

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

prompt

ttitleleft"***RollbackSegmentContention***"Skip1

selectclass,count

fromv$waitstat

whereclasslike%undo%

Union

selectname,value

fromv$sysstat

wherename=consistentgets

/



prompt

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

prompt9.1Investigation

prompttheratioofwaitsforanyclassshouldbelessthan1%ofthe

prompttotalnumberofrequests

promptIftheratioisgreaterthan1%,considercreatingmorerollbackseg

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

prompt

prompt

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

promptHowtoestimatethetransactionsize

promptbeforeexecute:selectusn,writesfromv$rollstat

promptafterexecute:selectusn,writesfromv$rollstat

promptcomparetheresult

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

prompt

ttitleoff



prompt

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

prompt+10.0FreeSpaceCoalesced+

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

prompt



ttitleleft"***FreeSpaceCoalesced***"Skip1



selectTablespace_name,percent_blocks_coalesced

fromdba_free_space_coalesced

orderbypercent_blocks_coalesced

/



prompt

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

prompt10.0Investigation

promptTheIdealPercentBlocksCoalescedshouldbe100%

promptUse"ALterTablespace<Name>coalesce"tocoalesce

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

prompt

ttitleoff



prompt

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

prompt+11.0LatchContention+

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

prompt



ttitleleft"********LatchInformation******"Skip1



column"RedoType"formatA20

columngetsformat9,999,990

columnWILLING_TO_WAITformat9,999,990

columnmissesformat9,999,990

column"IMMEDIATE"FORMAT999.99



selectName"RedoType",gets,misses,

decode(gets,0,0,(100*(misses/(gets+misses))))WILLING_TO_WAIT,

sleeps,immediate_gets,immediate_misses,

decode(immediate_gets,0,0,(100*(immediate_misses/(immediate_gets+immediate_misses))))"IMMEDIATE"

fromv$latch

wherenamelikeredo%

orderbyname

/



prompt

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

prompt11.0Investigation

promptIfWilling_to_waitandImmediateisgreaterthan1%,

promptincreaseLog_Simultaneous_copiestotwice#ofCPUs,

promptanddecreaseLog_Small_Entry_Max_Sizeininit.orafile

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

prompt

ttitleoff



prompt

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

prompt+11.1LatchContention(RevealNet)+

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

prompt



ttitleleft"********LatchInformation******"Skip1

SELECTa.name,

100.*b.sleeps/b.getsratio1,

100.*b.immediate_misses/DECODE((b.immediate_misses+b.immediate_gets),0,1)ratio2

FROMv$latchnamea,v$latchb

WHEREa.latch#=b.latch#

ANDb.sleeps>0

/

prompt

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

prompt11.1Investigation

prompt

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

prompt

ttitleoff



prompt

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

prompt+12.0TablespaceUsage+

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

prompt



setpagesize66

clearbreaks

clearcomputes



column"TotalBytes"format9,999,999,999,999

column"SQLBlocks"format9,999,999,999

column"BytesFree"format9,999,999,999,999

column"BytesUsed"format9,999,999,999,999

column"%Free"format9999.999

column"%Used"format9999.999

breakonreport

computesumof"TotalBytes"onreport

computesumof"SQLBlocks"onreport

computesumof"BytesFree"onreport

computesumof"BytesUsed"onreport

computesumof"%Free"onreport

computesumof"%Used"onreport



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

selectsubstr(fs.FILE_ID,1,3)"ID#",

fs.tablespace_name,

df.bytes"TotalBytes",

df.blocks"SQLBlocks",

sum(fs.bytes)"BytesFree",

(100*((sum(fs.bytes))/df.bytes))"%Free",

df.bytes-sum(fs.bytes)"BytesUsed",

100*((df.bytes-sum(fs.bytes))/df.bytes)"%Used"

fromsys.dba_data_filesdf,sys.dba_free_spacefs

wheredf.file_id(+)=fs.file_id

groupbyfs.file_id,fs.tablespace_name,df.bytes,df.blocks

orderbyfs.tablespace_name

/



prompt

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

prompt12.0Invesigation

promptifatablespacehasalldatafileswith%Usedgreater

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

prompt

ttitleoff



prompt

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

prompt+13.0DiskActivity+

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

prompt



column"FileName"formata35

column"FileTotal"format999,999,999,990

setpagesize33

ttitle"***Database:"xdbname",DatafileDiskActivity(Asof:"xdate")***"



selectsubstr(df.file#,1,2)"ID",

rpad(name,35,.)"FileName",

rpad(substr(phyrds,1,10),10,.)"PhyReads",

rpad(substr(phywrts,1,10),10,.)"PhyWrites",

rpad(substr(phyblkrd,1,10),10,.)"BlkReads",

rpad(substr(phyblkwrt,1,10),10,.)"BlkWrites",

rpad(substr(readtim,1,9),9,.)"ReadTime",

rpad(substr(writetim,1,10),10,.)"WriteTime",

sum(phyrds+phywrts+phyblkrd+phyblkwrt+readtim)"FileTotal"

fromv$filestatfs,v$datafiledf

wherefs.file#=df.file#

groupbydf.file#,df.name,phyrds,phywrts,phyblkrd,

phyblkwrt,readtim,writetim

orderbysum(phyrds+phywrts+phyblkrd+phyblkwrt+readtim)desc,df.name

/





prompt

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

prompt13.0Investigation

promptToreducediskcontention,insurethatdatafiles

promptwiththegreatestactivityarenotonthesamedisk.

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

prompt

ttitleoff

prompt

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

prompt+14.0FragmentationNeed+

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

prompt

setheadingon

settermouton

setpagesize66



ttitleleft"***Database:"xdbname",DEFRAGMENTATIONNEEDASOF:"xdate"***"



selectsubstr(de.owner,1,8)"Owner",

substr(de.segment_type,1,8)"SegType",

substr(de.segment_name,1,35)"TableName(Segment)",

substr(de.tablespace_name,1,20)"TableSpaceName",

COUNT(*)"FragNeed",

substr(df.name,1,40)"DatafileName"

fromsys.dba_extentsde,v$datafiledf

wherede.ownerSYS

andde.file_id=df.file#

andde.segment_type=TABLE

groupbyde.owner,de.segment_name,de.segment_type,de.tablespace_name,df.name

havingcount(*)>1

orderbycount(*)desc

/



prompt

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

prompt14.0Investigation

promptThemorefragmentedasegmentis,themoreI/Oneededtoread

promptthatinfo.Defragmentsthistableregularlytoinsureextents

prompt(FragNEED)donotgetmuchabove2.

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

prompt

ttitleoff

prompt

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

prompt+15.0promptHighWaterMark(HWM)+

prompt+Perform"ANALYZETABLE<TABLENAME>COMPUTESTATISTICS;"first+

prompt+ThisTranscationistakenmoreresources<Becarefullytouseit!>+

prompt+(Toanalyzetables,youcanuse+

prompt+DBMS_UTILITY.ANALYZE_SCHEMA(<USER>,COMPUTE))+

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

prompt



ttitleleftskip1-

left"************HighWaterMark*****************"skip1



columnOwnerformatA10

columnSegment_nameformatA40



selecta.Owner,

a.Segment_name,

a.blocks,

b.empty_blocks,

a.blocks-b.empty_blocks-1"HighWaterMark"

fromDBA_SEGMENTSa,DBA_TABLESb

wherea.Owner=b.Owner

anda.Segment_name=b.table_name

orderbya.owner,a.segment_name

/





prompt

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

prompt15.0Investigation

promptToanalyzethetablestatistics,youcanalsouse

promptDBMS_SPACE.Unused_space(<OWNER>,<OBJECT_NAME>,<OBJECT_TYPE>

prompt,<TotalBlocks>,<TotalBytes>,<UnusedBlocks>,<UnusedBytes>

prompt,<NumberVarible5>,<NumberVarible6>,<NumberVarible7>)

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

ttitleoff

spooloff
你不用花费很多时间和金钱来培训现有的职工，或者去花大价钱雇用那些拥有各种证书的开发者。因为MySQL的维护和管理在很大程度上是“傻瓜型”的。

小女巫 发表于 2015-1-20 05:03:07

可能有的朋友会抱怨集成的orderby，其实如果使用ranking函数,Orderby是少不了的。如果担心Orderby会影响效率，可以为orderby的字段建立聚集索引，查询计划会忽略orderby操作（因为本来就是排序的嘛）。

小魔女 发表于 2015-1-27 07:10:27

大侠们有推荐的书籍和学习方法写下吧。

若天明 发表于 2015-2-5 02:41:50

同样会为索引视图等应用带来麻烦。看看行级和事务级的快照数据放在tempdb中，就能感觉到目前架构的尴尬。

飘飘悠悠 发表于 2015-2-11 02:25:35

对于数据库来说，查询是数据库的灵魂，那么SQL查询效率究竟效率如何呢？下文将带对SQL查询的相关问题进行讨论，供您参考。

仓酷云 发表于 2015-3-1 19:37:48

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

蒙在股里 发表于 2015-3-10 22:36:59

原来公司用过MYSQL自己也只是建个表写个SQL

山那边是海 发表于 2015-3-17 11:56:07

发几份SQL课件，以飨阅者

再见西城 发表于 2015-3-24 09:30:21

一直以来个人感觉SQLServer的优化器要比Oracle的聪明。SQL2005的更是比2k聪明了不少。（有次作试验发现有的语句在200万级时还比50万级的相同语句要快show_text的一些提示没有找到解释。一直在奇怪。）

查看完整版本: MYSQL教程之有用的数据库反省程序(3)