How to know the optimizer mode for an SQL statement.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: How to know the optimizer mode for an SQL statement.

  1. #1
    Join Date
    Oct 2001
    Location
    India
    Posts
    36

    How to know the optimizer mode for an SQL statement.

    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

  2. #2
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865

    Re: How to know the optimizer mode for an SQL statement.

    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
    Code:
    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.
    Last edited by ggnanaraj; 03-03-2003 at 01:06 AM.

  3. #3
    Join Date
    Oct 2001
    Location
    India
    Posts
    36
    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.

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    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

  5. #5
    Join Date
    Oct 2001
    Location
    India
    Posts
    36
    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.

  6. #6
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Just tried this.. Is it correct to say that if CBO is used it displays cost, cardinality and bytes ??
    Code:
    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
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  7. #7
    Join Date
    Oct 2001
    Location
    India
    Posts
    36
    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

  8. #8
    Join Date
    Jan 2001
    Posts
    2,828
    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

  9. #9
    Join Date
    Oct 2001
    Location
    India
    Posts
    36
    Thanks all and sorry for disturbing.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width