Oracle 23c New Features for DBA'sFrom 12c onwards, Oracle optimizer maintains the directives that influence the optimizer to take appropriate decision while executing the statement or estimation.
As you read from previous posts, Optimizer maintains the execution statistics now,they can be used to create a directive for future executions. These directives can be used by optimizer and determine which optimization technique should be used, ex: statistics feedback or performance feedback or dynamic statistics etc.
Further, this directives are at expressions or object levels not unlike the statement level like profiles etc.So this directives can be used for any other executions also.
For example, a cardinality misestimate, statistics missing etc.
Directive can be viewed in the following view
dba_sql_plan_directives dba_sql_plan_dir_objects.
SQL Plan directives can be created automatically , no manual way is possible and they initial stored in shared pool and flushes every 15 minutes. A manual flush can also be possible using dbms_spd package.
DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE;
From the diagram as you see the automatic reoptimization uses directives and do appropriate estimation and therefore an optimal plan.
Take a look at directives in our database
SQL> l 1 select to_char(d.directive_id) dir_id, o.owner,o.object_name,o.subobject_name col_name,o.object_type, 2 d.type,d.state,d.reason 3 from dba_sql_plan_directives d,dba_sql_plan_dir_objects o 4 where o.directive_id=d.directive_id 5 and o.owner='OE' 6* order by 1,2,3,4,5 SQL> / DIR_ID OWNER OBJECT_NAME COL_NAME OBJECT TYPE STATE REASON -------------------- ----- ------------------------------ ------------------------- ------ ---------------- ------------- ------------------------------------ 11444517666625484927 OE PRODUCT_INFORMATION CATEGORY_ID COLUMN DYNAMIC_SAMPLING NEW SINGLE TABLE CARDINALITY MISESTIMATE OE PRODUCT_INFORMATION PRODUCT_NAME COLUMN DYNAMIC_SAMPLING NEW SINGLE TABLE CARDINALITY MISESTIMATE OE PRODUCT_INFORMATION TABLE DYNAMIC_SAMPLING NEW SINGLE TABLE CARDINALITY MISESTIMATE 14168459673260404402 OE ORDER_ITEMS TABLE DYNAMIC_SAMPLING NEW SINGLE TABLE CARDINALITY MISESTIMATE 14406986910879485910 OE ORDERS TABLE DYNAMIC_SAMPLING HAS_STATS JOIN CARDINALITY MISESTIMATE OE ORDER_ITEMS TABLE DYNAMIC_SAMPLING HAS_STATS JOIN CARDINALITY MISESTIMATE OE PRODUCT_INFORMATION TABLE DYNAMIC_SAMPLING HAS_STATS JOIN CARDINALITY MISESTIMATE 4398363510362722611 OE ORDER_ITEMS QUANTITY COLUMN DYNAMIC_SAMPLING HAS_STATS SINGLE TABLE CARDINALITY MISESTIMATE OE ORDER_ITEMS UNIT_PRICE COLUMN DYNAMIC_SAMPLING HAS_STATS SINGLE TABLE CARDINALITY MISESTIMATE OE ORDER_ITEMS TABLE DYNAMIC_SAMPLING HAS_STATS SINGLE TABLE CARDINALITY MISESTIMATE 4827112249096099760 OE PRODUCT_INFORMATION LIST_PRICE COLUMN DYNAMIC_SAMPLING HAS_STATS SINGLE TABLE CARDINALITY MISESTIMATE OE PRODUCT_INFORMATION MIN_PRICE COLUMN DYNAMIC_SAMPLING HAS_STATS SINGLE TABLE CARDINALITY MISESTIMATE OE PRODUCT_INFORMATION TABLE DYNAMIC_SAMPLING HAS_STATS SINGLE TABLE CARDINALITY MISESTIMATE 13 rows selected. SQL>
At the moment for OE schema,we have
Dynamic Sampling is the one directive whenever optimizer see the expression quantity,unit_price,category_id in a query it will use this directives On status column we have new directives and some objects that have statistics Status exaplanation NEW - Newly created directive MISSING_STATS - directive object does not have relevant statistics HAS_STATS - Objects have statistics CANDIDATE - Candidate directive, the server to evaluate effectiveness PERMANENT - A permanent directive determined by the server to be effective and useful Reason column says what lead to create this directive.we have single table and join cardinality is missing.
Lets flush the directives if any in the memory
SQL> exec DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE; SQL> 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 o.directive_id=d.directive_id and o.owner='OE' order by 1,2,3,4,5 DIR_ID OWNER OBJECT_NAME COL_NAME OBJECT TYPE STATE REASON -------------------- ----- ------------------------------ ------------------------- ------ ---------------- ------------- ------------------------------------ 11444517666625484927 OE PRODUCT_INFORMATION CATEGORY_ID COLUMN DYNAMIC_SAMPLING NEW SINGLE TABLE CARDINALITY MISESTIMATE OE PRODUCT_INFORMATION PRODUCT_NAME COLUMN DYNAMIC_SAMPLING NEW SINGLE TABLE CARDINALITY MISESTIMATE OE PRODUCT_INFORMATION TABLE DYNAMIC_SAMPLING NEW SINGLE TABLE CARDINALITY MISESTIMATE 14168459673260404402 OE ORDER_ITEMS TABLE DYNAMIC_SAMPLING NEW SINGLE TABLE CARDINALITY MISESTIMATE 14406986910879485910 OE ORDERS TABLE DYNAMIC_SAMPLING HAS_STATS JOIN CARDINALITY MISESTIMATE OE ORDER_ITEMS TABLE DYNAMIC_SAMPLING HAS_STATS JOIN CARDINALITY MISESTIMATE OE PRODUCT_INFORMATION TABLE DYNAMIC_SAMPLING HAS_STATS JOIN CARDINALITY MISESTIMATE 16873896872332424225 OE ORDER_ITEMS TABLE DYNAMIC_SAMPLING NEW JOIN CARDINALITY MISESTIMATE OE PRODUCT_INFORMATION TABLE DYNAMIC_SAMPLING NEW JOIN CARDINALITY MISESTIMATE 4398363510362722611 OE ORDER_ITEMS QUANTITY COLUMN DYNAMIC_SAMPLING HAS_STATS SINGLE TABLE CARDINALITY MISESTIMATE OE ORDER_ITEMS UNIT_PRICE COLUMN DYNAMIC_SAMPLING HAS_STATS SINGLE TABLE CARDINALITY MISESTIMATE OE ORDER_ITEMS TABLE DYNAMIC_SAMPLING HAS_STATS SINGLE TABLE CARDINALITY MISESTIMATE 4827112249096099760 OE PRODUCT_INFORMATION LIST_PRICE COLUMN DYNAMIC_SAMPLING HAS_STATS SINGLE TABLE CARDINALITY MISESTIMATE OE PRODUCT_INFORMATION MIN_PRICE COLUMN DYNAMIC_SAMPLING HAS_STATS SINGLE TABLE CARDINALITY MISESTIMATE OE PRODUCT_INFORMATION TABLE DYNAMIC_SAMPLING HAS_STATS SINGLE TABLE CARDINALITY MISESTIMATE 15 rows selected.
As you observe there are few more (2) directives pushed and stored to disk now.
How to find the directive has been used. a) Note section in execution plan Example:- Once the statement is marked reoptimizable Y
-Thanks
Geek DBA
Follow Me!!!