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