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
- 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

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.

SQL ID is available

If you have the SQL ID of the query from the AWR, login to RSA Identity Governance & Lifecycle as avuser and plug the SQL ID into one of the following two queries:

SQL> SET LINESIZE 250 PAGESIZE 0 TRIMS ON TABS OFF LONG 1000000
SQL> COLUMN REPORT FORMAT a220
SQL> SELECT
   DBMS_SQL_MONITOR.REPORT_SQL_MONITOR
        (sql_id=>'an05rsj1up1k5',report_level=>'ALL') report
FROM DUAL;

SQL> SELECT * FROM TABLE(dbms_xplan.display_awr(sql_id=>'ffnypa8au0p5g'));

SQL ID is not available (SQL*Plus)

Login to RSA Identity Governance & Lifecycle as avuser, run the query and the SQL that generates the Explain Plan. Note the example below shows the spool command so the output may be sent to RSA Support. Leave out the spool 
 

$ sqlplus avuser/<avuser_password>
SQL> SET LINESIZE 132;
SQL> SPOOL explain_plan_TMEU
SQL> EXPLAIN PLAN FOR SELECT COUNT(*) FROM T_MASTER_ENTERPRISE_USERS;
SQL> SELECT * FROM TABLE(dbms_xplan.display);
SQL> SPOOL OFF;
SQL> ROLLBACK;
SQL> EXIT

• The explain plan results will be written to the screen and to the Linux file system as explain_plan_TMEU.lst.
• The explain plan for keywords are critical for getting the Explain Plan and must preface the actual SQL command being analyzed.
• Any query for which you need an Explain Plan may be substituted after the keywords explain plan for.
• The query SELECT * FROM TABLE(dbms_xplan.display) will display the last explain plan executed for this session.

SQL ID is not available (SQL Developer)

If you are using SQL Developer, you can obtain the plan by first selecting the query in question and then pressing the F10 key (or right click and click on Explain Plan...  An example of how to do this using SQL Developer is shown below: