Home » RDBMS Server » Performance Tuning » oracle 10g cbo slower than rbo
oracle 10g cbo slower than rbo [message #151233] Tue, 13 December 2005 19:22 Go to next message
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 #151263 is a reply to message #151233] Wed, 14 December 2005 00:38 Go to previous messageGo to next message
Frank
Messages: 7897
Registered: March 2000
Senior Member
Nice formatting; makes it all really readable.
Did you analyze your tables and indexes?
What sizes are your tables?
Re: oracle 10g cbo slower than rbo [message #151269 is a reply to message #151263] Wed, 14 December 2005 00:53 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member

Just curious : Is RBO supported in 10g?

As i know RBO is desupported in 10g.

regards,
tarun
Re: oracle 10g cbo slower than rbo [message #151298 is a reply to message #151233] Wed, 14 December 2005 02:29 Go to previous messageGo to next message
lazycat
Messages: 57
Registered: November 2005
Member
user hits
Re: oracle 10g cbo slower than rbo [message #151345 is a reply to message #151298] Wed, 14 December 2005 07:11 Go to previous messageGo to next message
vjeedigunta
Messages: 201
Registered: March 2005
Location: Hyderabad
Senior Member

I have personally found in many situations that RBO is much better than CBO ..Especially for the tables which are huge in size and which are probe for frequent mass updates (cant help !!! as per the design of the app) ..rather than analyzing the table regular -- RBO gives better results ..

Not sure about 10G though

-Sai Jeedigunta
Re: oracle 10g cbo slower than rbo [message #151359 is a reply to message #151345] Wed, 14 December 2005 07:49 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10706
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
@vjeedigunta
Are you using Oracle 7.x or earlier versions?
Then, all you said may hold true.
RBO was designed to work only with those versions.



Re: oracle 10g cbo slower than rbo [message #151559 is a reply to message #151233] Thu, 15 December 2005 09:38 Go to previous message
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.
Previous Topic: SQL - very slow response
Next Topic: my application is too slow.
Goto Forum:
  


Current Time: Mon Nov 28 06:18:43 CST 2022