000027918 - KB-1588 How to generate a SQL explain plan if OEM access is not available

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000027918
Applies ToAffected 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
- An access method for each table mentioned in the statement
- A join method for tables affected by join operations in the statement
- Data operations like filter, sort, or aggregation

In addition to the row source tree, the plan table contains information about the following:

- Optimization, such as the cost and cardinality of each operation
- Partitioning, such as the set of accessed partitions
- Parallel execution, such as the distribution method of join inputs

The EXPLAIN PLAN results let you determine whether the optimizer selects a particular execution plan, such as, nested loops join. It also helps you to understand the optimizer decisions, such as why the optimizer chose a nested loops join instead of a hash join, and lets you understand the performance of a query


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> select * from table(dbms_xplan.display);

sql> spool off

sql> exit
Note: the query 'select * from table(dbms_xplan.display)' will display the last explain plan executed for this session.