oracle 当前top sql,定位Oracle中的top sql

定位Oracle中的top sql

很多时候数据库效率低是少数几个top sql造成的，从top sql入手进行数据库调优是常用的手段。

10G中用sql_id代替address和hash_value

首先要找出top sql，可以从以下几个角度入手：

占用资源最多的sql：

按照读的次数找

SELECT http://www.doczj.com/doc/f7276ae981c758f5f61f67f0.htmlername username,a.sql_id, a.executions exec, a.buffer_gets buffer, a.disk_reads disk,

a.parse_calls parse,a.sql_text statement

FROM V$SQLAREA a, dba_users b

WHERE a.parsing_user_id=http://www.doczj.com/doc/f7276ae981c758f5f61f67f0.htmler_id and ( a.buffer_gets > 10000000 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最大的SQL

select snap_id,buffer_gets_delta,disk_reads_delta reads,executions_delta exec,a.sql_id,b.sql_text

from dba_hist_sqlstat a,dba_hist_sqltext b

where a.sql_id=b.sql_id and (a.BUFFER_GETS_DELTA > 10000000 OR a.disk_reads_delta > 1000000) order by a.buffer_gets_delta + 100 * a.disk_reads_delta desc;

SELECT http://www.doczj.com/doc/f7276ae981c758f5f61f67f0.htmlername 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 statement

FROM V$SQL a, dba_users b

WHERE a.parsing_user_id=http://www.doczj.com/doc/f7276ae981c758f5f61f67f0.htmler_id and ( a.buffer_gets > 10000000 OR a.disk_reads > 1000000 ) ORDER BY a.buffer_gets + 100 * a.disk_reads DESC;

也可以找到PLAN_HASH_VALUE，和v$sql_plan联合起来查找它的执行计划

----列出拥有超过400个cursor的sessionID