creating sql baselines in oracle 11g

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.