How to know the optimizer mode for an SQL statement.

[V6163]

Hi All,

I would like to know in which optimizer mode my sqls are being processed.

Here are my DB details:
1. Optimizer mode in init.ora has been set to CHOOSE.
2. I am not using any hints in my sql statements.
3. Out of 700 tables in my database, I have analyzed only 10 tables, which are the main tables.

My observations:
1. I am aware that even one table has statistics in the sql statement the oracle chooses the COST mode.

2. When I queried my v$sql view with the column OPTIMIZER_MODE and count(*) at one point of time, I got this info

4986 records with CHOOSE,
2994 records with NONE
278 records with RULE.

What is the 'NONE' optimizer mode. And when quered all the records in the CHOOSE, the sql's are the user given sqls.
And also when I queried the records with RULE, they are all system generated sqls.

My questions:
1. When the value of the OPTIMIZER_MODE is in CHOOSE, I would like to know whether it has choosen CBO or RBO.

2. With the above info from v$sql, doest it mean that none of sqls are using RULE and all of them are using CBO?

3. Now I want to analyze all my tables, After this, I would like to know the difference after and before this.

Plese clarify me on the above three questions.


Thanks and Regards
Venu.K

[ggnanaraj]

Originally posted by V6163

3. Out of 700 tables in my database, I have analyzed only 10 tables, which are the main tables.

You have to analyze all USER tables and indexes in your database. You can do this using

execute dbms_utility.analyze_schema('SCHEMA_NAME', 'COMPUTE');

This will enable the database engine when using the COST based optimizer to get accurate statistics to choose the best plan. This is with reference to what you mentioned about the Cost based optimizer being used when even one table is analyzed in a query (All tables in the query must be analyzed).

HTH.

[V6163]

Thank you ggnanaraj

Got your inputs, I would like to know with the current status, what is the status of sqls, whether they are using CBO or RBO. As i gave the analysis of the v$sql.

Before anayzing all the tables, i would like to know the details of current sqls.

Hope I am clear in my question.

Awaiting for your replies.

[hrishy]

Hi

Its very simple..from sql alone you cannot tell wheather they used CBO or RBO..its like this

If your optimizer_mode=choose

then all sql statements will use the CBO

when the tables they are acessing will have statistics collected..

then all sql statements will use the RBO

when the tables they are acessing will have no statistics..

You acn enable tarcing in those users session if you want to see whaether they are using CBO or RBO

regards
Hrishy

[V6163]

Thanks for the inputs.

I aware that when the optimizer mode is choose, what will happen.
Now my question is some thing over that. There is a column in v$sql called optimizer_mode. when i queired count for this, I got the output as given in my earlier post.

So if query a particular record in the v$sql, It should tell me whether it has used CBO or RBO right?

But when i queried the v$sql it is giving me inputs like 'CHOOSE', 'RBO' and 'NONE'.

As my optimizer mode is CHOOSE. I would like to know what all the queries used CBO and RBO till. I dont what for the future sqls. I want this info only for the sqls in the v$sql view.

To keep this precisely, I want his, I have queried v$sql for a record, then i got the value for the optimizer mode as 'CHOOSE', this is not telling me whether it has used CBO or RBO.

Sorry friends, I think I am confusing all of you.

This is very useful for me.

[SANJAY_G]

Just tried this.. Is it correct to say that if CBO is used it displays cost, cardinality and bytes ??

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select * from test;

      COL1
----------
         1


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=6)
   1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=1 Card=1 Bytes=6)




Statistics
----------------------------------------------------------
         28  recursive calls
          3  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        226  bytes sent via SQL*Net to client
        232  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> analyze table test delete statistics;

Table analyzed.

SQL> select * from test;

      COL1
----------
         1


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'TEST'




Statistics
----------------------------------------------------------
          0  recursive calls
          3  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        226  bytes sent via SQL*Net to client
        232  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

[V6163]

Hi Friends,

I am getting lot of inputs, but the exact answer what i want I am not getting.

Let me clearly tell the problem once again.

I know how to check whether particular query will select CBO and RBO based on the optimizer mode and statistics collected. Now my boss gave me a dump of v$sql view from the production server. Now I should tell him which query has choosen CBO or RBO. But for most of the records in the v$SQL is having the 'CHOOSE' in the value of OPTIMIZER_MODE. Now how can I tell whether is has choosen CBO or RBO. Now these queries I cant run on the production server manually and check for the optimizer mode.

Now what I want is , a query which is already executed thru the application(Front End), I would like to know which optimizer it has used. Is there any database views to see this.


I want to know at a particular instance, how many qeries are being selected for CBO or RBO.

If any one is not clear on my inputs, I am ready to explain once again. As this information is very important for my current task.

Thanks and Regards
V6163

[hrishy]

Hi

well a clear answer

from v$sql there is now way to find out whaether your query used CBO or RBO .Plaese tell your boss that

You should take the sql from the V$sql dump and execute it from the sqlplus prompt..(but since your sql will contain bind variables ) you probably cannot tell whaether you used CBO or RBO

You should enable tracing of the application on the production server
There is simply no other way..

v$sql cannot tell you whaether you used CBO or RBO.tracing of the application on the server is the only way.

regards
Hrishy

[V6163]

Thanks all and sorry for disturbing.