Re: delete performance from a standalone sql versus the same call from a procedure
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Fri, 13 Nov 2009 23:24:48 +0100
Message-ID: <4afddcb0$0$22917$e4fe514c_at_news.xs4all.nl>
z1hou1 schreef:
> 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: Fri, 13 Nov 2009 23:24:48 +0100
Message-ID: <4afddcb0$0$22917$e4fe514c_at_news.xs4all.nl>
z1hou1 schreef:
> 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
Did you use a cursor inside your pl/sql app? If you use a plain sql statement with a procedure parameter, bind variables are not used. If you use a cursor with a parameter, they are. Still doesn't explain the exist/in difference....
Your exist could have used
where exists (select 1 from ...) by the way.
Shakespeare Received on Fri Nov 13 2009 - 16:24:48 CST