statspack使用笔记 5

roothomes

statspack使用笔记 5

检查动态性能视图，从中找到资源占用多的SQL语句。

Top 10 by Buffer Gets: 

set linesize 100 

set pagesize 100 

select *  from (select substr(sql_text, 1, 100) sql,

        buffer_gets, 

        executions,  

        buffer_gets / executions "gets/exec", 

        hash_value,  

        address  

        from v$sqlarea 

  where buffer_gets > 10000 

        and executions > 1 

        order by buffer_gets desc) where rownum <= 10;

        Top 10 by Physical Reads: 

select * from (select substr(sql_text, 1, 100) sql,

    disk_reads,

    executions,

    disk_reads / executions "reads/exec",

    hash_value,

    address

        from v$sqlarea

  where disk_reads > 1000

        and executions > 1

        order by disk_reads desc)  where rownum <= 10;

         

Top 10 by Executions: 

select * from (select substr(sql_text, 1, 100) sql,

        executions,

        rows_processed,

        rows_processed / executions "rows/exec",

        hash_value,

        address

        from v$sqlarea

  where executions > 100

        order by executions desc)

        where rownum <= 10;

         

Top 10 by Parse Calls: 

select * from (select substr(sql_text, 1, 100) sql,

    parse_calls,

        executions,

        hash_value,

        address

        from v$sqlarea

  where parse_calls > 1000

        order by parse_calls desc)

        where rownum <= 10;

         

Top 10 by Sharable Memory:

select * from (select substr(sql_text, 1, 100) sql,

        sharable_mem,

        executions,

        hash_value,

        address

  from v$sqlarea

        where sharable_mem > 1048576

        order by sharable_mem desc)

         where rownum <= 10;

         

Top 10 by Version Count:

select * from (select substr(sql_text, 1, 100) sql,

        version_count,

        executions,

        hash_value,

        address

        from v$sqlarea

  where version_count > 20

        order by version_count desc)

        where rownum <= 10;