ITPub博客

首页 > 数据库 > Oracle > ORACLE优化查询资源消耗的语句

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

原创 Oracle 作者:DBA_每日记 时间:2019-11-08 14:58:04 0 删除 编辑

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;



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/69950231/viewspace-2663235/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2019-10-09

  • 博文量
    73
  • 访问量
    29678