SQL Tuning

How to Get the Execution Plan of a Specific Statement

Sometime, there is no OEM around to check the explain plan of a specific statement. We need a bare way to do the job. Here are examples to check the explain plan.
SQL> explain plan FOR SELECT COUNT(*) FROM SH.SALES;

Explained.

SQL> set linesize 120;
SQL> select * from table(dbms_xplan.display);

Since the result is too wide to be displayed well, so we take a screen shot as below:

The dbms_xplan will retrieve the most recent execution plan for you. By default, all execution plans will go to PLAN_TABLE. You can query the table by yourself also. If there is no PLAN_TABLE in your database, you can create it by executing utlxplan.sql under $ORACLE_HOME/rdbms/admin.

If you would like to display only basic column with less information other than a typical one, you can query like this:
SQL> select * from table(DBMS_XPLAN.DISPLAY(format => 'BASIC'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 1123225294

----------------------------------------------------------
| Id  | Operation                      | Name            |
----------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |
|   1 |  SORT AGGREGATE                |                 |
|   2 |   PARTITION RANGE ALL          |                 |
|   3 |    BITMAP CONVERSION COUNT     |                 |
|   4 |     BITMAP INDEX FAST FULL SCAN| SALES_PROMO_BIX |
----------------------------------------------------------

11 rows selected.


A finer way to get the execution plans is to set the statement_id for every statement, and then retrieve specific plains by statement_id.
SQL> explain plan set statement_id='Count SH.SALES' for select count(*) from sh.sales;

Explained.

SQL> column ID format 99;
SQL> column OPERATION format a25;
SQL> column OPTIONS format a15;
SQL> column OBJECT_NAME format a15;
SQL> column OBJECT_ALIAS format a15;
SQL> column QBLOCK_NAME format a10;
SQL> column POSITION format 99;
SQL> set linesize 120;
SQL> SELECT id,
  2    LPAD(' ',2*(LEVEL-1))
  3    ||operation operation,
  4    OPTIONS,
  5    object_name,
  6    object_alias,
  7    qblock_name,
  8    position
  9  FROM plan_table
 10    START WITH id       = 0
 11  AND statement_id      = 'Count SH.SALES'
 12    CONNECT BY PRIOR id = parent_id
 13  AND statement_id      = 'Count SH.SALES'
 14  order by id;

The result is as below:

Leave a Reply

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