这里只总结最common的步骤
----------------------------------------
1.通过操作系统的命令找到系统资源的bottleneck，如:CPU, Memory, I/O, Network
同时主要关注IOWait, PI/PO, Memory的使用情况
2.通过查询v$sys_time_model来观察数据库系统整体在基于time的标准上整体的运行状况.
SQL> select * from v$sys_time_model;
STAT_ID STAT_NAME VALUE
---------- ------------------------------ ----------
3649082374 DB time 9772556023
2748282437 DB CPU 6045546
4157170894 background elapsed time 94505855
2451517896 background cpu time 22728949
4127043053 sequence load elapsed time 4797
1431595225 parse time elapsed 16369523
372226525 hard parse elapsed time 15624250
2821698184 sql execute elapsed time 9780485995
1990024365 connection management call ela 333283
psed time
1824284809 failed parse elapsed time 1633
4125607023 failed parse (out of shared me 0
mory) elapsed time
3138706091 hard parse (sharing criteria) 294918
elapsed time
268357648 hard parse (bind mismatch) ela 412
psed time
2643905994 PL/SQL execution elapsed time 2007111
290749718 inbound PL/SQL rpc elapsed tim 0
e
1311180441 PL/SQL compilation elapsed tim 910380
e
751169994 Java execution elapsed time 0
1159091985 repeated bind elapsed time 24014
2411117902 RMAN cpu time (backup/restore) 0
这里我们看到在DB time中大部分的时间花在了sql execute elapsed time指标上，记录下stat_id=2821698184
3.查询v$sess_time_model视图来获得具体的session等待信息.
SQL> select sid,stat_name,value from v$sess_time_model where stat_id=2821698184;
SID STAT_NAME VALUE
---------- ------------------------------ ----------
144 sql execute elapsed time 7122395220
146 sql execute elapsed time 0
148 sql execute elapsed time 0
150 sql execute elapsed time 53570
151 sql execute elapsed time 1387
155 sql execute elapsed time 0
158 sql execute elapsed time 0
160 sql execute elapsed time 0
161 sql execute elapsed time 0
162 sql execute elapsed time 0
163 sql execute elapsed time 0
164 sql execute elapsed time 0
165 sql execute elapsed time 0
166 sql execute elapsed time 0
167 sql execute elapsed time 0
168 sql execute elapsed time 0
169 sql execute elapsed time 0
170 sql execute elapsed time 0
这里我们又可以看到，大部分的时间花在了sid=144这个session中。执行时间=7122395220(微秒)
这么长的执行时间，看看是否有等待发生
4.查询v$session或v$session_wait视图来获得实际的执行事件event信息。
SQL> select sid,event,wait_time,seconds_in_wait,state,wait_class from v$session_
wait where sid=144;
SID EVENT WAI
T_TIME SECONDS_IN_WAIT STATE WAIT_CLASS
---------- ---------------------------------------------------------------- ----
------ --------------- ------------------- -------------------------------------
---------------------------
144 enq: TX - row lock contention
0 7123 WAITING Application
可以得知session 144正在经历着锁等待的事件。
wait_time 表示: A zero value means the session is currently waiting.
seconds_in_wait 表示: If WAIT_TIME = 0, then SECONDS_IN_WAIT is the seconds spent in the current wait condition. If WAIT_TIME > 0, then SECONDS_IN_WAIT is the seconds since the start of the last wait, and SECONDS_IN_WAIT - WAIT_TIME is the active seconds since the last wait ended
当然也可以查询v$session_wait_class来查询具体的wait_class信息：
SQL> select wait_class_id,wait_class,total_waits,time_waited from v$session_wait
_class
2 where sid=144;
WAIT_CLASS_ID WAIT_CLASS T
OTAL_WAITS TIME_WAITED
------------- ---------------------------------------------------------------- -
---------- -----------
1893977003 Other
2 0
4217450380 Application
2670 799742
3386400367 Commit
3 0
2723168908 Idle
37 13313
2000153315 Network
37 0
1740759767 User I/O
68 29
5.找到占用系统资源特别大的Oracle的Session及执行的SQL语句。
利用系统进程的PID
-----------------
select a.username,a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_text from v$session a, v$process b,v$sqltext c
where b.spid=&pid and b.addr=a.paddr and a.sql_address=c.address(+) order by c.piece;
在Oracle10g中，我们可以普遍采用SQL_ID来代替sql_address等信息了
利用Oracle Session的SID
-----------------------
select sid,sql_text from v$session s,v$sql q
where sid = 144 and (q.sql_id=s.sql_id or q.sql_id = s.prev_sql_id);
查询Top 10 SQL
--------------
select * from
(select parsing_user_id,executions,sorts,command_type,disk_reads,sql_text from v$sqlarea
order by disk_reads desc) where rownum < 10;
查询I/O消耗过多的SQL
---------------------
SELECT se.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,se.program,se.MODULE, se.sql_address,st.event,st.p1text,si.physical_reads,si.block_changes
FROM v$session se, v$session_wait st,v$sess_io si,v$process pr
WHERE st.sid=se.sid AND st.sid=si.sid
AND se.PADDR=pr.ADDR
AND se.sid>6
AND st.wait_time=0
ORDER BY physical_reads DESC
查询出SID后，获得实际的SQL Text
select sql_address from v$session where sid=&sid;
select * from v$sqltext where address=&address;
6.针对SQL做相应的优化
