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:
