很多时候数据库效率低是少数几个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 statement
FROM V$SQLAREA a, dba_users b
WHERE a.parsing_user_id=b.user_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 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 statement
FROM V$SQL a, dba_users b
WHERE a.parsing_user_id=b.user_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
SELECT sid, count(0) ct FROM v$open_cursor GROUP BY sid HAVING COUNT(0) > 400 ORDER BY ct desc;
------打到排序多的SQL
SELECT 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_WAIT
FROM V$SESSION_WAIT
WHERE 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 client','smon timer') group by inst_id,event;
SELECT /*+ ordered */ hash_value,sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN (
SELECT DECODE (sql_hash_value,
0, prev_hash_value,
sql_hash_value
),
DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.sid in (select sid from v$session_wait where event = 'db file%read' ))
ORDER BY hash_value,piece ASC
---根据OS的进程号找出当前正在执行的sql
SELECT /*+ rule */
sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN (
SELECT DECODE (sql_hash_value,
0, prev_hash_value,
sql_hash_value
),
DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.sid in (select sid from v$session c,v$process d where c.paddr=d.addr and d.spid=&spid))
order by piece;
看看系统的时间模型，或者会话的时间模型 v$sess_time_model
select stat_name,value from v$sys_time_model order by value desc;
drill down top sql
查看top sql的执行计划
可以在视图v$sql_plan中查找执行计划，可以使用dbms_xplan包中的3个函数display display_cursor display_awr查找
select * from table(dbms_xplan.display_cursor('2807ymxx3uqs3',null,'ALLSTATS'));
第二个参数是child_number，第三个参数是format，如果用'ALL'也没有运行时统计信息，需要用IOSTATS
这个时候查询时通常是没有运行时统计信息的，可以用 hint 'gather_plan_statistics'
或者在会话级把 'statistics_level' 设置成'ALL'再运行即可，例如：
select /*+ gather_plan_statistics */d.dname from scott.emp e, scott.dept d where e.empno=7782 and e.deptno=d.deptno;
注意评估基数E-Rows和A-Rows如果区别过大应该是统计信息不准，可能造成执行计划的低效。
检查执行环境信息 v$sys_optimizer_env v$ses_optimizer_env v$sql_optimizer_env
select * from v$sys_optimizer_env where isdefault='NO';
可以使用新手的调优利器：DBMS_SQLTUNE包由优化器来帮你调优
select a.channel_id,count(1) bb from sh.sales a,sh.channels b where a.channel_id=b.channel_id group by a.channel_id order by bb;