Re: 10053 trace PL/SQL
- From: Jared Still <jkstill@xxxxxxxxx>
- To: richa03@xxxxxxxxx
- Date: Wed, 1 Sep 2010 09:24:46 -0700
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:
