oracle 中如何定位重要(消耗资源多)的SQL

1、查看值得怀疑的SQL

select substr(to_char(s.pct, '99.00' ),2)|| '%' load ,         s.executions executes,         p.sql_text from ( select address,                 disk_reads,                 executions,                 pct,                 rank()over( order by disk_reads desc ) ranking           from ( select address,                         disk_reads,                         executions,                        100*ratio_to_report(disk_reads)over() pct                   from sys.v_$sql                  where command_type!=47)          where disk_reads>50*executions) s,         sys.v_$sqltext p where s.ranking<=5    and p.address=s.address order by 1, s.address, p.piece;

2、查看消耗内存多的sql

select b.username,         a. buffer_gets,         a.executions,         a.disk_reads / decode(a.executions, 0, 1, a.executions),         a.sql_text SQL    from v$sqlarea a, dba_users b   where a.parsing_user_id = b.user_id     and a.disk_reads > 10000   order by disk_reads desc ;

3、查看逻辑读多的SQL

select * from ( select buffer_gets, sql_text           from v$sqlarea          where buffer_gets>500000          order by buffer_gets desc ) where rownum<=30;

4、查看执行次数多的SQL

select sql_text, executions    from ( select sql_text, executions from v$sqlarea order by executions desc )   where rownum < 81;

5、查看读硬盘多的SQL

select sql_text, disk_reads from ( select sql_text, disk_reads from v$sqlarea order by disk_reads desc ) where rownum<21;

6、查看排序多的SQL

select sql_text, sorts from ( select sql_text, sorts from v$sqlarea order by sorts desc ) where rownum<21;

7、分析的次数太多，执行的次数太少，要用绑变量的方法来写sql

select substr(sql_text, 1, 80) "sql" , count (*), sum (executions) "totexecs"

   from v$sqlarea

  where executions < 5

  group by substr(sql_text, 1, 80)

having count (*) > 30

  order by 2;

转载:http://www.xifenfei.com/699.html