Job Runs - Get nothing meaningful
Mark, August 05, 2005 - 12:26 pm UTC
Hi Tom,
I have the following Package I built based on information from the book "Oracle Wait Interface: A Practical Guide to Performance Diagnostics and Tuning" (Oracle Press - Shee, Deshpande, Gopalakrishnan 2004), an excellent book by the way.
My Table Defs are at the end:
CREATE OR REPLACE PACKAGE HT4.DC_COLLECTOR
AS
PROCEDURE dc;
FUNCTION capture_sql (
in_wer_id NUMBER
,in_hash_value IN v$session.sql_hash_value%TYPE
,in_event IN v$session_wait.event%TYPE
,in_machine IN v$session.machine%TYPE
,in_module IN v$session.module%TYPE
,in_mode IN VARCHAR2 DEFAULT NULL
)
RETURN BOOLEAN;
END;
/
CREATE OR REPLACE PACKAGE BODY HT4.DC_COLLECTOR
AS
/* global cursor */
CURSOR current_event
IS
SELECT a.sid, a.seq#, a.event, a.p1text, a.p1, a.p1raw, a.p2text, a.p2,
a.p2raw, a.p3text, a.p3, a.p3raw, a.wait_time, a.seconds_in_wait,
a.state, b.serial#, b.username, b.osuser, b.paddr, b.logon_time,
b.process, b.sql_hash_value, b.saddr, b.module, b.row_wait_obj#,
b.row_wait_file#, b.row_wait_block#, b.row_wait_row#, b.machine
FROM v$session_wait a, v$session b
WHERE a.sid = b.sid
AND b.username IS NOT NULL
AND b.TYPE <> 'BACKGROUND'
AND a.event IN (
'db file sequential read',
'db file scattered read',
'latch free',
'direct path read',
'direct path write',
'enqueue',
'library cache pin',
'library cache load lock',
'buffer busy waits',
'free buffer waits'
);
rec current_event%ROWTYPE;
PROCEDURE dc
AS
CURSOR get_obj_name(
in_p1 IN v$session_wait.p1%TYPE,
in_p2 v$session_wait.p2%TYPE
)
IS
SELECT segment_name, partition_name
FROM dba_extents_rep
WHERE in_p2 BETWEEN block_id AND (block_id + blocks - 1)
AND file_id = in_p1;
CURSOR c_dbfn(
in_p1 v$session_wait.p1%TYPE
)
IS
SELECT name, 'DATAFILE' filetype
FROM v$datafile
WHERE file# = in_p1
UNION ALL
SELECT a.name, 'TEMPFILE' filetype
FROM v$tempfile a, v$parameter b
WHERE b.name = 'db_files'
AND a.file# + b.VALUE = in_p1;
CURSOR c_segtype(
in_saddr IN v$session.saddr%TYPE,
in_serial# IN v$session.serial#%TYPE
)
IS
SELECT DISTINCT DECODE(
ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4,
'INDEX', 5, 'LOB_DATA', 6, 'LOB_INDEX',
'UNDEFINED'
) AS segtype
FROM sys.x_$ktsso
WHERE inst_id = USERENV('instance')
AND ktssoses = in_saddr
AND ktssosno = in_serial#;
CURSOR c_sesslockinfo(
in_p2 IN v$session_wait.p2%TYPE,
in_p3 v$session_wait.p3%TYPE
)
IS
SELECT a.sid, a.serial#, a.username, a.paddr, a.logon_time,
a.sql_hash_value, b.TYPE, b.lmode, b.ctime
FROM v$session a, v$lock b
WHERE a.sid = b.sid
AND b.id1 = in_p2
AND b.id2 = in_p3
AND b.block = 1;
CURSOR c_tob(
in_p1 IN v$session_wait.p1%TYPE,
in_p2 IN v$session_wait.p2%TYPE
)
IS
SELECT segment_type || ' header block'
FROM dba_segments
WHERE header_file = in_p1
AND header_block = in_p2
UNION ALL
SELECT segment_type || ' freelist group block'
FROM dba_segments
WHERE header_file = in_p1
AND in_p2 BETWEEN header_block + 1 AND (
header_block +
freelist_groups
)
AND freelist_groups > 1
UNION ALL
SELECT segment_type || ' data block'
FROM dba_extents
WHERE in_p2 BETWEEN block_id AND (block_id + blocks - 1)
AND file_id = in_p1
AND NOT EXISTS(SELECT 1
FROM dba_segments
WHERE header_file = in_p1
AND header_block = in_p2);
CURSOR c_blockinginfo(
in_p1raw IN v$session_wait.p1raw%TYPE
)
IS
SELECT a.sid, a.serial#, a.username, a.paddr, a.logon_time,
a.sql_hash_value, b.kglpnmod
FROM v$session a, sys.x_$kglpn b
WHERE a.saddr = b.kglpnuse
AND b.inst_id = USERENV('instance')
AND b.kglpnreq = 0
AND b.kglpnmod NOT IN (0, 1)
AND b.kglpnhdl = in_p1raw;
/* variable Declarations for specific event processing */
v_segment_name dba_extents_rep.segment_name%TYPE;
v_partition_name dba_extents_rep.partition_name%TYPE;
v_latchname v$latchname.name%TYPE;
v_object_name wait_events_repository.object_name%TYPE;
v_subobject_name dba_objects.subobject_name%TYPE;
v_datafile v$datafile.name%TYPE;
v_blocking_hash_value v$session.sql_hash_value%TYPE;
v_filetype VARCHAR2(100);
v_segtype VARCHAR2(10);
v_lock_mode VARCHAR2(10);
v_lock_type NUMBER;
v_wer_id NUMBER;
blnrtn BOOLEAN := FALSE;
v_stillrunning NUMBER := 0;
v_jobid NUMBER;
BEGIN
-- Examine DBA_JOBS_RUNNING to make sure this is not still running or hung
BEGIN
BEGIN
SELECT job
INTO v_jobid
FROM user_jobs
WHERE what LIKE '%DC_COLLECTOR%';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN;
END;
SELECT COUNT(*)
INTO v_stillrunning
FROM dba_jobs_running d
WHERE d.job = v_jobid;
IF v_stillrunning <> 0
THEN
RETURN;
END IF;
END;
FOR rec IN current_event
LOOP
/* EVENT: db file sequential read/db file scattered read ### */
IF rec.event = 'db file sequential read'
OR rec.event = 'db file scattered read'
THEN
/* 1) Determine the object name and the partition name (if applicable)
using the P1 and P2 values supplied by the CURRENT_EVENT cursor
DBA_EXTENTS_REP table CURSOR GET_OBJ_NAME
*/
FOR rec_obj IN get_obj_name(rec.p1, rec.p2)
LOOP
v_object_name := rec_obj.segment_name;
END LOOP;
/* rec_sql: extract_sql */
END IF;/* 'db file sequential read', 'db file scattered read' */
/* EVENT: latch_free */
IF rec.event = 'latch free'
THEN
/* latch name */
SELECT name
INTO v_object_name
FROM v$latchname
WHERE latch# = rec.p2;
END IF;
/* EVENT: direct path read/direct path write */
IF rec.event = 'direct path read'
OR rec.event = 'direct path write'
THEN
/* OBJECT NAME */
FOR rec_obj IN get_obj_name(rec.p1, rec.p2)
LOOP
v_object_name := 'OBJ: ' || rec_obj.segment_name || ' ';
END LOOP;
/* db filename */
FOR rec_dbfn IN c_dbfn(rec.p1)
LOOP
v_object_name :=
v_object_name || 'DATAFILE: ' || rec_dbfn.name || ' ';
v_object_name :=
v_object_name || 'FILETYPE: ' || rec_dbfn.filetype || ' ';
END LOOP;
/* segment type */
FOR rec_segtype IN c_segtype(rec.saddr, rec.serial#)
LOOP
v_object_name :=
v_object_name || 'SEGTYPE: ' || rec_segtype.segtype;
END LOOP;
v_object_name := TRIM(v_object_name);
END IF;
/* EVENT: enqueue */
IF rec.event = 'enqueue'
THEN
/*
1) determine lock type and lock mode */
BEGIN
SELECT CHR(bitand(rec.p1, -16777216) / 16777215) ||
CHR(bitand(rec.p1, 16711680) / 65535) lock_mode,
MOD(rec.p1, 16) lock_type
INTO v_lock_mode,
v_lock_type
FROM dual;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
v_object_name :=
'LOCKMODE: ' || v_lock_mode || ' LOCK_TYPE: ' || v_lock_type || ' ';
/*
2) name of object */
BEGIN
SELECT object_name, subobject_name
INTO v_object_name,
v_subobject_name
FROM dba_objects
WHERE object_id = rec.row_wait_obj#;
v_object_name :=
'ENQOBJ: ' || v_object_name || ' ENQSOBJ: ' || v_subobject_name ||
' ';
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
/*
3) blocking session and lock information */
FOR rec_sesslockinfo IN c_sesslockinfo(rec.p2, rec.p3)
LOOP
v_blocking_hash_value := rec_sesslockinfo.sql_hash_value;
v_object_name :=
v_object_name || ' BLOCKING SESSION - LOCK TYPE: ' ||
rec_sesslockinfo.TYPE ||
' LOCK MODE: ' ||
rec_sesslockinfo.lmode ||
' TIME LOCKED: ' ||
rec_sesslockinfo.ctime ||
' SQL_HASH: ' ||
rec_sesslockinfo.sql_hash_value;
END LOOP;
END IF;
/* EVENT: buffer busy waits */
IF rec.event = 'buffer busy waits'
THEN
FOR rec_obj IN get_obj_name(rec.p1, rec.p2)
LOOP
v_object_name :=
'SEGMENT: ' || rec_obj.segment_name || ' PART: ' ||
rec_obj.partition_name;
END LOOP;
END IF;
/* EVENT: free buffer waits */
IF rec.event = 'free buffer waits'
THEN
NULL;-- capture sql
END IF;
/* EVENT: library cache pin */
IF rec.event = 'library cache pin'
THEN
/* get name of object that the pin is for */
SELECT kglnaobj
INTO v_object_name
FROM sys.x_$kglob
WHERE inst_id = USERENV('instance')
AND kglhdadr = rec.p1raw;
END IF;
SELECT seq_wer_id.nextval
INTO v_wer_id
FROM dual;
/* INSERT current_event data into WAIT_EVENTS_REPOSITORY */
INSERT INTO wait_events_repository
(
wer_id,
sid,
seq#,
event,
p1text,
p1,
p1raw,
p2text,
p2,
p2raw,
p3text,
p3,
p3raw,
wait_time,
seconds_in_wait,
state,
sql_hash_value,
serial#,
logon_time,
object_name,
sampling_date
)
VALUES(
v_wer_id,
rec.sid,
rec.seq#,
rec.event,
rec.p1text,
rec.p1,
rec.p1raw,
rec.p2text,
rec.p2,
rec.p2raw,
rec.p3text,
rec.p3,
rec.p3raw,
rec.wait_time,
rec.seconds_in_wait,
rec.state,
rec.sql_hash_value,
rec.serial#,
rec.logon_time,
v_object_name,
SYSDATE
);
/* capture SQL */
blnrtn :=
capture_sql(
v_wer_id, rec.sql_hash_value, rec.event, rec.machine,
rec.module, NULL
);
/* enqueue event? get blocking sql as well */
blnrtn :=
capture_sql(
v_wer_id, v_blocking_hash_value, rec.event, rec.machine,
rec.module, 'BLOCKER'
);
/*
blnrtn :=
capture_blocking_sql (
v_wer_id
,v_blocking_hash_value
,rec.event
,rec.machine
,rec.module
);
*/
/* reset variables */
v_object_name := NULL;
v_subobject_name := NULL;
END LOOP;/* rec: current_event */
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error(-20001, SQLERRM);
END dc;
FUNCTION capture_sql(
in_wer_id IN NUMBER,
in_hash_value IN v$session.sql_hash_value%TYPE,
in_event IN v$session_wait.event%TYPE,
in_machine IN v$session.machine%TYPE,
in_module IN v$session.module%TYPE,
in_mode IN VARCHAR2 DEFAULT NULL
)
RETURN BOOLEAN
IS
CURSOR extract_sql(
in_hash_value v$session.sql_hash_value%TYPE
)
IS
SELECT st.hash_value, st.address, st.command_type, st.piece,
st.sql_text, sa.disk_reads, sa.buffer_gets
FROM v$sqltext st, v$sqlarea sa
WHERE st.hash_value = in_hash_value
AND st.hash_value = sa.hash_value
ORDER BY st.piece;
BEGIN
FOR r IN extract_sql(in_hash_value)
LOOP
IF in_mode = 'BLOCKER'
THEN
INSERT INTO blocker_repository
(
wer_id,
sql_id,
address,
hash_value,
command_type,
piece,
sql_text,
disk_reads,
buffer_gets,
osuser,
machine,
module
)
VALUES(
in_wer_id,
seq_sql_stmt_id.nextval,
r.address,
r.hash_value,
r.command_type,
r.piece,
r.sql_text,
r.disk_reads,
r.buffer_gets,
in_event,
in_machine,
in_module
);
ELSE
INSERT INTO sql_stmt_repository
(
wer_id,
sql_id,
address,
hash_value,
command_type,
piece,
sql_text,
disk_reads,
buffer_gets,
osuser,
machine,
module
)
VALUES(
in_wer_id,
seq_sql_stmt_id.nextval,
r.address,
r.hash_value,
r.command_type,
r.piece,
r.sql_text,
r.disk_reads,
r.buffer_gets,
in_event,
in_machine,
in_module
);
END IF;
END LOOP;
COMMIT;
RETURN TRUE;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error(-20001, SQLERRM);
RETURN FALSE;
END capture_sql;
END;
/
CREATE TABLE HT4.WAIT_EVENTS_REPOSITORY
(WER_ID NUMBER
,SID NUMBER
,SEQ# NUMBER
,EVENT VARCHAR2(64)
,P1TEXT VARCHAR2(64)
,P1 NUMBER
,P1RAW RAW(4)
,P2TEXT VARCHAR2(64)
,P2 NUMBER
,P2RAW RAW(4)
,P3TEXT VARCHAR2(64)
,P3 NUMBER
,P3RAW RAW(4)
,WAIT_TIME NUMBER
,SECONDS_IN_WAIT NUMBER
,STATE VARCHAR2(19)
,SQL_HASH_VALUE NUMBER
,SERIAL# NUMBER
,LOGON_TIME DATE
,OBJECT_NAME VARCHAR2(1000)
,SAMPLING_DATE DATE
)
/
CREATE TABLE HT4.WAIT_EVENT_PARAMS
(EVENT VARCHAR2(64)
,P1TEXT VARCHAR2(100)
,P2TEXT VARCHAR2(100)
,P3TEXT VARCHAR2(100)
)
/
/* INSERTS FOR ABOVE TABLE */
insert into WAIT_EVENT_PARAMS
(EVENT, P1TEXT, P2TEXT, P3TEXT)
VALUES ('buffer busy waits', 'Absolute file number where data block resides', 'actual block number', 'reason for the wait')
/
insert into WAIT_EVENT_PARAMS
(EVENT, P1TEXT, P2TEXT, P3TEXT)
VALUES ('control file parallel write', 'Number of control files the server process is writing to', 'Total number of blocks to write to the control files', 'Number of I/O requests')
/
insert into WAIT_EVENT_PARAMS
(EVENT, P1TEXT, P2TEXT, P3TEXT)
VALUES ('direct path write', 'Absolute file number to write to', 'Starting block number to write from', 'Number of blocks to write')
/
insert into WAIT_EVENT_PARAMS
(EVENT, P1TEXT, P2TEXT, P3TEXT)
VALUES ('db file parallel read', 'Number of files to read from', 'Total number of blocks to read', 'Total number of I/O requests (same as P2)')
/
insert into WAIT_EVENT_PARAMS
(EVENT, P1TEXT, P2TEXT, P3TEXT)
VALUES ('db file parallel write', 'Number of files to write to', 'Total number of blocks to write', 'Total number of I/O requests (same as P2)')
/
insert into WAIT_EVENT_PARAMS
(EVENT, P1TEXT, P2TEXT, P3TEXT)
VALUES ('db file scattered read', 'File number to read blocks from', 'Starting block number to begin reading', 'Number of blocks to read')
/
insert into WAIT_EVENT_PARAMS
(EVENT, P1TEXT, P2TEXT, P3TEXT)
VALUES ('db file sequential read', 'File number to read the data block from', 'Starting block number to read', '1 in most cases, but for temporary segments can be more than 1')
/
insert into WAIT_EVENT_PARAMS
(EVENT, P1TEXT, P2TEXT, P3TEXT)
VALUES ('db file single write', 'File number to write to', 'Starting block number to write to', 'The number of blocks to write, typically 1')
/
insert into WAIT_EVENT_PARAMS
(EVENT, P1TEXT, P2TEXT, P3TEXT)
VALUES ('direct path read', 'Absolute file number to read from', 'Starting block number to read from', 'Number of blocks to read')
/
insert into WAIT_EVENT_PARAMS
(EVENT, P1TEXT, P2TEXT, P3TEXT)
VALUES ('enqueue', 'Enqueue name and mode requested by the waiting process', 'Resource identifier ID1 for the requested lock, same as V$LOCK.ID1', 'Resource identifier ID2 for the requested lock, same as V$LOCK.ID2')
/
insert into WAIT_EVENT_PARAMS
(EVENT, P1TEXT, P2TEXT, P3TEXT)
VALUES ('latch free', 'Address of the latch for which the process is waiting', 'Number of the latch, same as V$LATCHNAME.LATCH#', 'Number of tries; a counter showing the number of attempts the process made to acquire the latch')
/
insert into WAIT_EVENT_PARAMS
(EVENT, P1TEXT, P2TEXT, P3TEXT)
VALUES ('log buffer space', 'N/A', 'N/A', 'N/A')
/
insert into WAIT_EVENT_PARAMS
(EVENT, P1TEXT, P2TEXT, P3TEXT)
VALUES ('log file parallel write', 'Number of logs to write to', 'Number of OS blocks to write to', 'Number of I/O requests')
/
insert into WAIT_EVENT_PARAMS
(EVENT, P1TEXT, P2TEXT, P3TEXT)
VALUES ('log file sequential read', 'Relative sequence number of the redo log file within the redo log group', 'Block number to start reading from', 'Number of OS blocks to read starting from the P2 value')
/
insert into WAIT_EVENT_PARAMS
(EVENT, P1TEXT, P2TEXT, P3TEXT)
VALUES ('log file switch (archiving needed)', 'N/A', 'N/A', 'N/A')
/
insert into WAIT_EVENT_PARAMS
(EVENT, P1TEXT, P2TEXT, P3TEXT)
VALUES ('log file switch (checkpoint incomplete)', 'N/A', 'N/A', 'N/A')
/
insert into WAIT_EVENT_PARAMS
(EVENT, P1TEXT, P2TEXT, P3TEXT)
VALUES ('log file switch completion', 'N/A', 'N/A', 'N/A')
/
insert into WAIT_EVENT_PARAMS
(EVENT, P1TEXT, P2TEXT, P3TEXT)
VALUES ('log file sync', 'The number of the buffer in the log buffer that needs to be synchronized', 'Not Used', 'Not Used')
/
insert into WAIT_EVENT_PARAMS
(EVENT, P1TEXT, P2TEXT, P3TEXT)
VALUES ('SQL*Net message from client', 'ASCII value showing what type of network driver is in use by the client connections; bequeath, TCP', 'The number of bytes received by the session from the client', 'Not used')
/
insert into WAIT_EVENT_PARAMS
(EVENT, P1TEXT, P2TEXT, P3TEXT)
VALUES ('SQL*Net message to client', 'ASCII value showing what type of network driver is in use by the client connections; bequeath, TCP', 'Number of bytes sent to client', 'Not Used')
/
CREATE TABLE HT4.SQL_STMT_REPOSITORY
(ADDRESS RAW(4)
,HASH_VALUE NUMBER
,COMMAND_TYPE NUMBER
,PIECE NUMBER
,SQL_TEXT VARCHAR2(64)
,DISK_READS NUMBER
,BUFFER_GETS NUMBER
,OSUSER VARCHAR2(30)
,MACHINE VARCHAR2(64)
,MODULE VARCHAR2(48)
,SQL_ID NUMBER NOT NULL
,WER_ID NUMBER
,PRIMARY KEY (SQL_ID)
)
/
CREATE TABLE HT4.BLOCKER_REPOSITORY
(ADDRESS RAW(4)
,HASH_VALUE NUMBER
,COMMAND_TYPE NUMBER
,PIECE NUMBER
,SQL_TEXT VARCHAR2(64)
,DISK_READS NUMBER
,BUFFER_GETS NUMBER
,OSUSER VARCHAR2(30)
,MACHINE VARCHAR2(64)
,MODULE VARCHAR2(48)
,SQL_ID NUMBER NOT NULL
,WER_ID NUMBER
,PRIMARY KEY (SQL_ID)
)
/
The goal of this code is to record wait events for analysis during slower periods. It is a self-made Package that I developed while reading the aformentioned book.
Now I set up a job to run this Package procedure call as DC_COLLECTOR.DC; with an interval of 1 minute. The OWNER of this Package and the LOG_USER of the Job is HT4. My problem is that when the job runs, it does not seem to collect any data into my repository tables (it's an active system and it should at worst encounter some latch free or db file sequential read waits occasionally). If I run it from SQL* Plus, it does record rows in my repository tables.
Any ideas?
Excellent answers as always from you...thanks.
August 05, 2005 - 2:20 pm UTC
that's alot of code. can you shrink it down to just the single sql statement that is causing you grief?