Stored OutLines v/s BaseLines
Stored outlines also make a plan stable, but they make it rigid. The optimizer identifies that there is an outline for a SQL statement and stops generating new plans. Baselines, on the other hand, never stop the optimizer from generating a new plan.
Stored outlines are deprecated in Oracle 11g. From Oracle 11g onward, you should use SQL Plan Management.
The SQL plan management feature enables you to store validated or well-known plans for SQL statements in the form of baselines, which can be very useful in diagnosing sudden performance degradations. Because the baselines (and the corresponding plans) are stored in a repository, you can also compare them and decide to make the most efficient use of them .
Reference
https://asktom.oracle.com/Misc/oramag/baselines-and-better-plans.html
https://oracle-base.com/articles/11g/sql-plan-management-11gr1
Stored Outlines
https://oracle-base.com/articles/misc/outlines
-- Grant the necessary privileges for outline creation.
CONN sys/password AS SYSDBA
GRANT CREATE ANY OUTLINE TO SCOTT;
GRANT EXECUTE_CATALOG_ROLE TO SCOTT;
-- Create an outline for a specific SQL statement.
CREATE OUTLINE EMP_Dept FOR CATEGORY scott_outlines
ON SELECT e.empno, e.ename, d.dname FROM emp e, dept d
WHERE e.deptno = d.deptno;
-- Check the outline as been created correctly.
SELECT name, category, sql_text, node, stage, join_pos, hint FROM user_outlines WHERE category = 'SCOTT_OUTLINES';
SELECT node, stage, join_pos, hint FROM user_outline_hints WHERE name = 'EMP_DEPT';
NODE STAGE JOIN_POS HINT
---------- ---------- ---------- --------------------------------------------------
1 1 0 NO_EXPAND(@"SEL$1" )
1 1 0 PQ_DISTRIBUTE(@"SEL$1" "E"@"SEL$1" NONE NONE)
1 1 0 USE_MERGE(@"SEL$1" "E"@"SEL$1")
1 1 0 LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
1 1 0 NO_STAR_TRANSFORMATION(@"SEL$1" )
1 1 0 NO_FACT(@"SEL$1" "E"@"SEL$1")
1 1 0 NO_FACT(@"SEL$1" "D"@"SEL$1")
1 1 2 FULL(@"SEL$1" "E"@"SEL$1")
1 1 1 INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
1 1 0 NO_REWRITE(@"SEL$1" )
1 1 0 NO_REWRITE(@"SEL$1" )SQL Plan Management using BaseLine Plan
First, we enable the baseline capture in the session:
alter session set optimizer_capture_sql_plan_baselines = true;
Now all the SQL statements executed in this session will be captured, along with their optimization plans, in the SQL management base.
Every time the plan changes for a SQL statement, it is stored in the plan history.
-- First execution. Default Environment
select * /* ARUP */ from sales where quantity_sold > 1 order by cust_id;
-- Change the optimizer mode
alter session set optimizer_mode = first_rows;
-- Second execution. Opt Mode changed
select * /* ARUP */ from sales
where quantity_sold > 1 order by cust_id;
-- Gather stats now
dbms_stats.gather_table_stats (
ownname => 'SH',
tabname => 'SALES',
cascade => TRUE,
no_invalidate => FALSE,
method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',
granularity => 'GLOBAL AND PARTITION',
estimate_percent => 10,
degree => 4
);
-- Third execution. After stats
select * /* ARUP */ from sales where quantity_sold > 1 order by cust_id;
If the plan changes in each of the executions of the SQL in Listing 1, the different plans will be captured in the plan history for that SQL statement.
The easiest way to view the plan history is through Oracle Enterprise Manager. From the Database main page, choose the Server tab and then click SQL Plan Control .
From that page, choose the SQL Plan Baseline tab. On that page, search for the SQL statements containing the name ARUP .
The important columns on the screen are as follows:
- Enabled indicates whether the plan is active.
- Accepted indicates whether the plan should be considered by the optimizer. If more than one plan is accepted, the optimizer will select the best plan among them.
- Fixed indicates whether the plan is to be used permanently for that SQL statement. If more than one plan is fixed, the optimizer will select the best plan among them.
- Auto Purge indicates whether the plan, if unused, will automatically be deleted from the plan history after a specified amount of time.
Using Baselines
With the SQL plan baselines captured, we now enable the optimizer to use them:
alter session set optimizer_use_sql_plan_baselines = true;
With baseline use enabled, when the optimizer reparses a SQL statement, it examines the plans stored in the baseline for that SQL statement and chooses the best among them. This is where the most important benefit of baselines comes in. The optimizer also still reparses the SQL statements—the presence of a baseline does not prevent that—and if the newly generated plan is not found in the plan history of the SQL, it will be added, but not as “accepted.”
The LOAD_PLANS_FROM_CURSOR_CACHE functions allow SQL statements to be loaded from the cursor cache
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache( sql_id => '1fkh93md0802n');
END;
The DBA_SQL_PLAN_BASELINES view provides information about the SQL plan baselines.
SELECT sql_handle, plan_name, enabled, accepted
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%spm_test_tab%'
AND sql_text NOT LIKE '%dba_sql_plan_baselines%';
SQL_HANDLE PLAN_NAME ENA ACC
------------------------------ ------------------------------ --- ---
SYS_SQL_7b76323ad90440b9 SYS_SQL_PLAN_d90440b9b65c37c8 YES YES
1 row selected.
Evolution
You can compare the performance of the plans by using the evolve function (in Oracle Enterprise Manager -> SQL Plan Control page -> SQL Plan Baseline tab
or using the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function from the command line).
SELECT DBMS_SPM.evolve_sql_plan_baseline( sql_handle => 'SYS_SQL_PLAN_27a47aa15003759b')
FROM dual;
Code Listing 3: Baseline evolution report
-----------------------------------------------------
Evolve SQL Plan Baseline Report
-----------------------------------------------------
Inputs:
----
PLAN_LIST = SYS_SQL_PLAN_27a47aa15003759b
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SYS_SQL_PLAN_27a47aa15003759b
----------------------
Plan was verified: Time used 41.06 seconds.
Failed performance criterion: Compound improvement ratio < .36
Baseline Plan Test Plan Improv. Ratio
-------------- --------- -------------
Execution Status: COMPLETE COMPLETE
Rows Processed: 0 0
Elapsed Time(ms): 5036 1033 4.88
CPU Time(ms): 254 700 .36
Buffer Gets: 1728 43945 .04
Disk Reads: 254 22 11.55
Direct Writes: 0 0
Fetches: 49 22 2.23
Executions: 1 1
--------------------------------------------------------------------
Report Summary
--------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 0.
Failed performance criterion:
Compound improvement ratio < .36.
The line clearly shows that the newly considered plan performed worse than the original plan so it was rejected as a replacement for the optimizer‘s best plan choice. Had the comparison ratio yielded an improvement factor greater than 1, SQL plan management would have accepted that plan as a candidate for the optimizer to consider.
Another example from https://oracle-base.com/articles/11g/sql-plan-management-11gr1
Plan: SYS_SQL_PLAN_d90440b9ed3324c0
-----------------------------------
Plan was verified: Time used .05 seconds.
Passed performance criterion: Compound improvement ratio >= 15.4.
Plan was changed to an accepted plan.
Baseline Plan Test Plan Improv. Ratio
------------- --------- -------------
Execution Status: COMPLETE COMPLETE
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_7B76323AD90440B9')
--------------------------------------------------------------------------------
Rows Processed: 1 1
Elapsed Time(ms): 2 0
CPU Time(ms): 2 0
Buffer Gets: 46 3 15.33
Disk Reads: 0 0
Direct Writes: 0 0
Fetches: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_7B76323AD90440B9')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 1.
Here, in this example we can see the improvement of new plan by 15.33 . So the plan will also be
accepted by dba_sql_Plan_baseline .What if you feel that the decision made by the evolve function is not accurate and you would rather force the optimizer to use a specific plan? You can do that by making the plan fixed in the baseline. You can make a plan fixed by executing the alter_sql_plan_baseline function in the dbms_spm package, as shown in Listing 4.
Code Listing 4: Fixing a plan baseline
declare
l_plans pls_integer;
begin
l_plans := dbms_spm.alter_sql_plan_baseline (
sql_handle => 'SYS_SQL_f6b17b4c27a47aa1',
plan_name => 'SYS_SQL_PLAN_27a47aa15003759b',
attribute_name => 'fixed',
attribute_value => 'YES'
);
end;
Another method for baseLine evolution is using @$dbhome$/rdbms/admin/sqltrpt.sql <sql-id>, passing sql_id. You will get the below reports and finding--
https://www.youtube.com/watch?v=OdkFPXj9RMU&t=212s
Then use the dbms_sqltune.create_sql_plan_baseline(task=? , owner=>sys, plan_hash_value=>?);
