Oracle常见面试题及答案详解4

会计考友

4.    如何定位重要(消耗资源多)的SQL
使用CPU多的用户session
* l. P( x  Y( f9 p  V0 t% XSELECT a.SID, spid, status, SUBSTR (a.program, 1, 40) prog, a.terminal,a.SQL_TEXT, osuser, VALUE / 60 / 100 VALUE
FROM v$session a, v$process b, v$sesstat c
WHERE c.statistic# = 12 AND c.SID = a.SID AND a.paddr = b.addr
ORDER BY VALUE DESC;
) Y0 ^2 u. e4 A( N, s+ f$ p  
( |% J3 F9 E, k" O) @! U- yselect sql_text from v$sql  
where disk_reads > 1000 or (executions > 0 and buffer_gets/executions > 30000);