This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Custom Report - Top SQL by Any Metric - Oracle

Introduction

This custom report will produce a list of Top SQLs by any metric that DPA for Oracle collects. It is designed to be run from SQL*Plus while connected to the DPA/Ignite repository. It will prompt for everything it needs and the valid values for the metrics prompt is: executions, execs, logical reads, lio, parses, disk reads, pio, lio / exec, lio_exec, pio / exec, pio_exec, total exec time, wait, avg exec time, avg.

SQL Script

set pagesize 999

set linesize 132

set serveroutput on

column name format a50

select id, name from cond order by name;

prompt Which Database ID

accept DBID

prompt Enter Start Date (mm/dd/yy hh24:mi)

accept BeginDate

prompt Enter End Date (mm/dd/yy hh24:mi)

accept EndDate

prompt How many SQLs should be displayed

accept HowMany

prompt Which metric do you want to sort by (see script for possible values):

accept Metric

DECLARE

   nDBID number := &DBID;

   dBeginDate date := to_date('&BeginDate','mm/dd/yy hh24:mi');

   dEndDate date := to_date('&EndDate','mm/dd/yy hh24:mi');

   nHowMany integer := &HowMany;

   sMetric varchar2(20) := '&Metric';

   sMetricCol varchar2(30);

   sSQL varchar2(1000);

   nCount integer;

BEGIN

   IF UPPER(sMetric) = 'EXECUTIONS' or UPPER(sMetric) = 'EXECS' THEN

      sMetricCol := 'SUM(EXECS)';

   ELSIF UPPER(sMetric) = 'LOGICAL READS' or UPPER(sMetric) = 'LIO' THEN

      sMetricCol := 'SUM(BGETS)';

   ELSIF UPPER(sMetric) = 'PARSES' THEN

      sMetricCol := 'SUM(PARSES)';

   ELSIF UPPER(sMetric) = 'DISK READS' or UPPER(sMetric) = 'PIO' THEN

      sMetricCol := 'SUM(DREADS)';

   ELSIF UPPER(sMetric) = 'LIO / EXEC' or UPPER(sMetric) = 'LIO_EXEC' THEN

      sMetricCol := 'SUM(BGETS)/SUM(EXECS)';

   ELSIF UPPER(sMetric) = 'PIO / EXEC' or UPPER(sMetric) = 'PIO_EXEC' THEN

      sMetricCol := 'SUM(DREADS)/SUM(EXECS)';

   ELSIF UPPER(sMetric) = 'TOTAL EXEC TIME' or UPPER(sMetric) = 'WAIT' THEN

      sMetricCol := 'SUM(TIMESECS)';

   ELSIF UPPER(sMetric) = 'AVG EXEC TIME' or UPPER(sMetric) = 'AVG' THEN

      sMetricCol := 'SUM(TIMESECS)/SUM(EXECS)';

   ELSE

      sMetricCol := 'Invalid Metric';

   END IF;

   IF sMetricCol = 'Invalid Metric' THEN

      DBMS_OUTPUT.PUT_LINE('Invalid Metric Name Provided. Valid values are executions, execs, logical reads, lio, parses, disk reads, pio, lio / exec, lio_exec, pio / exec, pio_exec, total exec time, wait, avg exec time, avg');

   ELSE

      -- create a temporary table to store this data, drop it if it still exists

      SELECT COUNT(1) INTO nCount FROM user_tables WHERE table_name='TEMP_TOP_SQL';

      IF nCount > 0 THEN

         sSQL := 'DROP TABLE TEMP_TOP_SQL';

         EXECUTE IMMEDIATE sSQL;

      END IF;

      sSQL := 'CREATE TABLE TEMP_TOP_SQL (row_num number, NameOrHash varchar2(20), executions number, logical_reads number, parses number, disk_reads number, lio_exec number, pio_exec number, parses_exec number, total_exec_time number, avg_exec_time number)';

      EXECUTE IMMEDIATE sSQL;

      sSQL := 'INSERT INTO TEMP_TOP_SQL SELECT ROWNUM as row_num, A.* FROM ( ' ||

                 'SELECT NVL(N.NAME, SS.SQLHASH) "NameOrHash", ' ||

                 'SUM(EXECS) as "Executions", ' ||

                 'SUM(BGETS) as "Logical Reads", ' ||

                 'SUM(PARSES) as "Parses", ' ||

                 'SUM(DREADS) as "Physical Reads", ' ||

                 'SUM(BGETS)/SUM(EXECS) as "LIO / Exec", ' ||

                 'SUM(DREADS)/SUM(EXECS) as "PIO / Exec", ' ||

                 'SUM(PARSES)/SUM(EXECS) as "Parses / Exec", ' ||

                 'SUM(TIMESECS) "Total Wait Time", ' ||

                 'SUM(TIMESECS)/SUM(EXECS) "Avg Exec Time" ' ||

             'FROM CON_STATS_SUM_' || nDBID || ' SS ' ||

             'LEFT OUTER JOIN CON_SQL_NAME N ON SS.SQLHASH = N.HASH ' ||

             'WHERE DATEHOUR BETWEEN :1 AND :2 ' ||

             'GROUP BY NVL(N.NAME, SS.SQLHASH) HAVING SUM(EXECS)<>0 ' ||

             'ORDER BY ' || sMetricCol || 'DESC) A ' ||

             'WHERE ROWNUM <= :3';

     DBMS_OUTPUT.PUT_LINE(sSQL);

     EXECUTE IMMEDIATE sSQL USING dBeginDate, dEndDate, nHowMany;

   END IF;

END;

/

-- Now go select the data that was created by the above

SELECT * FROM temp_top_sql ORDER BY row_num

/