Oracle provides the ability to examine how a SQL statement will be parsed by oracle with explain plan.
1. Logon to sqlplus with a user that has DBA privileges.
[oracle@MYLINUX ~]$ sqlplus orcl_admin/*******@ORCL
SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 13:20:51 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
2. Create the plan table with the oracle script $ORACLE_HOME/rdbms/admin/utlxplan.sql. NOTE: The ‘?’ sign in sqlplus refers to the variable ORACLE_HOME
SQL> @?/rdbms/admin/utlxplan.sql
Table created.
SQL>
3. Now you have the tables created to hold the parsing information, you can execute the explain command. NOTE: We use the ‘SET’ clause in this example to allow us to easily identify our statement.
SQL> explain plan
2 set statement_id=’TEST1′ for
3 select count(*) from nais.nais_tab;
Explained.
SQL>
4. Now we can query the table plan_table in our schema.
set pagesize 160
select plan_table_output from table(dbms_xplan.display(‘PLAN_TABLE’,’TEST1′,’TYPICAL’));
PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 1794364880
—————————————————————————————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
——————————————————————————–
——————————————–
| 0 | SELECT STATEMENT | | 1 | 334K (1)| 01:18:02 | | | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | | | Q1,00 | PCWP | |
| 5 | PX PARTITION RANGE ALL| | 554M| 334K (1)| 01:18:02 | 1 | 791 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL | TEST_TAB| 554M| 334K (1)| 01:18:02 | 1 | 791 | Q1,00 | PCWP | |
—————————————————————————————————————————-
5. This completes the use of explain plan for Oracl