定位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