I’m not very keen on bending the rules on production systems, I’d prefer to do things that look as if they could have happened in a completely legal fashion, but sometimes it’s necessary to abuse the system and here’s an example to demonstrate the point. I’ve got a simple SQL statement consisting of nothing more than an eight table join where the optimizer (on the various versions I’ve tested, including 12c) examines 5,040 join orders (even though _optimizer_max_permutations is set to the default of 2,000 – and that might come as a little surprise if you thought you knew what that parameter was supposed to do):
select /*+ star */ d1.p1, d2.p2, d3.p3, d4.p4, d5.p5, d6.p6, d7.p7, f.small_vc from dim1 d1, dim2 d2, dim3 d3, dim4 d4, dim5 d5, dim6 d6, dim7 d7, fact_tab f where d1.v1 = 'abc' and d2.v2 = 'def' and d3.v3 = 'ghi' and d4.v4 = 'ghi' and d5.v5 = 'ghi' and d6.v6 = 'ghi' and d7.v7 = 'ghi' and f.id1 = d1.id and f.id2 = d2.id and f.id3 = d3.id and f.id4 = d4.id and f.id5 = d5.id and f.id6 = d6.id and f.id7 = d7.id ;
It’s useful to have such extreme examples because they make it easy to notice certain features of Oracle’s behaviour – in this case the difference between SQL Plan Baselines and SQL Profiles. After I had created a baseline for this statement the optimizer still examined 5,040 join orders because that’s the way that baselines work – the optimizer first tries to optimize the statement without the baseline in case it can produce a better plan (for future evaluation and evolution) than the plan dictated by the baseline.
In my example this wasn’t really a problem (apart from the memory requirement in the SGA) but one of my clients has a production query that takes 7 seconds to optimize then runs in a few seconds more, so I’d like to get rid of that 7 seconds … without touching the code. Adding a baseline won’t reduce the time to optimize. (Note: 7 seconds may not seem like a lot, but when several copies of this query are executed concurrently using a different literal value as an input, that 7 seconds can go up to anything between 40 and 500 seconds of CPU parse time per query).
If I take a different tack and write some code to acquire the content of the SQL Plan Baseline (viz. the outline section from the in-memory execution plan) but store it as an SQL Profile the optimizer simply follows the embedded hints and examines just one join order (because the set of hints includes a leading() hint specifying exactly the join order required). This is why I will, occasionally, take advantage of the code that Kerry Osborne and Randolf Geist produced some time ago to raid the library cache for a child cursor and store its plan outline as an SQL profile.
Footnote:
This dirty trick doesn’t always solve the problem – the first example I tried to use for my demonstration did a complex concatenation that took a couple of minutes to optimise, and storing the baseline as a profile didn’t help.
Note: to my best knowledge SQL profile (even if only used) requires tuning pack license.
Comment by laimisnd — November 26, 2014 @ 12:56 pm GMT Nov 26,2014 |
p.s. On the other hand, sql patch might solve the issue.
Comment by laimisnd — November 26, 2014 @ 12:58 pm GMT Nov 26,2014 |
[…] Note that using SQL Baselines will probably not help (see https://jonathanlewis.wordpress.com/2014/11/23/baselines/). […]
Pingback by Tackling “cursor: pin S wait on X” wait event issue – svenweller — May 23, 2018 @ 5:15 pm BST May 23,2018 |