定位Oracle中的top sql.docVIP

定位Oracle中的top sql 很多时候数据库效率低是少数几个top sql造成的，从top sql入手进行数据库调优是常用的手段。 10G中用sql_id代替address和hash_value 首先要找出top sql，可以从以下几个角度入手：占用资源最多的sql： 按照读的次数找SELECT b.username username,a.sql_id, a.executions exec, a.buffer_gets buffer, a.disk_reads disk,a.parse_calls parse,a.sql_text statementFROM V$SQLAREA a, dba_users bWHERE a.parsing_user_id=b.user_id and ( a.buffer_gets OR a.disk_reads 1000000 )ORDER BY a.buffer_gets + 100 * a.disk_reads DESC;也可以将v$sqlarea换成v$sql，再增加一个child_number字段，就可以找到子游标 在10.2的版本中推荐在v$sqlstats找。如果当前已经恢复正常，可以查询v$active_session_history打出top sql 在DBA_HIST_SQLSTAT中找DISK_READS_DELTA最大的SQLselect snap_id,buffer_gets_delta,disk_reads_delta reads,executions_delta exec,a.sql_id,b.sql_textfrom dba_hist_sqlstat a,dba_hist_sqltext bwhere a.sql_id=b.sql_id and (a.BUFFER_GETS_DELTA OR a.disk_reads_delta 1000000)order by a.buffer_gets_delta + 100 * a.disk_reads_delta desc; SELECT b.username username,a.sql_id, a.executions exec, a.buffer_gets buffer, a.disk_reads disk,a.child_number child,PLAN_HASH_VALUE,a.parse_calls parse,a.sql_text statementFROM V$SQL a, dba_users bWHERE a.parsing_user_id=b.user_id and ( a.buffer_gets OR a.disk_reads 1000000 )ORDER BY a.buffer_gets + 100 * a.disk_reads DESC;也可以找到PLAN_HASH_VALUE，和v$sql_plan联合起来查找它的执行计划 列出拥有超过400个cursor的sessionIDSELECT sid, count(0) ct FROM v$open_cursor GROUP BY sid HAVING COUNT(0) 400 ORDER BY ct desc; 打到排序多的SQLSELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text FROM v$session a, v$tempseg_usage b, v$sqlarea c WHERE a.saddr = b.session_addr AND c.address= a.sql_address AND c.hash_value = a.sql_hash_value ORDER BY b.tablespace, b.blocks; 也可以按目前的等待事件查找，查看当前会话的等待事件找出在等latch的会话SELECT EVENT, SUM(P3) SLEEPS, SUM(SECONDS_IN_WAIT) SECONDS_IN_WAITFROM V$SESSION_WAITWHERE EVENT LIKE latch%GROUP BY EVENT; select inst_id,event,count(1) from gv$session_wait where event not in (SQL*Net message to client,rdbms ipc message,SQL*Net message from