Re: 10053 trace PL/SQL
- From: Rich <richa03@xxxxxxxxx>
- To: Jared Still <jkstill@xxxxxxxxx>
- Date: Wed, 1 Sep 2010 10:02:22 -0700
Brandon Allen wrote:
"I’m not sure if this is the problem or not, but it looks like you’re
missing the “level 1” at the end of the alter session command. Try this:
execute immediate 'alter session set events ''10053 trace name context
forever, level 1''';
Regards,
Brandon
"
Thanks Brandon - that's a good catch, however, even with the , level 1 it
still refuses to put 10053 trace into the trace file.
Also, Jared, I did try exactly as you posted below before at an SQL*Plus
prompt - every reference to a PL/SQL variable is a bind variable.
However, I can't get it to repro at the prompt after a number of times in a
number of sessions.
I'm running both the SQL and the PL/SQL as the schema owner to try to ensure
no optimizer changes, etc.
I should have mentioned both in my previous post.
This is really confusing me...it doesn't make any sense.
Any other help?
Thanks,
Rich
On Wed, Sep 1, 2010 at 9:24 AM, Jared Still <jkstill@xxxxxxxxx> wrote:
> On Wed, Sep 1, 2010 at 8:11 AM, Rich <richa03@xxxxxxxxx> wrote:
>
>> 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.
>>
>>
> Rich,
>
> I don't know how to solve the 10053 issue from within PL/SQL, but may
> be of some help replicating the 'problem' from sqlplus.
>
> You didn't mention just how you went about running this same query in
> sqlplus,
> but if the test query didn't use bind variables, the execution plan will be
> different
> than that in the PL/SQL procedure.
>
> Something like this should do it:
>
>
> var P_CONV_KEY number
> var P_FROM_DATE VARCHAR2(32)
> var P_TO_DATE VARCHAR2(32)
> var P_FROM_KEY number
> var P_TO_KEY number
>
> begin
> :P_CONV_KEY := 505467;
> :P_FROM_DATE := NULL;
> :P_TO_DATE := '2010-07-27 20:09:21';
> :P_FROM_KEY := NULL;
> :P_TO_KEY := 5104895;
> end;
>
> /
>
> 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 <= TO_DATE(P_TO_DATE, 'yyyy-mm-dd hh24:mi:ss')
> AND ( (P_FROM_DATE IS NOT NULL AND t.tran_created_date >=
> TO_DATE(:P_FROM_DATE, 'yyyy-mm-dd hh24:mi:ss')) 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;
>
>
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
> Oracle Blog: http://jkstill.blogspot.com
> Home Page: http://jaredstill.com
>
>
Other related posts:
