I creates a brand new table tt, no stats, But Oracle still chooses the CBO (CHOOSE).
Why? Unless RULE hint applied.

sql> create table tt as select * from dba_objects;

Table created.

sql> create index tt_oi_idx on tt(object_id);

Index created.

sql> /* there is no statistics collected */
sql> show parameter optimizer_mode


NAME TYPE VALUE
------------------------------------ ------- ------------------------------
optimizer_mode string CHOOSE



sql> select count(*) from tt where object_id>12000;

COUNT(*)
---------
8886

sql> select /*+ RULE */ count(*) from tt where object_id>12000;


COUNT(*)
---------
8886


TKPROF output:

select count(*)
from
tt where object_id>12000


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.10 21 22 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.02 0.12 21 22 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 22

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
8886 INDEX RANGE SCAN (object id 21570)

********************************************************************************

select /*+ RULE */ count(*)
from
tt where object_id>12000


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.01 0 22 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.02 0.02 0 22 0 1

Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 22

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
8886 INDEX RANGE SCAN (object id 21570)

********************************************************************************

CHOOSE means choose -- that means "i will use the RBO if no stats are present and no constructs like partitioning are used -- some constructs force the use of CBO regardless".

In the above -- CHOOSE *is in fact rule* since you have a simple table with no statistics.  The second query with RULE shows RULE for the simple reason that you "forced" rule to be used.

You can see this using AUTOTRACE better perhaps:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x int primary key );
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t select rownum from all_users;
34 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace traceonly explain;

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t where x < 5;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   INDEX (RANGE SCAN) OF 'SYS_C006757' (UNIQUE)

<b>there I can tell RBO was used simply by the lack of CARD/COST/BYTES.. see below for example of CBO with same query..</b>


ops$tkyte@ORA817DEV.US.ORACLE.COM> select /*+ RULE */ * from t where x < 5;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   INDEX (RANGE SCAN) OF 'SYS_C006757' (UNIQUE)

<b>Again, RBO, not CBO.  Now a tkprof would say the first was run with choose (and it WAS, that is not at all inaccurate) and the second with RBO -- that again is correct, neither contradicts the other!</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t compute statistics;
Table analyzed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t where x < 5;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE <b>(Cost=1 Card=5 Bytes=10)</b>
   1    0   INDEX (RANGE SCAN) OF 'SYS_C006757' (UNIQUE) (Cost=1 Card=5 Bytes=10)

<b>that additional info will appear ONLY when using the CBO.  That shows that in this case, choose means CBO, not RBO.  The tkprof would still show choose and it would be CORRECT.</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select /*+ RULE */ * from t where x < 5;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   INDEX (RANGE SCAN) OF 'SYS_C006757' (UNIQUE)



ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace off

<b>and back to rule again, nothing is amiss here, what you see is both normal and correct</b>
 

Hi Tom,
Your explanation on this topic has impressed me a lot. Now I have a scenario like this.
My database has the optimizer mode as CHOOSE.
Out of 700+ tables, only 10 major tables are analyzed. Now I would like to analyze all the tables.
My queries are:

1. Now I want to know with the current setup, how many sqls are choosen the RBO or CBO. In the V$sql view, we have a column optimizer_cost, can I assume that, at any particular point of time, the number of records in this view where the optimizer_cost=0 will give me the information that these many sqls are choosen for RBO. The same exercise I would like to do after analyzing all the tables, so that I will come to know that all my sqls are using CBO.

2.There are 3 application users in my database. First I would like to analyze all the objects for one user. So When I have tried this procedure

dbms_stats.gather_schema_stats('username')

in my development server, I found that only the tables are analysed and not the indexes( This info i have seen from the user_tables and user_indexes). So please let me know which procedure I have to use inorder to analyze one schema.


Thanks in Advance
SUBBARAO.

Hi Tom,

My Intention is not to collect the count, My main intention is this, Now that i didnt anlayze 99% of my tables in my database, Now I want to analyze all the objects. There is some way to measure out the performance after analyzing the table right?. My boss is not understanding the advantage of analyzing the tables. So I should prove him that after anlayzing this is the status.
I am not asking this mainly to show it to my boss, whereas even I would like to measure the performance difference.

Thanks in advance
SUBBARAO.

Hi Tom,
I am planning to do this in the productions system. Right now I have already collected the statspack for a weeks time, after anlayzing also I will collect the statspack and compare the outputs.

