Re: delete performance from a standalone sql versus the same call from a procedure
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Sat, 14 Nov 2009 07:01:43 -0800 (PST)
Message-ID: <ca1c8cbe-5887-4673-88c5-bab468c324fc_at_s31g2000yqs.googlegroups.com>
On Nov 13, 5:16 pm, z1hou1 <z1h..._at_gmail.com> wrote:
> Hi,
> Thank you for getting back.
>
> We have resolved the performance issue by tweaking the SQL. But we
> still do not understand why there is such a huge difference between
> the 'IN' clause and an EXISTS clause as far as the plan is concerned.
>
> In this case, the EXISTS clause was the culprit and that surprised me.
>
> Here is the SQL along with the plan on the IN that did improve the
> performance both inside the package and the standalone.
>
> DELETE FROM TABLE_FACT F1
> WHERE F1.GL_PK IN
> (SELECT F.GL_PK FROM TABLE_FACT F
> WHERE F.GL_PK = F1.REVERSAL_PARENT_GL_PK
> AND F.SLICE_DATE = '11-NOV-2009'
> AND F.SOURCE_ID = 4
> AND F.POSTING_TYPE_IND = 0
> AND F.REVERSE_ENTRY = 0 AND F.SS_POSTING_FLAG = 0 AND F.ACCRL_RVSL =
> 'Y')
>
> Plan
> DELETE STATEMENT, GOAL = ALL_ROWS Cost=533 Cardinality=1 Bytes=62
> DELETE Object owner=CRAW Object name=TABLE_FACT
> NESTED LOOPS Cost=533 Cardinality=1 Bytes=62
> PARTITION RANGE SINGLE Cost=531 Cardinality=1 Bytes=26
> TABLE ACCESS FULL Object owner=CRAW Object name=TABLE_FACT
> Cost=531 Cardinality=1 Bytes=26
> TABLE ACCESS BY GLOBAL INDEX ROWID Object owner=ABC Object
> name=TABLE_FACT Cost=2 Cardinality=1 Bytes=36
> INDEX UNIQUE SCAN Object owner=CRAW Object name=IDX_PK Cost=1
> Cardinality=1
>
> ****
> Here is the SQL with the EXISTS clause and the plan that slowed the
> delete down to a crawl inside the proc, but in standalone mode it
> executed as fast as the IN clause above. Unfortunately we did not
> capture the plan inside the proc when a trace was carried out earlier.
>
> DELETE FROM TABLE_FACT F1
> WHERE EXISTS
> (SELECT F.GL_PK FROM TABLE_FACT F
> WHERE F.GL_PK = F1.REVERSAL_PARENT_GL_PK
> AND F.SLICE_DATE = '11-NOV-2009'
> AND F.SOURCE_ID = 4
> AND F.POSTING_TYPE_IND = 0
> AND F.REVERSE_ENTRY = 0 AND F.SS_POSTING_FLAG = 0 AND F.ACCRL_RVSL =
> 'Y')
>
> Plan
> DELETE STATEMENT, GOAL = ALL_ROWS Cost=1869 Cardinality=1 Bytes=62
> DELETE Object owner=CRAW Object name=TABLE_FACT
> HASH JOIN RIGHT SEMI Cost=1869 Cardinality=1 Bytes=62
> PARTITION RANGE SINGLE Cost=531 Cardinality=1 Bytes=26
> TABLE ACCESS FULL Object owner=CRAW Object name=TABLE_FACT
> Cost=531 Cardinality=1 Bytes=26
> PARTITION RANGE ALL Cost=1336 Cardinality=319706 Bytes=11509416
> TABLE ACCESS FULL Object owner=CRAW Object name=TABLE_FACT
> Cost=1336 Cardinality=319706 Bytes=11509416
> ***
>
> Bind variables
> All values on the WHERE clauses are parametrized inside the package.
>
> Again, thanking you all for your inputs.
>
> Regards,
> z1hou1
Date: Sat, 14 Nov 2009 07:01:43 -0800 (PST)
Message-ID: <ca1c8cbe-5887-4673-88c5-bab468c324fc_at_s31g2000yqs.googlegroups.com>
On Nov 13, 5:16 pm, z1hou1 <z1h..._at_gmail.com> wrote:
> Hi,
> Thank you for getting back.
>
> We have resolved the performance issue by tweaking the SQL. But we
> still do not understand why there is such a huge difference between
> the 'IN' clause and an EXISTS clause as far as the plan is concerned.
>
> In this case, the EXISTS clause was the culprit and that surprised me.
>
> Here is the SQL along with the plan on the IN that did improve the
> performance both inside the package and the standalone.
>
> DELETE FROM TABLE_FACT F1
> WHERE F1.GL_PK IN
> (SELECT F.GL_PK FROM TABLE_FACT F
> WHERE F.GL_PK = F1.REVERSAL_PARENT_GL_PK
> AND F.SLICE_DATE = '11-NOV-2009'
> AND F.SOURCE_ID = 4
> AND F.POSTING_TYPE_IND = 0
> AND F.REVERSE_ENTRY = 0 AND F.SS_POSTING_FLAG = 0 AND F.ACCRL_RVSL =
> 'Y')
>
> Plan
> DELETE STATEMENT, GOAL = ALL_ROWS Cost=533 Cardinality=1 Bytes=62
> DELETE Object owner=CRAW Object name=TABLE_FACT
> NESTED LOOPS Cost=533 Cardinality=1 Bytes=62
> PARTITION RANGE SINGLE Cost=531 Cardinality=1 Bytes=26
> TABLE ACCESS FULL Object owner=CRAW Object name=TABLE_FACT
> Cost=531 Cardinality=1 Bytes=26
> TABLE ACCESS BY GLOBAL INDEX ROWID Object owner=ABC Object
> name=TABLE_FACT Cost=2 Cardinality=1 Bytes=36
> INDEX UNIQUE SCAN Object owner=CRAW Object name=IDX_PK Cost=1
> Cardinality=1
>
> ****
> Here is the SQL with the EXISTS clause and the plan that slowed the
> delete down to a crawl inside the proc, but in standalone mode it
> executed as fast as the IN clause above. Unfortunately we did not
> capture the plan inside the proc when a trace was carried out earlier.
>
> DELETE FROM TABLE_FACT F1
> WHERE EXISTS
> (SELECT F.GL_PK FROM TABLE_FACT F
> WHERE F.GL_PK = F1.REVERSAL_PARENT_GL_PK
> AND F.SLICE_DATE = '11-NOV-2009'
> AND F.SOURCE_ID = 4
> AND F.POSTING_TYPE_IND = 0
> AND F.REVERSE_ENTRY = 0 AND F.SS_POSTING_FLAG = 0 AND F.ACCRL_RVSL =
> 'Y')
>
> Plan
> DELETE STATEMENT, GOAL = ALL_ROWS Cost=1869 Cardinality=1 Bytes=62
> DELETE Object owner=CRAW Object name=TABLE_FACT
> HASH JOIN RIGHT SEMI Cost=1869 Cardinality=1 Bytes=62
> PARTITION RANGE SINGLE Cost=531 Cardinality=1 Bytes=26
> TABLE ACCESS FULL Object owner=CRAW Object name=TABLE_FACT
> Cost=531 Cardinality=1 Bytes=26
> PARTITION RANGE ALL Cost=1336 Cardinality=319706 Bytes=11509416
> TABLE ACCESS FULL Object owner=CRAW Object name=TABLE_FACT
> Cost=1336 Cardinality=319706 Bytes=11509416
> ***
>
> Bind variables
> All values on the WHERE clauses are parametrized inside the package.
>
> Again, thanking you all for your inputs.
>
> Regards,
> z1hou1
Z1hou1, These appear to be two different SQL statements to me. The first delete is written using an IN clause and the second uses an EXISTS clause. The existts subquery is fired once for each and every row in the driving table. It would apear that then IN cluase version allows the optimizer to perform partition elimination at an earlier point in the logic.
HTH -- Mark D Powell -- Received on Sat Nov 14 2009 - 09:01:43 CST