The series so far:
- Transformations by the Oracle Optimizer
- The effects of NULL with NOT IN on Oracle transformations
- Oracle subquery caching and subquery pushing
- Oracle optimizer removing or coalescing subqueries
So far, this series has examined the shape of some of the execution plans that Oracle’s optimizer can produce when there are subqueries in the WHERE clause and noted that the optimizer will often unnest a subquery to produce a join instead of using a filter operation that repeatedly runs the subquery. You’ve also seen that it’s possible to dictate the optimizer’s choice and have some control over the point in the execution plan where the filter operation takes place.
This installment moves on to a couple of the more sophisticated transformations that the optimizer can apply to reduce the number of subqueries that end up in the execution plan.
Removing aggregate subqueries
If you browse any of the public Oracle forums you’ve probably seen suggestions that certain patterns of queries would be more efficient if they were rewritten to use analytic functions rather than using a strategy involving self-referencing subqueries. It’s not commonly known that you don’t necessarily have to rewrite such queries; you may simply be able to tell the optimizer to do an internal rewrite for you.
To demonstrate this, I’ll use the emp and dept tables from the scott schema ($ORACLE_HOME/rdbms/admin/utlsampl.sql) as I did in the previous installment, running the demonstration from SQL*Plus on Oracle 19c (19.11.0.0). I’m going to both extend and simplify the query I wrote in that installment for ‘employess with a salary greater than the departmental average.’ The extension is that I’m going to include the department name in the output; the simplification is that I’m going to remove (temporarily) the reference to nvl(comm,0):
select
/*+
qb_name(main)
*/
e1.*, d.dname
from emp e1, dept d
where d.deptno = e1.deptno
and e1.sal > (
select /*+ qb_name(subq) */
avg(e2.sal)
from emp e2
where e2.deptno = d.deptno
)
order by
e1.deptno, e1.empno
/
You might not expect the addition of the department name or the elimination of the nvl() expression to make a significant difference to the optimizer, but the resulting plan is dramatically different. Thanks to referential integrity, the dept table behaves as a sort of ‘focal point,’ allowing the optimizer to connect the two appearances of the emp table and use a mechanism to ‘remove aggregate subquery.’ Here’s the execution plan for the modified query (generated by autotrace):
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1694 | 6 (34)| 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 1694 | 6 (34)| 00:00:01 |
|* 2 | VIEW | VW_WIF_1 | 14 | 1694 | 5 (20)| 00:00:01 |
| 3 | WINDOW SORT | | 14 | 756 | 5 (20)| 00:00:01 |
|* 4 | HASH JOIN | | 14 | 756 | 4 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL| EMP | 14 | 574 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("VW_COL_11" IS NOT NULL)
4 - access("D"."DEPTNO"="E1"."DEPTNO")
The optimizer has eliminated the aggregate subquery, reducing the query from three tables and two blocks to a simple two-table join with an analytic aggregate – at least that’s what the window sort operation and the generated view name vw_wif_1 suggest.
It’s a reasonable guess that the internal rewrite uses the avg() over approach, but this is a case where you probably ought to check the 10053 (CBO) trace file if you want to be confident about the details. Here’s the ‘unparsed’ query (extracted from the trace file but with a lot of cosmetic editing) that the optimizer finally produced for costing this plan:
select /*+ qb_name (main) */
vw_wif_1.item_1 empno, vw_wif_1.item_2 ename, vw_wif_1.item_3 job,
vw_wif_1.item_4 mgr, vw_wif_1.item_5 hiredate, vw_wif_1.item_6 sal,
vw_wif_1.item_7 comm, vw_wif_1.item_8 deptno, vw_wif_1.item_9 dname
from (
select
e1.empno item_1, e1.ename item_2, e1.job item_3,
e1.mgr item_4, e1.hiredate item_5, e1.sal item_6,
e1.comm item_7, e1.deptno item_8, d.dname item_9,
case
when e1.sal > avg(e1.sal) over (partition by e1.deptno)
then e1.rowid
end vw_col_10
from
test_user.dept d,
test_user.emp e1
where
d.deptno = e1.deptno
) vw_wif_1
where
vw_wif_1.vw_col_10 is not null
order by
vw_wif_1.item_8,
vw_wif_1.item_1
/
It’s interesting to note that the optimizer has introduced a CASE expression in the inline view (vw_wif_1) to generate a column that can be tested for nullity in the main query block. The human version for this strategy would probably have been to generate the ‘average over department’ as a column in the inline view that could be compared with sal in the outer query, e.g.:
select
e2.*
from
(
select
e1.*, d.dname,
avg(e1.sal) over(partition by e1.deptno) avg_sal
from emp e1, dept d
where d.deptno = e1.deptno
) e2
where
e2.sal > e2.avg_sal
order by
e2.deptno,
e2.empno
/
I said I’d simplified the query by removing the reference to nvl(comm,0); I had to do this initially because the transformation would otherwise not be used. Eventually, I did get the transformation to appear with the expression sal + nvl(comm,0), but I had to add a virtual column to the table matching the expression and rewrite the query using the virtual column:
alter table emp add nvl_earnings -- invisible
generated always as (sal + nvl(comm,0))
/
This anomaly is probably one introduced in Oracle 19.9 by a fix for bug 30593046 which produced wrong results in some cases involving ‘scalar subquery unnesting’ in 12.2.0.1. Unfortunately, the fix blocked too many cases where unnesting was safe, so a replacement bug fix (33325981) appeared in 19.13 to restore some of the blocked cases. However, my example (where unnesting is a necessary precursor to subquery removal) still isn’t allowed past the block. When I ran the test case on 12.2.0.1, the optimizer did unnest the subquery with the original expression but didn’t carry on to remove the subquery.
In previous installments, I’ve discussed using hints to control the optimizer’s strategy when you think you know more about the data than the optimizer does. In this case, the transformation relies on sorting (i.e., the Window Sort at operation 3), so there are likely to be occasions when a bad choice of plan does a lot more work than the optimizer’s arithmetic suggests, and you might want to block the transformation. Conversely there may be cases where the transformation doesn’t appear when it would be a really good idea. Unfortunately, even though the transformation has been available since 10gR2, there is no custom hint to force it or block it. The best you can do if you want to stop the transformation from taking place is to disable the feature using an alter session command or adding the opt_param() hint to the query: opt_param(‘_remove_aggregate_subquery’,’false’). However, if you think the optimizer isn’t using the transformation when it should be, there’s not a lot you can do about it. It’s possible that you may find cases where a hint to unnest the critical subquery will result in the optimizer deciding to go one step further and remove the subquery – but that’s just a conjecture, I haven’t yet produced an example to demonstrate that this is actually possible.
Coalescing subqueries
Consider the following query which will be repeated in its full context a little later:
select /*+ qb_name(main) */
*
from test_t1 t1
where
t1.is_deleted='N'
and (
t1.id2 in (select /*+ qb_name(id2) */ t2.id2
from test_t2 t2
where t2.is_deleted='N' and t2.id=:p_id
)
or t1.id3 in (select /*+ qb_name(id3) */ t2.id2
from test_t2 t2
where t2.is_deleted='N' and t2.id=:p_id
)
);
When you examine the two subqueries, you’ll notice that they are identical, and both are driven by the same bind variable :p_id. The same subquery appears twice because the result is compared with two different columns from table test_t1, so it’s tempting to ask the question: “can we get each row just once then compare it twice?”
Since the optimizer often converts IN subqueries into existence subqueries you could consider transforming both these subqueries into existence subqueries then merging them into a single subquery that pushes the OR condition inside the subquery, doing something like:
Step 1:
and (
exists (select /*+ qb_name(id2) */ null
from test_t2 t2
where t2.is_deleted='N' and t2.id=:p_id
and t2.id2 = t1.id2
)
or exists (select /*+ qb_name(id3) */ null
from test_t2 t2
where t2.is_deleted='N' and t2.id=:p_id
and t2.id2 = t1.id3
)
);
Step 2:
and (
exists (select /*+ qb_name(SEL$????????) */ null
from test_t2 t2
where t2.is_deleted='N' and t2.id=:p_id
and (t2.id2 = t1.id2 or t2.id2 = t1.id3)
)
);
The mechanism displayed in step 2 is known as ‘subquery coalescing,’ and it has been available to the optimizer and enabled since 11.2.0.1, and comes complete with the pair of hints /*+ [no_]coalesce_sq() */.
In this example, it’s, fairly obvious that if you had an index on nothing but t2(is_deleted, id) and if (despite the clue in the second column name) there were lots of rows matching the predicates t2.is_deleted=’N’ and t2.id = :p_id then it would make sense to combine the two subqueries so that you would only have to visit those rows once each, and could stop at the first occurrence of either the t1.id2 value or the t1.id3 value. On the other hand, if you had an index like t2(id, id2) there may be no benefit gained from the transformation, so it’s nice that the mechanism can be blocked with a simple hint.
In fact, someone raised a question about this specific example in one of the public Oracle forums because the transformation had had a most undesirable side effect and produced a bad plan that took far longer to run than the best possible plan. Here’s some code (supplied on the forum, but with a few enhancements) to produce a model of what the data looked like:
rem
rem Script: coalesce_sq_2.sql
rem Author: Forum Member, edited: Jonathan Lewis
rem Dated: March 2022
rem
create table test_t1 as
select
level id, level id2, level id3, 'N' as is_deleted
from dual
connect by
level < 1e5
;
alter table test_t1 add constraint test_t1_pk primary key (id);
create index test_t1_idx2 on test_t1(id2);
create index test_t1_idx3 on test_t1(id3);
create table test_t2 as select * from test_t1;
alter table test_t2 add constraint test_t2_pk primary key (id);
variable p_id number
exec :p_id := 5000
alter session set statistics_level = all;
set serveroutput off
select /*+ qb_name(main) */
*
from test_t1 t1
where
t1.is_deleted='N'
and (
t1.id2 in (select /*+ qb_name(id2) */ t2.id2
from test_t2 t2
where t2.is_deleted='N' and t2.id=:p_id
)
or t1.id3 in (select /*+ qb_name(id3) */ t2.id2
from test_t2 t2
where t2.is_deleted='N' and t2.id=:p_id
)
);
select *
from table(dbms_xplan.display_cursor(format=>' allstats last -rows'));
Here’s the resulting plan:
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.24 | 200K|
|* 1 | FILTER | | 1 | 1 |00:00:00.24 | 200K|
|* 2 | TABLE ACCESS FULL | TEST_T1 | 1 | 99999 |00:00:00.01 | 305 |
|* 3 | TABLE ACCESS BY INDEX ROWID| TEST_T2 | 99999 | 1 |00:00:00.17 | 200K|
|* 4 | INDEX UNIQUE SCAN | TEST_T2_PK | 99999 | 99999 |00:00:00.09 | 100K|
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
2 - filter("T1"."IS_DELETED"='N')
3 - filter((("T2"."ID2"=:B1 OR "T2"."ID2"=:B2) AND "T2"."IS_DELETED"='N'))
4 - access("T2"."ID"=:P_ID)
As usual, when pulling a live execution plan from memory the text of the subquery used for the FILTER at operation 1 has disappeared from the Predicate Information, but you can infer from the predicates reported at operation 3 that subquery coalescing has taken place. For confirmation, you could use explain plan and dbms_xplan.display() to get a report that shows the missing predicate information (being careful to remember that explain plan knows nothing about the bind variable – not even its type – which is why you see to_number(:p_id) in the following:
1 - filter( EXISTS (SELECT /*+ QB_NAME ("ID2") */ 0
FROM "TEST_T2" "T2" WHERE
"T2"."ID"=TO_NUMBER(:P_ID)
AND ("T2"."ID2"=:B1 OR "T2"."ID2"=:B2) AND
"T2"."IS_DELETED"='N'))
In fact, if you had reported the outline information in the call to dbms_xplan.display_cursor(), you would also have seen that it included the following two directives:
Outline Data
-------------
COALESCE_SQ(@"ID3")
COALESCE_SQ(@"ID2")
The key thing to note, however, is the amount of work that Oracle has had to do. For every row in the table, it has executed that subquery. Looking at the definition of table test_t2 (and especially its primary key) it’s easy to see that there can be at most one row where t2.id = :p_id, so why didn’t the optimizer unnest the subquery and use it to drive into test_t1? The answer is that while you would use only one row and one value of t2.id2 to drive a join into test_t1, there are two different columns in test_t1 in the query, and you’d have to do something complicated to unravel the pieces and join to test_t1 twice per row from test_t2 – so the optimizer doesn’t (yet) try it. There are further comments about this complication in the following blog article by Mohamed Houri: https://hourim.wordpress.com/2017/08/12/unnesting-of-coalesced-subqueries/
Since subquery coalescing has blocked a strategy that seems sensible to the human eye it’s worth telling the optimizer not to use the feature just to see if something interesting happens. Add the no_coalesce_sq() hint for the two named subquery blocks to the main query:
select /*+
qb_name(main)
no_coalesce_sq(@id2)
no_coalesce_sq(@id3)
-- or_expand(@main (1) (2))
*/
*
from test_t1 t1
where
t1.is_deleted='N'
and (
t1.id2 in (select /*+ qb_name(id2) */ t2.id2
from test_t2 t2
where t2.is_deleted='N' and t2.id=:p_id
)
or t1.id3 in (select /*+ qb_name(id3) */ t2.id2
from test_t2 t2
where t2.is_deleted='N' and t2.id=:p_id
)
);
The or_expand(@main (1) (2)) hint – that I’ve commented out here – was something I added to force 12.2.0.1 to produce the plan that I got from 19.11.0.0. Here’s the more complicated, but far more efficient, execution plan that appeared:
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.01 | 16 |
| 1 | VIEW | VW_ORE_7F40D524 | 1 | 1 |00:00:00.01 | 16 |
| 2 | UNION-ALL | | 1 | 1 |00:00:00.01 | 16 |
| 3 | NESTED LOOPS | | 1 | 1 |00:00:00.01 | 7 |
|* 4 | TABLE ACCESS BY INDEX ROWID | TEST_T2 | 1 | 1 |00:00:00.01 | 3 |
|* 5 | INDEX UNIQUE SCAN | TEST_T2_PK | 1 | 1 |00:00:00.01 | 2 |
|* 6 | TABLE ACCESS BY INDEX ROWID BATCHED | TEST_T1 | 1 | 1 |00:00:00.01 | 4 |
|* 7 | INDEX RANGE SCAN | TEST_T1_IDX2 | 1 | 1 |00:00:00.01 | 3 |
|* 8 | FILTER | | 1 | 0 |00:00:00.01 | 9 |
| 9 | NESTED LOOPS | | 1 | 1 |00:00:00.01 | 6 |
|*10 | TABLE ACCESS BY INDEX ROWID | TEST_T2 | 1 | 1 |00:00:00.01 | 3 |
|*11 | INDEX UNIQUE SCAN | TEST_T2_PK | 1 | 1 |00:00:00.01 | 2 |
|*12 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_T1 | 1 | 1 |00:00:00.01 | 3 |
|*13 | INDEX RANGE SCAN | TEST_T1_IDX3 | 1 | 1 |00:00:00.01 | 2 |
|*14 | TABLE ACCESS BY INDEX ROWID | TEST_T2 | 1 | 1 |00:00:00.01 | 3 |
|*15 | INDEX UNIQUE SCAN | TEST_T2_PK | 1 | 1 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T2"."IS_DELETED"='N')
5 - access("T2"."ID"=:P_ID)
6 - filter("T1"."IS_DELETED"='N')
7 - access("T1"."ID2"="T2"."ID2")
8 - filter(LNNVL( IS NOT NULL))
10 - filter("T2"."IS_DELETED"='N')
11 - access("T2"."ID"=:P_ID)
12 - filter("T1"."IS_DELETED"='N')
13 - access("T1"."ID3"="T2"."ID2")
14 - filter(("T2"."ID2"=:B1 AND "T2"."IS_DELETED"='N'))
15 - access("T2"."ID"=:P_ID)
A key feature that you can spot very easily is that this plan accesses a total of only 16 buffers, handling only a few rows, rather than accessing 200,000 buffers and handling 10,000 rows. It’s clearly a significant reduction in the workload, but how does this plan actually work?
Effectively the optimizer has turned the OR’ed pair of subqueries into a UNION ALL, then unnested the union all, then pushed the join to test_t1 inside the UNION ALL (in a step rather like the reverse of join factorization – which will be reviewed in a future installment). To eliminate rows that have already appeared in the first branch of the UNION ALL the optimizer has then added a filter subquery to the second branch.
You might note that if I had not had a unique (primary key) index on test_t2 there would have been SORT UNIQUE operations applied to the rows selected from test_t2 in both branches before the joins into test_t1.
In effect the query has been transformed into:
select {columns}
from t2, t1
where t1.id2 = t2.id2 and etc...
union all
select {columns}
from t2, t1
where t1.id3 = t2.id2 and etc...
and not exists (
select {columns}
from t2, t1
where t1.id2 = t2.id2
)
I won’t extract and reformat the ‘unparsed’ query from the CBO trace file since it is a little long and messy and adds little value to the SQL sketch above. Notice, however, from the Predicate Information provided by the call to explain plan that where I’ve used not exists(subquery) in the simplified framework, the optimizer actually uses lnnvl( exists( subquery ) ) at operation 8 of the plan. Oracle is just applying its generic ‘does not evaluate to true’ function to allow for possible nulls rather than using a simple ‘not’.
Although the resulting plan looks a little complex it is just a combination of a small number of simple transformations done in the right order, so it’s a little surprising that the optimizer doesn’t find it automatically as it searches its space of transformations. This omission has now been noted as (unpublished) bug 33957043: “Subquery Coalesce prevents Or Expansion/transformation”.
As a reminder about how difficult it is to use hints to control the fine detail of the optimizer, when I checked the Outline Information for this execution plan, it held 35 hints that would be needed in an SQL Plan Baseline to reproduce the plan. Though the list included an or_expand() hint, the no_coalesce_sq() hint didn’t make an appearance.
Summary
Converting an aggregate subquery strategy to an analytic function strategy is a rewrite that many people now do by hand when they spot the option and the numbers look right. The optimizer can do this as an internal transformation, though it doesn’t seem to take the option as often as it could. Unfortunately, there’s no explicit hint to force the transformation (though an unnest() hint may have a lucky side effect), and the only possible hint to block it is the opt_param() hint which may be needed in cases where the transformation introduces a large sorting overhead. In some cases, you may need to help the optimizer to pick the path by simplifying the SQL through the use of virtual columns, but that requirement may become redundant in a future release.
If you have multiple subqueries which look sufficiently similar and are used in a similar fashion, the optimizer can do the equivalent of ‘find the least common multiple’ so that it has to run a smaller number of subqueries, potentially using each call for multiple tests. This transformation has a related pair of hints, so it can be blocked or (if legal) forced. The demonstration of the transformation in this article highlighted a case where the optimizer missed an opportunity for using or expansion after doing subquery coalescing, so it’s worth knowing that the mechanism can be blocked.
A future installment will examine Or Expansion and Join Factorisation.
The post Oracle optimizer removing or coalescing subqueries appeared first on Simple Talk.
from Simple Talk https://ift.tt/WrKtRFL
via
No comments:
Post a Comment