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;