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