| oracle 10g cbo slower than rbo [message #151233] |
Tue, 13 December 2005 19:22  |
lazycat
Messages: 57
Registered: November 2005
|
Member |
|
|
i have sql which i can run it with rbo
but we can't get the result if i use the cbo
it is too slow
why
who can help me
21:50:37 SQL> EXPLAIN PLAN FOR
21:51:11 2 update lei_tzmcc_ydgh a set (a.remain_money,a.reserve)=
21:51:15 3 (select c.SPECIAL_CARD_BAL/100,c.ACC_BAL_TOTAL/100 from databak.cm_user b,databak.cm_account c
21:51:28 4 where a.tele_num=b.bill_id and b.acc_id=c.acc_id);
Explained.
21:51:49 SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)|
--------------------------------------------------------------------------------
-------
| 0 | UPDATE STATEMENT | | 16443 | 642K| 23
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
(5)|
| 1 | UPDATE | LEI_TZMCC_YDGH | | |
|
| 2 | TABLE ACCESS FULL | LEI_TZMCC_YDGH | 16443 | 642K| 23
(5)|
| 3 | TABLE ACCESS BY INDEX ROWID| CM_ACCOUNT | 1 | 39 | 1
(0)|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 4 | NESTED LOOPS | | 35976 | 2143K| 23554
(1)|
|* 5 | VIEW | index$_join$_002 | 35976 | 772K| 1944
(4)|
|* 6 | HASH JOIN | | | |
|
|* 7 | INDEX RANGE SCAN | INDX_CM_USER | 35976 | 772K| 2
(50)|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 8 | INDEX FAST FULL SCAN | INDX_CM_USER_1 | 35976 | 772K| 1890
(2)|
|* 9 | INDEX RANGE SCAN | INDX_CM_ACCOUNT_1 | 1 | | 1
(0)|
--------------------------------------------------------------------------------
-------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("B"."BILL_ID"=:B1)
6 - access(ROWID=ROWID)
7 - access("B"."BILL_ID"=:B1)
9 - access("B"."ACC_ID"="C"."ACC_ID")
Note
-----
- 'PLAN_TABLE' is old version
----------------------------------------------------------
--
----------------------------------------------------------
21:54:43 SQL> EXPLAIN PLAN FOR
21:54:50 2 update /*+rule*/lei_tzmcc_ydgh a set (a.remain_money,a.reserve)=
21:54:54 3 (select c.SPECIAL_CARD_BAL/100,c.ACC_BAL_TOTAL/100 from databak.cm_user b,databak.cm_account c
21:54:57 4 where a.tele_num=b.bill_id and b.acc_id=c.acc_id);
Explained.
21:55:02 SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------------
| 0 | UPDATE STATEMENT | |
| 1 | UPDATE | LEI_TZMCC_YDGH |
| 2 | TABLE ACCESS FULL | LEI_TZMCC_YDGH |
| 3 | TABLE ACCESS BY INDEX ROWID | CM_ACCOUNT |
| 4 | NESTED LOOPS | |
| 5 | TABLE ACCESS BY INDEX ROWID| CM_USER |
|* 6 | INDEX RANGE SCAN | INDX_CM_USER |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 7 | INDEX RANGE SCAN | INDX_CM_ACCOUNT_1 |
------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("B"."BILL_ID"=:B1)
7 - access("B"."ACC_ID"="C"."ACC_ID")
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- 'PLAN_TABLE' is old version
- rule based optimizer used (consider using cbo)
24 rows selected.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: oracle 10g cbo slower than rbo [message #151559 is a reply to message #151233] |
Thu, 15 December 2005 09:38  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I'm with Frank on this one, I'm not going to even try to read a plan with that kind of formatting, I've got better things to do.
And I'd be happy to work on a query that someone thinks is better with RBO than CBO. Just post us a nice test case that we can reproduce and I'll give it a shot.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
