DBA常用的一些性能优化方面的SQL语句.doc

1. 监控事例的等待??select? ?event,? ? sum(decode(wait_Time,0,0,1)) Prev, ? ? sum(decode(wait_Time,0,1,0)) Curr,? ? count(*) Tot ? ?? ? from??v$session_Wait ? ?? ? group by event ? ?? ? order by 4;??2. 回滚段的争用情况? ?? ?select? ?name, waits, gets, waits/gets Ratio ? ?? ? from??v$rollstat a, v$rollname b ? ?? ? where??a.usn = b.usn; ??3. 监控表空间的 I/O 比例? ?? ?select? ?df.tablespace_name name,df.file_name file,f.phyrds pyr,? ?? ???f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw? ?? ? from??v$filestat f, dba_data_files df? ?? ? where??f.file# = df.file_id? ?? ? order by df.tablespace_name;??4. 监控文件系统的 I/O 比例? ?? ?select? ?substr(a.file#,1,2) #, substr(a.name,1,30) Name, ? ?? ???a.status, a.bytes, b.phyrds, b.phywrts ? ?? ? from??v$datafile a, v$filestat b ? ?? ? where??a.file# = b.file#; ??5.在某个用户下找所有的索引? ?? ?select b.table_name, b.index_name, uniqueness, column_name,b.status from user_ind_columns a, user_indexes b where a.index_name = b.index_name and a.table_name = b.table_name order by b.table_type, b.table_name, b.index_name, column_position; 6. 监控 SGA 的命中率? ?? ?select? ?a.value + b.value logical_reads, ? ?? ???c.value phys_reads,? ?? ???round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) BUFFER HIT RATIO ? ?? ? from??v$sysstat a, v$sysstat b, v$sysstat c? ?? ? where??a.statistic# = 38 and b.statistic# = 39 and ? ?? ???c.statistic# = 40; ??7. 监控 SGA 中字典缓冲区的命中率? ?? ?select? ?parameter, gets,Getmisses , ? ?? ???getmisses/(gets+getmisses)*100 miss ratio,? ?? ???(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 Hit ratio? ?? ? from??v$rowcache ? ?? ? where??gets+getmisses lt;0? ?? ? group by parameter, gets, getmisses; ??8. 监控 SGA 中共享缓存区的命中率，应该小于1%? ?? ?select? ?sum(pins) Total Pins, sum(reloads) Total Reloads,? ?? ???sum(reloads)/sum(pins) *100 libcache? ?? ? from??v$librarycache;? ?? ?select? ?sum(pinhits-reloads)/sum(pins) hit radio,? ?? ???sum(reloads)/s