This discussion is archived
10 Replies Latest reply: Oct 17, 2010 6:22 PM by 762809 RSS

TOP SQL

762809 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks :)

Legend

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