DBMS_XPLAN + AWR to show execution plan in history : What was happening? (3)

Q: my applications ran very slow yesterday,  Can I know the execution plan at that time?
A: Sure if you are lucky to have AWR report, then DBA_HIST_*** will help you . And they are not limited in SQL plan only.  History won’t go easily 🙂

1 Make sure AWR works and created snapshots in that timeframe you interested.
select snap_interval,retention from dba_hist_wr_control;
select snap_id,begin_interval_time,end_interval_time from dba_hist_snapshot;

2 Get SNAP_ID you want to drill down

3 Show all expensive SQL statement
I created ad_hoc PL/SQL code to make it more easy, fully utilize DBA_HIST_** (our friend in performance tuning)

CREATE OR REPLACE TYPE my_sqlplan_row
    AS OBJECT (sql_id VARCHAR2(13),plan_id NUMBER,cost NUMBER)
/

CREATE OR REPLACE TYPE my_sqlplan_table
    AS TABLE OF my_sqlplan_row
/

CREATE OR REPLACE FUNCTION my_sqlplan_cost_in_snapshot(
     snap_id    IN INTEGER DEFAULT 1)
     RETURN my_sqlplan_table  PIPELINED
IS
BEGIN
         FOR s IN (SELECT a.sql_id sql_id , a.plan_hash_value plan_id , a.cost cost FROM dba_hist_sql_plan a,dba_hist_sqlstat b WHERE b.snap_id=snap_id and a.sql_id=b.sql_id and a.plan_hash_value=b.plan_hash_value) LOOP
           PIPE ROW (my_sqlplan_row(s.sql_id,s.plan_id,s.cost));
    END LOOP;
    RETURN;
END;
/

4 Drill down to special SQL and its execution plan
Do it after identified suspected SQL from step 3
I created another PL/SQL to unleash power of DBMS_XPLAN though it is optional, think about it.

CREATE OR REPLACE PROCEDURE my_sqlplan(
     sql_id    IN VARCHAR2 DEFAULT NULL,
     plan_id IN NUMBER DEFAULT NULL)
IS
BEGIN
    DBMS_OUTPUT.ENABLE(1000000);
    FOR i IN (SELECT plan_table_output FROM table (DBMS_XPLAN.DISPLAY_AWR(sql_id,plan_id))) LOOP
        DBMS_OUTPUT.PUT_LINE (i.plan_table_output);
    END LOOP;
END;
/
 
Example
SQL> set serveroutput on
SQL> set long 4000
SQL> set linesize 1000
SQL>
SQL> select * from table(my_sqlplan_cost_in_snapshot(7932)) where cost>22500;

SQL_ID           PLAN_ID       COST
————- ———- ———-
4zmbcythh0m13 3982944357       22715
4zmbcythh0m13 3982944357       22954
4zmbcythh0m13 3982944357       22954
1rthr0cvbr5g7 2749150488       23002


SQL> exec my_sqlplan(‘&sql_id’,’&plan_id’);
Enter value for sql_id: 1rthr0cvbr5g7
Enter value for plan_id: 2749150488
SQL_ID 1rthr0cvbr5g7
——————–
INSERT INTO
***
AND o.owner#        = u.user#(+)

Plan hash value: 2749150488
Plan hash value: 2749150488
——————————————————————————————————————
| Id  | Operation                                                      | Name          | Rows | Bytes  | Cost (%CPU)| Time     |
——————————————————————————————————————
|   0 | INSERT STATEMENT                                  |                   |         |             |  2592 (100)|             |
|   1 |  NESTED LOOPS OUTER                               |                   |   291 |  1330K |  2591   (1) | 00:00:32 |
|   2 |   NESTED LOOPS OUTER                             |                   |   291 |  1326K |  2300   (1)| 00:00:28 |
|   3 |    NESTED LOOPS                                        |                   |   291 |  1317K |  2008   (1)| 00:00:25 |
|   4 |     VIEW                                                       |                   |    89 |  3051   |    34   (3)| 00:00:01 |
|   5 |      SORT GROUP BY                                     |                   |    89 |  3051   |    34   (3)| 00:00:01 |
|   6 |       FILTER                                                  |                   |         |             |               |               |
|   7 |        FIXED TABLE FULL                              | ***             |   100 |  3400  |    24   (0)| 00:00:01 |
|   8 |        INDEX UNIQUE SCAN                         | ***             |     1  |    31     |     1   (0)| 00:00:01   |
|   9 |     FIXED TABLE FIXED INDEX                  | ***             |     3  | 13806  |    21   (0)| 00:00:01  |
|  10 |    TABLE ACCESS BY INDEX ROWID          | ***             |     1  |    32    |     1   (0)| 00:00:01   |
|  11 |     INDEX UNIQUE SCAN                            | I_OBJ1       |     1  |            |     0   (0)|                |
|  12 |   TABLE ACCESS CLUSTER                          | USER$        |     1 |    14     |     1   (0)| 00:00:01   |
|  13 |    INDEX UNIQUE SCAN                            | I_USER#    |     1 |             |     0   (0)|                 |
——————————————————————————————————————

PL/SQL procedure successfully completed.

Advertisements

About Pang

Oracle DBA working in database, fusion middleware and identity management and cloud.
This entry was posted in Oracle database. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s