how to determine top SQL query??
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: how to determine top SQL query??

  1. #1
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456

    how to determine top SQL query??

    hi guys,

    I must apologize...i hv been trying to use the search function but w/o success as it prompted me it need at least 4 chars word for search...I hv been trying to search this forum on topic on how i can determine wat's the top SQL queries...anyone with this info on how to go abt doing that??

    thanks in advance.

    rgds,

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Read about statspack.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Jul 2001
    Location
    Singapore(Asia)-WebCentre business company
    Posts
    456
    is there a way going abt checking top query w/o install statpack ?? like in some dictionary or somethg??

    thks.

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    STATSPACK is best. Try these two queries:

    select b.username, a.buffer_gets reads,
    a.executions exec, a.buffer_gets / decode
    (a.executions, 0, 1, a.executions) rds_exec_ratio,
    a.command_type, a.sql_text Statement
    from v$sqlarea a, dba_users b
    where a.parsing_user_id = b.user_id
    and a.buffer_gets > 100000
    order by a.buffer_gets desc;

    select b.username, a.disk_reads reads,
    a.executions exec, a.disk_reads / decode
    (a.executions, 0, 1, a.executions) rds_exec_ratio,
    a.command_type, a.sql_text Statement
    from v$sqlarea a, dba_users b
    where a.parsing_user_id = b.user_id
    and a.disk_reads > 100000
    order by a.disk_reads desc;
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Code:
    select diskexec, bufexec, executions, sql_text
      from
            (select dense_rank() over (order by decode(executions, 0, 0, buffer_gets/executions) desc) rank, 
                    decode(executions, 0, 0, buffer_gets/executions) bufexec, 
                    sql_text, 
                    executions, 
                    decode(executions, 0, 0, disk_reads/executions) diskexec,
                    hash_value
              from v$sql
             where executions > 10 )
     where rank <= 20;

  6. #6
    Join Date
    Jan 2001
    Posts
    3,131
    The problem is that TOP (offending) SQL can be defined in different ways. Physical reads, sorts, CPU consumption. OEM by default goes after disk reads per execution, but your problem may be CPU.

    Another thing I have noticed is that with reporting systems, "number of executions" is not always accurate since often a value is plugged in for one or two parameters, while the main SQL remains the same. I ran into that problem yesterday. My point is that usually you may disregard a TOP SQL if it appears to have been run only once, often there is more lying under the covers.

    MH
    I remember when this place was cool.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width