AWR – SQL Statistics

Oracle: 12.1.0.1

Probably most of you have access to AWR report or have your own scripts to find TOP SQL statements in your database.

But if you don’t and you don’t have access to dbms_workload_repository and you don’t have permission to run awrrpt.sql script and you don’t like Enterprise Manager (yuck)  or you just want to add some information to existing AWR report (e.g. plan_hash_value ), you should read this article 🙂

Below I posted my queries and the once used by Oracle to generate each section in AWR report – SQL Statistics.

If you understand these queries, you’ll be able to develop your own and have the data the way you like it 🙂

So enjoy … 🙂

My version

AWR – SQL Statistics

My version

set linesize 300
set verify off
column inst     format 9999
column type     format A10
column sql_text format A50

---------------PARAM----------------
define beg_date = '2017-07-17 08:00'  --begin date
define end_date = '2017-07-17 17:00'  --end date
define numrows  = 10                  --number of displayed rows
define inst_num  = 1                  --instance number
------------------------------------

 
WITH dane AS
(SELECT a.dbid,
        a.instance_number inst,
        st.sql_id,
        st.plan_hash_value,
        sum(st.executions_delta) exec,
        round(sum(st.elapsed_time_delta/1000000),2) time,
        round(sum(st.cpu_time_delta/1000000),2) cpu,
        round(sum(st.iowait_delta/1000000),2) io
   FROM dba_hist_snapshot a,
        dba_hist_sqlstat st
  WHERE a.instance_number = st.instance_number
    AND a.snap_id         = st.snap_id
    AND a.dbid            = st.dbid
---------------PARAM----------------
    AND a.begin_interval_time BETWEEN to_date('&beg_date','YYYY-MM-DD HH24:MI:SS') 
                                  AND to_date('&end_date','YYYY-MM-DD HH24:MI:SS')
    AND a.instance_number = &inst_num
------------------------------------
GROUP BY a.dbid,
         a.instance_number,
         st.sql_id,
         st.plan_hash_value
)
SELECT dn.inst,
       round(ratio_to_report(dn.time) over(),4) "%TM",
       dn.time "TIME(s)",
       CASE WHEN dn.exec = 0 THEN
              0
            ELSE
              round(dn.time/dn.exec,2)
       END tm_per_exec,
       dn.exec,
       dn.io,
       round(ratio_to_report(dn.io) over(),4) "%IO",
       dn.cpu,
       round(ratio_to_report(dn.cpu) over(),4) "%CPU",
       dn.sql_id,
       dn.plan_hash_value,
       cn.command_name type,
       stxt.sql_text
 FROM dane dn,
      dba_hist_sqltext stxt,
      dba_hist_sqlcommand_name cn
WHERE dn.dbid           = stxt.dbid(+)
  AND dn.sql_id         = stxt.sql_id(+)
  AND stxt.dbid         = cn.dbid(+)
  AND stxt.command_type = cn.command_type(+)
ORDER BY time desc
FETCH FIRST &numrows ROWS ONLY;
 
 INST	     %TM    TIME(s) TM_PER_EXEC       EXEC   IO        %IO	  CPU	    %CPU SQL_ID        PLAN_HASH_VALUE TYPE	  SQL_TEXT
