This discussion is archived
3 Replies Latest reply: Jan 28, 2013 4:28 AM by JohnWatson RSS

How to find the main sql

755443 Newbie
Currently Being Moderated
Hi all,

I'm working on a 11.2 Oracle Enterprise database.

I need a query to find the top 10 queries of my database. Please could you help me?

Best regards
dbajug
  • 1. Re: How to find the main sql
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Generate AWR

    Check
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:73325450402303
  • 2. Re: How to find the main sql
    moreajays Pro
    Currently Being Moderated
    Hi,

    AWR will give you all historic sql details
    @?/rdbms/admin/awrsqrpt.sql

    To know current top CPU queries you can use below sql
     col sql_text for a80
    set pages 200
    set line 900
    col PROGRAM for a20
    col MACHINE for a20
    col CPU 9999999999
    alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
    col event for a40
    select *
      from (select /*+ rule */
             sa.sql_id,
             sa.CPU_TIME "CPU",
             s.sid "SID",
             s.serial# "SERIAL",
             s.program "PROGRAM",
             s.machine "MACHINE",
             sa.SQL_TEXT "SQL_TEXT",
             vp.spid,
             sw.event,
             s.logon_time,
             s.last_call_et / 60,
             s.username,
             s.status
              from v$sqlarea sa, v$session s, v$process vp, v$session_wait sw
             where sa.address = s.sql_address
               and sw.sid = s.sid
               and s.paddr = vp.addr
             order by CPU_TIME desc)
     where rownum < 10;
    Thanks,
    Ajay More
    http://www.moreajays.com
  • 3. Re: How to find the main sql
    JohnWatson Guru
    Currently Being Moderated
    moreajays wrote:
    Hi,

    AWR will give you all historic sql details
    @?/rdbms/admin/awrsqrpt.sql

    To know current top CPU queries you can use below sql
    col sql_text for a80
    set pages 200
    set line 900
    col PROGRAM for a20
    col MACHINE for a20
    col CPU 9999999999
    alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
    col event for a40
    select *
    from (select /*+ rule */
    sa.sql_id,
    sa.CPU_TIME "CPU",
    s.sid "SID",
    s.serial# "SERIAL",
    s.program "PROGRAM",
    s.machine "MACHINE",
    sa.SQL_TEXT "SQL_TEXT",
    vp.spid,
    sw.event,
    s.logon_time,
    s.last_call_et / 60,
    s.username,
    s.status
    from v$sqlarea sa, v$session s, v$process vp, v$session_wait sw
    where sa.address = s.sql_address
    and sw.sid = s.sid
    and s.paddr = vp.addr
    order by CPU_TIME desc)
    where rownum < 10;
    Thanks,
    Ajay More
    http://www.moreajays.com
    Hey? This query is not going to help.
    Your join condition sa.address = s.sql_address will exclude all SQLs that are not the most recently executed by a session.
    And you are listing the same SQL several times if it is executed by multiple sessions, but taking no account of the number of times it was executed.
    You need to run something much simpler. Perhaps
    select sql_text,executions,disk_reads,buffer_gets,cpu_time,elapsed time from v$sql order by....;
    run it several times, sorting it on each of the projected columns.

Legend

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