13 Replies Latest reply: Feb 5, 2013 11:37 AM by jgarry RSS

    Top 10 SQL

    user522961
      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
          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
            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
              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
                John the Op dose not want to generate AWR, statpack will be more difficult
                • 5. Re: Top 10 SQL
                  JohnWatson
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  Maybe http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1396705600346612273