----- ---------- ---------- ----------- ---------- ---- ---------- ---------- ---------- ------------- --------------- ---------- ----------------------------------------
    1	   .1114      81.12	    .18        457   81      .1281	   .5	   .0096 cvn54b7yz0s8u	    3246118364 SELECT	  select /*+ index(idl_ub1$ i_idl_ub11) +*
																  / piece#,length,piece from idl_ub1$ wher

    1	   .0655      47.72	  47.72 	 1   44        .07	  .26	    .005 b9p45hkcx0pwh	    1388734953 SELECT	  select dbms_qopatch.get_opatch_lsinvento
																  ry() from dual

    1	   .0458      33.34	  33.34 	 1   32      .0506	  .13	   .0025 20x4skzx6dbjm	    1779570577 INSERT	  INSERT INTO OPATCH_XINV_TAB(XML_INVENTOR
																  Y) SELECT * FROM OPATCH_XML_INV

    1	   .0434      31.58	     .7 	45   13      .0208	  .17	   .0033 0qbzfjt00pbsx		     0 PL/SQL EXE BEGIN   dbms_isched.log_dbms_output( :lo
														       CUTE	  gid, :errs, :outputWasEnabled ); END;

Back to Top

AWR parameters

In each section you can find this code:

---your param---
define dbid        = 1391944761    --- DBID
define bid         = 112           --- BEGIN SNAP_ID 
define eid         = 115           --- END SNAP_ID 
define inst_num    = 1             --- INSTANCE_NUMBER 
----------------

Find your value using this script. Remember that begin snap_id and end snap_id must belong to the same STARTUP_TIME value.

column startup_time        format a30
column begin_interval_time format a30
column end_interval_time   format a30

set linesize 300

  SELECT sn.dbid,
         sn.instance_number,
         sn.snap_id,
         sn.startup_time,
         sn.begin_interval_time,
         sn.end_interval_time
    FROM dba_hist_snapshot sn
ORDER BY dbid,
         instance_number,
         snap_id;
      DBID INSTANCE_NUMBER    SNAP_ID STARTUP_TIME                   BEGIN_INTERVAL_TIME            END_INTERVAL_TIME
---------- --------------- ---------- ------------------------------ ------------------------------ ------------------------------
1391944761               1        112 17-JUL-17 01.39.10.000 PM      17-JUL-17 01.39.10.000 PM      17-JUL-17 01.49.45.425 PM
1391944761               1        113 17-JUL-17 01.39.10.000 PM      17-JUL-17 01.49.45.425 PM      17-JUL-17 03.00.50.997 PM
1391944761               1        114 17-JUL-17 01.39.10.000 PM      17-JUL-17 03.00.50.997 PM      17-JUL-17 04.00.56.267 PM
1391944761               1        115 17-JUL-17 01.39.10.000 PM      17-JUL-17 04.00.56.267 PM      17-JUL-17 05.00.01.558 PM
1391944761               1        116 17-JUL-17 08.16.02.000 PM      17-JUL-17 08.16.02.000 PM      17-JUL-17 08.26.58.693 PM

Back to Top

SQL ordered by Elapsed Time

SET linesize 300
SET pagesize 40
set verify off
COLUMN "SQL Module" format A10
column "SQL Text" format A50
COLUMN "PDB_NAME" format A2
---your param---
define dbid        = 1391944761
define bid         = 112
define eid         = 115
define inst_num    = 1
----------------
define tsql_max    = 65
define tsql_min    = 10
define top_pct_sql = 1

var dbtm number

begin
WITH dbtime AS (SELECT SUM(a.value) -lag(SUM(a.value)) over (order by snap_id)  dbtime  
                 FROM dba_hist_service_stat a
                 WHERE snap_id IN (&eid,&bid)
                   AND a.stat_name = 'DB time'
              GROUP BY a.snap_id)
SELECT dbtime
  into :dbtm
  FROM dbtime
 WHERE dbtime IS NOT NULL;
end;
/
define dbtime = :dbtm

with sqt as
      (select elap, cput, exec, iowt, norm_val, sql_id, module, rnum, con_dbid
         from (select sql_id, module, elap, norm_val, cput, exec, iowt, con_dbid,
                      rownum rnum
                 from (select sql_id
                            , max(module) module
                            , sum(elapsed_time_delta) elap
                            , (100 * (sum(elapsed_time_delta) /
                                      nullif(&dbtime,0))) norm_val
                            , sum(cpu_time_delta) cput
                            , sum(executions_delta) exec
                            , sum(iowait_delta)     iowt
                            , con_dbid
                        from dba_hist_sqlstat
                       where dbid = &dbid
                         and instance_number = &inst_num
                         and &bid < snap_id
                         and snap_id <= &eid
                       group by sql_id, con_dbid
                       order by nvl(sum(elapsed_time_delta), -1) desc,
                             sql_id, con_dbid))
          where rnum < &tsql_max
            and (rnum <= &tsql_min or norm_val > &top_pct_sql))
    select /*+ NO_MERGE(sqt) */
          nvl((sqt.elap/1000000), to_number(null)) "Elapsed Time(s)",
          sqt.exec "Executions",
          decode(sqt.exec, 0, to_number(null),(sqt.elap / sqt.exec / 1000000)) "Elapsed Time per Exec (s)",
          sqt.norm_val "%Total",
          decode(sqt.elap, 0, to_number(null), (100 * (sqt.cput / sqt.elap))) "%CPU",
          decode(sqt.elap, 0, to_number(null), (100 * (sqt.iowt / sqt.elap))) "%IO",
          sqt.sql_id  "SQL Id",
          sqt.module  "SQL Module",
          dbpdb.pdb_name,
          st.sql_text "SQL Text"
     from sqt, dba_hist_sqltext st,  (select p.con_dbid, p.pdb_name
       from dba_hist_pdb_instance p,
            (select pi.con_dbid, pi.instance_number, pi.startup_time,
                    pi.open_time,
                    rank() over (partition by pi.con_dbid
                      order by pi.open_time desc, pi.instance_number asc) ord
               from  dba_hist_pdb_instance pi, dba_hist_snapshot s
              where pi.startup_time = s.startup_time
                and pi.open_time < s.end_interval_time and pi.open_time >= s.startup_time
                and s.dbid  = &dbid
                and s.instance_number = &inst_num
                and pi.dbid = s.dbid
                and pi.instance_number = s.instance_number
                and s.snap_id = &eid) mia
      where p.dbid = &dbid
        and p.instance_number = mia.instance_number
        and p.startup_time = mia.startup_time
        and p.open_time = mia.open_time
        and mia.ord = 1)  dbpdb
    where st.sql_id(+) = sqt.sql_id and st.dbid(+) = &dbid
          and sqt.con_dbid = dbpdb.con_dbid(+)
          and sqt.con_dbid = st.con_dbid(+)
    order by sqt.rnum;

Back to Top

SQL ordered by CPU Time

SET linesize 300
SET pagesize 40
set verify off
COLUMN "SQL Module" format A10
column "SQL Text" format A50
COLUMN "PDB_NAME" format A2

---your param---
define dbid        = 1391944761
define bid         = 112
define eid         = 115
define inst_num    = 1
----------------

define tsql_max    = 65
define tsql_min    = 10
define top_pct_sql = 1

var cpu number

begin
SELECT b.value - a.value
  INTO :cpu
  FROM dba_hist_sys_time_model a,
       dba_hist_sys_time_model b
 WHERE a.dbid = &dbid 
   AND b.dbid = &dbid
   AND a.instance_number = &inst_num
   AND b.instance_number = &inst_num
   AND a.snap_id = &bid
   AND b.snap_id = &eid
   AND a.stat_id = b.stat_id
   AND a.stat_name = 'DB CPU'
   AND b.value - a.value > 0;