If it is a production system why you are telling this

"cause if this is a production box and you are just going to do this -- expect to have a very very very long week ahead of you."

What kind of problem you are expecting in a general OLTP system. And mainly what figures we need to look at in the statspack. What I thought is to see only the expensive SQLS in the statspack. is it enough?

Thanks in Advance
SUBBARAO

I learn that the RBO will be eliminated in future Oracle release and we are trying to use CBO as much as possible. The optimizer_mode set to choose in our database. We have statistics on every table and we update the statistics to keep them update. I assume that would lead to the use of CBO. However, there are some SQL queries, we cannot make them run faster unless we use the /*+RULE*/ hint (even I just analyzed all the tables involved). Using other CBO hint like /*+FIRST_ROWS*/ etc. will make the performance worse. My question: is the /*+RULE*/ hint going to go away in future release? Is the hint he same as using RBO and should we use it?
Thank you!

You wrote:

You need to find out *why* the queries are not functioning well with CBO. Using
the RULE hint uses the RBO and the RBO is *gone* in the next release.

Does this mean that the next major release of Oracle will not have the RBO? I've heard rumors of this but I haven't ever got 100% confirmation.

Some of our DW/BI-type queries/apps, etc have had dramatic performance benefits from using the Oracle 9i/CBO. So much so, that many developers are now keeping common sense aside when constructing their reports/queries. Join everything in sight, aggregate up the wazoo, use DISTINCT unnecessarily, etc. Given the volume of our data so far, CBO is indeed able to handle everything we throw at it.

My question is: Given all the smarts built into CBO, is it really safe to say that common-sense/intelligence is now "obsolete"? Just write queries any way you want and the CBO will just take care of it?

Thanks for your comments

Well, I was thinking about the new dbms_sqltune package in 10g when I wrote that.

If the database can recognize bad sql, offer suggestions to improve it and rank those suggestions, all things which dbms_sqltune is designed to do, well, then we are just one step away from doing

alter session set implement_sqltune=true

and voila! we have a sentient database that replaces us humans!

:-)

Tom, you are the best. Brilliant example, as usual.

You hit the nail on the head. Of course, the server can see only discrete SQL, not algorithms. Clearly, the database cannot rewrite "the approach", that would be like the AI-type computers in Star Trek!

However, that is not what I was referring to.

When faced with discrete SQL (which is what dbms_sqltune works with anyway), all I was saying (albeit slightly tongue-in-cheek), is that the database could take its own suggestions and implement them. I mean all the typical newbie-type SQL errors when using IN/EXISTS, correlated subqueries, etc. In all these cases, dbms_sqltune could rewrite the query to provide the same answer with much lesser LIO

Again, the reason I bring all this up is that 10g has made great strides as a "self managing" database from a DBA perspective but stupid (badly written) queries can still bring the database to its knees!

"typical newbie-type SQL errors when using IN/EXISTS"

I'm uncomfortable with calling those 'errors'. It's not doing anything incorrectly, even though it may be doing it inefficiently. And if it is doing it inefficiently, that is as more because of the way the query was interpreted than the way it was written.

There is no logical difference between "select col_a from table_b where col_c in (select col_d from table_e)" and "select col_a from table_b where exists (select 1 from table_e where table_e.col_d = table_b.col_c)"

The RBO treated them differently so as to give the developer the opportunity to tell the optimizer how best to get to the data needed. Sort of like using hints with the CBO. As the CBO gets brighter, it needs less direction.

Ideally, the optimizer should give the same plan (the quickest) to a query and any query that is logically identical (ie would give the same result set for all potential table contents).
As the optimizer should have more up-to-date information about the table contents when it creates the plan, in theory, it should be capable of producing a better plan than the writer of the SQL code might anticipate.

In practice, for complex queries, the optimizer will only be able to see a subset of all possible plans, so I think there will always be room for some direction from SQL writers.

As for 'common sense', it is still more important to be 'asking the database the right question' rather than 'posing the question in the best way'.

PS. When Tom can ask his interview question of "Give me the name of the oldest employee" directly to the optimizer, and it responds with "What if there's more than one ?", that's when we are all out of a job :)

<quote>
In practice, for complex queries, the optimizer will only be able to see a subset of all possible plans, so I think there will always be room for some direction from SQL writers.
<quote>

