multiple execution plan for a single delete query
HI All,
We are refreshing the materialized view. 3 explain plan is generated for a delete query.
I regather the stats on Materialized view using for column size 1 options.
Materialized view refresh is taking long time. Is it due to multiple explain plan or is it a Bug.
My environment details.
DB version - 11.2.0.1
OS version - IBM AIX 6.1
/* MV_REFRESH (DEL) */ DELETE FROM "APPS"."GL_BAL
" SNA$ WHERE "C2" IN (SELECT /*+ NO_MERGE */ * FROM (SELECT
CHARTOROWID("MAS$"."M_ROW$$") RID$ FROM "GL"."MLOG$_GL_BALANCES"
"MAS$" WHERE "MAS$".SNAPTIME$$ > :B_ST2 ) AS OF SNAPSHOT(:B_SCN) MAS$)
Plan hash value: 887562636
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | 4 (100)| |
| 1 | DELETE | GL_BAL | | | | |
| 2 | NESTED LOOPS | | 1 | 158 | 4 (25)| 00:05:02 |
| 3 | SORT UNIQUE | | 1 | 138 | 0 (0)| |
| 4 | TABLE ACCESS BY INDEX ROWID| MLOG$_GL_BALANCES | 1 | 138 | 0 (0)| |
| 5 | INDEX RANGE SCAN | MLOG$_GL_BALANCES_IDX | 1 | | 0 (0)| |
| 6 | INDEX RANGE SCAN | C2BAL | 1 | 20 | 3 (0)| 00:03:47 |
----------------------------------------------------------------------------------------------------------------
Plan hash value: 1230754107
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | 4 (100)| |
| 1 | DELETE | GL_BAL | | | | |
| 2 | NESTED LOOPS | | 1 | 158 | 4 (25)| 00:05:02 |
| 3 | SORT UNIQUE | | 1 | 138 | 0 (0)| |
| 4 | TABLE ACCESS BY INDEX ROWID| MLOG$_GL_BALANCES | 1 | 138 | 0 (0)| |
| 5 | INDEX RANGE SCAN | MLOGSNAP | 1 | | 0 (0)| |
| 6 | INDEX RANGE SCAN | GLC2BAL | 1 | 20 | 3 (0)| 00:03:47 |
----------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | 9744K(100)| |
| 1 | DELETE | GL_BAL | | | | |
| 2 | HASH JOIN RIGHT SEMI | | 1 | 148 | 9744K (1)|999:59:59 |
| 3 | TABLE ACCESS BY INDEX ROWID| MLOG$_GL_BALANCES | 1 | 138 | 1 (0)| 00:01:16 |
| 4 | INDEX RANGE SCAN | MLOGSNAP | 1 | | 2 (0)| 00:02:31 |
| 5 | MAT_VIEW ACCESS FULL | GL_BAL | 404M| 3859M| 9744K (1)|999:59:59 |
---------------------------------------------------------------------------------------------------------------
Please suggest
Thanks