Oracle Scratchpad

May 1, 2007

Optimizer Environment

Filed under: Troubleshooting — Jonathan Lewis @ 8:28 pm BST May 1,2007

From time to time you may notice that a single SQL statement has several different child cursors visible in v$sql. One reason for this happening is that different sessions my be running with different optimizer environments, for example your optimizer_mode may be all_rows while mine is first_rows_1. If your optimizer environment is different from my optimizer environment, then we cannot share cursors.

Oracle 10g allows us to see the optimizer environments at the system, session, and individual SQL level, through v$sys_optimizer_env, v$ses_optimizer_env, and v$sql_optimizer_env. This can be very helpful when tracking down anomalies, for example:

 
select 
        first_load_time, child_number, last_load_time, 
        plan_hash_value, loads, executions 
from 
        v$sql 
where 
	sql_id = 'g4pkmrqrgxg3b' 
;    

FIRST_LOAD_TIME     CHILD_NUMBER LAST_LOAD_TIME      PLAN_HASH_VALUE LOADS EXECUTIONS 
------------------- ------------ ------------------- --------------- ----- ---------- 
2007-04-27/08:21:19            0 2007-04-27/08:21:19      2598313856     1          2 
2007-04-27/08:21:19            1 2007-04-27/08:22:44       647803999     1          1   

When we notice that we have two child cursors (generated within a few minutes of each other – notice the last_load_time) for the same statement, we can run the following – 10g only – query to see if it gives us any clues:

break on child_number skip 1    

select 
        child_number, name, value 
from    v$sql_optimizer_env 
where 
	sql_id = 'g4pkmrqrgxg3b' 
order by 
        child_number, 
        name 
;    

CHILD_NUMBER NAME                                     VALUE 
------------ ---------------------------------------- ------------------------- 
           0 _db_file_optimizer_read_count            16 
             active_instance_count                    1 
             bitmap_merge_area_size                   1048576 
             cpu_count                                2 
             cursor_sharing                           exact 
             hash_area_size                           131072 
             optimizer_dynamic_sampling               2 
             optimizer_features_enable                10.2.0.3 
             optimizer_index_caching                  0 
             optimizer_index_cost_adj                 100 
             optimizer_mode                           first_rows_1 
             optimizer_secure_view_merging            true 
             parallel_ddl_mode                        enabled 
             parallel_dml_mode                        disabled 
             parallel_execution_enabled               true 
             parallel_query_mode                      enabled 
             parallel_threads_per_cpu                 2 
             pga_aggregate_target                     204800 KB 
             query_rewrite_enabled                    true 
             query_rewrite_integrity                  enforced 
             skip_unusable_indexes                    true 
             sort_area_retained_size                  0 
             sort_area_size                           65536 
             sqlstat_enabled                          true 
             star_transformation_enabled              false 
             statistics_level                         typical 
             workarea_size_policy                     auto  

           1 _db_file_optimizer_read_count            16 
             _hash_join_enabled                       false 
             active_instance_count                    1 
             bitmap_merge_area_size                   1048576 
             cpu_count                                2 
             cursor_sharing                           exact 
             hash_area_size                           131072 
             optimizer_dynamic_sampling               2 
             optimizer_features_enable                10.2.0.3 
             optimizer_index_caching                  0 
             optimizer_index_cost_adj                 100 
             optimizer_mode                           first_rows_1 
             optimizer_secure_view_merging            true 
             parallel_ddl_mode                        enabled 
             parallel_dml_mode                        disabled 
             parallel_execution_enabled               true 
             parallel_query_mode                      enabled 
             parallel_threads_per_cpu                 2 
             pga_aggregate_target                     204800 KB 
             query_rewrite_enabled                    true 
             query_rewrite_integrity                  enforced 
             skip_unusable_indexes                    true 
             sort_area_retained_size                  0 
             sort_area_size                           65536 
             sqlstat_enabled                          true 
             star_transformation_enabled              false 
             statistics_level                         typical 
             workarea_size_policy                     auto  

In this case, you can see that one of the cursors was generated by a session that had disabled hash joins. One convenience of this view is that it doesn’t normally show the hidden parameters but (just like v$parameter) if someone messes with a hidden parameter, they suddenly show up.

