10 Replies Latest reply: Oct 17, 2010 8:22 PM by 762809 RSS

    TOP SQL

    762809
      Hi Friends,

      I got this top sgl script from Metalink
      Example "Top SQL" queries from V$SQLAREA  
      
      --------------------------------------------------------------------------------
       
      
      PURPOSE
      -------
      
      This article contains a few ready-made queries on V$SQLAREA
      for identifying the top 10 most resource-consuming SQL statements
      with a variety of criteria.
      
      The thresholds used are the same as used by default in Statspack:
      
      Buffer Gets : 10,000
      Physical Reads : 1,000
      Executions : 100
      Parse Calls : 1,000
      Sharable Memory : 1,048576
      Version Count : 20
      
      Note: This article was constructed on legacy versions and although the 
      information may be still be valid, there may be better methods available 
      in supported versions to retrieve this information
      
      SCOPE & APPLICATION
      -------------------
      
      The queries found here may be useful to Database Administrators, 
      Application Developers, Oracle Support Engineers and generally
      anyone involved in an Oracle Database Performance Tuning activity.
      
      EXAMPLE V$SQLAREA QUERIES
      -------------------------
       
      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
      ;
      My question is, If I run this report every hour to check who is or what program is the the current top CPU/Memory consumer. Will the output remains the same even if I run it over and over? Since info is persistent on V$SQLAREA.

      How do I get the "current" top CPU/Memory program consumer, that is running "currently" as in "now".
      My understanding is that, even if the program was run last night and already finished, but if its info is still in the V$SQLAREA and suppose it was the biggest CPU/Mem consumer. It will still reflect as the No.1 top consumer over and over if you query against the V$SQLAREA every hour.

      Please help....


      Thanks
        • 1. Re: TOP SQL
          asifkabirdba
          Use AWR report for 10g and statpack report for 9i database. Default snapshot period is 1 hour.

          You can change the period as you like. Make it 30 minutes or 15 minutes. Also you can create a
          snapshot manually and create the statpack/AWR report between those two snapshot. Using AWR/Statpack
          define your top sql. Use the text version of statpack or AWR. Using AWR/statpack you will get the real picture
          of your top sql between a certain period of time.


          Regards
          Asif Kabir
          • 2. Re: TOP SQL
            Aman....
            I don't think that with the latest releases like version 10g and onwards, these scripts are needed. As suggested, using AWR at a more granular level like 30mnts or so, would get you the top sql's. Also, the EM , either database console or grid console, both come up with a TOP SQL link in the instance's Performance page which tells you the problematic queries in the last 5 minutes. I would look at that rather than running these queries every hour. Second, I think that saying that the output from V$SQLAREA would remain constant would be wrong. It would keep the information as long as shared pool would keep it. Once flushed form there, the info would be flushed from everywhere. And its not possible thatthe last night's top sql' are still shown to you in the output from the memory views. These are dynamic and get changed very quickly.

            Aman....
            • 3. Re: TOP SQL
              Lubiez Jean-Valentin
              Hello,


              In addition with the previous answers, since Oracle *10g* you have a useful tool to get a quick report of the Database activity. It's name is Active Session History or ASH , please find enclosed an ASH Report description:

              http://download.oracle.com/docs/cd/B19306_01/server.102/b28051/tdppt_transient.htm#TDPPT365

              You may get these reports by command line or straightly from EM .

              You'll need, as for ADDM (Automatic Database Diagnostic Monitor) or AWR, a licence for Oracle Diagnostic Pack .

              http://download.oracle.com/docs/cd/B28359_01/license.111/b28287/options.htm#CIHIHDDJ


              Hope this help.
              Best regards,
              Jean-Valentin
              • 4. Re: TOP SQL
                762809
                Yeah I tried statspack once, but I can not understand its output :( huhuhu , there are lots of info that are not needed. after that I did not bother to check it again.
                I just want simple codes for (simple minds) that I can easily understand, after all I just want to capture programs that
                eats lots of resources overtime, say every hour for 24 hrs.
                • 5. Re: TOP SQL
                  sb92075
                  I just want to capture programs that eats lots of resources overtime, say every hour for 24 hrs.
                  For sake of discussion, let us say you have identified the SQL which consume "lots" of resources.

                  Now what do you do next?

                  I have a foolproof way to make sure that NO SQL places any heavy load on the DB.
                  SQL> SHUTDOWN IMMEDIATE

                  If SQL does not generate work load, how does anyone benefit from the DB?

                  Edited by: sb92075 on Oct 17, 2010 5:22 PM
                  • 6. Re: TOP SQL
                    762809
                    Thanks sb92075 you really know your craft ;), your right....but its just a requirement from client, bottom line is we don't get paid if we do not submit reports :(
                    Anyways the client do not read it or understand it, sorry for that

                    If SQL does not generate work load, how does anyone benefit from the DB?
                    Would it not be helpful to see all the programs (good and fine tuned ones) that benefited from the DB , and which among those "good programs" are high consumers? I am still curious to see them who they are. :)



                    Thanks again
                    • 7. Re: TOP SQL
                      sb92075
                      The SQL you posted will generate results.
                      Nobody that I know or know about can look at a (any) SQL statement & conclude whether it performs well or is a resource hog.
                      From my perspective presenting a list of SQL statements to anyone is as effective as showing them Egyptian hieroglyphics.
                      No conclusion can be reached after seeing any such list.
                      • 8. Re: TOP SQL
                        762809
                        Yeah I got your point, I guess I have to get back and deal with that "dreaded" statspack later :(
                        It is not really "user friendly" to me, sorry to say that.
                        • 9. Re: TOP SQL
                          Aman....
                          Its not that tough either. Have a read at some of the posts from Jonathan Lewis about how to deal with Statspack and interpret its resuts.

                          http://jonathanlewis.wordpress.com/?s=statspack

                          HTH
                          Aman....
                          • 10. Re: TOP SQL
                            762809
                            Thanks :)