As you may remember, in one of my previous blog posts I wrote about the problem with wrong join cardinalities related to SQL plan directives. In a nutshell, join cardinalities are being adjusted to zero, irrespective of the dynamic sampling result.
In fact, you can find the evidence of that anomaly in the optimizer trace file:
Join Card adjusted from 444734.345351 to 0.000000 due to adaptive dynamic sampling, prelen=2In the example from the previous article, I used the no_unnest hint to reproduce the problem. Therefore, the example might appear just too artificial. So, I’m expanding it now to something that looks a bit more realistic. What I mean by that is that I engineered a new test case where CBO rather himself decides not to do subquery unnesting. This means that no hint is needed any more for reproducing the problem. In addition, the issue becomes even more obvious as, unlike in the previous test case, a wrong cardinality estimation leads to a disastrous execution plan.
Obviously, the original test case has undergone some changes. To begin with, I added the fourth table t4:
create table t1 ( n1 number ) ;
insert into t1
select trunc(level/4)+1
from dual connect by level <= 320000 ;
create table t4 ( n1 number ) ;
insert into t4
select level
from dual connect by level <= 80000 ;
commit ;
exec dbms_stats.gather_table_stats( null, 'T1' ) ;
exec dbms_stats.gather_table_stats( null, 'T4' ) ;
create table t2 ( n1 number , n2 number ) ;
create table t3 ( n1 number , n2 number ) ;
exec dbms_stats.gather_table_stats( null, 'T2' ) ;
exec dbms_stats.gather_table_stats( null, 'T3' ) ;
insert into t2
select level, level
from dual connect by level <=150000 ;
insert into t3
select level, trunc(level/3)
from dual connect by level <=450000 ;
commit ;
select distinct t3.n1
from t3
join t2 on t3.n2 = t2.n2
where t2.n1 = 1 ;
/
exec dbms_spd.flush_sql_plan_directive ;
EXEC dbms_stats.gather_table_stats( null, 'T2' );
EXEC dbms_stats.gather_table_stats( null, 'T3' );And then I referenced the table t4 in the subquery:
SELECT /*+ gather_plan_statistics */ t3.n1
FROM t3 JOIN t2 ON t3.n2 = t2.n2
WHERE t2.n1 IN (SELECT n1 FROM t1 MINUS SELECT n1 FROM t4 ) ;
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:16:06.57 | 84M| | | |
|* 1 | FILTER | | 1 | | 3 |00:16:06.57 | 84M| | | |
|* 2 | HASH JOIN | | 1 | 1 | 449K|00:00:01.00 | 1248 | 9268K| 2474K| 8122K (0)|
| 3 | TABLE ACCESS FULL | T2 | 1 | 150K| 150K|00:00:00.01 | 314 | | | |
| 4 | TABLE ACCESS FULL | T3 | 1 | 450K| 450K|00:00:00.16 | 931 | | | |
| 5 | MINUS | | 150K| | 1 |00:27:21.22 | 84M| | | |
| 6 | SORT UNIQUE NOSORT| | 150K| 4 | 80001 |00:24:06.86 | 73M| | | |
|* 7 | TABLE ACCESS FULL| T1 | 150K| 4 | 320K|00:17:40.57 | 73M| | | |
| 8 | SORT UNIQUE NOSORT| | 80001 | 1 | 80000 |00:03:14.01 | 10M| | | |
|* 9 | TABLE ACCESS FULL| T4 | 80001 | 1 | 80000 |00:03:13.69 | 10M| | | |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
2 - access("T3"."N2"="T2"."N2")
7 - filter("N1"=:B1)
9 - filter("N1"=:B1)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statementAs you can see, the query performed 84 million logical reads and executed in 16:06 minutes. The main reason for the bad plan is the misestimate in the step 2, which, in turn, is a consequence of the adjustment-to-zero issue.
However, the subsequent executions yield a far better execution plan:
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.40 | 1864 | | | |
|* 1 | HASH JOIN | | 1 | 948K| 3 |00:00:00.40 | 1864 | 25M| 4946K| 38M (0)|
| 2 | TABLE ACCESS FULL | T3 | 1 | 450K| 450K|00:00:00.01 | 930 | | | |
|* 3 | HASH JOIN | | 1 | 320K| 1 |00:00:00.12 | 934 | 9268K| 2474K| 8066K (0)|
| 4 | TABLE ACCESS FULL | T2 | 1 | 150K| 150K|00:00:00.01 | 314 | | | |
| 5 | VIEW | VW_NSO_1 | 1 | 320K| 1 |00:00:00.08 | 620 | | | |
| 6 | MINUS | | 1 | | 1 |00:00:00.08 | 620 | | | |
| 7 | SORT UNIQUE | | 1 | 320K| 80001 |00:00:00.05 | 492 | 3667K| 1421K| 3259K (0)|
| 8 | TABLE ACCESS FULL| T1 | 1 | 320K| 320K|00:00:00.01 | 492 | | | |
| 9 | SORT UNIQUE | | 1 | 80000 | 80000 |00:00:00.02 | 128 | 3667K| 822K| 3259K (0)|
| 10 | TABLE ACCESS FULL| T4 | 1 | 80000 | 80000 |00:00:00.01 | 128 | | | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T3"."N2"="T2"."N2")
3 - access("T2"."N1"="N1")This time, the query ran in less than a second and did only 1864 logical reads. Hadn't the optimizer erroneously adjusted the join cardinality to zero, the optimal plan would have been generated at the first execution.
In conclusion, the magnitude of the potential damage is even more visible in the modified test case.
