500 lines
17 KiB
MySQL
500 lines
17 KiB
MySQL
|
set echo off
|
||
|
|
SET term off ver off feed off trims on;
|
||
|
|
-- Seeded Parameters
|
||
|
|
define p_top = 10;
|
||
|
|
define p_factor_th = 0.0040;
|
||
|
|
|
||
|
|
variable v_count number;
|
||
|
|
variable v_buffer_gets number;
|
||
|
|
variable v_disk_reads number;
|
||
|
|
variable v_executions number;
|
||
|
|
variable v_bg_per_exec number;
|
||
|
|
variable v_dr_per_exec number;
|
||
|
|
variable v_istartup varchar2(15);
|
||
|
|
|
||
|
|
SET term on ver off feed off trims on;
|
||
|
|
PROMPT Calculating SQL Area totals per category...
|
||
|
|
SET term off;
|
||
|
|
BEGIN
|
||
|
|
SELECT COUNT(*),
|
||
|
|
TRUNC(SUM(ABS(BUFFER_GETS))),
|
||
|
|
TRUNC(SUM(ABS(DISK_READS))),
|
||
|
|
TRUNC(SUM(ABS(EXECUTIONS))),
|
||
|
|
TRUNC(SUM(ABS(BUFFER_GETS)/
|
||
|
|
DECODE(NVL(ABS(EXECUTIONS),0),0,1,ABS(EXECUTIONS)))),
|
||
|
|
TRUNC(SUM(ABS(DISK_READS)/
|
||
|
|
DECODE(NVL(ABS(EXECUTIONS),0),0,1,ABS(EXECUTIONS))))
|
||
|
|
INTO :v_count,
|
||
|
|
:v_buffer_gets,
|
||
|
|
:v_disk_reads,
|
||
|
|
:v_executions,
|
||
|
|
:v_bg_per_exec,
|
||
|
|
:v_dr_per_exec
|
||
|
|
FROM V$SQLAREA;
|
||
|
|
SELECT TO_CHAR(STARTUP_TIME,'DD-MON-YY HH24:MI')
|
||
|
|
INTO :v_istartup
|
||
|
|
FROM V$INSTANCE
|
||
|
|
WHERE ROWNUM = 1;
|
||
|
|
END;
|
||
|
|
/
|
||
|
|
|
||
|
|
SET term on;
|
||
|
|
PROMPT Creating COE_SQLAREA temp table...
|
||
|
|
SET term off;
|
||
|
|
DROP TABLE COE_SQLAREA;
|
||
|
|
CREATE TABLE COE_SQLAREA
|
||
|
|
(ROW_NUM NUMBER,HASH_VALUE NUMBER,ADDRESS RAW(4),BUFFER_GETS NUMBER,
|
||
|
|
DISK_READS NUMBER,EXECUTIONS NUMBER,BG_PER_EXEC NUMBER,DR_PER_EXEC NUMBER,
|
||
|
|
PARSING_USER_ID NUMBER,MODULE VARCHAR2(64),ACTION VARCHAR2(64),
|
||
|
|
SQL_TEXT VARCHAR2(64),P_BUFFER_GETS NUMBER,P_DISK_READS NUMBER,
|
||
|
|
P_EXECUTIONS NUMBER,P_BG_PER_EXEC NUMBER,P_DR_PER_EXEC NUMBER,
|
||
|
|
USERNAME VARCHAR2(30),T_BUFFER_GETS NUMBER,T_DISK_READS NUMBER,
|
||
|
|
T_EXECUTIONS NUMBER,T_BG_PER_EXEC NUMBER,T_DR_PER_EXEC NUMBER) NOLOGGING;
|
||
|
|
|
||
|
|
INSERT INTO COE_SQLAREA
|
||
|
|
SELECT ROWNUM,
|
||
|
|
HASH_VALUE,
|
||
|
|
ADDRESS,
|
||
|
|
ABS(BUFFER_GETS),
|
||
|
|
ABS(DISK_READS),
|
||
|
|
ABS(EXECUTIONS),
|
||
|
|
ROUND(ABS(BUFFER_GETS)/
|
||
|
|
DECODE(NVL(ABS(EXECUTIONS),0),0,1,ABS(EXECUTIONS))),
|
||
|
|
ROUND(ABS(DISK_READS)/
|
||
|
|
DECODE(NVL(ABS(EXECUTIONS),0),0,1,ABS(EXECUTIONS))),
|
||
|
|
PARSING_USER_ID,
|
||
|
|
MODULE,
|
||
|
|
ACTION,
|
||
|
|
SUBSTR(SQL_TEXT,1,64),
|
||
|
|
ROUND(ABS(BUFFER_GETS)*100/:v_buffer_gets,3),
|
||
|
|
ROUND(ABS(DISK_READS)*100/:v_disk_reads,3),
|
||
|
|
ROUND(ABS(EXECUTIONS)*100/:v_executions,3),
|
||
|
|
ROUND((ABS(BUFFER_GETS)/
|
||
|
|
DECODE(NVL(ABS(EXECUTIONS),0),0,1,ABS(EXECUTIONS)))*100/
|
||
|
|
:v_bg_per_exec,3),
|
||
|
|
ROUND((ABS(DISK_READS)/
|
||
|
|
DECODE(NVL(ABS(EXECUTIONS),0),0,1,ABS(EXECUTIONS)))*100/
|
||
|
|
:v_dr_per_exec,3),
|
||
|
|
NULL,
|
||
|
|
NULL,
|
||
|
|
NULL,
|
||
|
|
NULL,
|
||
|
|
NULL,
|
||
|
|
NULL
|
||
|
|
FROM V$SQLAREA
|
||
|
|
WHERE
|
||
|
|
-- PARSING_USER_ID <> 0 AND
|
||
|
|
(ABS(BUFFER_GETS)
|
||
|
|
> TO_NUMBER('&&p_factor_th')*:v_buffer_gets
|
||
|
|
OR ABS(DISK_READS)
|
||
|
|
> TO_NUMBER('&&p_factor_th')*:v_disk_reads
|
||
|
|
OR ABS(EXECUTIONS)
|
||
|
|
> TO_NUMBER('&&p_factor_th')*:v_executions
|
||
|
|
OR ABS(BUFFER_GETS)/DECODE(NVL(ABS(EXECUTIONS),0),0,1,ABS(EXECUTIONS))
|
||
|
|
> TO_NUMBER('&&p_factor_th')*:v_bg_per_exec
|
||
|
|
OR ABS(DISK_READS)/DECODE(NVL(ABS(EXECUTIONS),0),0,1,ABS(EXECUTIONS))
|
||
|
|
> TO_NUMBER('&&p_factor_th')*:v_dr_per_exec);
|
||
|
|
|
||
|
|
UPDATE COE_SQLAREA CS
|
||
|
|
SET USERNAME = (SELECT USERNAME
|
||
|
|
FROM ALL_USERS
|
||
|
|
WHERE USER_ID=CS.PARSING_USER_ID);
|
||
|
|
|
||
|
|
SET term on;
|
||
|
|
PROMPT Calculating Top &&p_top SQL per category...
|
||
|
|
SET term off;
|
||
|
|
DECLARE
|
||
|
|
c_top NUMBER;
|
||
|
|
c_rownum NUMBER;
|
||
|
|
cursor C1 is
|
||
|
|
SELECT ROW_NUM
|
||
|
|
FROM COE_SQLAREA
|
||
|
|
ORDER BY BUFFER_GETS DESC;
|
||
|
|
cursor C2 is
|
||
|
|
SELECT ROW_NUM
|
||
|
|
FROM COE_SQLAREA
|
||
|
|
ORDER BY DISK_READS DESC;
|
||
|
|
cursor C3 is
|
||
|
|
SELECT ROW_NUM
|
||
|
|
FROM COE_SQLAREA
|
||
|
|
ORDER BY EXECUTIONS DESC;
|
||
|
|
cursor C4 is
|
||
|
|
SELECT ROW_NUM
|
||
|
|
FROM COE_SQLAREA
|
||
|
|
ORDER BY BG_PER_EXEC DESC;
|
||
|
|
cursor C5 is
|
||
|
|
SELECT ROW_NUM
|
||
|
|
FROM COE_SQLAREA
|
||
|
|
ORDER BY DR_PER_EXEC DESC;
|
||
|
|
BEGIN
|
||
|
|
c_top := 1;
|
||
|
|
OPEN C1;
|
||
|
|
LOOP
|
||
|
|
FETCH C1 into c_rownum;
|
||
|
|
EXIT when C1%NOTFOUND;
|
||
|
|
EXIT when c_top = TO_NUMBER('&&p_top')+1;
|
||
|
|
UPDATE COE_SQLAREA
|
||
|
|
SET T_BUFFER_GETS = c_top
|
||
|
|
WHERE ROW_NUM = c_rownum
|
||
|
|
AND BUFFER_GETS > TO_NUMBER('&&p_factor_th')*:v_buffer_gets;
|
||
|
|
c_top := c_top+1;
|
||
|
|
END LOOP;
|
||
|
|
c_top := 1;
|
||
|
|
OPEN C2;
|
||
|
|
LOOP
|
||
|
|
FETCH C2 into c_rownum;
|
||
|
|
EXIT when C2%NOTFOUND;
|
||
|
|
EXIT when c_top = TO_NUMBER('&&p_top')+1;
|
||
|
|
UPDATE COE_SQLAREA
|
||
|
|
SET T_DISK_READS = c_top
|
||
|
|
WHERE ROW_NUM = c_rownum
|
||
|
|
AND DISK_READS > TO_NUMBER('&&p_factor_th')*:v_disk_reads;
|
||
|
|
c_top := c_top+1;
|
||
|
|
END LOOP;
|
||
|
|
c_top := 1;
|
||
|
|
OPEN C3;
|
||
|
|
LOOP
|
||
|
|
FETCH C3 into c_rownum;
|
||
|
|
EXIT when C3%NOTFOUND;
|
||
|
|
EXIT when c_top = TO_NUMBER('&&p_top')+1;
|
||
|
|
UPDATE COE_SQLAREA
|
||
|
|
SET T_EXECUTIONS = c_top
|
||
|
|
WHERE ROW_NUM = c_rownum
|
||
|
|
AND EXECUTIONS > TO_NUMBER('&&p_factor_th')*:v_executions;
|
||
|
|
c_top := c_top+1;
|
||
|
|
END LOOP;
|
||
|
|
c_top := 1;
|
||
|
|
OPEN C4;
|
||
|
|
LOOP
|
||
|
|
FETCH C4 into c_rownum;
|
||
|
|
EXIT when C4%NOTFOUND;
|
||
|
|
EXIT when c_top = TO_NUMBER('&&p_top')+1;
|
||
|
|
UPDATE COE_SQLAREA
|
||
|
|
SET T_BG_PER_EXEC = c_top
|
||
|
|
WHERE ROW_NUM = c_rownum
|
||
|
|
AND BUFFER_GETS/DECODE(NVL(EXECUTIONS,0),0,1,EXECUTIONS)
|
||
|
|
> TO_NUMBER('&&p_factor_th')*:v_bg_per_exec;
|
||
|
|
c_top := c_top+1;
|
||
|
|
END LOOP;
|
||
|
|
c_top := 1;
|
||
|
|
OPEN C5;
|
||
|
|
LOOP
|
||
|
|
FETCH C5 into c_rownum;
|
||
|
|
EXIT when C5%NOTFOUND;
|
||
|
|
EXIT when c_top = TO_NUMBER('&&p_top')+1;
|
||
|
|
UPDATE COE_SQLAREA
|
||
|
|
SET T_DR_PER_EXEC = c_top
|
||
|
|
WHERE ROW_NUM = c_rownum
|
||
|
|
AND DISK_READS/DECODE(NVL(EXECUTIONS,0),0,1,EXECUTIONS)
|
||
|
|
> TO_NUMBER('&&p_factor_th')*:v_dr_per_exec;
|
||
|
|
c_top := c_top+1;
|
||
|
|
END LOOP;
|
||
|
|
END;
|
||
|
|
/
|
||
|
|
|
||
|
|
UPDATE COE_SQLAREA CS
|
||
|
|
SET CS.ROW_NUM = ROWNUM
|
||
|
|
WHERE ( CS.T_BUFFER_GETS < TO_NUMBER('&&p_top')+1 OR
|
||
|
|
CS.T_DISK_READS < TO_NUMBER('&&p_top')+1 OR
|
||
|
|
CS.T_EXECUTIONS < TO_NUMBER('&&p_top')+1 OR
|
||
|
|
CS.T_BG_PER_EXEC < TO_NUMBER('&&p_top')+1 OR
|
||
|
|
CS.T_DR_PER_EXEC < TO_NUMBER('&&p_top')+1 );
|
||
|
|
|
||
|
|
COLUMN NAMESPACE FORMAT A15 HEADING 'Component';
|
||
|
|
COLUMN GETS FORMAT 999,999,999,999 HEADING 'Get Requests';
|
||
|
|
COLUMN GETHITRATIO FORMAT 999.9 HEADING 'Get|Hit|Ratio|Pct';
|
||
|
|
COLUMN PINS FORMAT 999,999,999,999 HEADING 'Pin Requests';
|
||
|
|
COLUMN PINHITRATIO FORMAT 999.9 HEADING 'Pin|Hit|Ratio|Pct';
|
||
|
|
COLUMN RELOADS FORMAT 999,999,999,999 HEADING 'Reloads'
|
||
|
|
COLUMN PINRELOADRATIO FORMAT 999.9 HEADING 'Pin|Reload|Ratio|Pct';
|
||
|
|
COLUMN INVALIDATIONS FORMAT 999,999,999,999 HEADING 'Invalidations';
|
||
|
|
COLUMN POOL_NAME FORMAT A20 HEADING 'SGA Structure';
|
||
|
|
COLUMN POOL_BYTES FORMAT 99,999,999,999 HEADING 'Size in Bytes';
|
||
|
|
COLUMN POOL_MBYTES FORMAT 99,999.9 HEADING 'Size in MB';
|
||
|
|
COLUMN SQL_COUNT FORMAT 999,999,999 HEADING 'SQL Count';
|
||
|
|
COLUMN S_BUFFER_GETS FORMAT 999,999,999,999 -
|
||
|
|
HEADING '(A)|SQL Area Sum of|Logical Reads|(Buffer Gets)';
|
||
|
|
COLUMN S_DISK_READS FORMAT 999,999,999,999 -
|
||
|
|
HEADING '(B)|SQL Area Sum of|Physical Reads|(Disk Reads)';
|
||
|
|
COLUMN S_EXECUTIONS FORMAT 999,999,999,999 -
|
||
|
|
HEADING '(C)|SQL Area Sum of|Number of|Executions';
|
||
|
|
COLUMN S_BG_PER_EXEC FORMAT 999,999,999,999 -
|
||
|
|
HEADING '(D)|SQL Area Sum of|Logical Reads|per Execution';
|
||
|
|
COLUMN S_DR_PER_EXEC FORMAT 999,999,999,999 -
|
||
|
|
HEADING '(E)|SQL Area Sum of|Physical Reads|per Execution';
|
||
|
|
COLUMN DATE_TIME FORMAT A15 HEADING 'Execution|Date and Time';
|
||
|
|
COLUMN ISTARTUP FORMAT A15 HEADING 'Instance|Startup';
|
||
|
|
COLUMN T_BUFFER_GETS FORMAT 9999 HEADING 'Top|LR';
|
||
|
|
COLUMN T_DISK_READS FORMAT 9999 HEADING 'Top|PR';
|
||
|
|
COLUMN T_EXECUTIONS FORMAT 9999 HEADING 'Top|Exec';
|
||
|
|
COLUMN T_BG_PER_EXEC FORMAT 9999 HEADING 'Top|LR|per|Exec';
|
||
|
|
COLUMN T_DR_PER_EXEC FORMAT 9999 HEADING 'Top|PR|per|Exec';
|
||
|
|
COLUMN BUFFER_GETS FORMAT 99,999,999,999 -
|
||
|
|
HEADING '(F)|Logical Reads|(Buffer Gets)';
|
||
|
|
COLUMN DISK_READS FORMAT 99,999,999,999 -
|
||
|
|
HEADING '(G)|Physical Reads|(Disk Reads)';
|
||
|
|
COLUMN EXECUTIONS FORMAT 99,999,999,999 -
|
||
|
|
HEADING '(H)|Number of|Executions';
|
||
|
|
COLUMN BG_PER_EXEC FORMAT 99,999,999,999 -
|
||
|
|
HEADING '(I)|Logical Reads|per Execution';
|
||
|
|
COLUMN DR_PER_EXEC FORMAT 99,999,999,999 -
|
||
|
|
HEADING '(J)|Physical Reads|per Execution';
|
||
|
|
COLUMN P_BUFFER_GETS FORMAT 999.999 -
|
||
|
|
HEADING 'LR(*)|percent|(F/A)';
|
||
|
|
COLUMN P_DISK_READS FORMAT 999.999 -
|
||
|
|
HEADING 'PR(*)|percent|(G/B)';
|
||
|
|
COLUMN P_EXECUTIONS FORMAT 999.999 -
|
||
|
|
HEADING 'Exec(*)|percent|(H/C)';
|
||
|
|
COLUMN P_BG_PER_EXEC FORMAT 999.999 -
|
||
|
|
HEADING 'LR(*)|per Exe|percent|(I/D)';
|
||
|
|
COLUMN P_DR_PER_EXEC FORMAT 999.999 -
|
||
|
|
HEADING 'PR(*)|per Exe|percent|(J/E)';
|
||
|
|
COLUMN ROW_NUM FORMAT 999999 HEADING 'SQL ID';
|
||
|
|
COLUMN USERNAME FORMAT A10 HEADING 'User';
|
||
|
|
COLUMN MODULE_ACTION FORMAT A40 HEADING 'Source (Module and Action)';
|
||
|
|
COLUMN SQL_TEXT_L1 FORMAT A64 HEADING 'SQL Text (first 64 bytes)';
|
||
|
|
COLUMN SQL_TEXT FORMAT A64 HEADING 'SQL Text';
|
||
|
|
COLUMN HASH_VALUE FORMAT 999999999999999 HEADING 'Hash Value';
|
||
|
|
COLUMN PIECE NOPRINT;
|
||
|
|
COLUMN DUMMY NOPRINT;
|
||
|
|
|
||
|
|
SET term on;
|
||
|
|
PROMPT Generating SPOOL file...
|
||
|
|
PROMPT
|
||
|
|
PROMPT
|
||
|
|
SET term off;
|
||
|
|
|
||
|
|
SET term on pages 10000 lines 156;
|
||
|
|
SPOOL coe_sqlarea_80.txt;
|
||
|
|
|
||
|
|
PROMPT Library Cache statistics for SQL and PL/SQL
|
||
|
|
PROMPT ===========================================
|
||
|
|
|
||
|
|
SELECT NAMESPACE,
|
||
|
|
GETS,
|
||
|
|
ROUND(GETHITRATIO*100,1) GETHITRATIO,
|
||
|
|
PINS,
|
||
|
|
ROUND(PINHITRATIO*100,1) PINHITRATIO,
|
||
|
|
RELOADS,
|
||
|
|
ROUND((PINS-RELOADS)*100/DECODE(NVL(PINS,0),0,1,PINS),1) PINRELOADRATIO,
|
||
|
|
INVALIDATIONS
|
||
|
|
FROM V$LIBRARYCACHE
|
||
|
|
WHERE NAMESPACE IN ('SQL AREA',
|
||
|
|
'TABLE/PROCEDURE',
|
||
|
|
'BODY',
|
||
|
|
'TRIGGER');
|
||
|
|
PROMPT
|
||
|
|
PROMPT
|
||
|
|
PROMPT Related Shared Pool SGA Structures
|
||
|
|
PROMPT ==================================
|
||
|
|
|
||
|
|
SELECT NAME POOL_NAME,
|
||
|
|
BYTES POOL_BYTES,
|
||
|
|
ROUND(BYTES/1048576,1) POOL_MBYTES
|
||
|
|
FROM V$SGASTAT
|
||
|
|
WHERE POOL = 'shared pool'
|
||
|
|
AND NAME IN ('free memory',
|
||
|
|
'sessions',
|
||
|
|
'dictionary cache',
|
||
|
|
'library cache',
|
||
|
|
'sql area')
|
||
|
|
UNION ALL
|
||
|
|
SELECT 'Shared Pool Reserved',
|
||
|
|
TO_NUMBER(VALUE),
|
||
|
|
ROUND(TO_NUMBER(VALUE)/1048576,1)
|
||
|
|
FROM V$PARAMETER
|
||
|
|
WHERE NAME = 'shared_pool_reserved_size'
|
||
|
|
UNION ALL
|
||
|
|
SELECT 'Total Shared Pool',
|
||
|
|
TO_NUMBER(VALUE),
|
||
|
|
ROUND(TO_NUMBER(VALUE)/1048576,1)
|
||
|
|
FROM V$PARAMETER
|
||
|
|
WHERE NAME = 'shared_pool_size';
|
||
|
|
|
||
|
|
PROMPT
|
||
|
|
PROMPT
|
||
|
|
PROMPT SQL Area grand totals per category
|
||
|
|
PROMPT ==================================
|
||
|
|
|
||
|
|
SELECT :v_count SQL_COUNT,
|
||
|
|
:v_buffer_gets S_BUFFER_GETS,
|
||
|
|
:v_disk_reads S_DISK_READS,
|
||
|
|
:v_executions S_EXECUTIONS,
|
||
|
|
:v_bg_per_exec S_BG_PER_EXEC,
|
||
|
|
:v_dr_per_exec S_DR_PER_EXEC,
|
||
|
|
TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI') DATE_TIME,
|
||
|
|
:v_istartup ISTARTUP
|
||
|
|
FROM DUAL;
|
||
|
|
|
||
|
|
PROMPT
|
||
|
|
PROMPT
|
||
|
|
PROMPT Top &&p_top most expensive SQL Statements per category
|
||
|
|
PROMPT =================================================
|
||
|
|
|
||
|
|
BREAK ON DUMMY;
|
||
|
|
COMPUTE SUM OF BUFFER_GETS P_BUFFER_GETS ON DUMMY;
|
||
|
|
SELECT NULL DUMMY,
|
||
|
|
T_BUFFER_GETS,
|
||
|
|
ROW_NUM,
|
||
|
|
BUFFER_GETS,
|
||
|
|
P_BUFFER_GETS,
|
||
|
|
SUBSTR(USERNAME,1,10) USERNAME,
|
||
|
|
SUBSTR(MODULE||' '||ACTION,1,40) MODULE_ACTION,
|
||
|
|
SQL_TEXT SQL_TEXT_L1
|
||
|
|
FROM COE_SQLAREA
|
||
|
|
WHERE T_BUFFER_GETS < TO_NUMBER('&&p_top')+1
|
||
|
|
ORDER BY T_BUFFER_GETS;
|
||
|
|
|
||
|
|
COMPUTE SUM OF DISK_READS P_DISK_READS ON DUMMY;
|
||
|
|
SELECT NULL DUMMY,
|
||
|
|
T_DISK_READS,
|
||
|
|
ROW_NUM,
|
||
|
|
DISK_READS,
|
||
|
|
P_DISK_READS,
|
||
|
|
SUBSTR(USERNAME,1,10) USERNAME,
|
||
|
|
SUBSTR(MODULE||' '||ACTION,1,40) MODULE_ACTION,
|
||
|
|
SQL_TEXT SQL_TEXT_L1
|
||
|
|
FROM COE_SQLAREA
|
||
|
|
WHERE T_DISK_READS < TO_NUMBER('&&p_top')+1
|
||
|
|
ORDER BY T_DISK_READS;
|
||
|
|
|
||
|
|
COMPUTE SUM OF EXECUTIONS P_EXECUTIONS ON DUMMY;
|
||
|
|
SELECT NULL DUMMY,
|
||
|
|
T_EXECUTIONS,
|
||
|
|
ROW_NUM,
|
||
|
|
EXECUTIONS,
|
||
|
|
P_EXECUTIONS,
|
||
|
|
SUBSTR(USERNAME,1,10) USERNAME,
|
||
|
|
SUBSTR(MODULE||' '||ACTION,1,40) MODULE_ACTION,
|
||
|
|
SQL_TEXT SQL_TEXT_L1
|
||
|
|
FROM COE_SQLAREA
|
||
|
|
WHERE T_EXECUTIONS < TO_NUMBER('&&p_top')+1
|
||
|
|
ORDER BY T_EXECUTIONS;
|
||
|
|
|
||
|
|
COMPUTE SUM OF BG_PER_EXEC P_BG_PER_EXEC ON DUMMY;
|
||
|
|
SELECT NULL DUMMY,
|
||
|
|
T_BG_PER_EXEC,
|
||
|
|
ROW_NUM,
|
||
|
|
BG_PER_EXEC,
|
||
|
|
P_BG_PER_EXEC,
|
||
|
|
SUBSTR(USERNAME,1,10) USERNAME,
|
||
|
|
SUBSTR(MODULE||' '||ACTION,1,40) MODULE_ACTION,
|
||
|
|
SQL_TEXT SQL_TEXT_L1
|
||
|
|
FROM COE_SQLAREA
|
||
|
|
WHERE T_BG_PER_EXEC < TO_NUMBER('&&p_top')+1
|
||
|
|
ORDER BY T_BG_PER_EXEC;
|
||
|
|
|
||
|
|
COMPUTE SUM OF DR_PER_EXEC P_DR_PER_EXEC ON DUMMY;
|
||
|
|
SELECT NULL DUMMY,
|
||
|
|
T_DR_PER_EXEC,
|
||
|
|
ROW_NUM,
|
||
|
|
DR_PER_EXEC,
|
||
|
|
P_DR_PER_EXEC,
|
||
|
|
SUBSTR(USERNAME,1,10) USERNAME,
|
||
|
|
SUBSTR(MODULE||' '||ACTION,1,40) MODULE_ACTION,
|
||
|
|
SQL_TEXT SQL_TEXT_L1
|
||
|
|
FROM COE_SQLAREA
|
||
|
|
WHERE T_DR_PER_EXEC < TO_NUMBER('&&p_top')+1
|
||
|
|
ORDER BY T_DR_PER_EXEC;
|
||
|
|
|
||
|
|
PROMPT
|
||
|
|
PROMPT Note(*): Percentage of grand total for SQL Area, per resource category
|
||
|
|
PROMPT
|
||
|
|
|
||
|
|
PROMPT
|
||
|
|
PROMPT Summary of SQL Statements on the 5 Top &&p_top lists
|
||
|
|
PROMPT ===============================================
|
||
|
|
|
||
|
|
BREAK ON DUMMY;
|
||
|
|
COMPUTE SUM OF BUFFER_GETS P_BUFFER_GETS DISK_READS P_DISK_READS -
|
||
|
|
EXECUTIONS P_EXECUTIONS BG_PER_EXEC P_BG_PER_EXEC -
|
||
|
|
DR_PER_EXEC P_DR_PER_EXEC ON DUMMY;
|
||
|
|
SELECT NULL DUMMY,
|
||
|
|
CS.ROW_NUM,
|
||
|
|
CS.BUFFER_GETS,
|
||
|
|
CS.P_BUFFER_GETS,
|
||
|
|
CS.DISK_READS,
|
||
|
|
CS.P_DISK_READS,
|
||
|
|
CS.EXECUTIONS,
|
||
|
|
CS.P_EXECUTIONS,
|
||
|
|
CS.BG_PER_EXEC,
|
||
|
|
CS.P_BG_PER_EXEC,
|
||
|
|
CS.DR_PER_EXEC,
|
||
|
|
CS.P_DR_PER_EXEC
|
||
|
|
FROM COE_SQLAREA CS
|
||
|
|
WHERE ( CS.T_BUFFER_GETS < TO_NUMBER('&&p_top')+1 OR
|
||
|
|
CS.T_DISK_READS < TO_NUMBER('&&p_top')+1 OR
|
||
|
|
CS.T_EXECUTIONS < TO_NUMBER('&&p_top')+1 OR
|
||
|
|
CS.T_BG_PER_EXEC < TO_NUMBER('&&p_top')+1 OR
|
||
|
|
CS.T_DR_PER_EXEC < TO_NUMBER('&&p_top')+1 )
|
||
|
|
ORDER BY CS.ROW_NUM;
|
||
|
|
|
||
|
|
PROMPT
|
||
|
|
PROMPT Note(*): Percentage of grand total for SQL Area, per resource category
|
||
|
|
PROMPT
|
||
|
|
|
||
|
|
SELECT CS.ROW_NUM,
|
||
|
|
CS.T_BUFFER_GETS,
|
||
|
|
CS.T_DISK_READS,
|
||
|
|
CS.T_EXECUTIONS,
|
||
|
|
CS.T_BG_PER_EXEC,
|
||
|
|
CS.T_DR_PER_EXEC,
|
||
|
|
CS.HASH_VALUE,
|
||
|
|
SUBSTR(CS.USERNAME,1,10) USERNAME,
|
||
|
|
SUBSTR(CS.MODULE||' '||CS.ACTION,1,40) MODULE_ACTION
|
||
|
|
FROM COE_SQLAREA CS
|
||
|
|
WHERE ( CS.T_BUFFER_GETS < TO_NUMBER('&&p_top')+1 OR
|
||
|
|
CS.T_DISK_READS < TO_NUMBER('&&p_top')+1 OR
|
||
|
|
CS.T_EXECUTIONS < TO_NUMBER('&&p_top')+1 OR
|
||
|
|
CS.T_BG_PER_EXEC < TO_NUMBER('&&p_top')+1 OR
|
||
|
|
CS.T_DR_PER_EXEC < TO_NUMBER('&&p_top')+1 )
|
||
|
|
ORDER BY CS.ROW_NUM;
|
||
|
|
|
||
|
|
PROMPT
|
||
|
|
PROMPT
|
||
|
|
PROMPT Full text of identified expensive SQL Statements ordered by SQL ID
|
||
|
|
PROMPT ==================================================================
|
||
|
|
|
||
|
|
BREAK ON ROW_NUM SKIP 1 ON T_BUFFER_GETS ON T_DISK_READS -
|
||
|
|
ON T_EXECUTIONS ON T_BG_PER_EXEC ON T_DR_PER_EXEC -
|
||
|
|
ON USERNAME ON MODULE_ACTION;
|
||
|
|
SELECT CS.ROW_NUM,
|
||
|
|
ST.PIECE,
|
||
|
|
ST.SQL_TEXT,
|
||
|
|
CS.T_BUFFER_GETS,
|
||
|
|
CS.T_DISK_READS,
|
||
|
|
CS.T_EXECUTIONS,
|
||
|
|
CS.T_BG_PER_EXEC,
|
||
|
|
CS.T_DR_PER_EXEC,
|
||
|
|
SUBSTR(CS.USERNAME,1,10) USERNAME,
|
||
|
|
SUBSTR(CS.MODULE||' '||CS.ACTION,1,40) MODULE_ACTION
|
||
|
|
FROM COE_SQLAREA CS,
|
||
|
|
V$SQLTEXT ST
|
||
|
|
WHERE
|
||
|
|
CS.HASH_VALUE = ST.HASH_VALUE
|
||
|
|
AND CS.ADDRESS = ST.ADDRESS
|
||
|
|
AND ( CS.T_BUFFER_GETS < TO_NUMBER('&&p_top')+1 OR
|
||
|
|
CS.T_DISK_READS < TO_NUMBER('&&p_top')+1 OR
|
||
|
|
CS.T_EXECUTIONS < TO_NUMBER('&&p_top')+1 OR
|
||
|
|
CS.T_BG_PER_EXEC < TO_NUMBER('&&p_top')+1 OR
|
||
|
|
CS.T_DR_PER_EXEC < TO_NUMBER('&&p_top')+1 )
|
||
|
|
ORDER BY CS.ROW_NUM, ST.PIECE;
|
||
|
|
|
||
|
|
PROMPT coe_sqlarea_80.txt has been generated.
|
||
|
|
PROMPT
|
||
|
|
PROMPT Recover the coe_sqlarea_80.txt spool file, compress into file
|
||
|
|
PROMPT coesqlarea.zip and send/upload the resulting coesqlarea.zip file for
|
||
|
|
PROMPT further analysis.
|
||
|
|
PROMPT
|
||
|
|
PROMPT If you wish to print the spool file nicely, open it in Wordpad or Word.
|
||
|
|
PROMPT Use File -> Page Setup (menu option) to change Orientation to Landscape.
|
||
|
|
PROMPT Using same menu option make all 4 Margins 0.2". Exit this menu option.
|
||
|
|
PROMPT Do a 'Select All' (Ctrl+A) and change Font to 'Courier New' Size 8.
|
||
|
|
PROMPT
|
||
|
|
SPOOL off;
|
||
|
|
UNDEFINE p_top,p_factor_th;
|
||
|
|
SET ver on feed on trims off long 80 pages 24 lin 80 feed on;
|
||
|
|
SET sqlp SQL> sqln on;
|
||
|
|
|
||
|
|
|