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