I have plenty of examples that Oracle 12cR1 selects poor execution plans over good ones.
This is a recent example from one of my databases. Notice that there are two different plans for the same SQL ID, one that seems to take long per exection, and a good plan that executes each statement very quickly:
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 =('fczwz45f4d46q')
ORDER BY LAST_ACTIVE_TIME DESC;
Result:
| Last active | SQL_ID | PLAN_HASH_VALUE | BASELINE | SQL_PROFILE | Child Num | ELAPSED_TIME | EXECUTIONS | duration (sec) | sec per exe |
|---|---|---|---|---|---|---|---|---|---|
| 16.08.2017 10:57 | fczwz45f4d46q | 2259800413 | SYS_SQLPROF_0156739adc130001 | 0 | 350642099493 | 311 | 350642 | 1127,5 | |
| 16.08.2017 01:39 | fczwz45f4d46q | 927328071 | SYS_SQLPROF_0156739adc130001 | 9 | 2675614671 | 26048 | 2676 | 0,1 |
In this case, my goal is to create a fixed baseline for the execution plan with hash value 927328071.
No comments:
Post a Comment