ORACLE性能监控之监控SQL语句

ORACLE性能监控之监控SQL语句分析表analyzetabletablenamecomputestatisticsforallindexes;analyzetabletablenamecomputestatisticsforallindexedcolumns;analyzetabletablenamecomputestatisticsfortable;监控事例的等待selectevent,sum(decode(wait_Time,0,0,1))"Prev",sum(decode(wait_Time,0,1,0))"Curr",count(*)"Tot"fromv$session_Waitgroupbyeventorderby4;查看碎⽚程度⾼的表SELECTsegment_nametable_name,COUNT(*)extentsFROMdba_segmentsWHEREownerNOTIN(SYS,SYSTEM)GROUPBYsegment_nameHAVINGCOUNT(*)=(SELECTMAX(COUNT(*)FROMdba_segmentsGROUPBYsegment_name);表、索引的存储情况检查selectsegment_name,sum(bytes),count(*)ext_quanfromdba_extentswheretablespace_name=&tablespace_nameandsegment_type=TABLEgroupbytablespace_name,segment_name;selectsegment_name,count(*)fromdba_extentswheresegment_type=INDEXandowner=&ownergroupbysegment_name;找使⽤CPU多的⽤户session12是cpuusedbythissessionselecta.sid,spid,status,substr(gram,1,40)prog,a.terminal,osuser,value/60/100valuefromv$sessiona,v$processb,v$sesstatcwherec.statistic#=12andc.sid=a.sidanda.paddr=b.addrorderbyvaluedesc;监控表空间的I/O⽐例selectdf.tablespace_namename,df.file_name"file",f.phyrdspyr,f.phyblkrdpbr,f.phywrtspyw,f.phyblkwrtpbwfromv$filestatf,dba_data_filesdfwheref.file#=df.file_idorderbydf.tablespace_name;回滚段的争⽤情况selectname,waits,gets,waits/gets"Ratio"fromv$rollstata,v$rollnamebwherea.usn=b.usn;在某个⽤户下找所有的索引selectuser_indexes.table_name,user_indexes.index_name,uniqueness,column_namefromuser_ind_columns,user_indexeswhereuser_ind_columns.index_name=user_indexes.index_nameanduser_ind_columns.table_name=user_indexes.table_nameorderbyuser_indexes.table_type,user_indexes.table_name,user_indexes.index_name,column_position;监控⽂件系统的I/O⽐例selectsubstr(a.file#,1,2)"#",substr(,1,30)"Name",a.status,a.bytes,b.phyrds,b.phywrtsfromv$datafilea,v$filestatbwherea.file#=b.file#;监控SGA中字典缓冲区的命中率selectparameter,gets,Getmisses,getmisses/(gets+getmisses)*100"missratio",(1-(sum(getmisses)/(sum(gets)+sum(getmisses))))*100"Hitratio"fromv$rowcachewheregets+getmisses<>0groupbyparameter,gets,getmisses;监控SGA中共享缓存区的命中率，应该⼩于1％selectsum(pins)"TotalPins",sum(reloads)"TotalReloads",sum(reloads)/sum(pins)*100libcachefromv$librarycache;selectsum(pinhits-reloads)/sum(pins)"hitradio",sum(reloads)/sum(pins)"reloadpercent"fromv$librarycache;监控SGA的命中率selecta.value+b.value"logical_reads",c.value"phys_reads",round(100*((a.value+b.value)-c.value)/(a.value+b.value))"BUFFERHITRATIO"fromv$sysstata,v$sysstatb,v$sysstatcwherea.statistic#=38andb.statistic#=39andc.statistic#=40;监控SGA中重做⽇志缓存区的命中率，应该⼩于1％SELECTname,gets,misses,immediate_gets,immediate_misses,Decode(gets,0,0,misses/gets*100)ratio1,Decode(immediate_gets+immediate_misses,0,0,immediate_misses/(immediate_gets+immediate_misses)*100)ratio2FROMv$latchWHEREnameIN(redoallocation,redocopy);显⽰所有数据库对象的类别和⼤⼩selectcount(name)num_instances,type,sum(source_size)source_size,sum(parsed_size)parsed_size,sum(code_size)code_size,sum(error_size)error_size,sum(source_size)+sum(parsed_size)+sum(code_size)+sum(error_size)size_requiredfromdba_object_sizegroupbytypeorderby2;监控内存和硬盘的排序⽐率，最好使它⼩于.10，增加sort_area_sizeSELECTname,valueFROMv$sysstatWHEREnameIN(sorts(memory),sorts(disk));监控当前数据库谁在运⾏什么SQL语句SELECTosuser,username,sql_textfromv$sessiona,v$sqltextbwherea.sql_address=b.addressorderbyaddress,piece;监控字典缓冲区SELECT(SUM(PINS-RELOADS))/SUM(PINS)"LIBCACHE"FROMV$LIBRARYCACHE;SELECT(SUM(GETS-GETMISSES-USAGE-FIXED))/SUM(GETS)"ROWCACHE"FROMV$ROWCACHE;SELECTSUM(PINS)"EXECUTIONS",SUM(RELOADS)"CACHEMISSESWHILEEXECUTING"FROMV$LIBRARYCACHE;后者除以前者,此⽐率⼩于1％,接近0％为好。SELECTSUM(GETS)"DICTIONARYGETS",SUM(GETMISSES)"DICTIONARYCACHEGETMISSES"FROMV$ROWCACHE监控MTSselectbusy/(busy+idle)"sharedserversbusy"fromv$dispatcher;此值⼤于0.5时，参数需加⼤selectsum(wait)/sum(totalq)"dispatcherwaits"fromv$queuewheretype=dispatcher;selectcount(*)fromv$dispatcher;selectservers_highwaterfromv$mts;servers_highwater接近mts_max_servers时，参数需加⼤碎⽚程度selecttablespace_name,count(tablespace_name)fromdba_free_spacegroupbytablespace_namehavingcount(tablespace_name)>10;altertablespacenamecoalesce;altertablenamedeallocateunused;createorreplaceviewts_blocks_vasselecttablespace_name,block_id,bytes,blocks,freespacesegment_namefromdba_free_spaceunionallselecttablespace_name,block_id,bytes,blocks,segment_namefromdba_extents;select*fromts_blocks_v;selecttablespace_name,sum(bytes),max(bytes),count(block_id)fromdba_free_spacegroupbytablespace_name;数据库性能监控的主要指标指标名说明SGA-systemglobalarea数据库系统全局区的⼤⼩（Oracle中的SGA）。SGA存放的是数据库中所有⽤户的共同信息，SGA的⼤⼩会严重影响数据库的性能————————————————————————————————————————————LogicalI/O每秒逻辑读/写的次数。当我们向数据库做任何的操作时，Oracle都会先到SGA中查找下有没有我们所需要的数据，如有就即时返回，没有再通过PhysicalI/O来向datafile读数据据，从⽽减少PhysicalI/O的读写————————————————————————————————————————————BuffercacheHitRatio共享池的命中率。这是衡量LogicalI/O的⼀个指标，如果命中率低，表明PhysicalI/O的读写次数必然增多，数据库性能必然下降。⼀般命中率都在95%以上，低于这个⽐率说明缓存区的设置太⼩————————————————————————————————————————————DBWR（N）数据库磁盘写进程的数⽬，当并发写多数据的时候，如果DBWR的数据不⾜以应付，DBWR写进程耗时必然多，容易产⽣等待时间或锁的时间————