Friday, December 4, 2020

How to mark an existing SQL Plan baseline as fixed

Thanks a lot to Tim Hall for providing this simple example:
SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SQL_7ff180a4583f257d',
    plan_name       => 'SQL_PLAN_7zwc0njc3y9bx2c993bf4',
    attribute_name  => 'fixed',
    attribute_value => 'YES');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/
The details for the plan baselines can be found like this:
SELECT sql_handle,plan_name,origin,enabled,accepted,fixed
FROM dba_sql_plan_baselines
WHERE plan_name ='SQL_PLAN_7zwc0njc3y9bx2c993bf4';
Result:
SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED FIXED
SQL_7ff180a4583f257d SQL_PLAN_7zwc0njc3y9bx2c993bf4 AUTO-CAPTURE YES YES YES

After fixing the plan, all the plan alternatives for the SQL ID can be seen below:
SELECT TO_CHAR(LAST_ACTIVE_TIME, 'dd.mm.yyyy hh24:mi') "last active",
       SQL_ID,
       PLAN_HASH_VALUE,
       SQL_PLAN_BASELINE "BASELINE", 
       SQL_PROFILE,
       IS_RESOLVED_ADAPTIVE_PLAN,
       CHILD_NUMBER AS "Child Num",
       ELAPSED_TIME,
       EXECUTIONS,
       ROUND(ELAPSED_TIME/1000000) "duration (sec)"
       ,CASE WHEN EXECUTIONS > 0 THEN
        ROUND( (ELAPSED_TIME/EXECUTIONS)/1000000, 1)
        ELSE
            NULL
       END "sec per exe"
FROM V$SQL 
WHERE SQL_ID in('2mympbsn3r4rk')
ORDER BY sql_id,LAST_ACTIVE_TIME DESC;
Result:
last active SQL_ID PLAN_HASH_VALUE BASELINE SQL_PROFILE IS_RESOLVED_ADAPTIVE_PLAN Child Num ELAPSED_TIME EXECUTIONS duration (sec) sec per exe
04.12.2020 09:49 2mympbsn3r4rk
480132689
SQL_PLAN_7zwc0njc3y9bx3bf43977    
1
612838711
116
613
5,3
04.12.2020 09:49 2mympbsn3r4rk
3102497174
SQL_PLAN_7zwc0njc3y9bx2c993bf4   Y
2
203961
191
0
0
04.12.2020 08:49 2mympbsn3r4rk
480132689
SQL_PLAN_7zwc0njc3y9bx3bf43977    
0
2247452482
30
2247
74,9

No comments:

Post a Comment