How can I view execution plans captured by statspack for an Amazon RDS Oracle DB instance?
I captured performance statistics for my Amazon Relational Database Service (Amazon RDS) Oracle DB instance using Statspack. But the Statspack report doesn't have any information about execution plans. How can I view execution plans for queries that were captured by Statspack?
"I captured performance statistics for my Amazon Relational Database Service (Amazon RDS) Oracle DB instance using Statspack. But the Statspack report doesn't have any information about execution plans. How can I view execution plans for queries that were captured by Statspack?Resolution1.    Take a Statspack snapshot that has a snap level greater than or equal to 6 (i_snap_level => 6) to capture SQL execution plans. For more information, see How do I check the performance statistics on an Amazon RDS DB instance that is running Oracle?2.    Confirm that the Begin Snap ID, End Snap ID, and the old hash value for the query by using the Statspack report. 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  NOHost Name             Platform                CPUs Cores Sockets   Memory (G)~~~~ ---------------- ---------------------- ----- ----- ------- ------------     ip-172-31-22-176 Linux x86 64-bit           2     2       1          3.7Snapshot       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 by using 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, which 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   73250552Module: SQL*PlusSELECT COUNT(*) FROM HOGE_TBL H1 INNER JOIN HOGE_TBL H2 USING(OBJECT_NAME)      0.75            1       0.75   11.3       0.77          18,994 2912400228Module: sqlplus@ip-172-31-22-176 (TNS V1-V3)BEGIN statspack.snap; END;      0.14          107       0.00    2.1       0.15             732 3879834072select TIME_WAITED_MICRO from V$SYSTEM_EVENT  where event = 'Shared IO Pool Memory'...3.    Connect to the DB instance using an Oracle client, such as SQL*Plus.4.    Invoke a query similar to the following to retrieve the execution plan:SELECT lpad(' ', 1 * ( depth - 1 ))     || operation AS operation,       object_name,       cardinality,       bytes,       costFROM   stats$sql_planWHERE  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 retrieved by SQL*Plus:SQL> col operation format a20col object_name format a20SQL>SELECT lpad(' ', 1 * ( depth - 1 ))     || operation AS operation,       object_name,       cardinality,       bytes,       costFROM   stats$sql_planWHERE  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                                                       1119SORT                                                1         70 HASH JOIN                                      87756    6142920       1119  TABLE ACCESS       HOGE_TBL                   72992    2554720        397  TABLE ACCESS       HOGE_TBL                   72992    2554720        397Related InformationHow do I check the performance statistics on an Amazon RDS DB instance that is running Oracle?Oracle StatspackOracle documentation for Using StatspackFollow"