10053 trace PL/SQL

From: Rich <richa03_at_gmail.com>
Date: Wed, 1 Sep 2010 08:11:53 -0700
Message-ID: <AANLkTimgCJA1GSHst066KknxN4FN1N_8YkChuA2LOPE6_at_mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Sep 01 2010 - 10:11:53 CDT