Usando AWS re:Post, accetti AWS re:Post Termini di utilizzo

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

4 minuti di lettura
0

I captured performance statistics for my Amazon Relational Database Service (Amazon RDS) for Oracle DB instance that uses Statspack. But the Statspack report doesn't have any information about execution plans. I want to view execution plans for queries that Statspack captured.

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

AWS UFFICIALE
AWS UFFICIALEAggiornata 6 mesi fa