oracle性能分析语句
1.cpu负载高的语句
select b.sql_text, a.buffer_gets, a.executions, a.buffer_gets/decode(a.executions , 0 , 1 , a.executions), c.username from V$sqlarea a, v$sqltext_with_newlines b, dba_users c where a.parsing_user_id = c.user_id and a.address = b.address order by a.buffer_gets desc , b.piece ;
select distinct ss from (select a.sql_text ss, a.buffer_gets, a.executions, a.buffer_gets/decode(a.executions , 0 , 1 , a.executions), c.username from V$sqlarea a, v$sqltext_with_newlines b, dba_users c where a.parsing_user_id = c.user_id and a.address = b.address and to_char(a.LAST_LOAD_TIME,'yyyy-MM-dd hh:mi:ss') > '2011-01-25 12:00:00'order by a.disk_reads desc , b.piece )
select distinct ss from (select a.sql_text ssfrom V$sqlarea a, v$sqltext_with_newlines b, dba_users c where a.parsing_user_id = c.user_id and a.address = b.address order by a.buffer_gets desc , b.piece );
select b.sql_text, a.disk_reads, a.executions, a.disk_reads/decode(a.executions , 0 , 1 , a.executions), c.username from v$sqlarea a, v$sqltext_with_newlines b, dba_users c where a.parsing_user_id = c.user_id and a.address = b.address order by a.disk_reads desc , b.piece ;
