It’s always worth browsing through the list of Oracle’s bug fixes each time a new release or patch comes out because it can give you clues about where to look for problems in your production release – and how to anticipate problems on the upgrade. This article is an example of a fix that I found while while looking at the List of Bug Fixes for 11.2.0.3 (MOS licence required for link) quite recently.
Over the last three or four years I’ve made several comments about how subquery factoring could result in changes in execution plans even if the “factored subquery” (or “common table expression” (CTE) to use the more appropriate technical term) was moved in line as the optimizer did its job. In the 11.2.0.3 bug fixes list, though, you’ll find the following bug fixes:
Bug 11740670 Different execution plans with and without subquery factoring (WITH clause)
Bug 9002661 Transitive predicate were not generated in WITH clause.
So here’s an example that’s been sitting on my laptop for a few years, waiting for the right bug fix (it’s using my standard setup of 8KB block size, LMT with 1MB uniform extents, but not ASSM, and CPU costing disabled):
rem rem Script: with_restriction.sql rem Author: Jonathan Lewis rem Dated: April 2007 rem create table emp ( dept_no not null, sal, emp_no, padding, constraint e_pk primary key(emp_no) ) as select mod(rownum,6), rownum, rownum, rpad('x',60) from all_objects where rownum <= 20000 -- > comment to avoid WordPress format issue ; begin dbms_stats.gather_table_stats( ownname => user, tabname =>'EMP', cascade => true, estimate_percent => null, granularity => 'DEFAULT', method_opt => 'for all columns size 1' ); end; / select outer.* from emp outer where sal > 1000000 and outer.sal > ( select avg(inner.sal) from emp inner where inner.dept_no = outer.dept_no ) ; with subq as ( select outer.* from emp outer where sal > 1000000 and outer.sal > ( select avg(inner.sal) from emp inner where inner.dept_no = outer.dept_no ) ) select * from subq ;
In 11.1.0.7 (and earlier) the first version of this query uses a simple filter subquery, and the second version inlines the factored subquery (which should, therefore, produce the same plan) but then unnests the subquery and uses “group by placement” to resolve the plan as a hash join followed by hash aggregation. This gives us two completely different plans:
Plan for first form of the query ------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 72 | 70 | |* 1 | FILTER | | | | | |* 2 | TABLE ACCESS FULL | EMP | 1 | 72 | 35 | | 3 | SORT AGGREGATE | | 1 | 8 | | |* 4 | TABLE ACCESS FULL| EMP | 3333 | 26664 | 35 | ------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OUTER"."SAL"> (SELECT AVG("INNER"."SAL") FROM "EMP" "INNER" WHERE "INNER"."DEPT_NO"=:B1)) 2 - filter("SAL">1000000) 4 - filter("INNER"."DEPT_NO"=:B1) Plan with factored subquery moved in line ------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 83 | 93 | |* 1 | FILTER | | | | | | 2 | HASH GROUP BY | | 1 | 83 | 93 | |* 3 | HASH JOIN | | 1667 | 135K| 71 | |* 4 | TABLE ACCESS FULL| EMP | 1 | 72 | 35 | | 5 | TABLE ACCESS FULL| EMP | 20000 | 214K| 35 | ------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OUTER"."SAL">AVG("INNER"."SAL")) 3 - access("INNER"."DEPT_NO"="OUTER"."DEPT_NO") 4 - filter("SAL">1000000)
Under 11.2.0.3, however, the first plan appears for both forms of the query.
On the plus side – this is good because Oracle is now behaving consistently. On the minus side – this is bad because someone, somewhere, is going to be the unlucky person who used to get a good execution plan by accident because of the bugs and will now get a bad execution plan because the bug has been fixed.
Maxim: when upgrading (to 11.2.0.3 or higher) keep a close eye on any queries you’ve got that include subquery factoring – in fact, you might decide to capture SQL Baselines (without activating them) for every single one of them before doing the upgrade so that you can enforce the pre-upgrade plan if the post-upgrade plan is less efficient.
Footnote: I haven’t finished with this example – there’s an interesting follow-up that appeared in the remainder of my test code when I hinted the subquery into materialization. But that’s a story for another day.
[…] I wrote a note last week about the fixes to the subquery factoring optimizer code in 11.2.0.3, I finished with a comment about having more to say on the test case if I […]
Pingback by Subquery Factoring « Oracle Scratchpad — February 16, 2012 @ 5:04 pm GMT Feb 16,2012 |
[…] Bug fixes in 11.2.0.3 change some plans that use subquery factoring […]
Pingback by CTE Catalogue | Oracle Scratchpad — June 10, 2020 @ 6:46 pm BST Jun 10,2020 |