Recently I have worked on huge sql query which was made up from several unioned subqueries.
My colleagues and I managed to parallelise job of tuning that query. Each of us worked on a single subquery.
After we had finished I collected all rewritten subquery and tried to run the whole sql.
But something went wrong. It seemed that some of subqueries changed its plans after they had been united into one query.
After a while I realised the root cause of a problem.
One of colleagues used hint no_query_transformation for his tuned subquery.
I was surprised by the fact that the hint in one of subquery could affect other ones in query with union.
Here is a sample.
Suppose I have got tuned query
and plan
with plan
But united query
produces bad plan
The FILTER operation appears instead of HASH JOIN RIGHT ANTI
If I remove no_query_transformation hint the plan changes: my part becomes good, my colleague`s part becomes wrong (join order in this case)
The solution is to remove no_query_transformation hint and to add no_merge hint into subquery in FROM clause.In real query there was similar solution.
select * from t1
where object_id not in (select /*+hash_aj*/ object_id from t2)
union all
select t1.*
from (select /*+no_merge*/ t2.object_id from t2, t3 where t2.object_id>100000 and t2.object_id=t3.object_id) t23,
t1
where t1.object_id = t23.object_id;
My colleagues and I managed to parallelise job of tuning that query. Each of us worked on a single subquery.
After we had finished I collected all rewritten subquery and tried to run the whole sql.
But something went wrong. It seemed that some of subqueries changed its plans after they had been united into one query.
After a while I realised the root cause of a problem.
One of colleagues used hint no_query_transformation for his tuned subquery.
I was surprised by the fact that the hint in one of subquery could affect other ones in query with union.
Here is a sample.
Suppose I have got tuned query
select * from t1
where object_id not in (select /*+hash_aj*/ object_id from t2);
and plan
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5987 | 824K| 322 (3)| 00:00:04 |
|* 1 | HASH JOIN RIGHT ANTI| | 5987 | 824K| 322 (3)| 00:00:04 |
| 2 | TABLE ACCESS FULL | T2 | 42135 | 534K| 159 (2)| 00:00:02 |
| 3 | TABLE ACCESS FULL | T1 | 48122 | 6015K| 160 (2)| 00:00:02 |
-----------------------------------------------------------------------------
select /*+no_query_transformation*/ *
from (select t2.object_id from t2, t3 where t2.object_id>100000 and t2.object_id=t3.object_id) t23,
t1
where t1.object_id = t23.object_id;
with plan
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 1248 | 481 (3)| 00:00:06 |
|* 1 | HASH JOIN | | 8 | 1248 | 481 (3)| 00:00:06 |
| 2 | VIEW | | 8 | 120 | 320 (2)| 00:00:04 |
|* 3 | HASH JOIN | | 8 | 520 | 320 (2)| 00:00:04 |
|* 4 | TABLE ACCESS FULL| T2 | 8 | 312 | 159 (2)| 00:00:02 |
|* 5 | TABLE ACCESS FULL| T3 | 8 | 208 | 160 (2)| 00:00:02 |
| 6 | TABLE ACCESS FULL | T1 | 48122 | 6626K| 160 (2)| 00:00:02 |
-----------------------------------------------------------------------------
But united query
select * from t1
where object_id not in (select /*+hash_aj*/ object_id from t2)
union all
select /*+no_query_transformation*/ t1.*
from (select t2.object_id from t2, t3 where t2.object_id>100000 and t2.object_id=t3.object_id) t23,
t1
where t1.object_id = t23.object_id;
produces bad plan
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2414 | 301K| 240K (2)| 00:48:03 |
| 1 | UNION-ALL | | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL | T1 | 48122 | 6015K| 161 (3)| 00:00:02 |
|* 4 | TABLE ACCESS FULL | T2 | 421 | 5473 | 159 (2)| 00:00:02 |
|* 5 | HASH JOIN | | 8 | 1128 | 481 (3)| 00:00:06 |
| 6 | VIEW | | 8 | 104 | 320 (2)| 00:00:04 |
|* 7 | HASH JOIN | | 8 | 208 | 320 (2)| 00:00:04 |
|* 8 | TABLE ACCESS FULL| T2 | 8 | 104 | 159 (2)| 00:00:02 |
|* 9 | TABLE ACCESS FULL| T3 | 8 | 104 | 160 (2)| 00:00:02 |
| 10 | TABLE ACCESS FULL | T1 | 48122 | 6015K| 160 (2)| 00:00:02 |
------------------------------------------------------------------------------
The FILTER operation appears instead of HASH JOIN RIGHT ANTI
If I remove no_query_transformation hint the plan changes: my part becomes good, my colleague`s part becomes wrong (join order in this case)
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 60970 | 9M| | 2026 (43)| 00:00:25 |
| 1 | UNION-ALL | | | | | | |
|* 2 | HASH JOIN RIGHT ANTI| | 60959 | 9M| 2016K| 1164 (1)| 00:00:14 |
| 3 | TABLE ACCESS FULL | T2 | 82571 | 1048K| | 287 (1)| 00:00:04 |
| 4 | TABLE ACCESS FULL | T1 | 60959 | 9405K| | 287 (1)| 00:00:04 |
|* 5 | HASH JOIN | | 11 | 2024 | | 862 (1)| 00:00:11 |
|* 6 | HASH JOIN | | 11 | 1881 | | 575 (1)| 00:00:07 |
|* 7 | TABLE ACCESS FULL | T1 | 12 | 1896 | | 287 (1)| 00:00:04 |
|* 8 | TABLE ACCESS FULL | T2 | 12 | 156 | | 287 (1)| 00:00:04 |
|* 9 | TABLE ACCESS FULL | T3 | 12 | 156 | | 287 (1)| 00:00:04 |
--------------------------------------------------------------------------------------
The solution is to remove no_query_transformation hint and to add no_merge hint into subquery in FROM clause.In real query there was similar solution.
select * from t1
where object_id not in (select /*+hash_aj*/ object_id from t2)
union all
select t1.*
from (select /*+no_merge*/ t2.object_id from t2, t3 where t2.object_id>100000 and t2.object_id=t3.object_id) t23,
t1
where t1.object_id = t23.object_id;
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5995 | 825K| 803 (61)| 00:00:10 |
| 1 | UNION-ALL | | | | | |
|* 2 | HASH JOIN RIGHT ANTI| | 5987 | 824K| 322 (3)| 00:00:04 |
| 3 | TABLE ACCESS FULL | T2 | 42135 | 534K| 159 (2)| 00:00:02 |
| 4 | TABLE ACCESS FULL | T1 | 48122 | 6015K| 160 (2)| 00:00:02 |
|* 5 | HASH JOIN | | 8 | 1128 | 481 (3)| 00:00:06 |
| 6 | VIEW | | 8 | 104 | 320 (2)| 00:00:04 |
|* 7 | HASH JOIN | | 8 | 208 | 320 (2)| 00:00:04 |
|* 8 | TABLE ACCESS FULL| T2 | 8 | 104 | 159 (2)| 00:00:02 |
|* 9 | TABLE ACCESS FULL| T3 | 8 | 104 | 160 (2)| 00:00:02 |
| 10 | TABLE ACCESS FULL | T1 | 48122 | 6015K| 160 (2)| 00:00:02 |
------------------------------------------------------------------------------