end;
/
define tcpu = :cpu

with sqt as
      (select elap, cput, exec, uiot, norm_val, sql_id, module, rnum, con_dbid
         from (select sql_id, module, elap, norm_val, cput, exec, uiot, con_dbid,
                      rownum rnum
                 from (select sql_id
                            , max(module) module
                            , sum(elapsed_time_delta) elap
                            , (100 * (sum(cpu_time_delta) /
                                      nullif(&tcpu,0))) norm_val
                            , sum(cpu_time_delta) cput
                            , sum(executions_delta) exec
                            , sum(iowait_delta)     uiot
                            , con_dbid
                        from dba_hist_sqlstat
                       where dbid = &dbid
                         and instance_number = &inst_num
                         and &bid < snap_id
                         and snap_id <= &eid
                       group by (sql_id, con_dbid)
                       order by nvl(sum(cpu_time_delta), -1) desc,
                                sql_id, con_dbid))
          where rnum < &tsql_max
            and (rnum <= &tsql_min or norm_val > &top_pct_sql))
    select /*+ NO_MERGE(sqt) */
          nvl((sqt.cput/1000000), to_number(null)) "CPU Time(s)",
          sqt.exec "Executions",
          decode(sqt.exec, 0, to_number(null), (sqt.cput / sqt.exec /1000000)) "CPU per Exec (s)",
          sqt.norm_val "%Total",
          nvl((sqt.elap/1000000), to_number(null)) "Elapsed Time(s)",
          decode(sqt.elap, 0, to_number(null), (100 * (sqt.cput / sqt.elap))) "%CPU",
          decode(sqt.elap, 0, to_number(null), (100 * (sqt.uiot / sqt.elap))) "%IO",
          sqt.sql_id  "SQL Id",
          sqt.module  "SQL Module",
          dbpdb.pdb_name,
          st.sql_text "SQL Text"
     from sqt, dba_hist_sqltext st,  (select p.con_dbid, p.pdb_name
       from dba_hist_pdb_instance p,
            (select pi.con_dbid, pi.instance_number, pi.startup_time,
                    pi.open_time,
                    rank() over (partition by pi.con_dbid
                      order by pi.open_time desc, pi.instance_number asc) ord
               from  dba_hist_pdb_instance pi, dba_hist_snapshot s
              where pi.startup_time = s.startup_time
                and pi.open_time < s.end_interval_time and pi.open_time >= s.startup_time
                and s.dbid  = &dbid
                and s.instance_number = &inst_num
                and pi.dbid = s.dbid
                and pi.instance_number = s.instance_number
                and s.snap_id = &eid) mia
      where p.dbid = &dbid
        and p.instance_number = mia.instance_number
        and p.startup_time = mia.startup_time
        and p.open_time = mia.open_time
        and mia.ord = 1)  dbpdb
    where st.sql_id(+) = sqt.sql_id and st.dbid(+) = &dbid
          and sqt.con_dbid = dbpdb.con_dbid(+)
          and sqt.con_dbid = st.con_dbid(+)
    order by sqt.rnum;

Back to Top

SQL ordered by User I/O Wait Time

SET linesize 210
SET pagesize 40
SET VERIFY OFF
COLUMN "SQL Module" format A10
COLUMN "SQL Text" format A50
COLUMN "PDB_NAME" format A2

---your param---
define dbid        = 1391944761
define bid         = 112
define eid         = 115
define inst_num    = 1
----------------

define tsql_max    = 65
define tsql_min    = 10
define top_pct_sql = 1

var iowt number

begin
SELECT SUM(e.time_waited_micro - nvl(b.time_waited_micro,0) )
  INTO :iowt
  FROM dba_hist_system_event b,
       dba_hist_system_event e
 WHERE b.snap_id (+) = &bid
   AND e.snap_id = &eid
   AND b.dbid (+) = e.dbid
   AND e.dbid = &dbid 
   AND b.instance_number (+) = e.instance_number
   AND e.instance_number = &inst_num
   AND b.event_id (+) = e.event_id
   AND e.wait_class = 'User I/O';
end;
/
define uiowt = :iowt