I disagree. The whole modus operandi of the CBO is brute force evaluation of the entire universe of all possible join methods, predicate selection, elimination, rewrite, etc, etc. As part of this costing exercise, it can, IMHO, easily _consider_ rewriting, for example, an IN query as EXISTS or vice versa and use the less costly one, without the SQL writer saying so. As you rightly put it, data changes and as long as the user keeps the stats up to date, the CBO is in a position to generate the optimal plan, which might be better than even the programmer anticipated. Isnt that one of the main reasons to be using the CBO in the first place? To generate optimal plans given changing data?

"As for 'common sense', it is still more important to be 'asking the database the right question' rather than 'posing the question in the best way'"

Disagree again. It is my data. You cant tell me that my question is "not right". I have the right to ask any question of the database, and as long as it is syntactically accurate and unambiguous, it is the CBO's job to interpret the question in light of current data distribution, apply all possible costing, rewrites, dbms_sqltune, best-practice heuristics to the question and give me the answer in the shortest possible time.

"the combinations of possible plans explodes rapidly into the BILLIONS and beyond -- even for simple queries. It cannot and does not consider each and every join method, permutation and so on"

Yes, I realize that. So instead of evaluating and costing the billions of plans, the CBO applies some heuristics and decides to evaluate only a subset of those (optimizer_max_permutations).

All I am saying is that as part of those heuristics, it _could_ consider rewriting a IN as EXISTS (or vice versa). As you yourself say, depending on the data, IN might be more efficient on Monday and EXISTS might be more efficient on Tuesday. That _is_ what the CBO should do, right? Adapt to changing data. Along the same lines, it _could_ consider rewriting it as per its own dbms_sqltune recommendations (configurable, of course, since this could dramatically slow down the parse time). And finally, it could say something like 'alter session set become_tom=true' and apply even more intelligent heuristics!

All I am saying is that while I agree that intelligence and common sense is not totally obsolete, there could be a sort of expert system/knowledge base-based aspect to the CBO that would enable it to "learn" over time, to enable it to become more smart over time, to "grow beyond what Oracle developers programmed it for". Something like a neural network in AI-speak! So, for example, the CBO in one environment could be smarter than the CBO in another environment simply because it has more "experience".

Thanks

Why wont it rewrite the former with the latter using analytic functions if that is indeed the less expensive one? Is 10g's dbms_sqltune able to come up with the latter as a more efficient alternative for the former? If so, maybe 11g's CBO will indeed have an option to do this automatically!

Let me ask you this: The CBO has kept improving by leaps and bounds over the last few years. You seem to be saying that "everything that can be done, the CBO already does". So, what do you think the CBO in Oracle 15"g" (or whatever the letter of the year is) will do? Where does the CBO have room for improvement? You never seem to concede that there is any, ever!

Thanks, I understand now.

1. But I am curious to know, given your vast knowledge and mastery of SQL, if you were a Oracle kernel developer, where do you think the CBO can do things better?

2. As someone who works for Oracle and might know the future direction for subsequent releases, have you seen a "sneak preview" (even just in theor) of the CBO in, say, 11g or 12g, etc?

Thanks a lot

Another tuning question...where the CBO plan drastically changes after adding a value in the IN predicate. Let me explain, I have a view like

CREATE OR REPLACE VIEW ALL_MISC_CASH_TRANSACTIONS
AS
SELECT
a.transactionid,
a.translink,
a.cashdate,
a.groupid,
a.controllerid,
b.thisaccttxn,
b.accountnum
FROM cgtmplttbl a,cgalttmplttbl b
WHERE a.transactionid=b.transactionid
UNION
SELECT
a.transactionid,
a.translink,
a.cashdate,
a.groupid,
a.controllerid,
b.thisaccttxn,
b.accountnum
FROM cashtrans a,cashtransalt b
WHERE a.transactionid=b.transactionid;

I have a query like

select TRANSACTIONID, TRANSLINK, CASHDATE, GROUPID, CONTROLLERID from all_misc_cash_transactions
where groupid in (11,12,13,14)

This has the following plan and it executes in less than a second

497 rows selected.


