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

1> SQL ordered by Gets

1 select * from 
2           (select substr(sql_text,1,40) sql,   buffer_gets,
3 executions, buffer_gets/executions "Gets/Exec",   
4           hash_value,address
5           from v$sqlarea        
6           where  buffer_gets > 0 and executions>0
7           order by buffer_gets desc) 
8           where rownum <= 10 ;

 

2> SQL ordered by Reads

1 select * from     
2               (select substr(sql_text,1,40) sql,      disk_reads,
3 executions, disk_reads/executions "Reads/Exec",     
4               hash_value,address   
5               from v$sqlarea    where disk_reads > 0 and executions >0     
6               order by disk_reads desc) where rownum <= 10;

 

3> SQL ordered by Executions

1 select * from      
2               (select substr(sql_text,1,40) sql,      executions,
3 rows_processed, rows_processed/executions "Rows/Exec",   
4               hash_value,address   
5               from v$sqlarea    where executions > 0
6               order by executions desc) where rownum <= 10 ;

 

4> SQL ordered by Parse Calls

1 select * from      
2               (select substr(sql_text,1,40) sql,      parse_calls,
3 executions, hash_value,address     
4               from v$sqlarea    where parse_calls > 0
5               order by parse_calls desc) where rownum <= 10 ;

 

5> Running Time top 10 sql

1 select * from
2               (select t.sql_fulltext, 
3               (t.last_active_time-to_date(t.first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,
4               disk_reads,buffer_gets,rows_processed,     
5               t.last_active_time,t.last_load_time,t.first_load_time
6               from v$sqlarea t order by t.first_load_time desc)     
7               where rownum < 10;