Oracle Scratchpad

July 10, 2012

Random Plans

Filed under: CBO,humour,Oracle — Jonathan Lewis @ 5:32 pm BST Jul 10,2012

Have you ever felt that the optimizer was persecuting you by picking plans at random ? Perhaps you’re not paranoid, perhaps it’s part of Oracle Corp’s. master plan to take over the world. If you look closely at the list of hidden parameters you’ll find that some details of this cunning plan have leaked. In 10.1.0.2 Oracle created a new parameter _optimizer_random_plan with the description “optimizer seed value for random plans”. Who knows what terrible effects we may see when the default value of this parameter changes.

7 Comments »

  1. Hehehe! I can’t even begin to imagine how many jokes this one is going to generate!

    Comment by Noons — July 10, 2012 @ 9:46 pm BST Jul 10,2012 | Reply

  2. I’m thinking it’s used for SQL diagnosis. The effect of this parameter just likes the hint of OPTIMIZER_FEATURES_ENABLE with a random feature version.

    Regards,

    Comment by Fuyuncat — July 11, 2012 @ 2:49 am BST Jul 11,2012 | Reply

  3. Talking of random plans, I was looking yesterday at the historic executions of a particular SQL statement that executes once per day – 62 distinct plans in 65 days! Doesn’t seem to be an issue as the resources used are pretty similar for each plan. Not had time to investigate further yet or even look at the plan differences.

    Comment by Dom Brooks — July 11, 2012 @ 9:14 am BST Jul 11,2012 | Reply

    • I should have had a closer look BEFORE I made that comment… on closer inspection it’s just different internal names for the materialized subquery temp table. Of course, every time you get a hard parse, you get a new unique name.

      Comment by Dom Brooks — July 11, 2012 @ 10:51 am BST Jul 11,2012 | Reply

      • Dom,

        I think there’s a bug/patch reported against that to get temporary names removed from the calculation of plan hash values. I forget which version it appeared in though – but it was fairly recent.

        Comment by Jonathan Lewis — July 11, 2012 @ 11:44 am BST Jul 11,2012 | Reply

        • Hi Jonathan,

          I believe the bug is 10162430.

          The description of what exactly is fixed in 12.1 and 11.2.0.3 is unclear because I’m on 11.2.0.3 and clearly these different plan

          But the important information from that doc is that when just these temporary table names differ, the plan_hash_2 value in other_xml should be the same. And I can verify that in my case it is.

          So my standard query at dba_hist_sqlstat to look at historic executions of particular sql statments, now includes a scalar subselect to get plan_hash_2, like so:

          select sn.snap_id
          ,      sn.end_interval_time
          ,      st.module
          ,      st.sql_id
          ,      st.plan_hash_value
          ,      (select extractvalue(xmltype(other_xml),'other_xml/info[@type="plan_hash_2"]')
                  from   dba_hist_sql_plan hp
                  where  hp.sql_id          = st.sql_id
                  and    hp.plan_hash_value = st.plan_hash_value
                  and    hp.other_xml is not null) plan_hash_2
          ,      rows_processed_delta rws
          ,      executions_delta     execs
          ,      elapsed_time_delta   elp
          ,      cpu_time_delta       cpu
          ,      buffer_gets_delta    gets
          ,      iowait_delta         io
          from   dba_hist_snapshot sn
          ,      dba_hist_sqlstat  st
          where  st.snap_id            = sn.snap_id
          and    st.sql_id             = '&sql_id'
          and    st.elapsed_time_delta > 0
          order by sn.snap_id desc; 
          

          So, thanks for that.

          Comment by Dom Brooks — July 12, 2012 @ 9:02 am BST Jul 12,2012

        • Perhaps this also starts to address the question of “what is plan_hash_2″? An enhanced plan_hash… a bit like the force_matching_signature of a sql statement? And do we start to see why plan_hash_2 is the plan id stored for sql plan baselines?

          Sorry – I’ve gone completely off-topic from the blog post.

          Comment by Dom Brooks — July 12, 2012 @ 9:10 am BST Jul 12,2012


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,267 other followers