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
/