Re: Strange problem.

Chuck wrote:
> 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)
>
>
>
> The performance problem seems to be coming from the way the optimizer
> is adding in the EXISTS subquery. If I rewrite it to use an IN I get
> a 10x performance increase.
>
> 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 just add an UNNEST hint to the subquery in the original SQL though,
> I get the exact same execution plan as the IN version gets, but here's
> the strange part. Executing the hinted version runs 5x longer and does
> 5x the number of consistent gets as the IN version. How can this be when
> the execution plans are identical? The bind variable used is the same
> for both.
>
> Here are the plans for the IN version, and the UNNEST version.
>
> ==========================================================================================
> IN version
> -----------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
> -----------------------------------------------------------------------------------------
> | 0 | UPDATE STATEMENT | | 1796 | 50288 | 659 (0)|
> | 1 | UPDATE | PS_TL_PAYABLE_TIME | | | |
> |* 2 | TABLE ACCESS BY INDEX ROWID| PS_TL_PAYABLE_TIME | 1 | 15 | 2 (50)|
> | 3 | NESTED LOOPS | | 1796 | 50288 | 659 (0)|
> | 4 | VIEW | VW_NSO_1 | 1796 | 23348 | |
> | 5 | SORT UNIQUE | | 1796 | 57472 | |
> | 6 | NESTED LOOPS | | 1796 | 57472 | 112 (1)|
>
> PLAN_TABLE_OUTPUT
> ------------------------------------------------------------------------------------------
> |* 7 | TABLE ACCESS FULL | PS_TL_XREF_TBL | 259K| 4817K| 110 (0)|
> |* 8 | INDEX UNIQUE SCAN | PS_PY_XREF_WRK | 1 | 13 | |
> |* 9 | INDEX RANGE SCAN | PSATL_PAYABLE_TIME | 1 | | |
> -----------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - filter("PS_TL_PAYABLE_TIME"."PAY_SYSTEM"='NA' AND
> "PS_TL_PAYABLE_TIME"."PAYABLE_STATUS"<>'PD' AND
> "PS_TL_PAYABLE_TIME"."PAYABLE_STATUS"<>'DL' AND
>
> PLAN_TABLE_OUTPUT
> ------------------------------------------------------------------------------------------
> "PS_TL_PAYABLE_TIME"."PAYROLL_REQ_NUM"<>1)
> 7 - filter("A"."PAY_SYSTEM"='NA')
> 8 - access("B"."PROCESS_INSTANCE"=TO_NUMBER(:Z) AND "B"."XREF_NUM"="A"."XREF_NUM")
> 9 - access("PS_TL_PAYABLE_TIME"."SEQ_NBR"="VW_NSO_1"."$nso_col_1")
>
> ==========================================================================================
> UNNEST version
> -----------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
> -----------------------------------------------------------------------------------------
> | 0 | UPDATE STATEMENT | | 1796 | 50288 | 659 (0)|
> | 1 | UPDATE | PS_TL_PAYABLE_TIME | | | |
> |* 2 | TABLE ACCESS BY INDEX ROWID| PS_TL_PAYABLE_TIME | 1 | 15 | 2 (50)|
> | 3 | NESTED LOOPS | | 1796 | 50288 | 659 (0)|
> | 4 | VIEW | VW_SQ_1 | 1796 | 23348 | |
> | 5 | SORT UNIQUE | | 1796 | 57472 | |
> | 6 | NESTED LOOPS | | 1796 | 57472 | 112 (1)|
>
> PLAN_TABLE_OUTPUT
> ---------------------------------------------------------------------------------------------------
> |* 7 | TABLE ACCESS FULL | PS_TL_XREF_TBL | 259K| 4817K| 110 (0)|
> |* 8 | INDEX UNIQUE SCAN | PS_PY_XREF_WRK | 1 | 13 | |
> |* 9 | INDEX RANGE SCAN | PSATL_PAYABLE_TIME | 1 | | |
> -----------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - filter("PS_TL_PAYABLE_TIME"."PAY_SYSTEM"='NA' AND
> "PS_TL_PAYABLE_TIME"."PAYABLE_STATUS"<>'PD' AND
> "PS_TL_PAYABLE_TIME"."PAYABLE_STATUS"<>'DL' AND
>
> PLAN_TABLE_OUTPUT
> ---------------------------------------------------------------------------------------------------
> "PS_TL_PAYABLE_TIME"."PAYROLL_REQ_NUM"<>1)
> 7 - filter("A"."PAY_SYSTEM"='NA')
> 8 - access("B"."PROCESS_INSTANCE"=TO_NUMBER(:Z) AND "B"."XREF_NUM"="A"."XREF_NUM")
> 9 - access("VW_SQ_1"."SEQ_NBR"="PS_TL_PAYABLE_TIME"."SEQ_NBR")
>
> .

Play with the NO_UNNEST and UNNEST hints.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)