SQL Plan Directives and extended statistics
Sometimes you will find in the notes of an Explainplan this: Note ----- - dynamic statistics used: dynamic sampling (level=2) - statistics feedback used for this statement - 1 Sql Plan Directive used for this statement This means oracle is doing a little magic - explained in detail in the link below. To check where Plan Directives occur: 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 , D.CREATED , D.ENABLED , D.LAST_MODIFIED , D.LAST_USED --, D.NOTES, D.CREATED, D.ENABLED, D.LAST_MODIFIED, D.LAST_USED from dba_sql_plan_directives d, dba_sql_plan_dir_objects o where d.directive_id = o.directive_id and o.owner = 'mySchema' order by o.owner , o.object_name , o.subobject_name , o.object_type , d.TYPE ; To show extended stats: select USE.TABLE_NAME , USE.EXT