oracle 消耗资源的语句,ORACLE优化查询资源消耗的语句

1> SQL ordered by Gets

select * from

(select substr(sql_text,1,40) sql,   buffer_gets,

executions, buffer_gets/executions "Gets/Exec",

hash_value,address

from v$sqlarea

where  buffer_gets > 0 and executions>0

order by buffer_gets desc)

where rownum <= 10 ;

2> SQL ordered by Reads

select * from

(select substr(sql_text,1,40) sql,      disk_reads,

executions, disk_reads/executions "Reads/Exec",

hash_value,address

from v$sqlarea    where disk_reads > 0 and executions >0

order by disk_reads desc) where rownum <= 10;

3> SQL ordered by Executions

select * from

(select substr(sql_text,1,40) sql,      executions,

rows_processed, rows_processed/executions "Rows/Exec",

hash_value,address

from v$sqlarea    where executions > 0

order by executions desc) where rownum <= 10 ;

4> SQL ordered by Parse Calls

select * from

(select substr(sql_text,1,40) sql,      parse_calls,

executions, hash_value,address

from v$sqlarea    where parse_calls > 0

order by parse_calls desc) where rownum <= 10 ;

5> Running Time top 10 sql

select * from

(select t.sql_fulltext,

(t.last_active_time-to_date(t.first_load_time,‘yyyy-mm-dd hh24:mi:ss‘))*24*60,

disk_reads,buffer_gets,rows_processed,

t.last_active_time,t.last_load_time,t.first_load_time

from v$sqlarea t order by t.first_load_time desc)

where rownum < 10;