with sqt as
      (select elap, cput, exec, uiot, norm_val, sql_id, module, rnum, con_dbid
         from (select sql_id, module, elap, norm_val, cput, exec, uiot, con_dbid,
                      rownum rnum
                 from (select sql_id
                            , max(module) module
                            , sum(elapsed_time_delta) elap
                            , (100 * (sum(iowait_delta) /
                                      nullif(&uiowt,0))) norm_val
                            , sum(cpu_time_delta) cput
                            , sum(executions_delta) exec
                            , sum(iowait_delta)     uiot
                            , con_dbid
                        from dba_hist_sqlstat
                       where dbid = &dbid
                         and instance_number = &inst_num
                         and &bid < snap_id
                         and snap_id <= &eid
                       group by sql_id, con_dbid
                       order by nvl(sum(iowait_delta), -1) desc,
                                sql_id, con_dbid))
          where rnum < &tsql_max
            and (rnum <= &tsql_min or norm_val > &top_pct_sql))
    select /*+ NO_MERGE(sqt) */
          nvl((sqt.uiot/1000000), to_number(null)) "User I/O Time (s)",
          sqt.exec "Executions",
          decode(sqt.exec, 0, to_number(null), (sqt.uiot / sqt.exec /1000000)) "UIO per Exec (s)",
          sqt.norm_val "%Total",
          nvl((sqt.elap/1000000), to_number(null)) "Elapsed Time(s)",
          decode(sqt.elap, 0, to_number(null), (100 * (sqt.cput / sqt.elap))) "%CPU",
          decode(sqt.elap, 0, to_number(null), (100 * (sqt.uiot / sqt.elap))) "%IO",
          sqt.sql_id  "SQL Id",
          sqt.module  "SQL Module",
          dbpdb.pdb_name,
          st.sql_text "SQL Text"
     from sqt, dba_hist_sqltext st,  (select p.con_dbid, p.pdb_name
       from dba_hist_pdb_instance p,
            (select pi.con_dbid, pi.instance_number, pi.startup_time,
                    pi.open_time,
                    rank() over (partition by pi.con_dbid
                      order by pi.open_time desc, pi.instance_number asc) ord
               from  dba_hist_pdb_instance pi, dba_hist_snapshot s
              where pi.startup_time = s.startup_time
                and pi.open_time < s.end_interval_time and pi.open_time >= s.startup_time
                and s.dbid  = &dbid
                and s.instance_number = &inst_num
                and pi.dbid = s.dbid
                and pi.instance_number = s.instance_number
                and s.snap_id = &eid) mia
      where p.dbid = &dbid
        and p.instance_number = mia.instance_number
        and p.startup_time = mia.startup_time
        and p.open_time = mia.open_time
        and mia.ord = 1)  dbpdb
    where st.sql_id(+) = sqt.sql_id and st.dbid(+) = &dbid
          and sqt.con_dbid = dbpdb.con_dbid(+)
          and sqt.con_dbid = st.con_dbid(+)
    order by sqt.rnum;

Back to Top

SQL ordered by Gets

SET linesize 210
SET pagesize 40
SET VERIFY OFF
COLUMN "SQL Module" format A10
COLUMN "SQL Text" format A50
COLUMN "PDB_NAME" format A2

---your param---
define dbid        = 1391944761
define bid         = 112
define eid         = 115
define inst_num    = 1
----------------

define tsql_max    = 65
define tsql_min    = 10
define top_pct_sql = 1

var lr number

begin
SELECT e.value - b.value
  INTO :lr
  FROM dba_hist_sysstat b,
       dba_hist_sysstat e
 WHERE b.snap_id =&bid
    AND e.snap_id =&eid
    AND b.dbid =&dbid
    AND e.dbid =&dbid
    AND b.instance_number =&inst_num
    AND e.instance_number =&inst_num
    AND b.stat_id = e.stat_id
    AND e.stat_name = 'session logical reads'
    AND e.value >= b.value
    AND e.value > 0;

end;
/
define slr = :lr

with sqt as
      (select elap, cput, exec, uiot, bget, norm_val, sql_id, module, rnum, con_dbid
         from (select sql_id, module, elap, norm_val, cput, exec, uiot, con_dbid,
                      bget, rownum rnum
                 from (select sql_id
                            , max(module) module
                            , sum(elapsed_time_delta) elap
                            , (100 * (sum(buffer_gets_delta) /
                                      nullif(&slr,0))) norm_val
                            , sum(cpu_time_delta) cput
                            , sum(executions_delta) exec
                            , sum(iowait_delta)     uiot
                            , sum(buffer_gets_delta) bget
                            , con_dbid
                        from dba_hist_sqlstat
                       where dbid = &dbid
                         and instance_number = &inst_num
                         and &bid < snap_id
                         and snap_id <= &eid
                       group by sql_id, con_dbid
                       order by nvl(sum(buffer_gets_delta), -1) desc,
                                sql_id, con_dbid))
          where rnum < &tsql_max
            and (rnum <= &tsql_min or norm_val > &top_pct_sql))
    select /*+ NO_MERGE(sqt) */
          sqt.bget "Buffer Gets",
          sqt.exec "Executions",
          decode(sqt.exec, 0, to_number(null), (sqt.bget / sqt.exec)) "UIO per Exec (s)",
          sqt.norm_val "%Total",
          nvl((sqt.elap/1000000), to_number(null)) "Elapsed Time(s)",
          decode(sqt.elap, 0, '     ',
             lpad(to_char(round((100 * (sqt.cput / sqt.elap)),1), 'TM9'),5)) "%CPU",
          decode(sqt.elap, 0, '     ',
             lpad(to_char(round((100 * (sqt.uiot / sqt.elap)),1), 'TM9'),5)) "%IO",
          sqt.sql_id  "SQL Id",
          sqt.module  "SQL Module",
          dbpdb.pdb_name,
          st.sql_text "SQL Text"
     from sqt, dba_hist_sqltext st,  (select p.con_dbid, p.pdb_name
       from dba_hist_pdb_instance p,
            (select pi.con_dbid, pi.instance_number, pi.startup_time,
                    pi.open_time,
                    rank() over (partition by pi.con_dbid
                      order by pi.open_time desc, pi.instance_number asc) ord
               from  dba_hist_pdb_instance pi, dba_hist_snapshot s
              where pi.startup_time = s.startup_time
                and pi.open_time < s.end_interval_time and pi.open_time >= s.startup_time
                and s.dbid  = &dbid
                and s.instance_number = &inst_num
                and pi.dbid = s.dbid
                and pi.instance_number = s.instance_number
                and s.snap_id = &eid) mia
      where p.dbid = &dbid
        and p.instance_number = mia.instance_number
        and p.startup_time = mia.startup_time
        and p.open_time = mia.open_time
        and mia.ord = 1)  dbpdb
    where st.sql_id(+) = sqt.sql_id and st.dbid(+) = &dbid
          and sqt.con_dbid = dbpdb.con_dbid(+)
          and sqt.con_dbid = st.con_dbid(+)
    order by sqt.rnum;

