How to know the optimizer mode for an SQL statement.
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.
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.
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.
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).
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.
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.