Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Saturday, 24 March 2012

Explain Plan in Oracle

An Explain Plan is a tool that you can use to have Oracle explain to you how it plans on executing your query. This is useful in tuning queries to the database to get them to perform better. Once you know how Oracle plans on executing your query, you can change your environment to run the query faster. Before you can use the EXPLAIN PLAN command, you need to have a PLAN_TABLE installed.
This can be done by simply running the $ORACLE_HOME/rdbms/admin/utlxplan.sql script in your schema. It creates the table for you. After you have the PLAN_TABLE created, you issue an EXPLAIN PLAN for the query you are interested in tuning. For Example

CONNECT HR/hr ;
SQL>@C:\oracle1\RDBMS\ADMIN\UTLXPLAN.SQL

How to Run EXPLAIN PLAN
EXPLAIN PLAN FOR  SQL_Statement;
For example:
EXPLAIN PLAN FOR SELECT last_name FROM employees;
This explains the plan into the PLAN_TABLE table. You can then select the execution plan from PLAN_TABLE.

SELECT  TIMESTAMP, OPERATION, OPTIONS, OBJECT_OWNER, OBJECT_NAME,  CARDINALITY, BYTES FROM PLAN_TABLE WHERE OBJECT_OWNER='HR' and OBJECT_NAME='EMPLOYEES';
TIMESTAMP
OPERATION
OPTIONS
OBJECT_OWNER
OBJECT_NAME
CARDINALITY
BYTES
Mar/22/2012 01:36:12 PM
TABLE ACCESS
FULL
HR
EMPLOYEES
107
856

Identifying Statements for EXPLAIN PLAN
EXPLAIN PLAN
SET STATEMENT_ID = 'bad1' FOR SELECT last_name FROM employees;

Specifying Different Tables for EXPLAIN PLAN
EXPLAIN PLAN INTO my_plan_table FOR
SELECT last_name FROM employees;

Displaying PLAN_TABLE Output
After you have explained the plan, use the two scripts provided by Oracle to display the most recent plan table output:
UTLXPLS.SQL - Shows plan table output for serial processing
UTLXPLP.SQL - Shows plan table output with parallel execution columns
Note: If you have specified a statement identifier, then you can write your own script to query with Pla_Table.

The Other way of Explain Plan
SQL> CONN sys/password AS SYSDBA
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
SQL> GRANT ALL ON sys.plan_table TO public;
SQL> CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;

SQL> SET AUTOTRACE ON;
SELECT * FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id
AND e.last_name = 'SMITH';
SQL@c:\oracle1\rdbms\admin\utlxpls.sql

EXPLAIN PLAN SET STATEMENT_ID = 'TIM' FOR
SELECT * FROM   hr.employees e, hr.departments d
WHERE  e.department_id = d.department_id AND e.last_name  = 'SMITH';

Minimizing Throw-Away
Examining an explain plan lets you look for throw-away in cases such as the following:
Full scans
Unselective range scans
Late predicate filters
Wrong join order
Late filter operations
Important Views While working on Plan Table
V$SQL_PLAN
V$SQL_PLAN_STATISTICS;
Plan_Table
V$SQL_PLAN_STATISTICS_ALL

EXPLAIN PLAN Restrictions
Oracle does not support EXPLAIN PLAN for statements performing implicit type conversion of date bind variables. With bind variables in general, the EXPLAIN PLAN output might not represent the real execution plan. From the text of a SQL statement, TKPROF cannot determine the types of the bind variables. It assumes that the type is CHARACTER, and gives an error message if this is not the case. You can avoid this limitation by putting appropriate type conversions in the SQL statement.





0 comments:

Post a Comment