Bug 9290526 - Poor plan for recursive SQL used for DML involving a UNIQUE constraint [ID 9290526.8] |
|
A suboptimal plan may be seen for the specific recursive SQL:
select c.name, u.name
from con$ c, cdef$ cd, user$ u where c.con# = cd.con# and cd.enabled =:1 and
c.owner# = u.user#;
This can cause inserts involving a unique constraint to show poor performance.
Workaround
Set optimizer_mode=rule for the affected DMLs.
sys@test>explain plan for
2 select c.name, u.name from con$ c, cdef$ cd, user$ u where c.con# =
3 cd.con# and cd.enabled = :v1 and c.owner# = u.user#;
Explained.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2222027377
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 336 | 10 (10)| 00:00:01 |
|* 1 | HASH JOIN | | 7 | 336 | 10 (10)| 00:00:01 |
|* 2 | HASH JOIN | | 7 | 203 | 8 (13)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| CDEF$ | 7 | 56 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I_CDEF4 | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | CON$ | 3584 | 75264 | 5 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | USER$ | 33 | 627 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C"."OWNER#"="U"."USER#")
2 - access("C"."CON#"="CD"."CON#")
4 - access("CD"."ENABLED"=TO_NUMBER(:V1))
20 rows selected.
sys@test>explain plan for
2 select /*+ rule */ c.name, u.name from con$ c, cdef$ cd, user$ u where c.con# = cd.con# and cd.enabled = :v1 and c.owner# = u.user#;
Explained.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1027684349
-------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS BY INDEX ROWID| CDEF$ |
|* 4 | INDEX RANGE SCAN | I_CDEF4 |
| 5 | TABLE ACCESS BY INDEX ROWID| CON$ |
|* 6 | INDEX UNIQUE SCAN | I_CON2 |
| 7 | TABLE ACCESS CLUSTER | USER$ |
|* 8 | INDEX UNIQUE SCAN | I_USER# |
-------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("CD"."ENABLED"=TO_NUMBER(:V1))
6 - access("C"."CON#"="CD"."CON#")
8 - access("C"."OWNER#"="U"."USER#")
Note
-----
- rule based optimizer used (consider using cbo)
26 rows selected.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7591490/viewspace-1058153/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7591490/viewspace-1058153/