This discussion is archived
13 Replies Latest reply: Feb 5, 2013 9:37 AM by jgarry RSS

Top 10 SQL

user522961 Newbie
Currently Being Moderated
Hi,
on 11g R2, other than AWR report
are there the queries that return :
10 Top SQL by excution time
10 Top SQL by CPU load
10 Top SQL by number of excutions

On ORACLE_HOME/rdbms/admin ??

Or somewhere else ?

Where can I find them ?



Thanks.
  • 1. Re: Top 10 SQL
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Google it


    SELECT * FROM
    (SELECT
        sql_fulltext,
        sql_id,
        child_number,
        disk_reads,
        executions,
        first_load_time,
        last_load_time
    FROM    v$sql
    ORDER BY elapsed_time DESC)
    WHERE ROWNUM < 10
    /
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:73325450402303


    But the question why you don't want to generate AWR ?
  • 2. Re: Top 10 SQL
    JohnWatson Guru
    Currently Being Moderated
    If you do not have the licence to use the AWR, then this information is best obtained by using statspack.
    --
    John Watson
    http://skillbuilders.com
  • 3. Re: Top 10 SQL
    Niket Kumar Pro
    Currently Being Moderated
    select c.* from
        (select disk_reads,
                buffer_gets,
                rows_processed,
                executions,
                first_load_time,
                sql_text
           from v$sqlarea
           where parsing_user_id !=0
           order by
              buffer_gets/decode(executions,null,1,0,1,executions) desc ) c
    where rownum < 11;
    
    select c.* from
        (select disk_reads,
                buffer_gets,
                rows_processed,
                executions,
                first_load_time,
                sql_text
           from v$sqlarea
           order by
              disk_reads/decode(rows_processed,null,1,0,1,rows_processed) desc ) c
    where rownum <11;
    
    --Top 10 by Buffer Gets:
    
    set linesize 100
    set pagesize 100
    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 > 10000
     ORDER BY buffer_gets DESC)
    WHERE rownum <= 10
    ;
    
    --Top 10 by Physical Reads:
    
    set linesize 100
    set pagesize 100
    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 > 1000
     ORDER BY disk_reads DESC)
    WHERE rownum <= 10
    ;
    
    --Top 10 by Executions:
    
    set linesize 100
    set pagesize 100
    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 > 100
     ORDER BY executions DESC)
    WHERE rownum <= 10
    ;
    
    --Top 10 by Parse Calls:
    
    set linesize 100
    set pagesize 100
    SELECT * FROM
    (SELECT substr(sql_text,1,40) 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:
    
    set linesize 100
    set pagesize 100
    SELECT * FROM
    (SELECT substr(sql_text,1,40) 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:
    
    set linesize 100
    set pagesize 100
    SELECT * FROM
    (SELECT substr(sql_text,1,40) sql,
            version_count, executions, hash_value,address
       FROM V$SQLAREA
      WHERE version_count > 20
     ORDER BY version_count DESC)
    WHERE rownum <= 10
    ;
  • 4. Re: Top 10 SQL
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    John the Op dose not want to generate AWR, statpack will be more difficult
  • 5. Re: Top 10 SQL
    JohnWatson Guru
    Currently Being Moderated
    Osama_mustafa wrote:
    John the Op dose not want to generate AWR, statpack will be more difficult
    More difficult than what? You can't get much simpler than statspack. Of course, you can waste resopurces running queries like those that Niket has posted, but they are useless because all they do is report on what happens to be in the library cache right now.
  • 6. Re: Top 10 SQL
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    I know the John but what i meant is that AWR is easier to generate and OP asking for another method more easier
  • 7. Re: Top 10 SQL
    user522961 Newbie
    Currently Being Moderated
    Really thank you Niket.

    What can be used instead of v$sqlarea ?
    I ask this because if database is restarted the information in v$sqlarea about the days before restart would be disappeared.

    Thank you.
  • 8. Re: Top 10 SQL
    Niket Kumar Pro
    Currently Being Moderated
    the information is always changing in v$ views you can not rely on them for older dates whether your database is running any time ....
    to capture the information of old days you need to take snapshots of database from that you will have to use either statspack or awr report to check top queries.....

    read about AWR or statspack report these are good to find top queries and wait events...
    AWR required licence and statspack you can use without any licence.....

    Edited by: Niket Kumar on Feb 5, 2013 6:13 PM
  • 9. Re: Top 10 SQL
    user522961 Newbie
    Currently Being Moderated
    Thanks to all.
    And about AWR.

    I'm not unfamiliar with that and use it. (Please see my other posts ).
    But now I'm asked to generate PDF reports on these TOP 10 SQLs every month automatically.
    In AWR there are many other information. And also we should provide the SNAPSHOTS number. Can we do it automatically in a non verbus way ?
    Also AWR keep the information only for retention time.
    Otherwise I'm interested to find the queries used by AWR to find the 10 TOP SQLs.

    Best regards.
  • 10. Re: Top 10 SQL
    user522961 Newbie
    Currently Being Moderated
    Thanks to all.
    And about AWR.

    I'm not unfamiliar with that and use it. (Please see my other posts ).
    But now I'm asked to generate PDF reports on these TOP 10 SQLs every month automatically.
    In AWR there are many other information. And also we should provide the SNAPSHOTS number. Can we do it automatically in a non verbus way ?
    Also AWR keep the information only for retention time.
    Otherwise I'm interested to find the queries used by AWR to find the 10 TOP SQLs.

    Best regards.
  • 11. Re: Top 10 SQL
    user522961 Newbie
    Currently Being Moderated
    Thanks to all.
    And about AWR.

    I'm not unfamiliar with that and use it. (Please see my other posts ).
    But now I'm asked to generate PDF reports on these TOP 10 SQLs every month automatically.
    In AWR there are many other information. And also we should provide the SNAPSHOTS number. Can we do it automatically in a non verbus way ?
    Also AWR keep the information only for retention time.
    Otherwise I'm interested to find the queries used by AWR to find the 10 TOP SQLs.

    Best regards.
  • 12. Re: Top 10 SQL
    sb92075 Guru
    Currently Being Moderated
    user522961 wrote:
    Thanks to all.
    And about AWR.

    I'm not unfamiliar with that and use it. (Please see my other posts ).
    But now I'm asked to generate PDF reports on these TOP 10 SQLs every month automatically.
    In AWR there are many other information. And also we should provide the SNAPSHOTS number. Can we do it automatically in a non verbus way ?
    Also AWR keep the information only for retention time.
    Otherwise I'm interested to find the queries used by AWR to find the 10 TOP SQLs.

    Best regards.
    SQL_TRACE=TRUE can capture all SQL used by AWR.
  • 13. Re: Top 10 SQL
    jgarry Guru
    Currently Being Moderated
    Maybe http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1396705600346612273

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points