|Applies To||Affected Versions: All Versions|
This Aveksa Knowledge base article is intended as an aid for diagnosing performance issues by displaying the Oracle Explain Plan for a given SQL statement. This document is not comprehensive nor intended as a replacement for Oracle Guidelines and Documentation.
What is an Explain Plan?
When diagnosing sql issues, especially performance issues, it can be very useful to understand how the Oracle 'engine' is processing certain sql statements. An 'Explain Plan' is created to help with this understanding. Oracle's definition of an explain plan is listed here.
The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations Oracle performs to run the statement.
The row source tree is the core of the execution plan. It shows the following information:
- An ordering of the tables referenced by the statement
- Optimization, such as the cost and cardinality of each operation
How to generate a SQL explain plan if OEM access is not available.
On an Aveksa Appliance, Oracle's OEM (Oracle Enterprise Manager) is typically available, and has an 'explain plan' option when viewing recent database activity. However, there are occasions when OEM might not be available. In this situation it is possible to generate an explain plan from the command line using sqlplus, or using a sql tool such as SQLDeveloper. If you are using SQLDeveloper, you can obtain the plan by firstly selecting the query in question and then hitting F-10 (or right click - Explain plan). An example of how to do this using sqlplus is shown below.
Notes regarding the example:
- These steps include sending the output to a file which can then be forwarded to support. The actual explain plan created using these statement is attached to this kbase as an example.
- The example below shows the SQLPlus 'sql>' prompt, which of course does not need to be typed by the user.
- These commands can be executed as AVUSER. The AVUSER password is not shown in this example. The appropriate password needs to be substituted.
- In the example below, the sql statement we wish to analyze is 'select * from v_entgrp_definitions'. Any sql statement needing analysis can be substituted here.
Example Explain Plan creation:
$ sqlplus avuser/<avuser-password>
sql> set linesize 132;
sql> spool explan_for_select_from_entgrp.txt
sql> explain plan for SELECT * FROM V_ENTGRP_DEFINITIONS;
sql> spool off