Back to Top

SQL ordered by Reads

SET linesize 210
SET pagesize 40
SET VERIFY OFF
COLUMN "SQL Module" format A10
COLUMN "SQL Text" format A50
COLUMN "PDB_NAME" format A2

---your param---
define dbid        = 1391944761
define bid         = 112
define eid         = 115
define inst_num    = 1
----------------

define tsql_max    = 65
define tsql_min    = 10
define top_pct_sql = 1

var pr number

begin
SELECT e.value - b.value
  INTO :pr
  FROM dba_hist_sysstat b,
       dba_hist_sysstat e
 WHERE b.snap_id =&bid
    AND e.snap_id =&eid
    AND b.dbid =&dbid
    AND e.dbid =&dbid
    AND b.instance_number =&inst_num
    AND e.instance_number =&inst_num
    AND b.stat_id = e.stat_id
    AND e.stat_name = 'physical reads'
    AND e.value >= b.value
    AND e.value > 0;

end;
/
define phyr = :pr

with sqt as
      (select elap, cput, exec, uiot, dskr,  norm_val, sql_id, module, rnum,
              con_dbid
         from (select sql_id, module, elap, norm_val, cput, exec, uiot,
                      dskr, rownum rnum, con_dbid
                 from (select sql_id
                            , max(module) module
                            , sum(elapsed_time_delta) elap
                            , (100 * (sum(disk_reads_delta) /
                                      nullif(&phyr,0))) norm_val
                            , sum(cpu_time_delta) cput
                            , sum(executions_delta) exec
                            , sum(iowait_delta)     uiot
                            , sum(disk_reads_delta) dskr
                            , con_dbid
                        from dba_hist_sqlstat
                       where dbid = &dbid
                         and instance_number = &inst_num
                         and &bid < snap_id
                         and snap_id <= &eid
                       group by sql_id, con_dbid
                       order by nvl(sum(disk_reads_delta), -1) desc,
                                sql_id, con_dbid))
          where rnum < &tsql_max
            and (rnum <= &tsql_min or norm_val > &top_pct_sql))
    select /*+ NO_MERGE(sqt) */
          sqt.dskr "Physical Reads",
          sqt.exec "Executions",
          decode(sqt.exec, 0, to_number(null), (sqt.dskr / sqt.exec)) "Reads pers Exec",
          sqt.norm_val "%Total",
          nvl((sqt.elap / 1000000), to_number(null)) "Elapsed Time (s)",
          decode(sqt.elap, 0, to_number(null), (100 * (sqt.cput / sqt.elap))) "%CPU",
          decode(sqt.elap, 0, to_number(null), (100 * (sqt.uiot / sqt.elap))) "%IO",
          sqt.sql_id  "SQL Id",
          sqt.module  "SQL Module",
          dbpdb.pdb_name,
          st.sql_text "SQL Text"
     from sqt, dba_hist_sqltext st,  (select p.con_dbid, p.pdb_name
       from dba_hist_pdb_instance p,
            (select pi.con_dbid, pi.instance_number, pi.startup_time,
                    pi.open_time,
                    rank() over (partition by pi.con_dbid
                      order by pi.open_time desc, pi.instance_number asc) ord
               from  dba_hist_pdb_instance pi, dba_hist_snapshot s
              where pi.startup_time = s.startup_time
                and pi.open_time < s.end_interval_time and pi.open_time >= s.startup_time
                and s.dbid  = &dbid
                and s.instance_number = &inst_num
                and pi.dbid = s.dbid
                and pi.instance_number = s.instance_number
                and s.snap_id = &eid) mia
      where p.dbid = &dbid
        and p.instance_number = mia.instance_number
        and p.startup_time = mia.startup_time
        and p.open_time = mia.open_time
        and mia.ord = 1)  dbpdb
    where st.sql_id(+) = sqt.sql_id and st.dbid(+) = &dbid
          and sqt.con_dbid = dbpdb.con_dbid(+)
          and sqt.con_dbid = st.con_dbid(+)
    order by sqt.rnum;

Back to Top

SQL ordered by Physical Reads (UnOptimized)

SET linesize 210
SET pagesize 40
SET VERIFY OFF
COLUMN "SQL Module" format A10
COLUMN "SQL Text" format A50
COLUMN "PDB_NAME" format A2

---your param---
define dbid        = 1391944761
define bid         = 112
define eid         = 115
define inst_num    = 1
----------------

define tsql_max    = 65
define tsql_min    = 10
define top_pct_sql = 1

var pr number

begin
SELECT e.value - b.value
  INTO :pr
  FROM dba_hist_sysstat b,
       dba_hist_sysstat e
 WHERE b.snap_id =&bid
    AND e.snap_id =&eid
    AND b.dbid =&dbid
    AND e.dbid =&dbid
    AND b.instance_number =&inst_num
    AND e.instance_number =&inst_num
    AND b.stat_id = e.stat_id
    AND e.stat_name = 'physical read IO requests'
    AND e.value >= b.value
    AND e.value > 0;

end;
/
define phydrq = :pr

