How can I view execution plans that Statspack captured for an Amazon RDS for Oracle DB instance?

Resolution

1. Take a Statspack snapshot that has a snap level greater than or equal to 6 to capture SQL execution plans. For example, i_snap_level => 6 For more information, see How do I check the performance statistics on an Amazon RDS DB instance that is running Oracle?

2. Use the Statspack report to review the Begin Snap ID, End Snap ID, and the old hash value for the query. In the following example, the Begin Snap ID is 22 and the End Snap ID is 23:

   STATSPACK report forDatabase    DB Id    Instance     Inst Num  Startup Time   Release     RAC
   ~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
             1234567890 silent              1 03-Jan-20 00:45 12.2.0.1.0  NO
   Host Name             Platform                CPUs Cores Sockets   Memory (G)
   ~~~~ ---------------- ---------------------- ----- ----- ------- ------------
        ip-172-31-22-176 Linux x86 64-bit           2     2       1          3.7
   Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment
   ~~~~~~~~    ---------- ------------------ -------- --------- ------------------
   Begin Snap:         22 03-Jan-20 01:30:36       41        .8
     End Snap:         23 03-Jan-20 01:39:12       40        .8
      Elapsed:       8.60 (mins) Av Act Sess:       0.0
      DB time:       0.11 (mins)      DB CPU:       0.11 (mins)
   ...

3. Find the query to view the execution plan with the old hash value. The Statspack report includes different SQL order by sections. For example, the SQL ordered by CPU DB/Inst section lists CPU intensive queries. The following example uses the old hash value 73250552. This value is the hash value of the most CPU intensive query around that time:

   ...SQL ordered by CPU  DB/Inst: SILENT/silent  Snaps: 22-23
   -> Total DB CPU (s):               7
   -> Captured SQL accounts for   80.3% of Total DB CPU
   -> SQL reported below exceeded  1.0% of Total DB CPU
       CPU                  CPU per             Elapsd                     Old
     Time (s)   Executions  Exec (s)  %Total   Time (s)    Buffer Gets  Hash Value
   ---------- ------------ ---------- ------ ---------- --------------- ----------
         4.03            3       1.34   60.8       4.08         528,477   73250552
   Module: SQL*Plus
   SELECT COUNT(*) FROM HOGE_TBL H1 INNER JOIN HOGE_TBL H2 USING(OB
   JECT_NAME)
         0.75            1       0.75   11.3       0.77          18,994 2912400228
   Module: sqlplus@ip-172-31-22-176 (TNS V1-V3)
   BEGIN statspack.snap; END;
         0.14          107       0.00    2.1       0.15             732 3879834072
   select TIME_WAITED_MICRO from V$SYSTEM_EVENT  where event = 'Sha
   red IO Pool Memory'
   ...

4. To connect to the DB instance, use an Oracle client.

5. To retrieve the execution plan, invoke a query similar to the following:

   SELECT lpad(' ', 1 * ( depth - 1 ))     || operation AS operation,
          object_name,
          cardinality,
          bytes,
          cost
   FROM   stats$sql_plan
   WHERE  plan_hash_value IN(SELECT plan_hash_value
                             FROM   stats$sql_plan_usage
                             WHERE  old_hash_value = OLD_HASH_VALUE
                                    AND snap_id BETWEEN BEGIN_SNAP_ID AND END_SNAP_ID
                                    AND plan_hash_value > 0)
   ORDER  BY plan_hash_value, id;

   Note: Replace OLD_HASH_VALUE, BEGIN_SNAP_ID, and END_SNAP_ID with your own values.

   The following example is the execution plan for the query that SQL*Plus retrieved:

   SQL> col operation format a20col object_name format a20
   SQL>SELECT lpad(' ', 1 * ( depth - 1 ))
        || operation AS operation,
          object_name,
          cardinality,
          bytes,
          cost
   FROM   stats$sql_plan
   WHERE  plan_hash_value IN(SELECT plan_hash_value
                             FROM   stats$sql_plan_usage
                             WHERE  old_hash_value = 73250552
                                    AND snap_id BETWEEN 22 AND 23
                                    AND plan_hash_value > 0)
   OPERATION            OBJECT_NAME          CARDINALITY      BYTES       COST
   -------------------- -------------------- ----------- ---------- ----------
   SELECT STATEMENT                                                       1119
   SORT                                                1         70
    HASH JOIN                                      87756    6142920       1119
     TABLE ACCESS       HOGE_TBL                   72992    2554720        397
     TABLE ACCESS       HOGE_TBL                   72992    2554720        397

Related information

Oracle Statspack

Using Statspack on the Oracle website