This is the third post of SQL Plan Management. In the previous post, Understanding SQL Plan Management– Part 2, I described the three main components of SPM, this time I want to show you how to manually capture plans from the cursor cache. In other words, manually capture plans that reside in memory.
In order to manually capture plans, I’m considering that automatic capture is disabled or OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is set to FALSE. Oracle provides a function named DBMS_SPM.load_plans_from_cursor_cache to manual capture plans from cursor cache.
DBMS_SPM.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’)
RETURN PLS_INTEGER;
The common use for this function, is to specify the sql_id of the statement we want to capture. We also can specify the plan_hash_value or the sql_text. Two more attributes can be set within this function, fixed is ‘NO’ by default and enabledis ‘YES’ by default. If you want to load plans, but you don’t want the CBO to make use if them; set the attribute enabled to ‘NO’.
In the below example I’m capturing all the SQL plans for sql_id4c372tsuhtunm and printing the number of plans loaded:
SQL> SET SERVEROUTPUT ON
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
sql_id => ‘4c372tsuhtunm’);
DBMS_OUTPUT.put_line(‘Plans Loaded: ‘ || l_plans_loaded);
END;
/SQL> 2 3 4 5 6 7 8 9
Plans Loaded: 8
PL/SQL procedure successfully completed.
To verify the SQL baselines created for this sql_id, I just need to query DBA_SQL_PLAN_BASELINES view:
SQL> select SIGNATURE,SQL_HANDLE,PLAN_NAME,ORIGIN,ENABLED,ACCEPTED,FIXED,REPRODUCED from dba_sql_plan_baselines;
SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED FIXED REPRODUCED
——————— ——————— —————————— ————– ——- ——– —– ———-
5535156277878016190 SQL_4cd0d34ee73148be SQL_PLAN_4tn6m9vmm2k5ye8c8b02d MANUAL-LOAD YES YES NO YES
5535156277878016190 SQL_4cd0d34ee73148be SQL_PLAN_4tn6m9vmm2k5yc568a49d MANUAL-LOAD YES YES NO YES
5535156277878016190 SQL_4cd0d34ee73148be SQL_PLAN_4tn6m9vmm2k5ybff74238 MANUAL-LOAD YES YES NO YES
5535156277878016190 SQL_4cd0d34ee73148be SQL_PLAN_4tn6m9vmm2k5y0310173f MANUAL-LOAD YES YES NO YES
5535156277878016190 SQL_4cd0d34ee73148be SQL_PLAN_4tn6m9vmm2k5y8e42f3cc MANUAL-LOAD YES YES NO YES
5535156277878016190 SQL_4cd0d34ee73148be SQL_PLAN_4tn6m9vmm2k5y25375ef9 MANUAL-LOAD YES YES NO YES
5535156277878016190 SQL_4cd0d34ee73148be SQL_PLAN_4tn6m9vmm2k5y9de69d5d MANUAL-LOAD YES YES NO YES
5535156277878016190 SQL_4cd0d34ee73148be SQL_PLAN_4tn6m9vmm2k5yb76f0084 MANUAL-LOAD YES YES NO YES
8 rows selected.
Look at the data for the ORIGIN column, it shows from where the plans were loaded, either from manual load or auto capture.
Another method is to manual load plans from SQL Tuning Sets (STS). The STS may contain plans that are not present in memory, like plans in the AWR repository. This method is very useful when you want to create baselines of plans that were created by the CBO few days ago and are not in the cursor cache at this time. Keep in mind that STS requires a special license in order to be used. Oracle provides the function DBMS_SPM.load_plans_from_sqlset to accomplish this task.
DBMS_SPM.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)
RETURN PLS_INTEGER;
You just need to specify the sqlset_name and the sqlset_owner in order to load the plans contained in the STS. It also has the default parameters fixed, set to ‘NO’ and enabled,set to ‘YES’. Two interesting attributes can be set within this function. The basic_filter attribute allows you to select only the plans that meet this filter criteria, in other words, is like a ‘where’ in a query.
basic_filter => ‘sql_text like ”select /*LOAD_STS*/%”’ orbasic_filter => ‘sql_id=”4c372tsuhtunm“‘
The commit_rows attribute, allows you to commit after the value specified, which is 1000 by default. Let’s assume that inside this STS we have 30000 plans and we want to load all of them, SPM will commit every 1000 plans to help to reduce the undo log.
Keep in mind that once you manually load plans for a specific SQL (signature), the CBO will continue capturing plans for it, even if the automatic capture is disabled. Those automatic captured plans will not be used by the CBO until you verify and evolve them.
In the next post, I’m going to show you how to evolve plans in 11g and 12c versions.
Thanks,
Alfredo