First create a test table
CREATE TABLE test (
id NUMBER,
description VARCHAR2(50)
);
Insert 10000 rows into it using the following code
BEGIN
FOR v_LoopCounter IN 1..10000 LOOP
INSERT INTO test (id)
VALUES (v_LoopCounter);
END LOOP;
END;
/
Gather stats on the test table
EXEC DBMS_STATS.gather_table_stats(USER, 'test', cascade=>TRUE);
Check the explain plan for the following query.. It should do a full table scan
SELECT description
FROM test
WHERE id = 13;
Find the sql_id for the above sql
SELECT *
FROM v$sql
WHERE sql_text LIKE '%test%'
AND sql_text NOT LIKE '%dba_sql_plan_baselines%'
AND sql_text NOT LIKE '%EXPLAIN%';
Since we are loading manually in this scenario it is called manual baseline.. There is a parameter which enables system wide automatic loading of baselines.. To enable it you have to set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to True. Default is False. Please dont enable on production systems without proper testing
Manual method
SET SERVEROUTPUT ON
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
sql_id => 'gat6z1bc6nc2d');
DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/
After the above baseline will be loaded..
You can check the loaded baselines using the following query
SELECT sql_handle, plan_name, enabled, accepted
FROM dba_sql_plan_baselines
You can see in the output.. It will be yes for both enabled and accepted.
This completes the manual loading of baselines.
To test if baseline is working.. Flush shared pool( not recommended on production again) this is for hard parsing..
ALTER SYSTEM FLUSH SHARED_POOL;
Now create index on the table
CREATE INDEX test_idx ON test(id);
Gather stats
EXEC DBMS_STATS.gather_table_stats(USER, 'test', cascade=>TRUE);
Check the explain plan for the following query.. It should do a full table scan even after creating index..
SELECT description
FROM test
WHERE id = 13;
Now run the following query..
SELECT sql_handle, plan_name, enabled, accepted
FROM dba_sql_plan_baselines
you will see a new baseline but it says not enabled.. This is the plan for new index we created but it wont be used as it is not tested.. Oracle uses new baseline only if performance is tested and better than previous baseline.
So to test that and evolve the new plan we grab the sql_handle from dba_sql_baselines and run the following..
SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SQL_7b76323ad90440b9') FROM dual;
Now if you run the above select query and check explain plan it should be using the plan with index.
CREATE TABLE test (
id NUMBER,
description VARCHAR2(50)
);
Insert 10000 rows into it using the following code
BEGIN
FOR v_LoopCounter IN 1..10000 LOOP
INSERT INTO test (id)
VALUES (v_LoopCounter);
END LOOP;
END;
/
Gather stats on the test table
EXEC DBMS_STATS.gather_table_stats(USER, 'test', cascade=>TRUE);
Check the explain plan for the following query.. It should do a full table scan
SELECT description
FROM test
WHERE id = 13;
Find the sql_id for the above sql
SELECT *
FROM v$sql
WHERE sql_text LIKE '%test%'
AND sql_text NOT LIKE '%dba_sql_plan_baselines%'
AND sql_text NOT LIKE '%EXPLAIN%';
Since we are loading manually in this scenario it is called manual baseline.. There is a parameter which enables system wide automatic loading of baselines.. To enable it you have to set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to True. Default is False. Please dont enable on production systems without proper testing
Manual method
SET SERVEROUTPUT ON
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
sql_id => 'gat6z1bc6nc2d');
DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/
After the above baseline will be loaded..
You can check the loaded baselines using the following query
SELECT sql_handle, plan_name, enabled, accepted
FROM dba_sql_plan_baselines
You can see in the output.. It will be yes for both enabled and accepted.
This completes the manual loading of baselines.
To test if baseline is working.. Flush shared pool( not recommended on production again) this is for hard parsing..
ALTER SYSTEM FLUSH SHARED_POOL;
Now create index on the table
CREATE INDEX test_idx ON test(id);
Gather stats
EXEC DBMS_STATS.gather_table_stats(USER, 'test', cascade=>TRUE);
Check the explain plan for the following query.. It should do a full table scan even after creating index..
SELECT description
FROM test
WHERE id = 13;
Now run the following query..
SELECT sql_handle, plan_name, enabled, accepted
FROM dba_sql_plan_baselines
you will see a new baseline but it says not enabled.. This is the plan for new index we created but it wont be used as it is not tested.. Oracle uses new baseline only if performance is tested and better than previous baseline.
So to test that and evolve the new plan we grab the sql_handle from dba_sql_baselines and run the following..
SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SQL_7b76323ad90440b9') FROM dual;
Now if you run the above select query and check explain plan it should be using the plan with index.
