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.
July 10, 2012
7 Comments »
RSS feed for comments on this post. TrackBack URI
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 GMT Jul 10,2012 |
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 GMT Jul 11,2012 |
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 GMT Jul 11,2012 |
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 GMT Jul 11,2012 |
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 GMT Jul 11,2012 |
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 GMT 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 GMT Jul 12,2012