3 Replies Latest reply: Jan 28, 2013 6:28 AM by JohnWatson RSS

    How to find the main sql

    755443
      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
          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
            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
              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.