CBO - Production; RBO - Development
Yogeeraj, July 01, 2002 - 8:25 am UTC
Hello,
We were thinking about setting the optimizer mode for our production database to CBO and that of our development database to RBO.
Is this a good idea? (or would be better to have both environment set to CBO?) What would be the advantages of "moving" statistics gathered on a production database to a development database?
{we would ensure that all the queries written by on the development database are well written - Rule-based; We would setup all the environment for statistics gathering and monitoring on the production database)
Thank you for a reply
Best Regards
Yogeeraj
July 01, 2002 - 8:44 am UTC
You are kidding right? wow.
Why not develop on Sybase and deploy on Oracle then?
That would be a horrible idea -- think about it.
a) the set of query plans possible with the CBO is much larger then RBO
b) what is "well written"? If you take a "finely tuned RBO query" and through it at a CBO database, well, it'll be optimized totally different. Doesn't matter how "nicely" it is written -- the CBO doesn't look at the syntax (order of tables -- doesn't matter, order of predicates, doesn't matter, and so on)
Development should be a mirror of production (as should TEST).
Could you please explain more on this
A reader, July 01, 2002 - 9:42 am UTC
"The rows with optimizer cost > 0, those are CBO, the others -- not."
Could you please explain more on this... I mean why would the cost be zero for RBO? Also, I was thinking that which optimizer is used at runtime depend solely on the OPTIMIZER_MODE parameter. Could you please explain how Oracle decide on which optimizer to use. Thanks.
July 01, 2002 - 10:30 am UTC
A zero cost would be invalid (not a cost at all, the smallest cost is 1)
they are sticking a zero in there when CBO is not used, thats all. (sometimes null).
The optimizer used is NOT dependent on the optmizer goal setting -- you can be using CBO when you ask for RBO and you can be using RBO when you ask for CBO.
Hints will override that setting (see my example, I used hints)
Use of features that require CBO will override that setting. Consider:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x int primary key ) organization index;
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t TKYTE_TEST;
no rows selected
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select b.sql_text, b.optimizer_cost
2 from v$sql b
3 where upper(b.sql_text) like '%TKYTE_TEST%';
SQL_TEXT OPTIMIZER_COST
---------------------------------------- --------------
select * from t TKYTE_TEST 1
select b.sql_text, b.optimizer_cost from 0
v$sql b where upper(b.sql_text) like '%
TKYTE_TEST%'
and IOT (index organzed table) only works in CBO -- so, the select * from t is CBO (cost=1, cost > 0). The other query -- the query used to find this query -- is in RBO (cost = 0, cost is not > 0)
A reader, July 01, 2002 - 10:36 am UTC
Tom,
One of the recent note in Metlink tells that RULE base optimizer is obsolete after 9.2(Oracle won't support after 9.2 release).
is that true?
That mean "Optimizer stability plan" (Outline) feature
also become obsotele after release 9.2.
am i right ?
Please explain.
Thanks
July 01, 2002 - 10:45 am UTC
As far as I know, yes, the RBO is "dead" in the next release.
No, that does not obviate the optimizer plan stability feature -- that feature is only needed when using the CBO.
Optimiser Statistics and the data dictionary
Matt, July 01, 2002 - 8:48 pm UTC
This definately answers my question. I am a little concerned about the comment about the hint and the v$ views though. I guess that the query runs slowly when the hint is included in the select is because all the base data dictionary tables are having stats generated at runtime??
In the system I am working on we have a user defined view that joins with v$session. Does this mean that to make sure that this user defined view performs reasonably that I will need to generate statistics on the data dictionary? Is there a better way around this?
July 02, 2002 - 7:50 am UTC
Do not generate statistics on the data dictionary. Do not touch the data dictionary (without support saying "do this")
You may use a hint in the query that accesses v$session if you find the generated plan to not be optimal.
Column optimizer_mode on v$sql
Robert Hayden, July 02, 2002 - 10:05 am UTC
I was checking this out in Oracle 8.1.7.3 set in RBO mode and noted an interesting issue that is confusing. When I use a all_rows hint, then the column, optimizer_mode in v$sql, is set to ALL_ROWS. When I use an index() hint, the optimizer_mode remains to be RULE, but the optimizer_cost is non-zero. Does this mean that the index hint invoked the CBO or not?
.
In Oracle 7.3, we have always thought that any Oracle hints invoked the CBO, but I have never been able to prove it because I have focused on the optimizer_mode and on index hints since that is what the application uses.
.
Thanks
July 02, 2002 - 12:50 pm UTC
The optimizer mode was RULE but the query was optimized by CBO cause the HINT overrides the optimizer mode. It was CBO'ed
Even in optimizer_mode=rule, the CBO can (and will) be used. The optimizer mode is just a setting that says "hey, if you have a choice, use the RBO". If you don't have a choice (a hint is used, a table with a non-default degree of parallelism was used, a partitioned table was used, and IOT was used, etc) then use whatever you need.
stats on dictionary
A reader, July 02, 2002 - 12:29 pm UTC
What if someone makes a mistake and generates statistics on the data dictionary? Can recreation of dictionary help?
July 02, 2002 - 2:43 pm UTC
well, that would be like using an atom bomb to crack a walnut.
I would just delete the statistics.
Which parameters influence the CBO the most?
Matt, January 14, 2003 - 1:00 am UTC
OK, we upgraded from 7.3.4.5.0 to 9.0.1.4.0 (the original post).We are currently struggling with the CBO. We are trying to implement this on a module by module basis as per the original post. Most of our modules when run vanilla with CBO out of the box run slower than the RBO.
Typically which parameters are likely to be the culprit when looking at poor plans? See below:
SORT_AREA_SIZE
HASH_AREA_SIZE
HASH_JOIN_ENABLED
DB_FILE_MULTIBLOCK_READ_COUNT
OPTIMIZER_INDEX_CACHING
OPTIMIZER_INDEX_COST_ADJ
others?
I will post a detailed question regarding the tuning issues at the proper time (when I see the NO BACKLOG banner)
Regards.
January 14, 2003 - 7:39 am UTC
Matt, January 14, 2003 - 5:19 pm UTC
Yep, I already had - mine was the first follow up comment on the linked question.
I will continue with my investigations and lodge a question with all the required detail later.
Regards,
Mergeable and Non-mergeable Views
GVN, February 06, 2003 - 11:05 am UTC
Tom,
In the oracle 'Oracle8i Designing and Tuning for Performance' documentation
</code>
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76992/optimops.htm#14720 <code>
The section of 'Mergeable and Non-mergeable Views' mentions the following
<quote>The optimizer can merge a view into a referencing query block when the view has one or more base tables</quote>
1) What is a referencing query block?
<quote>View merging is not possible for a view that has multiple base tables if it is on the right side of an outer join. However, if a view on the right side of an outer join has only one base table, then the optimizer can use complex view merging, even if an expression in the view can return a non-null value for a NULL</quote>
2) Does the above <quote> apply to an optimizer that uses CBO or does it just apply to RBO? I would apprecite it if you can give a little explanation.
Thank you in advance for the valuable advise.
February 06, 2003 - 11:27 am UTC
the outer query block
select *
from ( select empno, ename, sal, emp.deptno, dept.dname
from emp, dept
where ename like '%A%'
and emp.deptno = dept.deptno )
where dname like '%A%'
the bold stuff is the "referencing query block" to the non-bold stuff.
<quote from same place>
Merging the View's Query into the Statement
To merge the view's query into a referencing query block in the accessing statement, the optimizer replaces the name of the view with the names of its base tables in the query block and adds the condition of the view's query's WHERE clause to the accessing query block's WHERE clause.
</quote>
So here, the query will be processed as:
select empno, ename, sal, emp.deptno, dept.dname
from emp, dept
where ename like '%A%'
and emp.deptno = dept.deptno
and dname like '%A%'
Now, if the query where:
select *
from ( select empno, ename, sal, emp.deptno, dept.dname
from emp, dept
where ename like '%A%'
and emp.deptno = dept.deptno(+) )
where dname like '%A%'
we cannot do that merging
2) complex view merging can happen for both RBO and CBO but there are sooooo many things only the CBO can do that, well, you should be using it.
Mergeable and Non-mergeable Views
GVN, February 06, 2003 - 11:59 am UTC
What appeared to be Greek and Latin before posting the question is quite clear after your answer
Thank you
What is the difference in the settings OPTIMIZER_GOAL and OPTIMIZER_MODE?
Abhijit, March 04, 2003 - 7:01 am UTC
Hi Tom,
I am using Oracle 8i
Could you please tell me what is the difference in the following two statements?
1) alter session OPTIMIZER_GOAL = CHOOSE;
2) alter session OPTIMIZER_MODE = CHOOSE;
Thanks & Regards,
Abhijit
March 04, 2003 - 6:11 pm UTC
more or less the same, they both set the sessions optimizer technique to choose.
Thanks for the answer
Abhijit, March 04, 2003 - 10:57 pm UTC
However if my mode differs from my goal, which optimizer will be used?
Thanks & Regards,
Abhijit
March 05, 2003 - 7:34 am UTC
they are synonymous, whichever goes last. watch
ops$tkyte@ORA817DEV> set autotrace traceonly explain
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select * from dual;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'
<b>start out using RBO (no cost/card/bytes in the plan)</b>
ops$tkyte@ORA817DEV> alter session set OPTIMIZER_GOAL = FIRST_ROWS;
Session altered.
ops$tkyte@ORA817DEV> select * from dual;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1 Card=82 Bytes=164)
1 0 TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=82 Bytes=164)
<b>that put us in CBO....</b>
ops$tkyte@ORA817DEV> alter session set OPTIMIZER_MODE = CHOOSE;
Session altered.
ops$tkyte@ORA817DEV> select * from dual;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'
<b>and that took us back out</b>
ops$tkyte@ORA817DEV> alter session set OPTIMIZER_MODE = FIRST_ROWS;
Session altered.
ops$tkyte@ORA817DEV> select * from dual;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1 Card=82 Bytes=164)
1 0 TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=82 Bytes=164)
<b>now to show that mode is overriden by goal -- as we showed above that goal is overriden by mode</b>
ops$tkyte@ORA817DEV> alter session set OPTIMIZER_GOAL = CHOOSE;
Session altered.
ops$tkyte@ORA817DEV> select * from dual;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'
<b>we are back in rbo mode</b>
they cannot differ because they are really the same.
what about response time?
Tony, June 02, 2003 - 7:35 am UTC
Cost based optimizer always goes for lesser cost plan. My doubt is if the response time is considered or not for the cost calculation. What if I'm not bothered about the cost, but want a query to return rows as quickly as possible?. How to achive this in CBO?
June 02, 2003 - 8:09 am UTC
you would use FIRST_ROWS optimization to tell the optimizer "I'm interested in response time, the time to get the first row should be minimized"
And later, in your reports and batch programs, ALL_ROWS optimization is what you would want.
So, your interactive apps might consider "alter session set optimizer_goal=first_rows" where as your batch/reports might consider "=all_rows"
Response time and Cost
Anil Pant, September 08, 2003 - 8:42 am UTC
Im bit confused with Cost and Response time. The response time is based on cost. Higher the cost more the response. Or do u mean that cost incurred to fetch the row would be different.
September 08, 2003 - 11:28 am UTC
think about this.
if I decrease the cost of a query, but the plan stays the same, does the query actually execute faster????
they are loosely related. you cannot look at cost and say 'that'll take 5 minutes to run'
response time is "real"
cost is a "guess"
cost is based loosely on anticipated response time base on estimated resources need to execute the query.
Why worry of cost ?
Anil Pant, September 09, 2003 - 3:04 am UTC
Agreed. But Im more interested in response time and i want to minimize that then why should I worry abt cost ? In this case what to do ?
September 09, 2003 - 11:34 am UTC
tune the query?
not sure what to say here really.
cost is sort of related to response time, but not in a fashion that you can say "cost = 10324 means 5 minutes".
in theory a query plan with a lower cost that does the same thing as some other plan with a higher cost would run faster
however, it is just software based on a mathematical model -- hence, it might not be true in every case (i know it isn't)
so, you tune.
interesting
A reader, December 23, 2003 - 6:30 am UTC
I thought I was the only seen this but it seems that you use it too Tom
OPS$TKYTE SELECT NULL FROM DUAL FOR UPDATE NOWAIT
We have SELECT NULL FROM DUAL FOR UPDATE NOWAIT in our Peoplesoft ERP, what does this do and the purpose?
Nothing in my humble opinion.....
December 23, 2003 - 11:31 am UTC
it is a way to serialize operations.
only one person at a time can do that -- others block.
So, it has a profound effect on things -- it is used to serialize access to something, like a big lock.
I don't recall what application I might have been using that did that tho, or if it was part of another example. But -- it is a method of serializing processes.
serialize DUAL?!
A reader, December 23, 2003 - 4:24 pm UTC
err do you mean serialize what kind of operation... I mean noone is inserting dual isnt it?!
December 23, 2003 - 5:31 pm UTC
no, they are using DUAL to serialize SOME operation.
suppose you have a report, you only want this report run by a single user at a time.
make the first line of code in the report:
select null from dual for update;
and the last line:
commit;
now, one person will run the report at a time. it is just a serialization device
hmm how does dual serialize
A reader, December 23, 2003 - 5:35 pm UTC
Hi
It's DUAL Table magic or this works for any 1 row 1 column table?
I dont see how a report can be run by one guy only after setting that...?
If I do
select null from dual for update and select * from emp in session_a, none else in other sessions can run select * from emp?
December 23, 2003 - 5:54 pm UTC
works on ANY TABLE
you've said "hey,select all of the rows for update (lock them)"
no one else can do that until you commit.
no one else can select null from dual for update, anyone can select * from emp.
the point is, a program would use the for update on that table to serialize some process -- every instance of that process would go through the same steps -- get a lock, do something, release lock
endless pain moving RBO to CBO
A reader, January 21, 2004 - 6:43 am UTC
Hi
We are planning switch RBO to CBO in our customer care application. We are running on 8.1.7.4.
We have been sql tracing batch processes RBO vs CBO and the all time winner is RBO (BIG winner). Statistics such as 50000 consistent gets in RBO vs 500000 in CBO and so on.
For example in a 4 table join where two of them has 20 rows, one has 2.5million of rows and the other 30 million of rows, in RBO this runs lightling fast, around 45 seconds (all index scans), in CBO it chooses full table scan 3 tables, except one of the small tables...
I dont understand, this application is nothing fancy. Why CBO is choose so bad execution plans?
We use DBMS_STATS using 25 estimate and 75 buckets histograms, granularity ALL, cascade TRUE
A reader, July 28, 2004 - 5:57 pm UTC
Tom,
someone has told me that the results may be different under COST vs under RULE.
I always think only the performance may be various for the 2, and never know that the results might be chnaged to swicth from one to another.
July 29, 2004 - 7:27 am UTC
if the results "change" given the same inputs - that would clearly be a bug.
if the RBO gives the answer 55 and the CBO gives the answer 42 -- given the same data, that would be a bug.
However, the order of the data probably will change, unless you include an order by. they could have (erroneously) been relying on some expected, implicit order of data that changed due to a change in plan.
Moving from RBO to CBO
Peter, September 20, 2004 - 9:38 pm UTC
Tom,
I have an application with thousands of front end SQL statements with tolerable performance ( remember some queries running for 5 to 10 minutes also).
1.I am in 8.1.7. If I move the application from RBO to CBO except for the optimizer parameter tuning, can I be assured that all queries will atleast behave with the same performance as that of RBO?
2. In one of my attempts, some queries made the system to hang when moving from RBO to CBO. Pl note all tables, indexes analyzed. Why should CBO make the system to hang?
3.Hints are available only for CBO. However, there are articles / powerpoint slides stating that RBO can also have hints. Is it true?
September 21, 2004 - 7:35 am UTC
1) testing, testing, testing. testing to scale, testing for functionality.
As you would of course do with any sort of large change, we would never just flip the switch in production and see what happens...
2) if you truly believe the system was "hanging", please contact support. Else, use traditional techniques such as sql_tracing to see what the queries are doing, v$ views to see what the sessions are waiting on. In all probability, it was the result of a bad plan and the query was not "hung", just taking longer than you anticipated.
3) there are hints that work with the RBO, such as APPEND -- yes. they do not affect the access path really
set optimizer_mode parameter
Sean, May 22, 2005 - 11:08 am UTC
Hi Tom,
Optimizer_mode is a dynamic parameter according to reference manual. One should be able to use scope=both for such parameter, according to admin manual. I just want to change the parameter without bouncing the database. But I was not able to change this parameter with scope=both.
1* alter system set optimizer_mode='CHOOSE' scope=spfile
SQL> /
System altered.
SQL> alter system set optimizer_mode='CHOOSE' scope=both;
alter system set optimizer_mode='CHOOSE' scope=both
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option
Version: 9204 on Solaris.
Thanks so much for your help.
May 22, 2005 - 11:45 am UTC
ops$tkyte@ORA9IR2> select isses_modifiable, issys_modifiable
2 from v$parameter where name = 'optimizer_mode';
ISSES ISSYS_MOD
----- ---------
TRUE FALSE
it is SESSION modifiable, not SYSTEM modifiable.
If you need to do this and cannot bounce, you could consider creating a trigger to issue the alter session upon logon and dropping the trigger the next time you shutdown.
collect statistics information
Sean, May 25, 2005 - 11:11 am UTC
Hi Tom,
Our database is about 1 TB and have a lot of big partitioned tables. We used to use RBO and now we decide to use CBO. This is the first time I collect statistics by issuing
QL> execute dbms_stats.gather_database_stats;
It has been running more than 72 hrs and still running. I am almost the only user using the server now. It is the Sun box with 2x 1000 MHz cpu and 8G memory. Oracle is 9204.
My question is whether I should collect statistics this way or run multiple sessions, each against one schema or even one single big table.
Thanks so much for your help.
May 25, 2005 - 1:32 pm UTC
if you have a lot of big partitioned tables, I'm afraid to tell you that you have been using the CBO all along. It is not possible to even QUERY a partitioned table without using the CBO!!!
Are you sure you haven't been gathering statistics in the past?
Are you really doing this to a live production system people count on?
You probably do NOT want to gather database stats, you want to do this most likely table by table (I like table by table) as you might find you want slightly different options for some tables. Some you will compute, some you will estimate, some you will have histograms for columns, others you won't.
but please, tell me you are doing this NOT on a production box?
collecting statistics
Sean, May 25, 2005 - 2:39 pm UTC
Hi Tom,
It is the replica of production database with historical data, used to generate report. We have used RBO for a long time, even for very big partition tables, and developer didnt complain. Only very recently we had performance issue to create cube using olap tool and we decide to try CBO to see whether it helps or not.
I have killed the session and now run multiple sessions to collect table level statistics.
Thanks so much for your help.
May 25, 2005 - 7:32 pm UTC
<b>We have used RBO for a long time, even for very big partition tables,</b>
sorry -- but no, you haven't. It is quite impossible. consider:
ops$tkyte@ORA9IR2> CREATE TABLE nopart
2 (
3 dt date,
4 x int,
5 y varchar2(25)
6 )
7 /
Table created.
ops$tkyte@ORA9IR2> CREATE TABLE part
2 (
3 dt date,
4 x int,
5 y varchar2(25)
6 )
7 PARTITION BY RANGE (dt)
8 (
9 PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) tablespace users,
10 PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) tablespace tools,
11 PARTITION junk VALUES LESS THAN (MAXVALUE)
12 )
13 /
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select /*+ RULE */ * from nopart;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF 'NOPART'
ops$tkyte@ORA9IR2> select /*+ RULE */ * from part;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE (Cost=2 Card=246 Bytes=8856)
1 0 PARTITION RANGE (ALL)
2 1 TABLE ACCESS (FULL) OF 'PART' (Cost=2 Card=246 Bytes=8856)
see the cost=, card=, bytes=? they mean "CBO WAS USED", no doubts. It is very much impossible to query a partitioned table without the CBO.
Is Oracle using RBO or CBO?
Sean, May 26, 2005 - 9:33 am UTC
All I can say that we used optimizer_mode=RULE and we didnt collect statistics on any of tables. Are you saying that as long as there is value of cost in the explain plan, Oracle is using CBO? Quite shocking!
Thanks so much for your help.
May 26, 2005 - 10:17 am UTC
If you query an IOT -- cbo
If you query a partitioned table -- cbo
If you query a table with a non-default degree of parallelism -- cbo
If you query a table with a domain (text or spatial for example) index -- cbo
if you see the COST= in the plan, CBO
Steps To Migrate From RBO to CBO
Nikhilesh, February 13, 2006 - 5:47 am UTC
Dear Tom,
I'm also facing the same problem.We use ver 9.2 and have been using RBO till date. But now as we are having so many performance issues and also we want to implement partitioning for some of the tables, we need to migrate from RBO to CBO.
So could you please help me how to set the environment so that we can test the performance.
I mean
1) the correct method to collect the stats
2) the key CBO parameter we'll need to set or reset.
3) Any key areas that needs special attention.
I don't know if i could write this problem in short but
Thanks in Advance.....
February 13, 2006 - 8:38 am UTC
1) if there were a single correct method for all cases, there would be a single command "collect stats;"
2) none, you want to set *none*.
3) an understanding of what the statistics do, how they are used. So, when things do not work out the way you feel they should - you can understand "why" and try to adapt.
I suggest Jonathan Lewis's book, see </code>
http://asktom.oracle.com/Misc/rant-for-today.html <code>
why would rule based optimizer choose different plan when statistics change?
Jianhui, March 17, 2006 - 9:39 pm UTC
Hi Tom,
We did 9i upgrade from 8i but still using RBO with optimizer feature set to 817. We have a SQL with four tables join started having problem since upgrade. The execution plan has changed to hash join instead of original nest loop join, after we analyze one of the join table, it picked the right plan again. Why would RBO care about the statistics? It behaves like CBO in my case. Could you explain why?
Thanks
March 18, 2006 - 4:34 pm UTC
if you are using a hash join, well, I'm afraid that you are NOT using the RBO. The RBO doesn't generate plans with hash joins, it is not able to.
why not using RBO
jianhui, March 18, 2006 - 8:27 pm UTC
Tom,
Thanks for then answers. Then why does oracle uses CBO when i have optimizer_goal set to RBO, and none of the tables involved is partitioned? The execution plan even funny,it says optimizer is RULE, and it also gives me the cost. How to turn CBO off?
March 19, 2006 - 6:46 am UTC
give example. There are other things that force the use of the CBO such as
o partitioned tables
o accessing an IOT
o using a table with a non-default degree of parallelism
o accessing a table with a domain index
In the following, note that only T1 uses the RBO - the others *must* use the CBO and there is no way to "turn it off" short of removing the use of the feature that forces it:
ops$tkyte@ORA9IR2> create table t1 (x int primary key );
Table created.
ops$tkyte@ORA9IR2> create table t2 (x int primary key ) organization index;
Table created.
ops$tkyte@ORA9IR2> create table t3 (x int primary key, y varchar2(255) );
Table created.
ops$tkyte@ORA9IR2> create index t3_idx on t3(y) indextype is ctxsys.context;
Index created.
ops$tkyte@ORA9IR2> create table t4( x int primary key ) parallel 2;
Table created.
ops$tkyte@ORA9IR2> alter session set optimizer_goal = rule;
Session altered.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from t1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 TABLE ACCESS (FULL) OF 'T1'
ops$tkyte@ORA9IR2> select * from t2;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=5 Card=2000 Bytes=26000)
1 0 INDEX (FAST FULL SCAN) OF 'SYS_IOT_TOP_37028' (UNIQUE) (Cost=5 Card=2000 Bytes=26000)
ops$tkyte@ORA9IR2> select * from t3;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=2 Card=2000 Bytes=284000)
1 0 TABLE ACCESS (FULL) OF 'T3' (Cost=2 Card=2000 Bytes=284000)
ops$tkyte@ORA9IR2> select * from t4;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=1 Card=2000 Bytes=26000)
1 0 TABLE ACCESS* (FULL) OF 'T4' (Cost=1 Card=2000 Bytes=26000) :Q4000
1 PARALLEL_TO_SERIAL SELECT /*+ NO_EXPAND ROWID(A1) */ A1."X" FRO
M "T4" PX_GRANULE(0, BLOCK_RANGE, DYNAMIC)
A1
ops$tkyte@ORA9IR2> set autotrace off
CBO and DBMS_STATS
Ram K, March 02, 2007 - 3:03 pm UTC
Tom
Oracle 9iR2
If you analyze a table using ANALYZE command, does CBO use those STATS ?. Reason why iam asking is,
I have a table being Analyzed using ANALYZE and NOT DBMS_STATS. here is the sample...
==================================================
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ -----------
optimizer_mode string CHOOSE
SQL> analyze table center estimate statistics sample 25 percent;
Table analyzed.
SQL> select count(company_id) from center RAM_TEST;
COUNT(COMPANY_ID)
-----------------
475949
SQL> column sql_text format a24;
SQL> select b.sql_text, b.optimizer_cost
2 from v$sql b
3 where upper(b.sql_text) like '%RAM_TEST%';
SQL_TEXT OPTIMIZER_COST
------------------------ --------------
select b.sql_text, b.opt 0
select count(company_id) 123
==================================================
so obviously..my SQL statement is using CBO based on count = 123.
so how do you explain that based on Oracle Documentation that :
===========================================
"the cost-based optimizer will eventually use only statistics that have been collected by DBMS_ STATS."
===============================================
I collected my stats using ANALYZE command ..so why is Oracle instead of using RBO is using CBO. This TAble is not Partitioned table.
any help is appreciated..
Ram K
March 04, 2007 - 6:29 pm UTC
the keyword is
eventually
Fregmentation with RBO
A reader, May 07, 2007 - 8:57 am UTC
Tom,
Thanks for your help to Oracle users.
We are running a 9i database whic is stilll using RBO!!
In last few weeks we have started experiencing performance degrade in the database. I am asked to find out if there is any fregmentation in database tables. I was just wondering if you could point me some article where I can find the script to find out fregmentation in tables & indexes.
Cheers
May 08, 2007 - 10:55 am UTC
I am driving my car.
It is going slow.
I have been asked to find out if it is dirty.
Those sentences make as much connected sense as:
we are running 9i
it is going slow
I have been asked to find out if there is any fragmentation
The answer is always "yes, segements are by designed stored in oracle in fragments, next question"
Fregmentation with RBO
A reader, May 08, 2007 - 11:20 am UTC
Tom,
Sorry if I was not clear on my last post. The problem is I have not worked with RBO before and I wanted to check HWM for few tables as few queries which are going for full table scans are taking long.
Can you please supply me some script which could tell me empty blocks for a table when using RBO?
Thanks
May 10, 2007 - 8:44 pm UTC
why is "when using RBO" relevant at all here??? I am very curious.
why do you seem to imply it might be different with the CBO?
search this site for "showspace"
Fregmentation with RBO
Kyle, May 10, 2007 - 7:34 pm UTC
We had fragmentation. It slowed us down. It was not the only issue.
So, to see if your car is dirty, try this:
SELECT
fs.tablespace_name
,df.file_name
,COUNT(*) AS fragments
,ROUND(SUM(fs.bytes)/1024,2) AS total_kb
,ROUND(MAX(fs.bytes)/1024,2) AS biggest_kb
FROM
DBA_FREE_SPACE fs
,DBA_DATA_FILES df
WHERE
fs.file_id(+) = df.file_id
GROUP BY
fs.tablespace_name
,df.file_name
HAVING
COUNT(*) > 100
ORDER BY
3 DESC;
To wash your car, try something like this:
declare
X varchar2(128);
Y number;
cursor my_cursor is
SELECT
distinct 'ALTER TABLESPACE ' || fs.tablespace_name || ' COALESCE' as "MY_COLUMN"
FROM
sys.DBA_FREE_SPACE fs;
my_row my_cursor%rowtype;
begin
y := 0;
for my_row in my_cursor
loop
y := y + 1;
select my_row.MY_COLUMN into X from sys.dual;
dbms_output.PUT_LINE( 'EXECUTE IMMEDIATE ' || X );
--EXECUTE IMMEDIATE X;
end loop;
dbms_output.PUT_LINE('There are '||Y||' rows in here');
end;
In order to actually work, you need to uncomment the EXECUTE IMMEDIATE and have it run with sufficient permissions.
Then go update your statistics.
Then profile your code and look for tuning opportunities on the long running SQLs. Bitmap indexes and materialized views with query rewrite were what actually solved our problems.
May 11, 2007 - 11:26 am UTC
umm, that won't do a blasted thing to "reduce fragmentation"
that'll take adjacent free extents in a DICTIONARY managed tablespace and turn them into one large extent
but it won't actually move anything anywhere - the entire concept of "i am fragmented" is predicated by some fact - the fact that "my stuff is scattered"
i would presume the only way to "unfragment" something is to "unscatter it"
but someone would seriously have to define "my tablespace is fragmented" for me first
....
Bitmap indexes and materialized views with query rewrite were what actually solved our problems. ....
that was funny - "we found a dirty car, we washed it", and then found out that by putting gas in it, we could actually make it go somewhere.
???
where else
a, May 10, 2007 - 7:37 pm UTC
RBO to CBO in Oracle 11g
Lasse Jenssen, October 27, 2009 - 8:49 am UTC
Hi Tom. We are in the middle of testing a new version of our application. The big change this time is actually on the database layer. We're upgrading from 9.2.0.8 to 11g r1.
This qualify for another big change - the shift from RBO to CBO. Statistics are gathered and the tests run pretty ok, accept of a few SQLs (found by using trace). I've tested these queries (sqlplus and autotrace) and found that all of them run quite well if i set the RULE hint (as expected). But (and here comes the part that make you want to kick some developers in the ... well ... let's be polite and professional) I got some limitations:
- The application run through a middletier, which generates dynamic SQL based on the function input.
- The middle-tier sometimes uses bind variables
- The middle-tier is not to be changed
One option (which I really don't like) i guess, would be to set the OPTIMIZER_MODE=RULE.
This is not supported, but at least it would probably work.
Before I've used stored outlines to fix similar problems, but having a middle-tier not using binds i'm afraid that this is not possible. I have read about SQL Plan Management, but don't see that this is no different from stored outlines.
I have considered using CURSOR_SHARING, but are unsure if this will help the problem sith stored outlines/sql plan management and missing bind variables.
Do you see any other options than?
- Setting OPTIMIZER_MODE=Rule
- Rewriting the middle-tier (using RULE hint in appropriate functions)
Thanks!
Regards
Lasse Jenssen
October 27, 2009 - 11:53 am UTC
... - The middle-tier is not to be changed ...
that is likely going to be an issue.
.. but at least it would probably work. ...
no, it would not. The first and only thing you'll hear from support will be "turn it off and reproduce the issue"
... - Rewriting the middle-tier (using RULE hint in appropriate functions) ...
that is the last hint you would use, first_rows(25) maybe - but not rule.
how are you gathering statistics, did you look at the problem queries in general and say "you know, if they had these statistics.... if they had THIS knowledge"... (you can gather statistics on expressions like "(column1+column2)/2" and on groups of columns).
Also, remember, the default optimization goal is all_rows - which might not be right for an interactive application that wants the first 25 rows as fast as possible.
RBO to CBO in Oracle 11g
Lasse Jenssen, October 27, 2009 - 3:38 pm UTC
Thanks for your answer.
About the middle-tier I'm afraid you are right. The middle tier has existed for a long time without any changes. I'm afraid it will be a long - but maybe necessary struggle to get the right people to see the facts about doing some changes here.
Yes I'm aware of the the differenses between ALL_ROWS and FIRST_ROWS(thanks to your series of Oracle books). I've allready tried the FIRST_ROWS hint, but the executionplan is still the same (as with ALL_ROWS).
I don't know if you can say anything by looking at the query plans, but here they are:
Query plan with CBO:
Plan hash value: 1022386496
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 274 | 22 (5)| 00:00:01 |
| 1 | HASH UNIQUE | | 1 | 274 | 22 (5)| 00:00:01 |
| 2 | NESTED LOOPS ANTI | | 1 | 274 | 21 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 270 | 18 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | NESTED LOOPS OUTER | | 1 | 253 | 18 (0)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 244 | 16 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 232 | 15 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 216 | 14 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 196 | 13 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 187 | 12 (0)| 00:00:01 |
|* 11 | FILTER | | | | | |
| 12 | NESTED LOOPS OUTER | | 1 | 168 | 11 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 1 | 149 | 10 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | 1 | 137 | 9 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | 1 | 121 | 7 (0)| 00:00:01 |
| 16 | NESTED LOOPS | | 1 | 99 | 5 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID| KSD_OBHE | 1 | 32 | 4 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | I0008264 | 1 | | 3 (0)| 00:00:01 |
|* 19 | TABLE ACCESS BY INDEX ROWID| KSD_SIKK | 1 | 67 | 1 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | KSDSIKKK | 1 | | 0 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | KSDPALII | 1 | 22 | 2 (0)| 00:00:01 |
|* 22 | TABLE ACCESS BY INDEX ROWID | KLN_PRAV | 1 | 16 | 2 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | KWFPRAVV | 1 | | 1 (0)| 00:00:01 |
|* 24 | TABLE ACCESS BY INDEX ROWID | KWF_UTRE | 1 | 12 | 1 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | KWFUTREE | 1 | | 0 (0)| 00:00:01 |
| 26 | TABLE ACCESS BY INDEX ROWID | SGN_VAKO | 1 | 19 | 1 (0)| 00:00:01 |
|* 27 | INDEX UNIQUE SCAN | SGNVAKOO | 1 | | 0 (0)| 00:00:01 |
| 28 | TABLE ACCESS BY INDEX ROWID | KCI_KUND | 1 | 19 | 1 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | KCIKUNDD | 1 | | 0 (0)| 00:00:01 |
| 30 | TABLE ACCESS BY INDEX ROWID | KCO_OREN | 1 | 9 | 1 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | KCOORENN | 1 | | 0 (0)| 00:00:01 |
| 32 | TABLE ACCESS BY INDEX ROWID | KSD_SITY | 1 | 20 | 1 (0)| 00:00:01 |
|* 33 | INDEX UNIQUE SCAN | KSDSITYY | 1 | | 0 (0)| 00:00:01 |
| 34 | TABLE ACCESS BY INDEX ROWID | KSD_SIST | 1 | 16 | 1 (0)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | KSDSISTT | 1 | | 0 (0)| 00:00:01 |
| 36 | TABLE ACCESS BY INDEX ROWID | KCO_ARTA | 1 | 12 | 1 (0)| 00:00:01 |
|* 37 | INDEX UNIQUE SCAN | KCOARTAA | 1 | | 0 (0)| 00:00:01 |
| 38 | TABLE ACCESS BY INDEX ROWID | KSD_VDEP | 79 | 711 | 2 (0)| 00:00:01 |
|* 39 | INDEX RANGE SCAN | I0008214 | 1 | | 1 (0)| 00:00:01 |
|* 40 | INDEX UNIQUE SCAN | KSDOBHOO | 1 | 17 | 0 (0)| 00:00:01 |
| 41 | VIEW PUSHED PREDICATE | VW_SQ_1 | 1 | 4 | 3 (0)| 00:00:01 |
| 42 | NESTED LOOPS | | | | | |
| 43 | NESTED LOOPS | | 1 | 25 | 3 (0)| 00:00:01 |
|* 44 | TABLE ACCESS BY INDEX ROWID | KSD_VDEP | 1 | 11 | 2 (0)| 00:00:01 |
|* 45 | INDEX RANGE SCAN | IND_KSD_VDEP_SIKK_UTR_NY | 1 | | 1 (0)| 00:00:01 |
|* 46 | INDEX UNIQUE SCAN | KSDSIKKK | 1 | | 0 (0)| 00:00:01 |
|* 47 | TABLE ACCESS BY INDEX ROWID | KSD_SIKK | 1 | 14 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------
The queryplan with RULE hint (Just to show how an effectiv plan would look like. Not saying we'll use it):
----------------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT UNIQUE | |
|* 2 | FILTER | |
| 3 | NESTED LOOPS | |
| 4 | NESTED LOOPS | |
|* 5 | FILTER | |
| 6 | NESTED LOOPS OUTER | |
| 7 | NESTED LOOPS OUTER | |
| 8 | NESTED LOOPS | |
| 9 | NESTED LOOPS | |
| 10 | NESTED LOOPS | |
| 11 | NESTED LOOPS | |
| 12 | NESTED LOOPS | |
| 13 | NESTED LOOPS | |
|* 14 | FILTER | |
| 15 | NESTED LOOPS OUTER | |
| 16 | NESTED LOOPS | |
| 17 | TABLE ACCESS BY INDEX ROWID| KWF_UTRE |
|* 18 | INDEX RANGE SCAN | I0006815 |
|* 19 | TABLE ACCESS BY INDEX ROWID| KLN_PRAV |
|* 20 | INDEX RANGE SCAN | I0006812 |
| 21 | TABLE ACCESS BY INDEX ROWID | SGN_VAKO |
|* 22 | INDEX UNIQUE SCAN | SGNVAKOO |
| 23 | TABLE ACCESS BY INDEX ROWID | KSD_PALI |
|* 24 | INDEX RANGE SCAN | I0007946 |
|* 25 | TABLE ACCESS BY INDEX ROWID | KSD_SIKK |
|* 26 | INDEX UNIQUE SCAN | KSDSIKKK |
| 27 | TABLE ACCESS BY INDEX ROWID | KCI_KUND |
|* 28 | INDEX UNIQUE SCAN | KCIKUNDD |
| 29 | TABLE ACCESS BY INDEX ROWID | KCO_OREN |
|* 30 | INDEX UNIQUE SCAN | KCOORENN |
| 31 | TABLE ACCESS BY INDEX ROWID | KSD_SITY |
|* 32 | INDEX UNIQUE SCAN | KSDSITYY |
| 33 | TABLE ACCESS BY INDEX ROWID | KSD_SIST |
|* 34 | INDEX UNIQUE SCAN | KSDSISTT |
| 35 | TABLE ACCESS BY INDEX ROWID | KCO_ARTA |
|* 36 | INDEX UNIQUE SCAN | KCOARTAA |
| 37 | TABLE ACCESS BY INDEX ROWID | KSD_VDEP |
|* 38 | INDEX RANGE SCAN | I0008214 |
| 39 | TABLE ACCESS BY INDEX ROWID | KSD_OBHE |
|* 40 | INDEX RANGE SCAN | I0008264 |
|* 41 | INDEX UNIQUE SCAN | KSDOBHOO |
| 42 | NESTED LOOPS | |
| 43 | NESTED LOOPS | |
|* 44 | TABLE ACCESS BY INDEX ROWID | KSD_VDEP |
|* 45 | INDEX RANGE SCAN | I0008218 |
|* 46 | INDEX UNIQUE SCAN | KSDSIKKK |
|* 47 | TABLE ACCESS BY INDEX ROWID | KSD_SIKK |
----------------------------------------------------------------
The query actually returns no rows.
Statistics with CBO:
Ingen rader valgt (No rows selected)
Forløpt: 00:00:02.60
Statistikk
----------------------------------------------------------
0 recursive calls
0 db block gets
306184 consistent gets
73932 physical reads
0 redo size
1364 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
Statistics with RULE hint:
Forløpt: 00:00:00.03
Statistikk
----------------------------------------------------------
1 recursive calls
0 db block gets
53 consistent gets
32 physical reads
0 redo size
1364 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
Yes. I'm aware of the no-support condition when using desupported feature (RBO). I was not actually suggesting to use it (Well ... maybe i was ... i take it back).
So this really leaves me with the statistic option.
I definitly have to do some reading her. Any good suggestion about where to start?
I really don't know how the statistics was gathered. I just checked that the statistics where there. Is there any way to check how it was gathered?
I'll be thankful for all the advice you got on this Tom.
The obvious answer would be "Start reading!" (And right now thats what I'm doing :-))
October 27, 2009 - 5:17 pm UTC
... I don't know if you can say anything by looking at the query plans, but here they are: ...
nope, no one could
and the fact it got cut off means it is way way too long to look at here ;)
The rest ...(missing part from last review)
Lasse Jenssen, October 27, 2009 - 3:40 pm UTC
The queryplan with RULE hint (Just to show how an effectiv plan would look like. Not saying we'll use it):
----------------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT UNIQUE | |
|* 2 | FILTER | |
| 3 | NESTED LOOPS | |
| 4 | NESTED LOOPS | |
|* 5 | FILTER | |
| 6 | NESTED LOOPS OUTER | |
| 7 | NESTED LOOPS OUTER | |
| 8 | NESTED LOOPS | |
| 9 | NESTED LOOPS | |
| 10 | NESTED LOOPS | |
| 11 | NESTED LOOPS | |
| 12 | NESTED LOOPS | |
| 13 | NESTED LOOPS | |
|* 14 | FILTER | |
| 15 | NESTED LOOPS OUTER | |
| 16 | NESTED LOOPS | |
| 17 | TABLE ACCESS BY INDEX ROWID| KWF_UTRE |
|* 18 | INDEX RANGE SCAN | I0006815 |
|* 19 | TABLE ACCESS BY INDEX ROWID| KLN_PRAV |
|* 20 | INDEX RANGE SCAN | I0006812 |
| 21 | TABLE ACCESS BY INDEX ROWID | SGN_VAKO |
|* 22 | INDEX UNIQUE SCAN | SGNVAKOO |
| 23 | TABLE ACCESS BY INDEX ROWID | KSD_PALI |
|* 24 | INDEX RANGE SCAN | I0007946 |
|* 25 | TABLE ACCESS BY INDEX ROWID | KSD_SIKK |
|* 26 | INDEX UNIQUE SCAN | KSDSIKKK |
| 27 | TABLE ACCESS BY INDEX ROWID | KCI_KUND |
|* 28 | INDEX UNIQUE SCAN | KCIKUNDD |
| 29 | TABLE ACCESS BY INDEX ROWID | KCO_OREN |
|* 30 | INDEX UNIQUE SCAN | KCOORENN |
| 31 | TABLE ACCESS BY INDEX ROWID | KSD_SITY |
|* 32 | INDEX UNIQUE SCAN | KSDSITYY |
| 33 | TABLE ACCESS BY INDEX ROWID | KSD_SIST |
|* 34 | INDEX UNIQUE SCAN | KSDSISTT |
| 35 | TABLE ACCESS BY INDEX ROWID | KCO_ARTA |
|* 36 | INDEX UNIQUE SCAN | KCOARTAA |
| 37 | TABLE ACCESS BY INDEX ROWID | KSD_VDEP |
|* 38 | INDEX RANGE SCAN | I0008214 |
| 39 | TABLE ACCESS BY INDEX ROWID | KSD_OBHE |
|* 40 | INDEX RANGE SCAN | I0008264 |
|* 41 | INDEX UNIQUE SCAN | KSDOBHOO |
| 42 | NESTED LOOPS | |
| 43 | NESTED LOOPS | |
|* 44 | TABLE ACCESS BY INDEX ROWID | KSD_VDEP |
|* 45 | INDEX RANGE SCAN | I0008218 |
|* 46 | INDEX UNIQUE SCAN | KSDSIKKK |
|* 47 | TABLE ACCESS BY INDEX ROWID | KSD_SIKK |
----------------------------------------------------------------
The query actually returns no rows.
Statistics with CBO:
Ingen rader valgt (No rows selected)
Forløpt: 00:00:02.60
Statistikk
----------------------------------------------------------
0 recursive calls
0 db block gets
306184 consistent gets
73932 physical reads
0 redo size
1364 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
Statistics with RULE hint:
Forløpt: 00:00:00.03
Statistikk
----------------------------------------------------------
1 recursive calls
0 db block gets
53 consistent gets
32 physical reads
0 redo size
1364 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
Yes. I'm aware of the no-support condition when using desupported feature (RBO). I was not actually suggesting to use it (Well ... maybe i was ... i take it back).
So this really leaves me with the statistic option.
I definitly have to do some reading her. Any good suggestion about where to start?
I really don't know how the statistics was gathered. I just checked that the statistics where there. Is there any way to check how it was gathered?
I'll be thankful for all the advice you got on this Tom.
The obvious answer would be "Start reading!" (And right now thats what I'm doing :-))
October 27, 2009 - 5:18 pm UTC
... I definitly have to do some reading her. Any good suggestion about where to start? ...
Jonathan Lewis's book Cost Based Oracle would be an interesting read.
... I really don't know how the statistics was gathered. I just checked that the statistics where there. Is there any way to check how it was gathered? ...
not directly, why not ask though (the person that does that?)
RBO to CBO ... reading and testing pays of
Lasse Jenssen, October 27, 2009 - 5:36 pm UTC
Excellent!!
Reading (and testing) definitly pays off.
First i did a 10053 trace to see if I could see anything obvious. The query being pretty complex the trace didn't really give me much. I checked some of the statistics used by the optimzer (row count in tables) and the numbers looked ok (some small increase in rows since the statistics was gathered, but it look insignificant).
Then I re-gathered statistics using one of the recommended methods (i found on metalink). I thought this would give me a solled beginning. At least then I'd know how the statistics was gathered:
dbms_stats.gather_schema_stats(ownname=>'<SCHEMA_NAME>',method_opt=>'FOR ALL COLUMNS SIZE AUTO',cascade=>TRUE);
And guess what? The queries that used to be slow, now really finishes i no time. The CBO no uses basically the same JOIN methods, but has turned around on the order of the joins.
The easy way is often the best way!
But I definitly keep reading on Jonathan's book.
Actually I allready got in my bookshelf (but I guess it doesn't do any good just standing there :-))
Thanks Tom!