Re: delete performance from a standalone sql versus the same call from a procedure

From: z1hou1 <z1hou1_at_gmail.com>
Date: Fri, 13 Nov 2009 14:16:00 -0800 (PST)
Message-ID: <aec31345-8c82-49e3-8cad-c93f0c4abb6d_at_k4g2000yqb.googlegroups.com>

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 Received on Fri Nov 13 2009 - 16:16:00 CST