Loading....

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.

Please follow and like us:

Last Update: July 19, 2010  

July 19, 2010 38 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

, ,

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?