|Applies To||RSA Product Set: Identity Governance & Lifecycle|
This RSA Identity Governance & Lifecycle knowledge base article is intended as an aid for diagnosing performance issues by displaying the Oracle Explain Plan for a given SQL statement. An Explain Plan can be generated through Oracle Enterprise Manager (OEM). This document describes how to obtain an Explain Plain in the event that OEM is not available or access to OEM is not allowed.
This document is not a comprehensive article on the Explain Plan nor is it 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 below:
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
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
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
In the event that OEM is not available, it is possible to generate an Explain Plan from the command line using SQL*Plus or using a SQL tool such as SQL Developer.