with sqt as
      (select exec, pdr, prq, porq,  norm_val, sql_id, module, rnum, con_dbid
         from (select sql_id, module, norm_val, exec, con_dbid,
                      pdr, prq, porq, rownum rnum
                 from (select sql_id
                            , max(module) module
                            , (100 * ((sum(physical_read_requests_delta)
                                       - sum(optimized_physical_reads_delta)) /
                                      nullif(&phydrq,0))) norm_val
                            , sum(executions_delta) exec
                            , sum(physical_read_requests_delta) -
                              sum(optimized_physical_reads_delta) pdr
                            , sum(physical_read_requests_delta) prq
                            , sum(optimized_physical_reads_delta) porq
                            , con_dbid
                        from dba_hist_sqlstat
                       where dbid = &dbid
                         and instance_number = &inst_num
                         and &bid < snap_id
                         and snap_id <= &eid
                       group by sql_id, con_dbid
                       order by nvl(sum(physical_read_requests_delta)
                                    - sum(optimized_physical_reads_delta), -1)
                                desc, sql_id, con_dbid))
          where rnum < &tsql_max
            and (rnum <= &tsql_min or norm_val > &top_pct_sql))
    select /*+ NO_MERGE(sqt) */
          sqt.pdr "UnOptimized Reads Reqs",
          sqt.prq "Physical Reads Reqs",
          sqt.exec "Executions",
          decode(sqt.exec, 0, to_number(null), (sqt.pdr/ sqt.exec)) "UnOptimized Reqs per Exec",
          decode(sqt.prq, 0, to_number(null), (100 * (sqt.porq / sqt.prq))) "%Opt",
          sqt.norm_val "%Total",
          sqt.sql_id  "SQL Id",
          sqt.module  "SQL Module",
          dbpdb.pdb_name,
          st.sql_text "SQL Text"
     from sqt, dba_hist_sqltext st,  (select p.con_dbid, p.pdb_name
       from dba_hist_pdb_instance p,
            (select pi.con_dbid, pi.instance_number, pi.startup_time,
                    pi.open_time,
                    rank() over (partition by pi.con_dbid
                      order by pi.open_time desc, pi.instance_number asc) ord
               from  dba_hist_pdb_instance pi, dba_hist_snapshot s
              where pi.startup_time = s.startup_time
                and pi.open_time < s.end_interval_time and pi.open_time >= s.startup_time
                and s.dbid  = &dbid
                and s.instance_number = &inst_num
                and pi.dbid = s.dbid
                and pi.instance_number = s.instance_number
                and s.snap_id = &eid) mia
      where p.dbid = &dbid
        and p.instance_number = mia.instance_number
        and p.startup_time = mia.startup_time
        and p.open_time = mia.open_time
        and mia.ord = 1)  dbpdb
    where st.sql_id(+) = sqt.sql_id and st.dbid(+) = &dbid
          and sqt.con_dbid = dbpdb.con_dbid(+)
          and sqt.con_dbid = st.con_dbid(+)
    order by sqt.rnum;

Back to Top

SQL ordered by Executions

SET linesize 210
SET pagesize 40
SET VERIFY OFF
COLUMN "SQL Module" format A10
COLUMN "SQL Text" format A50
COLUMN "PDB_NAME" format A2

---your param---
define dbid        = 1391944761
define bid         = 112
define eid         = 115
define inst_num    = 1
----------------

define tsql_max    = 65
define tsql_min    = 10
define top_pct_sql = 1

var exec number

begin
SELECT e.value - b.value
  INTO :exec
  FROM dba_hist_sysstat b,
       dba_hist_sysstat e
 WHERE b.snap_id =&bid
    AND e.snap_id =&eid
    AND b.dbid =&dbid
    AND e.dbid =&dbid
    AND b.instance_number =&inst_num
    AND e.instance_number =&inst_num
    AND b.stat_id = e.stat_id
    AND e.stat_name = 'execute count'
    AND e.value >= b.value
    AND e.value > 0;

end;
/
define exe = :exec

with sqt as
      (select elap, cput, exec, uiot, rowp, sql_id, module, rnum, con_dbid
         from (select sql_id, module, elap, rowp, cput, exec, uiot, con_dbid,
                      rownum rnum
                 from (select sql_id
                            , max(module) module
                            , sum(elapsed_time_delta) elap
                            , sum(cpu_time_delta)     cput
                            , sum(executions_delta)   exec
                            , sum(iowait_delta)       uiot
                            , sum(rows_processed_delta) rowp
                            , con_dbid
                        from dba_hist_sqlstat
                       where dbid = &dbid
                         and instance_number = &inst_num
                         and &bid < snap_id
                         and snap_id <= &eid
                       group by sql_id, con_dbid
                       order by nvl(sum(executions_delta), -1) desc,
                                sql_id, con_dbid))
          where rnum < &tsql_max
            and (rnum <= &tsql_min or (100 * exec) / decode(&exe,0,NULL,&exe) > &top_pct_sql))
    select /*+ NO_MERGE(sqt) */
          sqt.exec "Executions",
          sqt.rowp "Rows Processed",
          decode(sqt.exec, 0, to_number(null), sqt.rowp / sqt.exec) "Rows per Exec",
          nvl((sqt.elap / 1000000), to_number(null)) "Elapsed Time(s)",
          decode(sqt.elap, 0, '     ',
             lpad(to_char(round((100 * (sqt.cput / sqt.elap)),1), 'TM9'),5)) "%CPU",
          decode(sqt.elap, 0, '     ',
             lpad(to_char(round((100 * (sqt.uiot / sqt.elap)),1), 'TM9'),5)) "%IO",
          sqt.sql_id  "SQL Id",
          sqt.module  "SQL Module",
          dbpdb.pdb_name,
          st.sql_text "SQL Text"
     from sqt, dba_hist_sqltext st,  (select p.con_dbid, p.pdb_name
       from dba_hist_pdb_instance p,
            (select pi.con_dbid, pi.instance_number, pi.startup_time,
                    pi.open_time,
                    rank() over (partition by pi.con_dbid
                      order by pi.open_time desc, pi.instance_number asc) ord
               from  dba_hist_pdb_instance pi, dba_hist_snapshot s
              where pi.startup_time = s.startup_time
                and pi.open_time < s.end_interval_time and pi.open_time >= s.startup_time
                and s.dbid  = &dbid
                and s.instance_number = &inst_num
                and pi.dbid = s.dbid
                and pi.instance_number = s.instance_number
                and s.snap_id = &eid) mia
      where p.dbid = &dbid
        and p.instance_number = mia.instance_number
        and p.startup_time = mia.startup_time
        and p.open_time = mia.open_time
        and mia.ord = 1)  dbpdb
    where st.sql_id(+) = sqt.sql_id and st.dbid(+) = &dbid
          and sqt.con_dbid = dbpdb.con_dbid(+)
          and sqt.con_dbid = st.con_dbid(+)
    order by sqt.rnum;

