10053 trace PL/SQL
- From: Rich <richa03@xxxxxxxxx>
- To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 1 Sep 2010 08:11:53 -0700
Hello List,
This is 64-bit 10.2.0.4 on RHEL 5.1 x86_64.
I have a small PL/SQL procedure which is using the "wrong" index.
The SQL only does this within the procedure - I cannot replicate the issue
externally with SQL*Plus.
I'd like to run a 10053 trace on it to determine why.
I tried adding:
execute immediate 'alter session set events ''10053 trace name context
forever''';
execute immediate 'alter session set tracefile_identifier = slow_plsql';
as shown below, however, it doesn't produce the 10053 trace in the file.
[It does produce the file, but there isn't anything in the file other than
the header.]
I also tried externally tracing the session using oradebug; that doesn't
work, either.
I know a hard parse is required for 10053 to emit.
I first tried changing the comment to change the statement, however that
didn't work.
I then used the following to "ensure" the statement is not in the cache (as
sqldba) and "force" a hard parse:
select sql_text, address, hash_value, executions, loads, version_count,
invalidations, parse_calls from v$sqlarea where sql_text like '% slow%';
alter session set events '5614566 trace name context forever';
exec sys.dbms_shared_pool.purge('&address, &hash_value','C');
It still doesn't appear that the 10053 trace is working as I expect.
Before the run, the above select on v$sqlarea shows the "DECLARE" statement
is not in the cache.
After the run, the select shows the "DECLARE" statement and that it was
executed & had only 1 parse call as expected.
Any help?
For what it's worth, this is the code:
SET SERVEROUTPUT ON
SET LINESIZE 100
DECLARE
TYPE RESULTSET IS REF CURSOR;
P_RV RESULTSET;
P_RESULT_SET RESULTSET;
v_trst_key hsr_translation.TRST_KEY%TYPE;
v_tran_created_date hsr_translation.TRAN_CREATED_DATE%TYPE;
v_tran_pkey hsr_translation.TRAN_PKEY%TYPE;
v_conv_key hsr_translation.CONV_KEY%TYPE;
v_busn_name_src hsd_business.BUSN_NAME%TYPE;
v_busn_name_dest hsd_business.BUSN_NAME%TYPE;
v_busn_key_src hsr_translation.BUSN_KEY_SRC%TYPE;
v_busn_key_dest hsr_translation.BUSN_KEY_DEST%TYPE;
v_dcls_name hsd_document_class.DCLS_NAME%TYPE;
v_tran_processing_mode hsr_translation.TRAN_PROCESSING_MODE%TYPE;
CURSOR C1 (P_FROM_DATE in VARCHAR2, V_FROM_DATE in DATE, V_TO_DATE in DATE,
P_FROM_KEY in INTEGER, P_TO_KEY in INTEGER, P_CONV_KEY INTEGER )
IS
SELECT /* slow2 */ t.TRST_KEY,t.tran_created_date,t.tran_pkey,t.conv_key
,SRC_BUSN.busn_name
busn_name_src,DEST_BUSN.busn_name busn_name_dest
,t.busn_key_src,t.busn_key_dest,d.dcls_name,t.tran_processing_mode
FROM hsr_translation t,hsd_business SRC_BUSN
,hsd_business DEST_BUSN,hsd_document_class d
WHERE t.tran_created_date <= V_TO_DATE
AND ( (P_FROM_DATE IS NOT NULL AND t.tran_created_date >=
V_FROM_DATE) OR (P_FROM_DATE IS NULL))
AND t.tran_pkey <= P_TO_KEY
AND ( (P_FROM_KEY IS NOT NULL AND t.tran_pkey >
P_FROM_KEY) OR (P_FROM_KEY IS NULL))
AND t.conv_key = P_CONV_KEY
AND d.dcls_pkey (+) = t.dcls_key
AND SRC_BUSN.busn_pkey (+) = t.busn_key_src
AND DEST_BUSN.busn_pkey (+) = t.busn_key_dest;
ORDER BY TRAN_CREATED_DATE DESC;
P_CONV_KEY INTEGER := 505467;
P_FROM_DATE VARCHAR2(32) := NULL;
P_TO_DATE VARCHAR2(32) := '2010-07-27 20:09:21';
P_FROM_KEY INTEGER := NULL;
P_TO_KEY INTEGER := 5104895;
V_FROM_DATE DATE;
V_TO_DATE DATE;
BEGIN
V_FROM_DATE := TO_DATE(P_FROM_DATE, 'yyyy-mm-dd hh24:mi:ss');
V_TO_DATE := TO_DATE(P_TO_DATE, 'yyyy-mm-dd hh24:mi:ss');
execute immediate 'alter session set events ''10053 trace name context
forever''';
execute immediate 'alter session set tracefile_identifier = fast_plsql';
OPEN C1 (P_FROM_DATE, V_FROM_DATE, V_TO_DATE, P_FROM_KEY,
P_TO_KEY, P_CONV_KEY);
DBMS_OUTPUT.PUT_LINE('Running using cursor C1');
LOOP
FETCH C1 INTO v_trst_key, v_tran_created_date, v_tran_pkey,
v_conv_key, v_busn_name_src,
v_busn_name_dest, v_busn_key_src,
v_busn_key_dest, v_dcls_name, v_tran_processing_mode;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_trst_key || ' ' || v_tran_pkey);
END LOOP;
CLOSE C1;
END;
/
Thanks in advance,
Rich
Other related posts:
