Friday, November 3, 2017

Query to search for details about a specific SQL ID in the cursor cache


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