Current outlines
SQL> select name,sql_text,migrated from user_outlines; NAME SQL_TEXT MIGRATED ------------ ------------------------------------------------------ ------------- WITH_INDEX select count(*) from big_table where p_id=:"SYS_B_0" NOT-MIGRATED JDBC_OUTLINE select count(*) from big_table where p_id=:1 NOT-MIGRATEDExecute DBMS_SPM.MIGRATE_STORED_OUTLINE function to migrate a particular outline
var migrate_output clob SQL> exec :migrate_output := dbms_spm.MIGRATE_STORED_OUTLINE(attribute_name => 'outline_name', attribute_value => 'JDBC_OUTLINE', fixed => 'NO'); PL/SQL procedure successfully completed.Once migrated the outline view shows plant status as migrated
SQL> select name,sql_text,migrated from user_outlines; NAME SQL_TEXT MIGRATED ------------ ------------------------------------------------------ ------------- WITH_INDEX select count(*) from big_table where p_id=:"SYS_B_0" NOT-MIGRATED JDBC_OUTLINE select count(*) from big_table where p_id=:1 MIGRATEDQuery plan base line view to verify outline is migrated into a SQL plan base line.
SQL> select sql_handle,sql_text,plan_name,origin,enabled,accepted from dba_sql_plan_baselines; SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC -------------------- --------------------------------------------- ------------- -------------- --- ---- SQL_fcd5bfb6b7986086 select count(*) from big_table where p_id=:1 JDBC_OUTLINE STORED-OUTLINE YES YESOrigin column indicates that plan baseline was created from a stored outline. Run the SQL query if plan base line is used
SQL> select * from table(dbms_xplan.display_cursor('4x4krrt8vq3js',0,'ALLSTATS OUTLINE')); SQL_ID 4x4krrt8vq3js, child number 0 ------------------------------------- select count(*) from big_table where p_id=:1 Plan hash value: 3425075646 --------------------------------------------------------- | Id | Operation | Name | E-Rows | --------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 | SORT AGGREGATE | | 1 | |* 2 | TABLE ACCESS FULL| big_table | 21352 | --------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') OPT_PARAM('optimizer_index_cost_adj' 25) OPT_PARAM('optimizer_index_caching' 90) FIRST_ROWS(1000) OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "big_table"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("p_id"=:1) Note ----- - SQL plan baseline JDBC_OUTLINE used for this statement - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level
Outline takes precedence over plan baseline (see 1524658.1 for more). Once migrated outline is not use even though the enabled column says "ENABLED" in the outline view. But if the outline was to be recreated again then instead of SQL plan baseline , the stored outline will be used. To recreate the outline that was migrated create a private outline and refresh from it.
SQL> create private outline myjdbc from jdbc_outline; Outline created. SQL> execute dbms_outln_edit.refresh_private_outline('MYJDBC'); PL/SQL procedure successfully completed. SQL> create or replace outline jdbc_outline from private MYJDBC; Outline created.After this the migrated status would be changed to not-migrated
SQL> select name,sql_text,migrated from user_outlines; NAME SQL_TEXT MIGRATED ------------ ------------------------------------------------------ ------------- WITH_INDEX select count(*) from big_table where p_id=:"SYS_B_0" NOT-MIGRATED JDBC_OUTLINE select count(*) from big_table where p_id=:1 NOT-MIGRATEDand outline will take precedence over the existing plan baseline.
Related Posts
Changing Execution Plan Using Stored Outline
Moving Stored Outlines From One Database To Another