Cardinality feedback was introduced in Oracle Database 11g Release 2. When the optimizer generates an execution plan the presence of missing statistics, stale statistics, complex predicates or complex operators may trigger the optimizer to monitor the cardinality of operations in the plan. Once the execution is complete, if there is a significant difference between the estimated and actual cardinalities, the actual cardinalities are stored in the SGA for later use and the statement is marked as reoptimizable. On next execution the statement is reoptimized using the stored cardinalities, allowing a better plan to be determined. Cardinality feedback is statement specific and is lost if the instance is restarted or the statement is aged out of the shared pool. In Oracle Database 12c, cardinality feedback has been renamed to statistics feedback. Statistics feedback information is stored in the SGA as hints in the view. Both the view and the hint are undocumented. The following code creates a pipelined table function to allow us to demonstrate statistics feedback. We know the optimizer always estimates the cardinality of pipelined table functions based on the database block size, so we can expect an incorrect estimate of the cardinality of a query against the pipelined table function. The following query returns 10 rows, but the optimizer estimates 8168 rows. The hint is used to allow us to display the expected and actual cardinalities. Checking the column of the view shows the optimizer has detected the incorrect cardinality estimates and has marked the statement to be reoptimized. If we run the statement again, we can see a more accurate cardinality estimate and a note telling us statistics feedback was used. Notice also the change in the value of the child number. In 11gR2 the note would read, "cardinality feedback used for this statement". The documentation suggests the detection of cardinality misestimates will also result in the production of SQL plan directives ( see last paragraph of point 1 ), which myself and others have misunderstood as meaning statistics feedback is persisted in the SQL plan directive. This does not appear to be the case and MOS Doc ID 1344937.1 confirms the statistics feedback information is lost when the statement is aged out of the shared pool or the instance is restarted. We can check for the presence of SQL plan directives as follows. As we can see, in this case no SQL plan directives were created. We will discuss this point a little more later.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117

CONN test/test@pdb1

-- Create the types to support the table function.
DROP TYPE t_tf_tab;
DROP TYPE t_tf_row;

CREATE TYPE t_tf_row AS OBJECT (
  id           NUMBER,
  description  VARCHAR2(50)
);
/

CREATE TYPE t_tf_tab IS TABLE OF t_tf_row;
/

-- Build the table function itself.
CREATE OR REPLACE FUNCTION get_tab_ptf (p_rows IN NUMBER) RETURN t_tf_tab PIPELINED AS
BEGIN
  FOR i IN 1 .. p_rows LOOP
    PIPE ROW (t_tf_row(i, 'Description for ' || i));
  END LOOP;

  RETURN;
END;
/

SELECT /*+ GATHER_PLAN_STATISTICS */
       *
FROM   TABLE(get_tab_ptf(10));

SET LINESIZE 200 PAGESIZE 100
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  0ktmsgvczysxy, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */        * FROM
TABLE(get_tab_ptf(10))

Plan hash value: 822655197

-------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name        | Starts | E-Rows | A-Rows |   A-Time   |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |             |      1 |        |     10 |00:00:00.01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| GET_TAB_PTF |      1 |   8168 |     10 |00:00:00.01 |
-------------------------------------------------------------------------------------------------

SQL>

COLUMN sql_text FORMAT A50
COLUMN is_reoptimizable FORMAT A16

SELECT sql_text, is_reoptimizable
FROM   v$sql
WHERE  sql_text LIKE '%get_tab_ptf%'
AND    sql_text NOT LIKE '%v$sql%';

SQL_TEXT                                           IS_REOPTIMIZABLE
-------------------------------------------------- ----------------
SELECT /*+ GATHER_PLAN_STATISTICS */        * FROM Y
   TABLE(get_tab_ptf(10))

SQL>

SELECT /*+ GATHER_PLAN_STATISTICS */
       *
FROM   TABLE(get_tab_ptf(10));

SET LINESIZE 200 PAGESIZE 100
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last'));


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  0ktmsgvczysxy, child number 1
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */        * FROM
TABLE(get_tab_ptf(10))

Plan hash value: 822655197

-------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name        | Starts | E-Rows | A-Rows |   A-Time   |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |             |      1 |        |     10 |00:00:00.01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| GET_TAB_PTF |      1 |     20 |     10 |00:00:00.01 |
-------------------------------------------------------------------------------------------------

