Using DBMS_XPLAN to obtain the EXPLAIN PLAN of a SQL Statement

Very often we run AWR, ASH and ADDM reports which does highlight the Top SQL statements by disk reads, CPU usage and elapsed time. But an important piece of information is missing which is the Explain Plan.

Using GUI tools like Enterprise Manager will enable us to drill down to the Explain Plan from an individual SQL statement, but how do we do it from the command line?

The answer is simply using DBMS_XPLAN.DISPLAY_AWR and provide to it as a parameter the SQL_ID in question (which can be picked up from the AWR or ASH report).

For example in the ASH report we see this section related to the Top SQL

Top SQL with Top Events     DB/Inst: FILESDB/filesdb  (Jul 19 13:23 to 13:38)

                                                       Sampled #
                SQL ID             Planhash        of Executions     % Activity
----------------------- -------------------- -------------------- --------------
Event                          % Event Top Row Source                    % RwSrc
------------------------------ ------- --------------------------------- -------
         a9j69t1bh6982           2008213504                    1           8.33
SQL*Net more data to client       8.33 TABLE ACCESS - FULL                  8.33
SELECT x."CUST_ID",x."CUST_FIRST_NAME",x."CUST_LAST_NAME",x."CUST_GENDER",x."CUS
T_YEAR_OF_BIRTH",x."CUST_MARITAL_STATUS",x."CUST_STREET_ADDRESS",x."CUST_POSTAL_

We obtain the SQL_ID which is “a9j69t1bh6982″ and now to view the Explain Plan for this SQL statement we provide it as a parameter to the query as shown below.

SQL> set linesize 120
SQL> set pagesize 500
SQL> select * from TABLE(dbms_xplan.display_awr('a9j69t1bh6982'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID a9j69t1bh6982
--------------------
SELECT x."CUST_ID",x."CUST_FIRST_NAME",x."CUST_LAST_NAME",x."CUST_GENDER
",x."CUST_YEAR_OF_BIRTH",x."CUST_MARITAL_STATUS",x."CUST_STREET_ADDRESS"
,x."CUST_POSTAL_CODE",x."CUST_CITY",x."CUST_CITY_ID",x."CUST_STATE_PROVI
NCE",x."CUST_STATE_PROVINCE_ID",x."COUNTRY_ID",x."CUST_MAIN_PHONE_NUMBER
",x."CUST_INCOME_LEVEL",x."CUST_CREDIT_LIMIT",x."CUST_EMAIL",x."CUST_TOT
AL",x."CUST_TOTAL_ID",x."CUST_SRC_ID",x."CUST_EFF_FROM",x."CUST_EFF_TO",
x."CUST_VALID" FROM "SH"."CUSTOMERS" x

Plan hash value: 2008213504

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |   414 (100)|          |
|   1 |  TABLE ACCESS FULL| CUSTOMERS | 55500 |  9755K|   414   (1)| 00:00:05 |
-------------------------------------------------------------------------------

We can see that the query is performing a full table scan of the Customers table – something which is not very evident just my reading the AWR or ASH report.