DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Wednesday, April 24, 2013

Hard_Parse Value for every one hour from AWR tables.

Script :
 
set serveroutput on
set lines 130
set verify off
accept v_begin_time prompt 'Enter BEGIN_TIME DD-MON-YY HH24:MI:SS->'
accept v_end_time prompt 'Enter END_TIME DD-MON-YY HH24:MI:SS->'
DECLARE
   lv_v_stat_name   dba_hist_sysstat.stat_name%TYPE;
   lv_v_per_sec     NUMBER;
   lv_n_cnt         NUMBER                            := 0;

BEGIN
   FOR c1 IN
      (SELECT TO_CHAR (begin_interval_time, 'DD/MON/YY:HH24') sun,TO_CHAR (begin_interval_time + (1/24), 'DD-MON-YY:HH24')  sun1
         FROM dba_hist_snapshot
        WHERE begin_interval_time BETWEEN   TO_DATE ('&v_begin_time',
                                                     'DD-MON-YYYY HH24:MI'
                                                    )
                                          - 1 / 24
                                      AND TO_DATE ('&v_end_time',
                                                   'DD-MON-YYYY HH24:MI'
                                                  )order by 1)
   LOOP
      BEGIN
      --dbms_output.put_line (c1.sun);
         SELECT distinct e.stat_name "Statistic Name",
                  ROUND
                     (  (e.VALUE - b.VALUE)
                      / (SELECT AVG
                                   (    EXTRACT
                                             (DAY FROM (  e1.end_interval_time
                                                        - b1.end_interval_time
                                                       )
                                             )
                                      * 24
                                      * 60
                                      * 60
                                    +   EXTRACT
                                            (HOUR FROM (  e1.end_interval_time
                                                        - b1.end_interval_time
                                                       )
                                            )
                                      * 60
                                      * 60
                                    +   EXTRACT
                                           (MINUTE FROM (  e1.end_interval_time
                                                         - b1.end_interval_time
                                                        )
                                           )
                                      * 60
                                    + EXTRACT
                                          (SECOND FROM (  e1.end_interval_time
                                                        - b1.end_interval_time
                                                       )
                                          )
                                   )
                           FROM dba_hist_snapshot b1, dba_hist_snapshot e1
                          WHERE b1.snap_id = b.snap_id
                            AND e1.snap_id = e.snap_id
                            AND b1.dbid = b.dbid
                            AND e1.dbid = e.dbid
                            AND b1.instance_number = b.instance_number
                            AND e1.instance_number = e.instance_number
                            AND b1.startup_time = e1.startup_time
                            AND b1.end_interval_time < e1.end_interval_time),
                      2
                     ) "Per Second"
             INTO lv_v_stat_name,
                  lv_v_per_sec
             FROM dba_hist_sysstat b, dba_hist_sysstat e
            WHERE b.snap_id  =
                     (SELECT max(snap_id)
                        FROM dba_hist_snapshot
                       WHERE begin_interval_time
                                BETWEEN   TO_DATE (c1.sun,
                                                   'DD-MON-YY HH24:MI'
                                                  )
                                        - 1 / 24
                                    AND TO_DATE (c1.sun,
                                                 'DD-MON-YY HH24:MI'))
              AND e.snap_id =
                     (SELECT max(snap_id)
                        FROM dba_hist_snapshot
                       WHERE begin_interval_time
                                BETWEEN   TO_DATE (c1.sun1,
                                                   'DD-MON-YY HH24:MI'
                                                  ) - 1/24
                                     AND TO_DATE (c1.sun1,
                                                 'DD-MON-YY HH24:MI'))
              AND b.stat_id = e.stat_id
              AND e.stat_name LIKE '%hard%'
              AND e.VALUE >= b.VALUE
              AND e.VALUE > 0
         ORDER BY 1 ASC;

         lv_n_cnt := lv_n_cnt + 1;
         DBMS_OUTPUT.put_line (c1.sun||'    '||lv_v_stat_name||'     '||lv_v_per_sec);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            DBMS_OUTPUT.put_line ('No Data Found');
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('When Others Error');
      END;
   END LOOP;
END;
/

====

Sample Output :

Date Stat_Name Value
18/APR/13:07 parse count (hard) 3.23
18/APR/13:08 parse count (hard) 3.95
18/APR/13:09 parse count (hard) 4.14
18/APR/13:10 parse count (hard) 4.26
18/APR/13:11 parse count (hard) 4.12

1 comment:

Sridevi K said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.