Explaining Explain Plan - 1

• When an SQL statement is passed to the server the Cost Based Optimizer (CBO) uses database statistics to create an execution plan which it uses to navigate through the data. This plan is used to execute the statement and the results are achieved.
• If you've highlighted a problem query the first thing you should do is EXPLAIN the statement to check the execution plan that the CBO has created. This will often reveal that the query is not using the relevant indexes, or indexes to support the query are missing.
• The explain plan process stores data in the PLAN_TABLE. The UTLXPLAN.SQL contains the plan_table syntax.

Ques1) How to get the explain plan?

Ans)

There are various ways to get the Explain Plan.

       1) AutoTrace Option: - This option exeutes the statement, gives the output and then gives the explain plan. For e.g.

SQL> set autotrace on
SQL> select * from test;

        C1 C2         C3
---------- ---------- ----------
         1 ABC        X
         2 DEF        X
         3 PQR        X
         4 PQR        X
         5 PQR        Y
         6 PQR        Y

6 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     6 |    36 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |     6 |    36 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
        201  recursive calls
          0  db block gets
         45  consistent gets
          0  physical reads
          0  redo size
        623  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          6  rows processed

The drawback of this query is if the statement takes lot of time to execute then we have to wait for that long to get the plan.

       2) Explain Plan: - This options doesn't executes the statement but stores the plan in the PLAN_TABLE. Later the plan table can be queried to get the results. following screenshots shows that

SQL> explain plan for select * from test;

Explained.

To retrieve the plan you can simply query the plan table. If you need the output in a formatted way use the following statment

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     6 |    36 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |     6 |    36 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2137789089

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    21   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |       |       |            |          |
---------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
         13  recursive calls
         12  db block gets
         41  consistent gets
          0  physical reads
          0  redo size
        963  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          8  rows processed

Ques 2) What is DBMS_XPLAN.DISPLAY ?

Ans:

DBMS_XPLAN.DISPLAY is a function to display the execution plan in formatted way. The function can accept 3 optional parameters:

  1) table_name - Name of the PLAN_TABLE, default value 'PLAN_TABLE'.
  2) statement_id - Statement id of the plan to be displayed. The default value is NULL, which displays the most recent execution plan in the PLAN_TABLE.
  3) format - Controls the level of detail displayed, default value 'TYPICAL'. Other values include 'BASIC', 'ALL', 'SERIAL','ADVANCED'.

If we put in all the 3 options the statement would look like

select * from table(dbms_xplan.display('PLAN_TABLE','1','BASIC')

Ques 3) What is statement_id?

Ans:

The Plan table stores only the recent execution plan. To keep a history of the execution plans you should use STATEMENT_ID while generating the explain plan. syntax would be like

explan plan set statement_id = <abc> FOR <query>

Ques 4) What if i have a different table to store the explain plan.

Ans:

By default explain plan for statement stores the plan in the plan_table created by oracle itself. If you have a different table then the syntax would be like

explain plan into <table_name> <sql>

Now, we know how to get the explain plan. Lets understand about the plan in the next blog.

Oracle Not Available

This post puts in the steps to bring back the oracle server. The syntax are more generic to versions.

1. go to the command prompt
2. sqlplus /nolog
3. conn sys/<pwd> as sysdba -- This would connect as ideal instance.
4. shutdown immediate
5. startup

The database will get mount properly and also open up.