Re: Strange problem.

From: Chuck <skilover_nospam_at_bluebottle.com>
Date: Wed, 07 Dec 2005 10:34:48 -0500
Message-ID: <dn6ves$8fa$1@domitilla.aioe.org>

IANAL_VISTA wrote:
> Chuck <skilover_nospam_at_bluebottle.com> wrote in
> news:1133889203.ba727e1adc34c4abde94a39bd7453edb_at_news.nntpserver.com:
>
>

>>I have an update that I'm trying to optimize.
>>
>>UPDATE PS_TL_PAYABLE_TIME
>>   SET PAYABLE_STATUS = 'RP'
>> WHERE PAY_SYSTEM = 'NA'
>>   AND PAYABLE_STATUS NOT IN ('PD', 'DL')
>>   AND PAYROLL_REQ_NUM <> 1
>>   AND EXISTS (
>>          SELECT 'X'
>>            FROM PS_TL_XREF_TBL A, PS_PY_XREF_WRK B
>>           WHERE A.PAY_SYSTEM = 'NA'
>>             AND A.SEQ_NBR = PS_TL_PAYABLE_TIME.SEQ_NBR
>>             AND B.XREF_NUM = A.XREF_NUM
>>             AND B.PROCESS_INSTANCE = :1)

>
>
> UPDATE PS_TL_PAYABLE_TIME
> SET PAYABLE_STATUS = 'RP'
> WHERE PAY_SYSTEM = 'NA'
> AND PAYABLE_STATUS NOT IN ('PD', 'DL')
> AND PAYROLL_REQ_NUM <> 1
> AND EXISTS (
> SELECT 'X'
> FROM PS_TL_XREF_TBL A,
> WHERE A.PAY_SYSTEM = 'NA'
> AND A.SEQ_NBR = PS_TL_PAYABLE_TIME.SEQ_NBR
> AND A.XREF_NUM in (SELECT B.XREF_NUM FROM PS_PY_XREF_WRK B
> WHERE B.PROCESS_INSTANCE = :1
> )
> )
>

If I could change the SQL I would. I could just manually unnest the SQL with something this...

UPDATE PS_TL_PAYABLE_TIME
   SET PAYABLE_STATUS = 'RP'
 WHERE PAY_SYSTEM = 'NA'
   AND PAYABLE_STATUS NOT IN ('PD', 'DL')    AND PAYROLL_REQ_NUM <> 1
   AND PS_TL_PAYABLE_TIME.SEQ_NBR IN (

          SELECT a.seq_nbr
            FROM PS_TL_XREF_TBL A, PS_PY_XREF_WRK B
           WHERE A.PAY_SYSTEM = 'NA'
             AND B.XREF_NUM = A.XREF_NUM
             AND B.PROCESS_INSTANCE = :1)

If I run it that way, a 20 minute update runs in 20 seconds. The problem is this is part of a delivered Peoplesoft COBOL program and modifications are not permitted. I can't get the same execution plan by creating an outline and editing it with the Oracle outline editor either so right now I'm stuck.

My original question though is why the UNNEST hint produced one execution plan with EXPLAIN plan but didn't use that plan at run time. The answer is a combination of things - a bug in bind variable peeking, and the _unnest_subquery=FALSE setting in the spfile.

-- 
To reply by email remove "_nospam"

Received on Wed Dec 07 2005 - 09:34:48 CST