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

最新推荐文章于 2024-05-26 07:34:53 发布

heqiyu34

最新推荐文章于 2024-05-26 07:34:53 发布

阅读量3.3k

点赞数

分类专栏： Oracle

Oracle 专栏收录该内容

161 篇文章 0 订阅

订阅专栏

链接：http://www.xifenfei.com/699.html

标题：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;