Back to Top

SQL ordered by Parse Calls

SET linesize 210
SET pagesize 40
SET VERIFY OFF
COLUMN "SQL Module" format A10
COLUMN "SQL Text" format A50
COLUMN "PDB_NAME" format A2

---your param---
define dbid        = 1391944761
define bid         = 112
define eid         = 115
define inst_num    = 1
----------------

define tsql_max    = 65
define tsql_min    = 10
define top_pct_sql = 1

var parsec number

begin
SELECT e.value - b.value
  INTO :parsec
  FROM dba_hist_sysstat b,
       dba_hist_sysstat e
 WHERE b.snap_id =&bid
    AND e.snap_id =&eid
    AND b.dbid =&dbid
    AND e.dbid =&dbid
    AND b.instance_number =&inst_num
    AND e.instance_number =&inst_num
    AND b.stat_id = e.stat_id
    AND e.stat_name = 'parse count (total)'
    AND e.value >= b.value
    AND e.value > 0;

end;
/
define prse = :parsec

with sqt as
      (select exec, prsc, norm_val, sql_id, module, rnum, con_dbid
         from (select sql_id, module, norm_val, exec, prsc, rownum rnum, con_dbid
                 from (select sql_id
                            , max(module) module
                            , (100 * (sum(parse_calls_delta) /
                                      nullif(&prse,0))) norm_val
                            , sum(executions_delta)  exec
                            , sum(parse_calls_delta) prsc
                            , con_dbid
                        from dba_hist_sqlstat
                       where dbid = &dbid
                         and instance_number = &inst_num
                         and &bid < snap_id
                         and snap_id <= &eid
                       group by sql_id, con_dbid
                       order by nvl(sum(parse_calls_delta), -1) desc,
                                sql_id, con_dbid))
          where rnum < &tsql_max
            and (rnum <= &tsql_min or norm_val > &top_pct_sql))
    select /*+ NO_MERGE(sqt) */
          sqt.prsc "Parse Calls",
          sqt.exec "Executions",
          sqt.norm_val "%Total Parses",
          sqt.sql_id  "SQL Id",
          sqt.module  "SQL Module",
          dbpdb.pdb_name,
          st.sql_text "SQL Text"
     from sqt, dba_hist_sqltext st,  (select p.con_dbid, p.pdb_name
       from dba_hist_pdb_instance p,
            (select pi.con_dbid, pi.instance_number, pi.startup_time,
                    pi.open_time,
                    rank() over (partition by pi.con_dbid
                      order by pi.open_time desc, pi.instance_number asc) ord
               from  dba_hist_pdb_instance pi, dba_hist_snapshot s
              where pi.startup_time = s.startup_time
                and pi.open_time < s.end_interval_time and pi.open_time >= s.startup_time
                and s.dbid  = &dbid
                and s.instance_number = &inst_num
                and pi.dbid = s.dbid
                and pi.instance_number = s.instance_number
                and s.snap_id = &eid) mia
      where p.dbid = &dbid
        and p.instance_number = mia.instance_number
        and p.startup_time = mia.startup_time
        and p.open_time = mia.open_time
        and mia.ord = 1)  dbpdb
    where st.sql_id(+) = sqt.sql_id and st.dbid(+) = &dbid
          and sqt.con_dbid = dbpdb.con_dbid(+)
          and sqt.con_dbid = st.con_dbid(+)
    order by sqt.rnum;

Back to Top

SQL ordered by Sharable Memory

SET linesize 210
SET pagesize 40
SET VERIFY OFF
COLUMN "SQL Module" format A10
COLUMN "SQL Text" format A50
COLUMN "PDB_NAME" format A2

---your param---
define dbid        = 1391944761
define bid         = 112
define eid         = 115
define inst_num    = 1
----------------

define tsql_max    = 65
define tsql_min    = 10
define top_pct_sql = 1

var spm number

begin
SELECT MAX(VALUE)
  INTO :spm
  FROM DBA_HIST_PARAMETER 
 WHERE SNAP_ID = &eid 
   AND DBID = &dbid 
   AND INSTANCE_NUMBER =&inst_num 
   AND PARAMETER_NAME ='__shared_pool_size';
end;
/
define espm = :spm
define shmem_thres = 1048576