--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5941 | 353K| | 1244 (1)|
| 1 | VIEW | ALL_MISC_CASH_TRANSACTIONS | 5941 | 353K| | |
| 2 | SORT UNIQUE | | 5941 | 1596K| 3656K| 1244 (1)|
| 3 | UNION-ALL | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | CGALTTMPLTTBL | 1 | 44 | | 2 (50)|
| 5 | NESTED LOOPS | | 5923 | 1590K| | 995 (0)|
| 6 | INLIST ITERATOR | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| CGTMPLTTBL | 4885 | 1101K| | 18 (0)|
|* 8 | INDEX RANGE SCAN | CGTMPLT_GROUP | 4885 | | | 1 (0)|
|* 9 | INDEX RANGE SCAN | CGALTTMPLTTBL_TXNID | 1 | | | 1 (0)|
| 10 | TABLE ACCESS BY INDEX ROWID | CASHTRANSALT | 2 | 88 | | 2 (50)|
| 11 | NESTED LOOPS | | 18 | 5598 | | 4 (0)|
| 12 | INLIST ITERATOR | | | | | |
| 13 | TABLE ACCESS BY INDEX ROWID| CASHTRANS | 11 | 2937 | | 2 (50)|
|* 14 | INDEX RANGE SCAN | ICCSHTXNGROUPID | 12 | | | 1 (0)|
|* 15 | INDEX RANGE SCAN | ICCSHTXNALTTXNID | 2 | | | 1 (0)|
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

8 - access("A"."GROUPID"=11 OR "A"."GROUPID"=12 OR "A"."GROUPID"=13 OR "A"."GROUPID"=14)
9 - access("A"."TRANSACTIONID"="B"."TRANSACTIONID")
14 - access("A"."GROUPID"=11 OR "A"."GROUPID"=12 OR "A"."GROUPID"=13 OR "A"."GROUPID"=14)
15 - access("A"."TRANSACTIONID"="B"."TRANSACTIONID")

Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
380 consistent gets
0 physical reads
0 redo size
13675 bytes sent via SQL*Net to client
1014 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
497 rows processed

Now when I change the query to

select TRANSACTIONID, TRANSLINK, CASHDATE, GROUPID, CONTROLLERID from all_misc_cash_transactions
where groupid in (10,11,12,13,14)

The plan changes to

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7430 | 442K| | 1499 (1)|
| 1 | VIEW | ALL_MISC_CASH_TRANSACTIONS | 7430 | 442K| | |
| 2 | SORT UNIQUE | | 7430 | 1996K| 5944K| 1499 (1)|
| 3 | UNION-ALL | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | CGTMPLTTBL | 1 | 231 | | 1187 (1)|
| 5 | NESTED LOOPS | | 7404 | 1988K| | 1187 (1)|
| 6 | TABLE ACCESS FULL | CGALTTMPLTTBL | 131K| 5669K| | 58 (0)|
| 7 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 8 | BITMAP AND | | | | | |
| 9 | BITMAP CONVERSION FROM ROWIDS | | | | | |
|* 10 | INDEX RANGE SCAN | CGTMPLTTBL_TXNID | 1 | | | |
| 11 | BITMAP OR | | | | | |
| 12 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 13 | INDEX RANGE SCAN | CGTMPLT_GROUP | 1 | | | |
| 14 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 15 | INDEX RANGE SCAN | CGTMPLT_GROUP | 1 | | | |
| 16 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 17 | INDEX RANGE SCAN | CGTMPLT_GROUP | 1 | | | |
| 18 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 19 | INDEX RANGE SCAN | CGTMPLT_GROUP | 1 | | | |
| 20 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 21 | INDEX RANGE SCAN | CGTMPLT_GROUP | 1 | | | |
| 22 | TABLE ACCESS BY INDEX ROWID | CASHTRANSALT | 2 | 88 | | 2 (50)|
| 23 | NESTED LOOPS | | 26 | 8086 | | 5 (0)|
| 24 | INLIST ITERATOR | | | | | |
| 25 | TABLE ACCESS BY INDEX ROWID | CASHTRANS | 17 | 4539 | | 2 (50)|
|* 26 | INDEX RANGE SCAN | ICCSHTXNGROUPID | 17 | | | 1 (0)|
|* 27 | INDEX RANGE SCAN | ICCSHTXNALTTXNID | 2 | | | 1 (0)|
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