Note
-----
   - statistics feedback used for this statement

SQL>

CONN sys@pdb1 AS SYSDBA

EXEC DBMS_SPD.flush_sql_plan_directive;

SET LINESIZE 200

COLUMN dir_id FORMAT A20
COLUMN owner FORMAT A10
COLUMN object_name FORMAT A10
COLUMN col_name FORMAT A10

SELECT TO_CHAR(d.directive_id) dir_id, o.owner, o.object_name, 
       o.subobject_name col_name, o.object_type, d.type, d.state, d.reason
FROM   dba_sql_plan_directives d, dba_sql_plan_dir_objects o
WHERE  d.directive_id=o.directive_id
AND    o.owner = 'TEST'
ORDER BY 1,2,3,4,5;

no rows selected

SQL>

Oracle 11g Release 2 introduced the initialization parameter to simplify parallel query. It has a default value of , but when set to it enables automatic degree of parallelism, statement queuing, and in-memory parallel execution. Oracle 12c Release 1 has added the setting, which is similar to , but includes performance feedback. In this case, the optimizer decides if a statement should run in parallel and what degree of parallelism (DOP) is appropriate. On completion, the actual performance of the statement is compared to the estimated performance from the initial optimization phase. If there is a significant difference between them, the actual performance statistics are stored as statistics feedback and the statement is marked as reoptimizable. The next time the statement is executed the statistics feedback is used to select a more appropriate DOP. From 11gR2 onward, the use of a hint in a statement will result in automatic DOP selection, regardless of the value.

This section is mostly speculation, based on my experience of using statistics feedback and speaking with others. I am not an optimizer developer, so I can not guarantee the following information is true. If I get any evidence to the contrary I will happily post it here. I previously made the statement that statistics feedback does not get persisted as SQL plan directives. Although that seems to be true, the situation is a little more subtle. Statistics feedback is an indication that the optimizer has made a bad choice. Typically these bad choices are because it is missing vital information when deciding on an execution plan. Statistics feedback can be used to feed back into the reoptimzation, but it doesn't actually solve the initial problem. The base statistics are still not representative! SQL plan directives are "extra notes" that can prevent the optimizer making the same mistakes in future. In some circumstances, automatic reoptimization may result in the production of a SQL plan directive, but this does not contain the statistics/performance feedback itself, but rather instructions to perform dynamic sampling to resolve the discrepancy in the short term, so statistics feedback may no longer be necessary. Since SQL Plan directives influence the way future statistics are gathered by the package, they also have the potential to fix the root cause of the problem by adding additional information (extended statistics) to the base statistics, making the SQL plan directive itself and the statistics feedback no longer necessary. The example in this article uses a pipelined table function to demonstrate statistics feedback. Dynamic sampling of a pipelined table function is a really bad idea, as it will query the pipelined table function once to get the cardinality estimate, then again to process the actual statement. As a result, creating a SQL plan directive that suggests dynamic sampling of a pipelined table function would be a really bad idea and it seems the optimizer team have avoided this situation, which is good. So what am I saying here? Well, a situation that requires the use of statistics feedback may also result in the creation of a SQL plan directive, but that doesn't mean the SQL plan directive contains a persisted version of the statistics feedback. If it were as simple as statistics feedback being persisted as sql plan directives, we would expect it to work for pipelined table functions too. My article on SQL Plan Directives shows a situation where cardinality misestimates actually does result in the creation of SQL plan directives. There seem to be some interesting interactions between statistics feedback and SQL plan directives when they are both created as a result of cardinality misestimates. - In a situation where both statistics feedback and a SQL plan directive have been created in the SGA, but the SQL plan directive *has not* yet been persisted to the SYSAUX tablespace, the statistics feedback is used during reoptimization and the SQL plan directive is ignored. - In a situation where both statistics feedback and a SQL plan directive have been created, and the SQL plan directive *has* been persisted to the SYSAUX tablespace, the SQL plan directive, and possibly the statistics feedback, is used during reoptimization. Since SQL plan directives are only persisted periodically, this means the reoptimization you get could be totally different depending on how long you wait between the first and second execution of the SQL statement, making the outcome rather unpredictable. Remember, this is just speculation! For more information see: Hope this helps. Regards Tim...