with sqt as
      (select exec, sharable_mem, sql_id, module, rnum, con_dbid
         from (select sql_id, module, exec, sharable_mem,
                      rownum rnum, con_dbid
                 from
                 (select sql_id, module, exec, sharable_mem, con_dbid
                    from
                        (select sharable_mem, sql_id, con_dbid
                           from dba_hist_sqlstat
                          where dbid = &dbid
                            and snap_id = &eid
                            and instance_number = &inst_num
                            and sharable_mem > &shmem_thres) y
                      left outer join
                        (select sql_id, con_dbid
                               , max(module) module
                               , sum(executions_delta)  exec
                           from dba_hist_sqlstat
                          where dbid = &dbid
                            and instance_number = &inst_num
                            and &bid < snap_id
                            and snap_id <= &eid
                       group by sql_id, con_dbid) x
                       using (sql_id, con_dbid)
               order by nvl(sharable_mem, -1) desc, sql_id, con_dbid))
       where rnum <= &tsql_max)
    select /*+ NO_MERGE(sqt) */
          sqt.sharable_mem "Sharable Mem(b)",
          sqt.exec "Executions",
          decode(&espm, 0, 0, 100 * sqt.sharable_mem/&espm) "%Total",
          sqt.sql_id  "SQL Id",
          sqt.module  "SQL Module",
          dbpdb.pdb_name,
          st.sql_text "SQL Text"
     from sqt, dba_hist_sqltext st,  (select p.con_dbid, p.pdb_name
       from dba_hist_pdb_instance p,
            (select pi.con_dbid, pi.instance_number, pi.startup_time,
                    pi.open_time,
                    rank() over (partition by pi.con_dbid
                      order by pi.open_time desc, pi.instance_number asc) ord
               from  dba_hist_pdb_instance pi, dba_hist_snapshot s
              where pi.startup_time = s.startup_time
                and pi.open_time < s.end_interval_time and pi.open_time >= s.startup_time
                and s.dbid  = &dbid
                and s.instance_number = &inst_num
                and pi.dbid = s.dbid
                and pi.instance_number = s.instance_number
                and s.snap_id = &eid) mia
      where p.dbid = &dbid
        and p.instance_number = mia.instance_number
        and p.startup_time = mia.startup_time
        and p.open_time = mia.open_time
        and mia.ord = 1)  dbpdb
    where st.sql_id(+) = sqt.sql_id and st.dbid(+) = &dbid
          and sqt.con_dbid = dbpdb.con_dbid(+)
          and sqt.con_dbid = st.con_dbid(+)
    order by sqt.rnum;

Back to Top

SQL ordered by Version Count

SET linesize 210
SET pagesize 40
SET VERIFY OFF
COLUMN "SQL Module" format A10
COLUMN "SQL Text" format A50
COLUMN "PDB_NAME" format A2

---your param---
define dbid        = 1391944761
define bid         = 112
define eid         = 115
define inst_num    = 1
----------------

define vcnt_thres  = 20
define tsql_max    = 65
define tsql_min    = 10
define top_pct_sql = 1

with sqt as
      (select exec, version_count, sql_id, module, rnum, con_dbid
        from (select sql_id, module, exec, version_count,
                      rownum rnum, con_dbid
         from (select sql_id, module, exec, version_count, con_dbid
                 from
                     (select version_count, sql_id, con_dbid
                        from dba_hist_sqlstat
                       where dbid = &dbid
                         and snap_id = &eid
                         and instance_number = &inst_num
                         and version_count > &vcnt_thres) y
                   left outer join
                     (select sql_id, con_dbid
                            , max(module) module
                            , sum(executions_delta)  exec
                        from dba_hist_sqlstat
                       where dbid = &dbid
                         and instance_number = &inst_num
                         and &bid < snap_id
                         and snap_id <= &eid
                       group by sql_id, con_dbid) x
                     using (sql_id, con_dbid)
               order by nvl(y.version_count, -1) desc, sql_id, con_dbid))
       where rnum <= &tsql_max)
    select /*+ NO_MERGE(sqt) */
           sqt.version_count "Version Count",
          sqt.exec "Executions",
          sqt.sql_id  "SQL Id",
          sqt.module  "SQL Module",
          dbpdb.pdb_name,
          st.sql_text "SQL Text"
     from sqt, dba_hist_sqltext st,  (select p.con_dbid, p.pdb_name
       from dba_hist_pdb_instance p,
            (select pi.con_dbid, pi.instance_number, pi.startup_time,
                    pi.open_time,
                    rank() over (partition by pi.con_dbid
                      order by pi.open_time desc, pi.instance_number asc) ord
               from  dba_hist_pdb_instance pi, dba_hist_snapshot s
              where pi.startup_time = s.startup_time
                and pi.open_time < s.end_interval_time and pi.open_time >= s.startup_time
                and s.dbid  = &dbid
                and s.instance_number = &inst_num
                and pi.dbid = s.dbid
                and pi.instance_number = s.instance_number
                and s.snap_id = &eid) mia
      where p.dbid = &dbid
        and p.instance_number = mia.instance_number
        and p.startup_time = mia.startup_time
        and p.open_time = mia.open_time
        and mia.ord = 1)  dbpdb
    where st.sql_id(+) = sqt.sql_id and st.dbid(+) = &dbid
          and sqt.con_dbid = dbpdb.con_dbid(+)
          and sqt.con_dbid = st.con_dbid(+)
    order by sqt.rnum;


Back to Top

Leave a Reply

Be the First to Comment!

avatar
wpDiscuz