support/oracle
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
oracle/vg/coe_sqlarea_session.sql
root 93039b8489 2026-03-12 20:23:15
2026-03-12 21:23:47 +01:00

1200 lines
34 KiB
SQL
Raw Permalink Blame History

Set echo off
/*$Header: coe_sqlarea_session.sql 8.1-9.2 169630.1 2003/11/21 csierra coe $*/
SET term off;
SET term off ver off feed off trims on pages 0 lin 4050 long 32767 longc 78;
SET recsep off sqlp '' sqln off serveroutput on size 1000000 num 14;
DEFINE p_top = 5;
VARIABLE v_top NUMBER;
VARIABLE v_saddr VARCHAR2(16);
VARIABLE v_sid NUMBER;
VARIABLE v_serial# NUMBER;
VARIABLE v_paddr VARCHAR2(16);
VARIABLE v_command NUMBER;
VARIABLE v_taddr VARCHAR2(16);
VARIABLE v_lockwait VARCHAR2(16);
VARIABLE v_prev_sql_addr VARCHAR2(16);
VARIABLE v_prev_hash_value NUMBER;
VARIABLE v_sql_address VARCHAR2(16);
VARIABLE v_sql_hash_value NUMBER;
VARIABLE v_row_wait_obj# NUMBER;
VARIABLE v_row_wait_file# NUMBER;
VARIABLE v_row_wait_block# NUMBER;
VARIABLE v_row_wait_row# NUMBER;
VARIABLE v_latchwait VARCHAR2(16);
VARIABLE v_latchspin VARCHAR2(16);
VARIABLE v_object_owner VARCHAR2(30);
VARIABLE v_object_name VARCHAR2(128);
VARIABLE v_blocker_sid NUMBER;
VARIABLE v_blocker_paddr VARCHAR2(16);
VARIABLE v_blocker_taddr VARCHAR2(16);
ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
SET term on;
PROMPT
PROMPT ========================================================================
PROMPT bde_session.sql - Expensive SQL and Resource usage for one Session
PROMPT ========================================================================
PROMPT
PROMPT Usage:
PROMPT sqlplus apps/apps
PROMPT SQL> START bde_session.sql <sid>
PROMPT
BEGIN
:v_top := TO_NUMBER('&&p_top');
SELECT saddr,
sid,
serial#,
paddr,
command,
taddr,
lockwait,
prev_sql_addr,
prev_hash_value,
sql_address,
sql_hash_value,
row_wait_obj#,
row_wait_file#,
row_wait_block#,
row_wait_row#
INTO :v_saddr,
:v_sid,
:v_serial#,
:v_paddr,
:v_command,
:v_taddr,
:v_lockwait,
:v_prev_sql_addr,
:v_prev_hash_value,
:v_sql_address,
:v_sql_hash_value,
:v_row_wait_obj#,
:v_row_wait_file#,
:v_row_wait_block#,
:v_row_wait_row#
FROM v$session
WHERE sid = TO_NUMBER('&&sid')
AND rownum = 1;
SELECT latchwait,
latchspin
INTO :v_latchwait,
:v_latchspin
FROM v$process
WHERE addr = :v_paddr
AND rownum = 1;
IF :v_row_wait_obj# = -1 THEN
:v_object_owner := 'SYS';
:v_object_name := 'DUAL';
ELSE
SELECT owner,
object_name
INTO :v_object_owner,
:v_object_name
FROM all_objects
WHERE object_id = :v_row_wait_obj#
AND rownum = 1;
END IF;
IF :v_lockwait IS NOT NULL THEN
SELECT blocker.sid
INTO :v_blocker_sid
FROM v$lock blocker
WHERE blocker.sid <> :v_sid
AND (blocker.type, blocker.id1, blocker.id2) IN
(SELECT locked.type, locked.id1, locked.id2
FROM v$lock locked
WHERE locked.sid = :v_sid
AND locked.kaddr = :v_lockwait)
AND rownum = 1;
END IF;
IF :v_blocker_sid IS NOT NULL THEN
SELECT paddr,
taddr
INTO :v_blocker_paddr,
:v_blocker_taddr
FROM v$session
WHERE sid = :v_blocker_sid
AND rownum = 1;
END IF;
END;
/
CLEAR BREAKS COLUMNS;
COLUMN p_saddr NEW_VALUE p_saddr FORMAT A16;
COLUMN p_sid NEW_VALUE p_sid FORMAT A8;
COLUMN p_serial NEW_VALUE p_serial FORMAT A8;
COLUMN p_paddr NEW_VALUE p_paddr FORMAT A16;
COLUMN p_command NEW_VALUE p_command FORMAT A8;
COLUMN p_taddr NEW_VALUE p_taddr FORMAT A16;
COLUMN p_lockwait NEW_VALUE p_lockwait FORMAT A16;
COLUMN p_prev_sql_addr NEW_VALUE p_prev_sql_addr FORMAT A16;
COLUMN p_prev_hash_value NEW_VALUE p_prev_hash_value FORMAT A12;
COLUMN p_sql_address NEW_VALUE p_sql_address FORMAT A16;
COLUMN p_sql_hash_value NEW_VALUE p_sql_hash_value FORMAT A12;
COLUMN p_row_wait_obj NEW_VALUE p_row_wait_obj FORMAT A8;
COLUMN p_row_wait_file NEW_VALUE p_row_wait_file FORMAT A8;
COLUMN p_row_wait_block NEW_VALUE p_row_wait_block FORMAT A8;
COLUMN p_row_wait_row NEW_VALUE p_row_wait_row FORMAT A8;
COLUMN p_latchwait NEW_VALUE p_latchwait FORMAT A16;
COLUMN p_latchspin NEW_VALUE p_latchspin FORMAT A16;
COLUMN p_object_owner NEW_VALUE p_object_owner FORMAT A30;
COLUMN p_object_name NEW_VALUE p_object_name FORMAT A128;
COLUMN p_blocker_sid NEW_VALUE p_blocker_sid FORMAT A8;
COLUMN p_blocker_paddr NEW_VALUE p_blocker_paddr FORMAT A16;
COLUMN p_blocker_taddr NEW_VALUE p_blocker_taddr FORMAT A16;
COLUMN text FORMAT A78 WOR;
SELECT TO_CHAR(:v_sid) p_sid,
TO_CHAR(:v_serial#) p_serial,
:v_saddr p_saddr,
:v_paddr p_paddr,
TO_CHAR(:v_command) p_command,
:v_taddr p_taddr,
:v_lockwait p_lockwait,
:v_prev_sql_addr p_prev_sql_addr,
TO_CHAR(:v_prev_hash_value) p_prev_hash_value,
:v_sql_address p_sql_address,
TO_CHAR(:v_sql_hash_value) p_sql_hash_value,
TO_CHAR(:v_row_wait_obj#) p_row_wait_obj,
TO_CHAR(:v_row_wait_file#) p_row_wait_file,
TO_CHAR(:v_row_wait_block#) p_row_wait_block,
TO_CHAR(:v_row_wait_row#) p_row_wait_row,
:v_latchwait p_latchwait,
:v_latchspin p_latchspin,
:v_object_owner p_object_owner,
:v_object_name p_object_name,
TO_CHAR(:v_blocker_sid) p_blocker_sid,
:v_blocker_paddr p_blocker_paddr,
:v_blocker_taddr p_blocker_taddr
FROM dual;
PROMPT
PROMPT Creating COE staging objects...
DROP TABLE coe_sqlarea_&&p_sid;
CREATE TABLE coe_sqlarea_&&p_sid
(hash_value NUMBER,address RAW(20),buffer_gets$ NUMBER,disk_reads$ NUMBER,
mem NUMBER,executions$ NUMBER,bg_per_exec NUMBER,dr_per_exec NUMBER,
mem_per_exec NUMBER,parsing_user_id NUMBER,module VARCHAR2(64),
action VARCHAR2(64),sql_text VARCHAR2(64),username VARCHAR2(30),
row_num NUMBER,top_bg NUMBER,top_dr NUMBER,top_mem NUMBER,top_exec NUMBER,
top_bgpe NUMBER,top_drpe NUMBER,top_mempe NUMBER, bde_x VARCHAR2(3))
NOLOGGING CACHE;
DROP TABLE coe_sqltext_&&p_sid;
CREATE TABLE coe_sqltext_&&p_sid
(row_num NUMBER,piece NUMBER,sql_text VARCHAR2(64)) NOLOGGING CACHE;
DROP TABLE coe_text_&&p_sid;
CREATE TABLE coe_text_&&p_sid
(row_num NUMBER,text CLOB) NOLOGGING CACHE;
DROP TABLE bde_$values_&&p_sid;
CREATE TABLE bde_$values_&&p_sid
(column_id NUMBER,column_name VARCHAR2(30),column_values VARCHAR2(4000))
NOLOGGING CACHE;
DROP TABLE bde_$selection_&&p_sid;
CREATE TABLE bde_$selection_&&p_sid AS
SELECT * FROM dual;
PROMPT WARNING: "ORA-00942: table or view does not exist" ARE EXPECTED, PLEASE WAIT...
INSERT INTO coe_sqlarea_&&p_sid
SELECT sa.hash_value,
sa.address,
ABS(sa.buffer_gets),
ABS(sa.disk_reads),
ABS(sa.sharable_mem)+ABS(sa.persistent_mem)+ABS(sa.runtime_mem),
ABS(sa.executions),
ROUND(ABS(sa.buffer_gets)/
DECODE(NVL(ABS(sa.executions),0),0,1,ABS(sa.executions))),
ROUND(ABS(sa.disk_reads)/
DECODE(NVL(ABS(sa.executions),0),0,1,ABS(sa.executions))),
ROUND((ABS(sa.sharable_mem)+ABS(sa.persistent_mem)+ABS(sa.runtime_mem))/
DECODE(NVL(ABS(sa.executions),0),0,1,ABS(sa.executions))),
sa.parsing_user_id,
sa.module,
sa.action,
SUBSTR(sa.sql_text,1,64),
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
FROM v$sqlarea sa,
v$open_cursor oc
WHERE oc.saddr = :v_saddr
AND oc.sid = :v_sid
AND sa.hash_value = oc.hash_value
AND sa.address = oc.address;
UPDATE coe_sqlarea_&&p_sid cs
SET username = (SELECT username
FROM all_users
WHERE user_id=cs.parsing_user_id);
UPDATE coe_sqlarea_&&p_sid cs
SET bde_x = 'YES'
WHERE ( cs.hash_value = TO_NUMBER('&&p_sql_hash_value')
AND cs.address = '&&p_sql_address')
OR ( cs.hash_value = TO_NUMBER('&&p_prev_hash_value')
AND cs.address = '&&p_prev_sql_addr');
DECLARE
c_top NUMBER;
CURSOR c1 IS
SELECT rowid
FROM coe_sqlarea_&&p_sid
ORDER BY buffer_gets$ DESC;
CURSOR c2 IS
SELECT rowid
FROM coe_sqlarea_&&p_sid
ORDER BY disk_reads$ DESC,
buffer_gets$ DESC;
CURSOR c3 IS
SELECT rowid
FROM coe_sqlarea_&&p_sid
ORDER BY mem DESC,
buffer_gets$ DESC;
CURSOR c4 IS
SELECT rowid
FROM coe_sqlarea_&&p_sid
ORDER BY executions$ DESC,
buffer_gets$ DESC;
CURSOR c5 IS
SELECT rowid
FROM coe_sqlarea_&&p_sid
ORDER BY bg_per_exec DESC;
CURSOR c6 IS
SELECT rowid
FROM coe_sqlarea_&&p_sid
ORDER BY dr_per_exec DESC,
buffer_gets$ DESC;
CURSOR c7 IS
SELECT rowid
FROM coe_sqlarea_&&p_sid
ORDER BY mem_per_exec DESC,
buffer_gets$ DESC;
BEGIN
c_top := 1;
FOR t IN c1 LOOP
UPDATE coe_sqlarea_&&p_sid cs
SET top_bg = c_top
WHERE rowid = t.rowid;
c_top := c_top+1;
END LOOP;
c_top := 1;
FOR t IN c2 LOOP
UPDATE coe_sqlarea_&&p_sid cs
SET top_dr = c_top
WHERE rowid = t.rowid;
c_top := c_top+1;
END LOOP;
c_top := 1;
FOR t IN c3 LOOP
UPDATE coe_sqlarea_&&p_sid cs
SET top_mem = c_top
WHERE rowid = t.rowid;
c_top := c_top+1;
END LOOP;
c_top := 1;
FOR t IN c4 LOOP
UPDATE coe_sqlarea_&&p_sid cs
SET top_exec = c_top
WHERE rowid = t.rowid;
c_top := c_top+1;
END LOOP;
c_top := 1;
FOR t IN c5 LOOP
UPDATE coe_sqlarea_&&p_sid cs
SET row_num = c_top,
top_bgpe = c_top
WHERE rowid = t.rowid;
c_top := c_top+1;
END LOOP;
c_top := 1;
FOR t IN c6 LOOP
UPDATE coe_sqlarea_&&p_sid cs
SET top_drpe = c_top
WHERE rowid = t.rowid;
c_top := c_top+1;
END LOOP;
c_top := 1;
FOR t IN c7 LOOP
UPDATE coe_sqlarea_&&p_sid cs
SET top_mempe = c_top
WHERE rowid = t.rowid;
c_top := c_top+1;
END LOOP;
END;
/
UPDATE coe_sqlarea_&&p_sid cs
SET cs.bde_x = 'YES'
WHERE cs.top_bgpe < :v_top+1
AND cs.username <> 'SYS'
AND NOT UPPER(cs.sql_text) LIKE '%BEGIN%'
AND NOT UPPER(cs.sql_text) LIKE '%DECLARE%';
CREATE OR REPLACE PACKAGE bde_$v2_&&p_sid AS
PROCEDURE format_values
( num_rows_in IN NUMBER,
column_length_in IN NUMBER );
END bde_$v2_&&p_sid;
/
CREATE OR REPLACE PACKAGE BODY bde_$v2_&&p_sid AS
PROCEDURE format_values
( num_rows_in IN NUMBER,
column_length_in IN NUMBER )
IS
v_sql VARCHAR2(2000);
CURSOR columns_cursor IS
SELECT column_name
FROM bde_$values_&&p_sid;
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE bde_$selection_&&p_sid CACHE';
EXECUTE IMMEDIATE 'TRUNCATE TABLE bde_$values_&&p_sid';
EXECUTE IMMEDIATE 'INSERT INTO bde_$values_&&p_sid '||
'SELECT column_id, column_name, NULL '||
'FROM user_tab_columns '||
'WHERE table_name = ''BDE_$SELECTION_&&p_sid''';
FOR i IN 1..num_rows_in LOOP
FOR columns_record IN columns_cursor LOOP
v_sql:='UPDATE bde_$values_&&p_sid '||
'SET column_values = column_values || '||
'( SELECT RPAD(SUBSTR("'||columns_record.column_name||
'",1,'||TO_CHAR(column_length_in)||')||'' '','||
TO_CHAR(column_length_in+1)||') '||
'FROM bde_$selection_&&p_sid '||
'WHERE row_$num = '||TO_CHAR(i)||' ) '||
'WHERE column_name = '''||columns_record.column_name||'''';
BEGIN
EXECUTE IMMEDIATE v_sql;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR('*** ERROR: '||v_sql,1,255));
END;
END LOOP;
END LOOP;
END format_values;
END bde_$v2_&&p_sid;
/
SET term on;
PROMPT
PROMPT Storing SQL Text of selected statements into staging table...
SET term off;
INSERT INTO coe_sqltext_&&p_sid
SELECT DISTINCT
cs.row_num,
st.piece,
st.sql_text
FROM coe_sqlarea_&&p_sid cs,
v$sqltext st
WHERE
( cs.top_bg <= :v_top
OR cs.top_dr <= :v_top
OR cs.top_mem <= :v_top
OR cs.top_exec <= :v_top
OR cs.top_bgpe <= :v_top
OR cs.top_drpe <= :v_top
OR cs.top_mempe <= :v_top
OR cs.bde_x = 'YES'
)
AND cs.hash_value = st.hash_value
AND cs.address = st.address;
SET term on;
PROMPT
PROMPT Massaging SQL Text of expensive SQL (Top LR) to create Text files...
SET term off;
DECLARE
c_rownum NUMBER;
c_text VARCHAR2(32767);
CURSOR c1 IS
SELECT row_num
FROM coe_sqlarea_&&p_sid
WHERE bde_x = 'YES'
ORDER BY row_num;
CURSOR c2 IS
SELECT sql_text
FROM coe_sqltext_&&p_sid
WHERE row_num = c_rownum
ORDER BY piece;
BEGIN
FOR t1 IN c1 LOOP
c_rownum := t1.row_num;
c_text := NULL;
FOR t2 IN c2 LOOP
c_text := c_text||t2.sql_text;
END LOOP;
INSERT INTO coe_text_&&p_sid
VALUES (t1.row_num,c_text);
END LOOP;
END;
/
COMMIT;
SET term on;
PROMPT
PROMPT Extracting expensive SQL (Top LR per Exec) into O/S Text files...
PROMPT
SET term off;
SPOOL coe_sql_txt_&&p_sid..sql;
DECLARE
CURSOR c1 IS
SELECT TO_CHAR(row_num) row_num
FROM coe_sqlarea_&&p_sid
WHERE bde_x = 'YES'
ORDER BY row_num;
BEGIN
FOR t1 IN c1 LOOP
DBMS_OUTPUT.PUT_LINE('SPOOL sql_'||
TO_CHAR(:v_sid)||
'_'||t1.row_num||'.txt');
DBMS_OUTPUT.PUT_LINE('SELECT text '||
'FROM coe_text_&&p_sid '||
'WHERE row_num = '||t1.row_num||';');
DBMS_OUTPUT.PUT_LINE('SPOOL OFF;');
END LOOP;
END;
/
SPOOL OFF;
START coe_sql_txt_&&p_sid..sql;
SET term on;
PROMPT
PROMPT Creating bde_start_x.sql script...
PROMPT
SET term off;
SPOOL bde_start_x_&&p_sid..sql;
DECLARE
CURSOR c1 IS
SELECT TO_CHAR(row_num) row_num
FROM coe_sqlarea_&&p_sid
WHERE bde_x = 'YES'
ORDER BY row_num;
BEGIN
FOR t1 IN c1 LOOP
DBMS_OUTPUT.PUT_LINE('START bde_x.sql sql_'||
TO_CHAR(:v_sid)||
'_'||t1.row_num||'.txt;');
END LOOP;
END;
/
SPOOL OFF;
COLUMN SID FORMAT 99999999;
COLUMN SESSION_ID FORMAT 9999999999;
COLUMN SEQ# FORMAT 99999999;
COLUMN EVENT FORMAT A40;
COLUMN ORACLE_USERNAME FORMAT A15;
COLUMN WAIT_TIME FORMAT 99999999 HEADING 'WAIT|TIME';
COLUMN SECONDS_IN_WAIT FORMAT 99999999 HEADING 'SECONDS|IN WAIT';
COLUMN CLASS FORMAT A20;
COLUMN XIDUSN FORMAT 99999999;
COLUMN XIDSLOT FORMAT 99999999;
COLUMN XIDSQN FORMAT 99999999;
COLUMN OBJECT_NAME FORMAT A50;
COLUMN BUFFER_GETS$ FORMAT 99,999,999,999 -
HEADING 'Total|Logical Reads|(Buffer Gets)';
COLUMN DISK_READS$ FORMAT 99,999,999,999 -
HEADING 'Total|Physical Reads|(Disk Reads)';
COLUMN MEM FORMAT 99,999,999,999 -
HEADING 'Total|Memory|(bytes)';
COLUMN EXECUTIONS$ FORMAT 99,999,999,999 -
HEADING 'Total|Number of|Executions';
COLUMN BG_PER_EXEC FORMAT 99,999,999,999 -
HEADING 'Logical Reads|per Execution|(db blocks)';
COLUMN DR_PER_EXEC FORMAT 99,999,999,999 -
HEADING 'Physical Reads|per Execution|(db blocks)';
COLUMN MEM_PER_EXEC FORMAT 99,999,999,999 -
HEADING 'Memory|per Execution|(bytes)';
COLUMN TOP_BGPE FORMAT 9999 -
HEADING 'Top|LR|per|Exec';
COLUMN TOP_DRPE FORMAT 9999 -
HEADING 'Top|PR|per|Exec';
COLUMN TOP_MEMPE FORMAT 9999 -
HEADING 'Top|Mem|per|Exec';
COLUMN TOP_EXEC FORMAT 9999 -
HEADING 'Top|Num|of|Exec';
COLUMN TOP_BG FORMAT 9999 -
HEADING 'Top|Logc|Read|LR'
COLUMN TOP_DR FORMAT 9999 -
HEADING 'Top|Phys|Read|PR';
COLUMN TOP_MEM FORMAT 9999 -
HEADING 'Top|Mem';
COLUMN ROW_NUM FORMAT 999999 HEADING 'SQL ID';
COLUMN USERNAME FORMAT A10 HEADING 'User';
COLUMN MODULE_ACTION FORMAT A50 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 SQLADDRESS FORMAT A16 HEADING 'Address';
COLUMN PIECE NOPRINT;
COLUMN DUMMY NOPRINT;
SET term on;
PROMPT
PROMPT Generating Report (bde_session_&&p_sid..txt spool file)...
PROMPT
PROMPT
SET term off;
SPOOL bde_session_&&p_sid..txt;
SET term on recsep wr;
SET pages 0;
PROMPT bde_session.sql 8.1-9.0 169630.1 2002/06/01 SID: &&p_sid
PROMPT
PROMPT V$SESSION - Session ( &&p_sid &&p_blocker_sid )
PROMPT ===================
PROMPT
DROP TABLE bde_$selection_&&p_sid;
CREATE TABLE bde_$selection_&&p_sid AS
SELECT 1 row_$num,
v.*
FROM v$session v
WHERE v.saddr = '&&p_saddr'
AND v.sid = TO_NUMBER('&&p_sid')
AND v.serial# = TO_NUMBER('&&p_serial')
UNION ALL
SELECT 2 row_$num,
v.*
FROM v$session v
WHERE v.sid = TO_NUMBER('&&p_blocker_sid');
EXEC bde_$v2_&&p_sid..format_values(num_rows_in => 2, column_length_in => 30);
SELECT SUBSTR(column_name,1,30) column_name,
column_values
FROM bde_$values_&&p_sid
WHERE TRIM(column_values) IS NOT NULL
AND column_name <> 'ROW_$NUM'
ORDER BY
column_id;
PROMPT
SELECT RPAD('COMMAND (in progress): '||TO_CHAR(action),31)||name
FROM audit_actions
WHERE action = :v_command;
PROMPT
SELECT RPAD('ROW_WAIT_OBJ#: '||TO_CHAR(:v_row_wait_obj#),31)||
owner||'.'||object_name||' ('||object_type||') ROWID: '||
DBMS_ROWID.ROWID_CREATE(1,
NVL(TO_NUMBER('&&p_row_wait_obj'),0),
NVL(TO_NUMBER('&&p_row_wait_file'),0),
NVL(TO_NUMBER('&&p_row_wait_block'),0),
NVL(TO_NUMBER('&&p_row_wait_row'),0))
FROM all_objects
WHERE object_id = :v_row_wait_obj#;
PROMPT
DROP TABLE bde_$selection_&&p_sid;
CREATE TABLE bde_$selection_&&p_sid AS
SELECT rownum row_$num,
v.*
FROM &&p_object_owner..&&p_object_name v
WHERE TO_NUMBER('&&p_row_wait_obj') <> -1
AND v.rowid =
DBMS_ROWID.ROWID_CREATE(1,
NVL(TO_NUMBER('&&p_row_wait_obj'),0),
NVL(TO_NUMBER('&&p_row_wait_file'),0),
NVL(TO_NUMBER('&&p_row_wait_block'),0),
NVL(TO_NUMBER('&&p_row_wait_row'),0));
EXEC bde_$v2_&&p_sid..format_values(num_rows_in => 1, column_length_in => 150);
SELECT SUBSTR(column_name,1,30) column_name,
column_values
FROM bde_$values_&&p_sid
WHERE TRIM(column_values) IS NOT NULL
AND column_name <> 'ROW_$NUM'
ORDER BY
column_id;
SET pages 1000;
PROMPT
PROMPT
PROMPT V$LOCK - Locks ( &&p_lockwait &&p_sid &&p_blocker_sid )
PROMPT ==============
SELECT *
FROM v$lock
WHERE sid IN (:v_sid, :v_blocker_sid)
ORDER BY
DECODE(sid,:v_sid,1,2),
type,
id1,
id2;
PROMPT
PROMPT LMODE/REQUEST on V$LOCK rows ( &&p_sid &&p_blocker_sid )
PROMPT ============================
PROMPT 0: None
PROMPT 1: Null (NULL)
PROMPT 2: Row-S (SS) Row Share
PROMPT 3: Row-X (SX) Row Exclusive
PROMPT 4: Share (S) Share
PROMPT 5: S/Row-X (SSX) Share/Row Exclusive
PROMPT 6: Exclusive (X)
PROMPT
PROMPT
PROMPT V$LOCKED_OBJECT - Locked Objects ( &&p_sid &&p_blocker_sid )
PROMPT ================================
SELECT l.*,
o.owner||'.'||o.object_name object_name
FROM v$locked_object l,
all_objects o
WHERE l.session_id IN (:v_sid, :v_blocker_sid)
AND l.object_id = o.object_id
ORDER BY
DECODE(l.session_id,:v_sid,1,2),
l.xidusn,
l.xidslot,
l.xidsqn;
PROMPT
PROMPT
PROMPT V$SESSION_EVENT - Waits for an Event ( &&p_sid &&p_blocker_sid )
PROMPT ====================================
SELECT *
FROM v$session_event
WHERE sid IN (:v_sid, :v_blocker_sid)
ORDER BY
DECODE(sid,:v_sid,1,2),
event;
PROMPT
PROMPT
PROMPT V$SESSION_WAIT - Resources or Events waiting for ( &&p_sid &&p_blocker_sid )
PROMPT ================================================
SELECT sid,
seq#,
event,
p1raw,
p2raw,
p3raw,
p1,
p2,
p3,
wait_time,
seconds_in_wait,
state
FROM v$session_wait
WHERE sid IN (:v_sid, :v_blocker_sid)
ORDER BY
DECODE(sid,:v_sid,1,2),
event;
PROMPT
PROMPT
PROMPT V$SESSTAT - Session Statistics ( &&p_sid &&p_blocker_sid )
PROMPT ==============================
SELECT s.sid,
s.value,
n.name,
TO_CHAR(n.class)||
DECODE(n.class,1,' User',2,' Redo',4,' Enqueue',8,' Cache',
16,' OS',32,' Parallel Server',64,' SQL',128,' Debug',
72,' Cache + SQL',
NULL) class
FROM v$sesstat s,
v$statname n
WHERE s.statistic# = n.statistic#
AND s.value <> 0
AND s.sid IN (:v_sid, :v_blocker_sid)
ORDER BY
DECODE(s.sid,:v_sid,1,2),
n.name;
SET pages 0;
PROMPT
PROMPT
PROMPT V$SQLAREA - Currently Executing ( &&p_sql_address &&p_sql_hash_value )
PROMPT ===============================
PROMPT
DROP TABLE bde_$selection_&&p_sid;
CREATE TABLE bde_$selection_&&p_sid AS
SELECT rownum row_$num,
'&&p_sid' sid,
v.*
FROM v$sqlarea v
WHERE v.address = '&&p_sql_address'
AND v.hash_value = TO_NUMBER('&&p_sql_hash_value');
EXEC bde_$v2_&&p_sid..format_values(num_rows_in => 1, column_length_in => 1000);
SELECT column_values text
FROM bde_$values_&&p_sid
WHERE column_name = 'SQL_TEXT';
SELECT SUBSTR(column_name,1,30) column_name,
column_values
FROM bde_$values_&&p_sid
WHERE TRIM(column_values) IS NOT NULL
AND column_name NOT IN ('ROW_$NUM', 'SQL_TEXT')
ORDER BY
column_id;
PROMPT
PROMPT
PROMPT V$SQLAREA - Previous Execution ( &&p_prev_sql_addr &&p_prev_hash_value )
PROMPT ==============================
PROMPT
DROP TABLE bde_$selection_&&p_sid;
CREATE TABLE bde_$selection_&&p_sid AS
SELECT rownum row_$num,
'&&p_sid' sid,
v.*
FROM v$sqlarea v
WHERE v.address = '&&p_prev_sql_addr'
AND v.hash_value = TO_NUMBER('&&p_prev_hash_value');
EXEC bde_$v2_&&p_sid..format_values(num_rows_in => 1, column_length_in => 1000);
SELECT column_values text
FROM bde_$values_&&p_sid
WHERE column_name = 'SQL_TEXT';
SELECT SUBSTR(column_name,1,30) column_name,
column_values
FROM bde_$values_&&p_sid
WHERE TRIM(column_values) IS NOT NULL
AND column_name NOT IN ('ROW_$NUM', 'SQL_TEXT')
ORDER BY
column_id;
PROMPT
PROMPT
PROMPT V$PROCESS - Process ( &&p_sid &&p_paddr &&p_blocker_sid &&p_blocker_paddr )
PROMPT ===================
PROMPT
DROP TABLE bde_$selection_&&p_sid;
CREATE TABLE bde_$selection_&&p_sid AS
SELECT 1 row_$num,
'&&p_sid' sid,
v.*
FROM v$process v
WHERE v.addr = '&&p_paddr'
UNION ALL
SELECT 2 row_$num,
'&&p_blocker_sid' sid,
v.*
FROM v$process v
WHERE v.addr = '&&p_blocker_paddr';
EXEC bde_$v2_&&p_sid..format_values(num_rows_in => 2, column_length_in => 30);
SELECT SUBSTR(column_name,1,30) column_name,
column_values
FROM bde_$values_&&p_sid
WHERE TRIM(column_values) IS NOT NULL
AND column_name <> 'ROW_$NUM'
ORDER BY
column_id;
PROMPT
PROMPT
PROMPT V$TRANSACTION - Transaction ( &&p_sid &&p_taddr &&p_blocker_sid &&p_blocker_taddr )
PROMPT ===========================
PROMPT
DROP TABLE bde_$selection_&&p_sid;
CREATE TABLE bde_$selection_&&p_sid AS
SELECT 1 row_$num,
'&&p_sid' sid,
v.*
FROM v$transaction v
WHERE v.addr = '&&p_taddr'
UNION ALL
SELECT 2 row_$num,
'&&p_blocker_sid' sid,
v.*
FROM v$transaction v
WHERE v.addr = '&&p_blocker_taddr';
EXEC bde_$v2_&&p_sid..format_values(num_rows_in => 2, column_length_in => 30);
SELECT SUBSTR(column_name,1,30) column_name,
column_values
FROM bde_$values_&&p_sid
WHERE TRIM(column_values) IS NOT NULL
AND column_name <> 'ROW_$NUM'
ORDER BY
column_id;
SET pages 10000;
PROMPT
PROMPT Summary of SQL Statements linked to Session ( &&p_sid )
PROMPT ===========================================
SELECT cs.row_num,
cs.bg_per_exec,
cs.dr_per_exec,
cs.mem_per_exec,
cs.executions$,
cs.buffer_gets$,
cs.disk_reads$,
cs.mem,
cs.top_bgpe,
cs.top_drpe,
cs.top_mempe,
cs.top_exec,
cs.top_bg,
cs.top_dr,
cs.top_mem
FROM coe_sqlarea_&&p_sid cs
ORDER BY cs.row_num;
SELECT cs.row_num,
sql_text sql_text_l1,
SUBSTR(username,1,10) username,
cs.hash_value,
cs.address sqladdress,
SUBSTR(module||' '||action,1,80) module_action
FROM coe_sqlarea_&&p_sid cs
ORDER BY cs.row_num;
SET pages 0;
PROMPT
PROMPT
PROMPT Top SQL in terms of Logical Reads per Execution ( &&p_sid )
PROMPT ===============================================
PROMPT
DROP TABLE bde_$selection_&&p_sid;
CREATE TABLE bde_$selection_&&p_sid AS
SELECT cs.top_bgpe row_$num,
cs.bg_per_exec,
cs.row_num sql_id,
v.*
FROM coe_sqlarea_&&p_sid cs,
v$sqlarea v
WHERE cs.top_bgpe <= TO_NUMBER('&&p_top')
AND cs.hash_value = v.hash_value
AND cs.address = v.address
ORDER BY
cs.top_bgpe;
EXEC bde_$v2_&&p_sid..format_values(num_rows_in => :v_top, column_length_in => 30);
SELECT SUBSTR(column_name,1,30) column_name,
column_values
FROM bde_$values_&&p_sid
WHERE TRIM(column_values) IS NOT NULL
ORDER BY
column_id;
PROMPT
PROMPT
PROMPT Top SQL in terms of Physical Reads per Execution ( &&p_sid )
PROMPT ================================================
PROMPT
DROP TABLE bde_$selection_&&p_sid;
CREATE TABLE bde_$selection_&&p_sid AS
SELECT cs.top_drpe row_$num,
cs.dr_per_exec,
cs.row_num sql_id,
v.*
FROM coe_sqlarea_&&p_sid cs,
v$sqlarea v
WHERE cs.top_drpe <= TO_NUMBER('&&p_top')
AND cs.hash_value = v.hash_value
AND cs.address = v.address
ORDER BY
cs.top_drpe;
EXEC bde_$v2_&&p_sid..format_values(num_rows_in => :v_top, column_length_in => 30);
SELECT SUBSTR(column_name,1,30) column_name,
column_values
FROM bde_$values_&&p_sid
WHERE TRIM(column_values) IS NOT NULL
ORDER BY
column_id;
PROMPT
PROMPT
PROMPT Top SQL in terms of Memory per Execution ( &&p_sid )
PROMPT ========================================
PROMPT
DROP TABLE bde_$selection_&&p_sid;
CREATE TABLE bde_$selection_&&p_sid AS
SELECT cs.top_mempe row_$num,
cs.mem_per_exec,
cs.row_num sql_id,
v.*
FROM coe_sqlarea_&&p_sid cs,
v$sqlarea v
WHERE cs.top_mempe <= TO_NUMBER('&&p_top')
AND cs.hash_value = v.hash_value
AND cs.address = v.address
ORDER BY
cs.top_mempe;
EXEC bde_$v2_&&p_sid..format_values(num_rows_in => :v_top, column_length_in => 30);
SELECT SUBSTR(column_name,1,30) column_name,
column_values
FROM bde_$values_&&p_sid
WHERE TRIM(column_values) IS NOT NULL
ORDER BY
column_id;
PROMPT
PROMPT
PROMPT Top SQL in terms of Total Number of Executions ( &&p_sid )
PROMPT ==============================================
PROMPT
DROP TABLE bde_$selection_&&p_sid;
CREATE TABLE bde_$selection_&&p_sid AS
SELECT cs.top_exec row_$num,
cs.executions$,
cs.row_num sql_id,
v.*
FROM coe_sqlarea_&&p_sid cs,
v$sqlarea v
WHERE cs.top_exec <= TO_NUMBER('&&p_top')
AND cs.hash_value = v.hash_value
AND cs.address = v.address
ORDER BY
cs.top_exec;
EXEC bde_$v2_&&p_sid..format_values(num_rows_in => :v_top, column_length_in => 30);
SELECT SUBSTR(column_name,1,30) column_name,
column_values
FROM bde_$values_&&p_sid
WHERE TRIM(column_values) IS NOT NULL
ORDER BY
column_id;
PROMPT
PROMPT
PROMPT Top SQL in terms of Total Logical Reads ( &&p_sid )
PROMPT =======================================
PROMPT
DROP TABLE bde_$selection_&&p_sid;
CREATE TABLE bde_$selection_&&p_sid AS
SELECT cs.top_bg row_$num,
cs.buffer_gets$,
cs.row_num sql_id,
v.*
FROM coe_sqlarea_&&p_sid cs,
v$sqlarea v
WHERE cs.top_bg <= TO_NUMBER('&&p_top')
AND cs.hash_value = v.hash_value
AND cs.address = v.address
ORDER BY
cs.top_bg;
EXEC bde_$v2_&&p_sid..format_values(num_rows_in => :v_top, column_length_in => 30);
SELECT SUBSTR(column_name,1,30) column_name,
column_values
FROM bde_$values_&&p_sid
WHERE TRIM(column_values) IS NOT NULL
ORDER BY
column_id;
PROMPT
PROMPT
PROMPT Top SQL in terms of Total Physical Reads ( &&p_sid )
PROMPT ========================================
PROMPT
DROP TABLE bde_$selection_&&p_sid;
CREATE TABLE bde_$selection_&&p_sid AS
SELECT cs.top_dr row_$num,
cs.disk_reads$,
cs.row_num sql_id,
v.*
FROM coe_sqlarea_&&p_sid cs,
v$sqlarea v
WHERE cs.top_dr <= TO_NUMBER('&&p_top')
AND cs.hash_value = v.hash_value
AND cs.address = v.address
ORDER BY
cs.top_dr;
EXEC bde_$v2_&&p_sid..format_values(num_rows_in => :v_top, column_length_in => 30);
SELECT SUBSTR(column_name,1,30) column_name,
column_values
FROM bde_$values_&&p_sid
WHERE TRIM(column_values) IS NOT NULL
ORDER BY
column_id;
PROMPT
PROMPT
PROMPT Top SQL in terms of Total Memomy ( &&p_sid )
PROMPT ================================
PROMPT
DROP TABLE bde_$selection_&&p_sid;
CREATE TABLE bde_$selection_&&p_sid AS
SELECT cs.top_mem row_$num,
cs.mem,
cs.row_num sql_id,
v.*
FROM coe_sqlarea_&&p_sid cs,
v$sqlarea v
WHERE cs.top_mem <= TO_NUMBER('&&p_top')
AND cs.hash_value = v.hash_value
AND cs.address = v.address
ORDER BY
cs.top_mem;
EXEC bde_$v2_&&p_sid..format_values(num_rows_in => :v_top, column_length_in => 30);
SELECT SUBSTR(column_name,1,30) column_name,
column_values
FROM bde_$values_&&p_sid
WHERE TRIM(column_values) IS NOT NULL
ORDER BY
column_id;
SET pages 10000;
PROMPT
PROMPT
PROMPT Full text of identified expensive SQL Statements ordered by SQL ID ( &&p_sid )
PROMPT ==================================================================
BREAK ON ROW_NUM SKIP 1;
SELECT csa.row_num,
cst.piece,
cst.sql_text
FROM coe_sqlarea_&&p_sid csa,
coe_sqltext_&&p_sid cst
WHERE csa.row_num = cst.row_num
ORDER BY
csa.row_num,
cst.piece;
PROMPT
PROMPT bde_session_&&p_sid..txt has been generated.
PROMPT
PROMPT Recover the bde_session_&&p_sid..txt spool file.
PROMPT Consolidate and compress together with other files generated into same directory.
PROMPT Upload consolidated/compressed file bde_results.zip file for further analysis.
PROMPT On NT, files may get created under $ORACLE_HOME/bin.
PROMPT
SPOOL off;
DROP PACKAGE bde_$v2_&&p_sid;
DROP TABLE coe_sqlarea_&&p_sid;
DROP TABLE coe_sqltext_&&p_sid;
DROP TABLE coe_text_&&p_sid;
DROP TABLE bde_$values_&&p_sid;
DROP TABLE bde_$selection_&&p_sid;
SET ver on feed on trims off long 80 pages 24 lin 80 feed on;
SET sqlp SQL> sqln on serveroutput off num 10;
CLEAR BREAKS COLUMNS;
PROMPT
PROMPT Executing bde_x.sql for Expensive SQL statements
PROMPT
START bde_start_x_&&p_sid..sql;
PROMPT
COLUMN ENDEDSE FORMAT A21 HEADING 'bde_session.sql ended';
SELECT TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS') endedse FROM sys.dual;
Reference in New Issue View Git Blame Copy Permalink