10 - access("A"."TRANSACTIONID"="B"."TRANSACTIONID")
13 - access("A"."GROUPID"=10)
15 - access("A"."GROUPID"=11)
17 - access("A"."GROUPID"=12)
19 - access("A"."GROUPID"=13)
21 - access("A"."GROUPID"=14)
26 - access("A"."GROUPID"=10 OR "A"."GROUPID"=11 OR "A"."GROUPID"=12 OR "A"."GROUPID"=13 OR "A"."GROUPID"=14)
27 - access("A"."TRANSACTIONID"="B"."TRANSACTIONID")

and the query just doesnt return at all. I waited for 10 minutes.

Why does the plan tank so badly by adding the groupid 10 to the predicate? What the heck are all the bitmap conversions in the plan?

The RBO plan for both queries is

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | VIEW | ALL_MISC_CASH_TRANSACTIONS | | | |
| 2 | SORT UNIQUE | | | | |
| 3 | UNION-ALL | | | | |
| 4 | CONCATENATION | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID | CGALTTMPLTTBL | | | |
| 6 | NESTED LOOPS | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| CGTMPLTTBL | | | |
|* 8 | INDEX RANGE SCAN | CGTMPLT_GROUP | | | |
|* 9 | INDEX RANGE SCAN | CGALTTMPLTTBL_TXNID | | | |
| 10 | TABLE ACCESS BY INDEX ROWID | CGALTTMPLTTBL | | | |
| 11 | NESTED LOOPS | | | | |
| 12 | TABLE ACCESS BY INDEX ROWID| CGTMPLTTBL | | | |
|* 13 | INDEX RANGE SCAN | CGTMPLT_GROUP | | | |
|* 14 | INDEX RANGE SCAN | CGALTTMPLTTBL_TXNID | | | |
| 15 | TABLE ACCESS BY INDEX ROWID | CGALTTMPLTTBL | | | |
| 16 | NESTED LOOPS | | | | |
| 17 | TABLE ACCESS BY INDEX ROWID| CGTMPLTTBL | | | |
|* 18 | INDEX RANGE SCAN | CGTMPLT_GROUP | | | |
|* 19 | INDEX RANGE SCAN | CGALTTMPLTTBL_TXNID | | | |
| 20 | TABLE ACCESS BY INDEX ROWID | CGALTTMPLTTBL | | | |
| 21 | NESTED LOOPS | | | | |
| 22 | TABLE ACCESS BY INDEX ROWID| CGTMPLTTBL | | | |
|* 23 | INDEX RANGE SCAN | CGTMPLT_GROUP | | | |
|* 24 | INDEX RANGE SCAN | CGALTTMPLTTBL_TXNID | | | |
| 25 | CONCATENATION | | | | |
| 26 | TABLE ACCESS BY INDEX ROWID | CASHTRANSALT | | | |
| 27 | NESTED LOOPS | | | | |
| 28 | TABLE ACCESS BY INDEX ROWID| CASHTRANS | | | |
|* 29 | INDEX RANGE SCAN | ICCSHTXNGROUPID | | | |
|* 30 | INDEX RANGE SCAN | ICCSHTXNALTTXNID | | | |
| 31 | TABLE ACCESS BY INDEX ROWID | CASHTRANSALT | | | |
| 32 | NESTED LOOPS | | | | |
| 33 | TABLE ACCESS BY INDEX ROWID| CASHTRANS | | | |
|* 34 | INDEX RANGE SCAN | ICCSHTXNGROUPID | | | |
|* 35 | INDEX RANGE SCAN | ICCSHTXNALTTXNID | | | |
| 36 | TABLE ACCESS BY INDEX ROWID | CASHTRANSALT | | | |
| 37 | NESTED LOOPS | | | | |
| 38 | TABLE ACCESS BY INDEX ROWID| CASHTRANS | | | |
|* 39 | INDEX RANGE SCAN | ICCSHTXNGROUPID | | | |
|* 40 | INDEX RANGE SCAN | ICCSHTXNALTTXNID | | | |
| 41 | TABLE ACCESS BY INDEX ROWID | CASHTRANSALT | | | |
| 42 | NESTED LOOPS | | | | |
| 43 | TABLE ACCESS BY INDEX ROWID| CASHTRANS | | | |
|* 44 | INDEX RANGE SCAN | ICCSHTXNGROUPID | | | |
|* 45 | INDEX RANGE SCAN | ICCSHTXNALTTXNID | | | |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

