Quick overview of loading plans into a baseline

Quick overview of DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE and DBMS_SPM.LOAD_PLANS_FROM_SQLSET:

load_plans_from_cursor_cache
(attribute_name   IN VARCHAR2,
 attribute_value  IN VARCHAR2,
 fixed            IN VARCHAR2 := 'NO',
 enabled          IN VARCHAR2 := 'YES')

Loads plans into baselines from cursor cache, for example get all plans with a certain MODULE or a certain parsing schema. Or just everything. Load it all up (NOT).

 
load_plans_from_cursor_cache
(sql_id           IN VARCHAR2,
 plan_hash_value  IN NUMBER := NULL,
 fixed            IN VARCHAR2 := 'NO',
 enabled          IN VARCHAR2 := 'YES')

Loads a specific sql_id, and a specific plan if specified by hash, into a baseline (must be an existing sql id and plan_hash).

                                       
load_plans_from_cursor_cache
(sql_id           IN VARCHAR2,
 plan_hash_value  IN NUMBER := NULL,
 sql_text         IN CLOB,
 fixed            IN VARCHAR2 := 'NO',
 enabled          IN VARCHAR2 := 'YES')

Allows you to use a baseline to apply a specific plan in the cache (must be an existing sql id and plan_hash)
to another sql statement (sql_text). Remember baselines are by signature so all we need is the sql text which will be passed through a filter to make it space and case desensitised. But using sql_text is a bit unwieldy, particularly for large statements so see method below.

                                       
load_plans_from_cursor_cache
(sql_id           IN VARCHAR2,
 plan_hash_value  IN NUMBER := NULL,
 sql_handle       IN VARCHAR2,
 fixed            IN VARCHAR2 := 'NO',
 enabled          IN VARCHAR2 := 'YES')

Similar to above, allows to you take an existing plan and apply it to an existing sql_handle in dba_sql_plan_baselines.

Using sql_text in previous method is not that convenient if you’re got big statements, etc. So you could load up the existing plans into a baseline, disable the existing baselined plans, then load up the desired plan from source statement to the target statement.

What is sql_handle?
Thanks to Hemant for pointing me in the direction of Marcin Przepiorowski – “SQL_HANDLE contain hexadecimal representation of EXACT_MATCHING_SIGNATURE from V$SQL”. I’d read that article before but that that particular info didn’t sink in.

                                       
load_plans_from_sqlset
(sqlset_name        IN VARCHAR2,
 sqlset_owner       IN VARCHAR2 := NULL,
 basic_filter       IN VARCHAR2 := NULL,
 fixed              IN VARCHAR2 := 'NO',
 enabled            IN VARCHAR2 := 'YES',
 commit_rows        IN NUMBER := 1000)

Loads plans into baselines from an existing sql tuning set.
Here’s an example on loading up the top 10 statments by elapsed time between two awr snaps into
a sqlset then a baseline:

DECLARE
 baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
 l_sqlset_name VARCHAR2(200) := my_test_tuning_set;
 l_op   PLS_INTEGER;
BEGIN
 OPEN baseline_cursor FOR
      SELECT VALUE(p)
      FROM   TABLE(dbms_sqltune.select_workload_repository
             (begin_snap        => <begin_snap>,
              end_snap          => <end_snap>,
              basic_filter      => NULL,
              ranking_measure1  => 'elapsed_time',
              result_limit      => 10,
              attribute_list    => 'ALL'
              )) p; 
 DBMS_SQLTUNE.CREATE_SQLSET
 (sqlset_name     => l_sqlset_name);
 DBMS_SQLTUNE.LOAD_SQLSET
 (sqlset_name     => l_sqlset_name,
  populate_cursor => baseline_cursor);
l_op := 
 DBMS_SPM.load_plans_from_sqlset
 (sqlset_name        => l_sqlset_name);
END;
/

About these ads