Hi. It it due to sql plan directives. If we get a bad estimate (versus the actuals) we might "make a note" that if we see that query again, we should do some dynamic sampling via a "sql plan directive", eg
SQL> create table t
2 as
3 select *
4 from dba_objects a
5 union all
6 select *
7 from dba_objects a
8 where owner = 'SYS'
9 ;
Table created.
SQL>
SQL> create index t_ix on t ( owner );
Index created.
SQL>
SQL> exec dbms_stats.gather_table_stats('','T',method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
SQL>
SQL> commit;
Commit complete.
--
-- so our database currently has 279 directives already there
--
SQL>
SQL> select count(*) from DBA_SQL_PLAN_DIRECTIVES;
COUNT(*)
----------
279
1 row selected.
--
-- now I run my query
--
SQL> select /*+ gather_plan_statistics */ count(created) from t where owner = 'SYS' and object_type in ('SYNONYM','JAVA CLASS');
COUNT(CREATED)
--------------
55480
1 row selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------
SQL_ID 2up5bxj23rp92, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(created) from t where owner
= 'SYS' and object_type in ('SYNONYM','JAVA CLASS')
Plan hash value: 1874539213
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.03 | 2174 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.03 | 2174 |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 152 | 55480 |00:00:00.03 | 2174 |
|* 3 | INDEX RANGE SCAN | T_IX | 1 | 3568 | 84814 |00:00:00.02 | 179 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("OBJECT_TYPE"='JAVA CLASS' OR "OBJECT_TYPE"='SYNONYM'))
3 - access("OWNER"='SYS')
22 rows selected.
--
-- You can see above, the actuals differed hugely from the estimate. We notice this
-- and mark the statement as perhaps needing more optimization work.
--
-- You can see this on V$SQL
--
SQL>
SQL> select sql_text, is_reoptimizable
2 from v$sql
3 where sql_text like 'select /*+ gather_plan_statistics */ count(created)%';
SQL_TEXT I
---------------------------------------------------------------- -
select /*+ gather_plan_statistics */ count(created) from t where Y
owner = 'SYS' and object_type in ('SYNONYM','JAVA CLASS')
1 row selected.
--
-- Normally, after 15mins, we'd flush what we need in terms of getting
-- better optimization, but we can flush it manually
--
SQL>
SQL> exec dbms_spd.flush_sql_plan_directive;
PL/SQL procedure successfully completed.
--
-- and we get some new directives.
--
SQL>
SQL> select count(*) from DBA_SQL_PLAN_DIRECTIVES;
COUNT(*)
----------
281
1 row selected.
--
-- Now when I run my query again, notice the new notes at the bottom
--
SQL>
SQL> select /*+ gather_plan_statistics */ count(created) from t where owner = 'SYS' and object_type in ('SYNONYM','JAVA CLASS');
COUNT(CREATED)
--------------
55480
1 row selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------
SQL_ID 2up5bxj23rp92, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ count(created) from t where owner
= 'SYS' and object_type in ('SYNONYM','JAVA CLASS')
Plan hash value: 2966233522
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2270 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2270 |
|* 2 | TABLE ACCESS FULL| T | 1 | 55480 | 55480 |00:00:00.01 | 2270 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("OWNER"='SYS' AND INTERNAL_FUNCTION("OBJECT_TYPE")))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- statistics feedback used for this statement
- 1 Sql Plan Directive used for this statement
26 rows selected.
SQL>
SQL>
SQL>
The sql plan directive said - "we dont have good enough stats to satisfy this query, so next time, do some dynamic sampling"