Introduction
Temp temporary transformation is the operation in the execution plan where Oracle stores the subquery results in a temporary table. Cardinality estimates can get different if the temp table transformation is used in Oracle 12c. The purpose of this post is to explain why this happens.
Test data
To prove the point, we will first create the test table t with unevenly populated column value:
create table t as
SELECT level id,'UNPOPULAR VALUE' value
FROM dual
CONNECT BY level <= 10;
insert into t
SELECT level,'POPULAR_VALUE'
FROM dual
CONNECT BY level <= 90;
commit ;
EXEC DBMS_STATS.gather_table_stats('SYS', 'T');
First execution ( just to create SQL plan directive )
Subsequently, we will execute the following query, which selects unpopular values.
SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM t WHERE value = 'UNPOPULAR VALUE' and id >= 1 ;
Because of the missing histograms, the cardinality estimate is obviously wrong (50 rows estimated vs. 10 actual rows):
------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 5 | |* 1 | TABLE ACCESS FULL| T | 1 | 50 | 10 |00:00:00.01 | 5 | ------------------------------------------------------------------------------------
However, because of the “AND” clause in the filter predicate, the statistics feedback kicked in and as a result, the SQL plan directive was created, which we will persist with the following statement:
EXEC DBMS_SPD.flush_sql_plan_directive;
This means, that the second execution should yield correct cardinality estimates, which indeed happens:
SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM t e2 WHERE value = 'UNPOPULAR VALUE' and id >= 1 ;
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 5 |
|* 1 | TABLE ACCESS FULL| T | 1 | 10 | 10 |00:00:00.01 | 5 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("VALUE"='UNPOPULAR VALUE' AND "ID">=1))
Note
-----
- statistics feedback used for this statement
Temp table transformation
In the next step, the query is slightly rewritten, to establish the baseline which will be compared afterwards with the query, which uses temp table transformation:
select * from
(with q as (
select *
from t
where value='UNPOPULAR VALUE' and id>= 1
)
select /*+ GATHER_PLAN_STATISTICS */ * from q
) ;
It can be seen, that the cardinality estimate is still correct:
------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 5 | |* 1 | TABLE ACCESS FULL| T | 1 | 10 | 10 |00:00:00.01 | 5 |
Finally, we will enforce the temp table transformation by using the undocumented materialize hint:
select * from
(with q as (
select /*+ materialize */ *
from t
where value='UNPOPULAR VALUE' and id>= 1
)
select /*+ GATHER_PLAN_STATISTICS */ * from q
);
The estimated cardinality is wrong now:
--------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | --------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 20 | 1 | 1 | | 1 | VIEW | | 1 | 50 | 10 |00:00:00.01 | 20 | 1 | 1 | | 2 | TEMP TABLE TRANSFORMATION | | 1 | | 10 |00:00:00.01 | 20 | 1 | 1 | | 3 | LOAD AS SELECT | | 1 | | 0 |00:00:00.01 | 8 | 0 | 1 | |* 4 | TABLE ACCESS FULL | T | 1 | 10 | 10 |00:00:00.01 | 4 | 0 | 0 | | 5 | VIEW | | 1 | 50 | 10 |00:00:00.01 | 7 | 1 | 0 | | 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6F66_B0C71D84 | 1 | 50 | 10 |00:00:00.01 | 7 | 1 | 0 | ---------------------------------------------------------------------------------------------------------------------------------------
The reason for this phenomena can be found by comparing the relevant sections in 10053 trace.
Without temp table transformation, there is a SQL plan directive for dynamic sampling of the T table, which adjusts the originally calculated cardinality :
SPD: Directive valid: dirid = 866821167068028328, state = 2, flags = 1, loc = 1 {C(2611393)[1, 2]}
SPD: Return code in qosdDSDirSetup: EXISTS, estType = TABLE
Card: Original: 100.000000 >> Single Tab Card adjusted from 50.000000 to 10.000000 due to adaptive dynamic sampling
In the execution plan with the temp table transformation such directive is missing for the temporary table SYS_TEMP_0FD9D6F66_B0C71D84:
Access path analysis for SYS_TEMP_0FD9D6F65_B0C71D84 *************************************** SINGLE TABLE ACCESS PATH Single Table Cardinality Estimation for SYS_TEMP_0FD9D6F65_B0C71D84[T1] SPD: Return code in qosdDSDirSetup: NOQBCTX, estType = TABLE Table: SYS_TEMP_0FD9D6F65_B0C71D84 Alias: T1 Card: Original: 50.000000 Rounded: 50 Computed: 50.000000 Non Adjusted: 50.000000
Curiously, Oracle sampled the T table, but it didn’t use the data to estimate the cardinality of the temporary table:
Access path analysis for T
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T[T]
SPD: Directive valid: dirid = 866821167068028328, state = 2, flags = 1, loc = 1 {C(2611393)[1, 2]}
SPD: Return code in qosdDSDirSetup: EXISTS, estType = TABLE
Table: T Alias: T
Card: Original: 100.000000 >> Single Tab Card adjusted from 50.000000 to 10.000000 due to adaptive dynamic sampling
Rounded: 10 Computed: 10.000000 Non Adjusted: 50.000000
Update 6.1.2016: Oracle support filed a bug for this problem – Bug 22497876 : INCORRECT CARDINALITY FOR QUERY USING MATERIALIZED WITH CLAUSE AND DS DIRECTIVE
Hi Nenad,
yes, you are right. Temp table transformations got some weakness as it loses some information from time to time. Randolf Geist has recently published several blog posts about such loss as well, e.g.:
– http://oracle-randolf.blogspot.de/2015/05/temp-table-transformation-cardinality.html
– http://oracle-randolf.blogspot.de/2015/05/temp-table-transformation-cardinality_29.html
However (depending on your usage and business case) you may can work around this issue with _fix_control=’14605040:OFF’. Afterwards cardinality feedback also kicks in for temp tables.
Regards from Bavaria
Stefan
Hi Stefan,
With _fix_control=’14605040:OFF’ the statistics feedback for the temporary table kicks in. As a result, the correct cardinality estimate for the second execution is achieved:
select * from (with q as ( select /*+ materialize */ * from t where
value=’UNPOPULAR VALUE’ and id>= 1) select /*+ GATHER_PLAN_STATISTICS
*/ * from q )
—————————————————————————————————————————————
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes |
—————————————————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 20 | 1 | 1 |
| 1 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 20 | 1 | 1 |
| 2 | TEMP TABLE TRANSFORMATION | | 1 | | 10 |00:00:00.01 | 20 | 1 | 1 |
| 3 | LOAD AS SELECT | | 1 | | 0 |00:00:00.01 | 8 | 0 | 1 |
|* 4 | TABLE ACCESS FULL | T | 1 | 10 | 10 |00:00:00.01 | 4 | 0 | 0 |
| 5 | VIEW | | 1 | 10 | 10 |00:00:00.01 | 7 | 1 | 0 |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D718C_B0C71D84 | 1 | 10 | 10 |00:00:00.01 | 7 | 1 | 0 |
—————————————————————————————————————————————
Predicate Information (identified by operation id):
—————————————————
4 – filter((“VALUE”=’UNPOPULAR VALUE’ AND “ID”>=1))
Note
—–
– dynamic statistics used: dynamic sampling (level=2)
– statistics feedback used for this statement
– 1 Sql Plan Directive used for this statement
Furthermore, the 10053 trace shows the adjustment because of the dynamic sampling:
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for SYS_TEMP_0FD9D6609_B0F17C53[T1]
SPD: Return code in qosdDSDirSetup: NOQBCTX, estType = TABLE
Table: SYS_TEMP_0FD9D6609_B0F17C53 Alias: T1
Card: Original: 50.000000 SPD: qosdCreateFindingSingTab retCode = INVOBJ, fid = 0
SPD: qosdCreateDirCmp retCode = INVOBJ, fid = 0
>> Single Tab Card adjusted from 50.000000 to 10.000000 due to opt_estimate hint
Rounded: 10 Computed: 10.000000 Non Adjusted: 50.000000
Sadly, the directive is not persisted.
Though, even without _fix_control=’14605040:OFF’ the main table gets sampled. Oddly, the results are not used to adjust the cardinality of the temporary table. I opened a SR for that.
Regards from Zurich,
Nenad
Hi Nenad,
> Sadly, the directive is not persisted.
Yes and this has two valid reasons as far as i can think of. First SPDs should trigger permanent statistics (New -> Missing stats – Has stats -> Permanent) later on (DBMS_STATS) – for example a switch from dynamic sampling to extended stats. However this is not possible with temporary tables, but you got session specific statistics for GTT with 12c for such issues. In addition SPDs are not designed to sample dynamically forever. Secondly this could blow up the SPD repository based on all possible query transformations.
> Though, even without _fix_control=’14605040:OFF’ the main table gets sampled. Oddly, the results are not used to adjust the cardinality of the temporary table.
Yes, but this is the “limitation” / weakness of temp table transformation.
As far as i understand you expect to get a fix with SPDs for the lack of information in temp table transformation, but SPD was not designed for this.
Regards
Stefan
Hi Stefan
> Sadly, the directive is not persisted.
>>Yes and this has two valid reasons as far as i can think of. First SPDs should trigger permanent statistics (New -> Missing stats – Has stats -> Permanent) later on (DBMS_STATS) – for example a switch from dynamic sampling to extended stats. However this is not possible with temporary tables, but you got session specific statistics for GTT with 12c for such issues. In addition SPDs are not designed to sample dynamically forever. Secondly this could blow up the SPD repository based on all possible query transformations.
>>> These are the valid reasons indeed.
> Though, even without _fix_control=’14605040:OFF’ the main table gets sampled. Oddly, the results are not used to adjust the cardinality of the temporary table.
>>Yes, but this is the “limitation” / weakness of temp table transformation.
As far as i understand you expect to get a fix with SPDs for the lack of information in temp table transformation, but SPD was not designed for this.
>>>Please correct me if I’m wrong, but as the sampling of the underlying table is done because of the existing SPD, I can’t think of any reason why these data shouldn’t be used to adjust the cardinality of the temporary table.
Regards,
Nenad
Hi Nenad,
> Please correct me if I’m wrong, but as the sampling of the underlying table is done because of the existing SPD, I can’t think of any reason why these data shouldn’t be used to adjust the cardinality of the temporary table.
Yes, but you have different final query blocks (= OUTLINE_LEAF in Outline Data) that are optimized independently – this is how the CBO works with transformations. The cardinality adjustment only happens in query block SEL$1 (as the base object is table T), but the base object is the temporary table SYS_TEMP_0FD9D661F_1C8D15 (alias T1) in a different query block SEL$D67CB2D2. I have simplified your test case a bit to demonstrate what i mean. In theory the CBO needs to implement some cross-query block optimization, if you want to get what you expect. Maybe the CBO just forgets to consider the dynamic sampling results (different array?) as the statistics should be based on the LOAD AS SELECT – would be interesting to know what Oracle replies in your SR.
Testcase:
SQL> with q as (
select /*+ materialize */ *
from t
where value=’UNPOPULAR VALUE’ and id>= 1
)
select /*+ GATHER_PLAN_STATISTICS */ * from q;
Plan hash value: 2328698527
——————————————————————————————————————————————————————–
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | Writes |
——————————————————————————————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 10 |00:00:03.02 | 23 | 1 | 1 |
| 1 | TEMP TABLE TRANSFORMATION | | 1 | | | | | 10 |00:00:03.02 | 23 | 1 | 1 |
| 2 | LOAD AS SELECT | | 1 | | | | | 0 |00:00:03.02 | 11 | 0 | 1 |
|* 3 | TABLE ACCESS FULL | T | 1 | 10 | 190 | 4 (0)| 00:00:01 | 10 |00:00:00.01 | 7 | 0 | 0 |
| 4 | VIEW | | 1 | 50 | 1500 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 7 | 1 | 0 |
| 5 | TABLE ACCESS FULL | SYS_TEMP_0FD9D661F_1C8D15 | 1 | 50 | 950 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 7 | 1 | 0 |
——————————————————————————————————————————————————————–
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$2
2 – SEL$1
3 – SEL$1 / T@SEL$1
4 – SEL$D67CB2D2 / Q@SEL$2
5 – SEL$D67CB2D2 / T1@SEL$D67CB2D2
Outline Data
————-
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘12.1.0.2’)
DB_VERSION(‘12.1.0.2′)
ALL_ROWS
OUTLINE_LEAF(@”SEL$1″)
OUTLINE_LEAF(@”SEL$D67CB2D2″)
OUTLINE_LEAF(@”SEL$2″)
OUTLINE(@”SEL$1″)
NO_ACCESS(@”SEL$2″ “Q”@”SEL$2″)
FULL(@”SEL$D67CB2D2” “T1″@”SEL$D67CB2D2″)
FULL(@”SEL$1” “T”@”SEL$1”)
END_OUTLINE_DATA
*/
*** CBO Trace ***
…
kkoqbc: optimizing query block SEL$1 (#0)
…
****************
QUERY BLOCK TEXT
****************
select /*+ materialize */ *
from t
where value=’UNPOPULAR VALUE’ and id>= 1
…
Query Block SEL$1 (#0)
Applicable DS directives:
dirid = 12242313028031127501, state = 1, flags = 1, loc = 1 {C(91655)[1, 2]}
…
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T[T]
SPD: Directive valid: dirid = 12242313028031127501, state = 1, flags = 1, loc = 1 {C(91655)[1, 2]}
SPD: Return code in qosdDSDirSetup: EXISTS, estType = TABLE
Table: T Alias: T
Card: Original: 100.000000 >> Single Tab Card adjusted from 50.000000 to 10.000000 due to adaptive dynamic sampling
…
kkoqbc: finish optimizing query block SEL$1 (#0)
kkoqbc: optimizing query block SEL$D67CB2D2 (#0)
…
****************
QUERY BLOCK TEXT
****************
with q as (
select /*+ materialize */ *
from t
where value=’UNPOPULAR VALUE’ an
…
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for SYS_TEMP_0FD9D6627_1C8D15[T1]
SPD: Return code in qosdDSDirSetup: NOQBCTX, estType = TABLE
Table: SYS_TEMP_0FD9D6627_1C8D15 Alias: T1
Card: Original: 50.000000 Rounded: 50 Computed: 50.000000 Non Adjusted: 50.000000
…
kkoqbc: finish optimizing query block SEL$D67CB2D2 (#0)
…
Regards
Stefan
P.S.: I hope the comment section does not destroy the format completely.
Hi Stefan,
Thank you for your analysis, which provided the additional insights to the problem.
Yes indeed, the CBO would need some cross-query block optimization to leverage the dynamic sampling results obtained in other final query blocks.
The support analyst reproduced the problem. I’ll update the post after I get his feedback.
Regards,
Nenad