10 Comments »

  1. Hi Jonathan,

    After an upgrade of a database from 8i to 9i, fresh statistics have been gathered but the optimizer_fearures_enabled was set to 8.1.7. Now, I want to set this parameter to 9.2.0, do I need to re-gather the statistics ?

    Regards

    Comment by viveshar — August 21, 2007 @ 5:14 pm BST Aug 21,2007 | Reply

  2. Viveshar,

    There is no special need to collect stats again – the collection in 9i is not significantly different from the collection in 8i. However you will find that the 9i optimizer has a number of options that are not available in 8i so some executon plans may change quite dramatically.

    Two particular changes to watch out for are subquery unnesting and bitmap conversions.

    Comment by Jonathan Lewis — August 21, 2007 @ 7:03 pm BST Aug 21,2007 | Reply

  3. Hi Jonathan,

    I noticed a change in the OPTIMIZER_ENV_HASH_VALUE for a SQL_ID in the DBA_HIST_SQLSTAT. The PLAN_HASH_VALUE remained the same for that SQL ID.

    AWR has got a view called DBA_HIST_OPTIMIZER_ENV
    which contains a column in raw named OPTIMIZER_ENV.

    Can I use this column to determine what has changed for the optimizer ?

    Regards

    Comment by Hans — September 24, 2010 @ 12:39 pm BST Sep 24,2010 | Reply

    • > I struggled, though, to find the connection between Oracle and something being free.

      Hans,

      Possibly – I remember Julian Dyke talking about this column as it appears in v$sql and saying something about how he’d worked out how to unravel it. You may find something about it on his website.

      If there’s nothing on the website, you could dump a 10053 trace, and cross check v$sql_optimizer_env for a child cursor with the raw column in v$sql and the trace output and see how the raw output varies as you modify different optimizer parameters. Personally I wouldn’t bother because it’s the type of thing that’s fairly likely to change on every point release.

      Comment by Jonathan Lewis — September 25, 2010 @ 8:53 am BST Sep 25,2010 | Reply

  4. Hi Jonathan,

    if a session changes PGA workarea policy to MANUAL setting and other PGA parameters this generates a child cursor with a different execution plan. Can this child cursor be shared by another session that uses a different optimizer environment ? If yes how Oracle chooses which child cursor should be used ?

    Regards,

    Atta

    Comment by attagianno — November 3, 2011 @ 12:35 am BST Nov 3,2011 | Reply

    • Atta,

      No – the optimizer environment is a critical part of generating an execution plan.
      If you check v$sql you will find that there are two columns assocated with the optimizer environment: optimizer_env (which is a raw listing of the optimizer environment in which the statement was optimized) and optimizer_env_hash_value – which I assume is the thing that a session uses as a quick check to determine that a child cursor is not appropriate for it to use.

      Comment by Jonathan Lewis — November 13, 2011 @ 3:41 pm BST Nov 13,2011 | Reply

  5. [...] I decided to take a look at recent developments in the “fix control” list, and the “optimizer environment” parameters. Here’s a breakdown of the number of entries in recent versions of [...]

    Pingback by Upgrades « Oracle Scratchpad — September 22, 2012 @ 11:02 am BST Sep 22,2012 | Reply

  6. Hi Jonathan,
    I have a performance issue on client side; The query causing the performance issue have all the time the same execution plan but two optimizer_env_hash_value. I have found this on DBA_HIST_SQLSTAT ; for one of these optimizer_env_hash_value the query takes an important time to be executed. The query is executed (with the bad optimizer_env_hash_value) many times between Day J 11H30 and DAY J+1 00h:30. I can not found this sql_id into v$sql_optimizer_env. Does this mean that a Oracle parameter has been modified ?

    Thanks,
    Cherif.

    Comment by bijga — March 2, 2013 @ 10:28 pm BST Mar 2,2013 | Reply

    • bijga,
      The basic answer to your question is that if the optimizer_env_hash_value has changed then one of the optimizer parameters has changed. v$sql_optimizer_env, however, only shows a fraction of the total number of parameters.

      How are you able to find the two different execution plans without finding the v$sql_optimizer_env ?

      It is possible to get the same plan_hash_value (which means the same “shape” of execution plan with the same object names) and get different execution paths. When working out the plan_hash_value, Oracle doesn’t consider the predicate sections, or the object_id; there are several consequences that might apply.

      If identically named objects exist in different schemas, you could end up with two plans that look the same and have the same plan_hash_value but reference different objects and happen to perform very differently.

      If sessions have different optimizer environments (e.g. manual vs. automatic pga) then they could get exactly the same plans from exactly the same objects with exactly the same query for exactly the same data – but still do different amounts of work, such as sorting to disc instead of memory.

      If the sessions have different NLS settings if may be that the plans look alike, but the predicate show one plan doing a very efficient access on character predicate while the other needs to do a much less efficient filter over a much larger range scan.

      It’s even possible that a change in the optimizer environment could be “harmless”, but the person with the different environment just happens to be the person that uses a bind variable that does a lot more work – perhaps a check v$sql for the two child cursors would show that one averages a lot more rows returned than the other.

      Apart from looking for v$sql_optimizer_env, you could look at v$sql_shared_cursor for clues why new cursors appeared.

      *** I pressed “reply” and then realised that perhaps you can see v$sql_optimizer_env and they look the same. If you have access as SYS, you can query the x$ that sits under v$sql_optimizer_env. and that’s x$kqlfsqce, which you can access through columns KQLFSQCE_SQLID and KQLFSQCE_CHNO. If you can’t access the x$ then, with the right version of Oracle, you might be able to enable trace event 10132 for just that SQL statement and dump the full optimizer environment to disc every time it is optimised – I’ll have to check the method before publishing it, though.

      *** Follow-up: the following seems to do almost what I would want (in 11.1.0.7, at least):

      alter system set events '10132 [SQL:cvmkv0hyc7xhp]'
      

      This dumps a trace file showing the statement, plan, outline, predicates, bind_variables and complete optimizer environment and a few other bits and pieces every time the given SQL_ID is optimized. The trouble is it does seem to do the same for any recursive SQL related to populating the dictionary cache at the same time.

      Comment by Jonathan Lewis — March 3, 2013 @ 3:35 pm BST Mar 3,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,268 other followers