8 - access("A"."GROUPID"=14)
9 - access("A"."TRANSACTIONID"="B"."TRANSACTIONID")
13 - access("A"."GROUPID"=13)
14 - access("A"."TRANSACTIONID"="B"."TRANSACTIONID")
18 - access("A"."GROUPID"=12)
19 - access("A"."TRANSACTIONID"="B"."TRANSACTIONID")
23 - access("A"."GROUPID"=11)
24 - access("A"."TRANSACTIONID"="B"."TRANSACTIONID")
29 - access("A"."GROUPID"=14)
30 - access("A"."TRANSACTIONID"="B"."TRANSACTIONID")
34 - access("A"."GROUPID"=13)
35 - access("A"."TRANSACTIONID"="B"."TRANSACTIONID")
39 - access("A"."GROUPID"=12)
40 - access("A"."TRANSACTIONID"="B"."TRANSACTIONID")
44 - access("A"."GROUPID"=11)
45 - access("A"."TRANSACTIONID"="B"."TRANSACTIONID")

Note: rule based optimization

Well, the plan with the 10 has 2 more access predicates at the bottom of the plan but it is basically the same plan...a concatenation of all the individual groupids.

I gathered stats on all the tables using dbms_stats, cascade=>true and 'for all columns size auto'

The data distribution for the groupids accessed is

select groupid,count(*) from cgtmplttbl
where groupid in (10,11,12,13,14)
group by groupid;


GROUPID COUNT(*)
10 141
13 79
14 209

So, adding 10 to the IN clause shouldnt really change things this drastically!

I know the CBO is zillion times smarter than RBO and everything but it is these kinds of things that start a FUD out there, just adding another value to a existing IN clause makes the query tank badly!

Help!!!

select count(distinct groupid) from cashmgt.cgtmplttbl;

gives me 92

Bind variables: The IN list varies every time, so I really cant use bind variables unless I use some of the techniques mentioned in

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:110612348061, <code>

But my question had nothing to do with using bind variables.

Yes, if the first encounter of this query since my instance started were to use IN (11,12,13,14), I would get the good plan and if I use using bind variables using one of the techniques in that link, even if I use IN (10,11,13,13,14), I would get the same plan.

But that is relying on chance! What if the first encounter of this query were to be IN (10,11,12,13,14) and the plan was that horrible plan! Now I am screwed for every execution of this query!

Anyway, my question was about why that one plan was so bad?

Hm, I just reanalyzed the tables involved in the query and now the plan is fine! This is really puzzling since I have a nightly job that does 'gather stale' and all my tables are set to monitoring so the stats should be up to date anyway.

I know you dont like myths like these, but on more than one occasion I have seen query performance improve by simple gathering stats when the gather stale job should have done the same thing.

On a related note, is there any disadvantage to always doing

method_opt=>'FOR ALL COLUMNS SIZE 254'

i.e. using 254-bucket histograms always?

Thanks

1. So if I do determine that I need to gather histograms 'for all indexed columns', is there any harm in always using 254 (the maximum allowed) buckets?

2. In general, what is the use of analyzing 'for all columns'? If a column is not indexed, what use does the CBO make of knowing the data distribution of that column? The access path for that column is always FTS, so why do histograms matter for unindexed columns?

Thanks

I'm curious about the discussion here regarding those
who have their optimizer_mode=choose and want to revert
to rbo because some queries run better with RULE.

I believe Tom answered switching back to RBO is "trivial"
along with deleting statistics??

how does one go about deleting statistics once they've
been analyzed and recorded.

thanks

Great information for someone looking to convert RBO to CBO.

Oracle 9.2.0.4

Over the last few months, since we upgraded from 8.1.7.4 to 9.2.0.4, I have encountered a dozen relatively simple queries involving no more than 3-4 tables that the CBO just cannot get right. Up to date stats, no hints, CBO has all the information it needs, but it just cannot come up with a good plan. I tweaked the optimizer_* parameters, worked with Oracle Support, all to no avail.

In every single case, switching to RBO for that query fixed it right up! In every single case, Support asked me to try upgrading to the lastest patchset (9.2.0.6 as of today)! I thought 9.2.0.4 was pretty stable thats why I waited so long to upgrade from 8.1.7.4! :-(

I am afraid that developers will "give up" on this CBO thingy and start to embed /*+ rule */ all over the place even when CBO works fine 95% of the time!

Comments? Thanks