Q

What is Explain Plan in Oracle, & how do we use it?

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. The command is of the form:

EXPLAIN PLAN SET STATEMENT_ID='somevalue' FOR some SQL statement;

You need to use a statement_id and then give your SQL statement. For instance, suppose I have a query to tune. How does it get executed? I issue the following in SQL*Plus:

 

 SQL> explain plan set statement_id = 'q1' for 2 select object_name from test where object_name like 'T%'; Explained.

I used 'q1' for my statement id (short for query 1). But you can use anything you want. My SQL statement is the second line. Now I query the PLAN_TABLE to see how this statement is executed. This is done with the following query:

 

 SQL> SELECT LPAD(' ',2*(level-1)) || operation || ' ' || options || ' ' || 2 object_name || ' ' || DECODE(id,0,'Cost = ' || position) AS "Query Plan",other 3 FROM plan_table 4 START WITH id = 0 5 AND statement_id='q1' 6 CONNECT BY PRIOR ID = PARENT_ID 7* AND statement_id = 'q1' Query Plan OTHER -------------------------------------------------- -------------------------------------------------- SELECT STATEMENT Cost = TABLE ACCESS FULL TEST

This tells me that my SQL statement will perform a FULL table scan on the TEST table (TABLE ACCESS FULL TEST). Now let's add an index on that table!

 

 SQL> create index test_name_idx on test(object_name); Index created. SQL> truncate table plan_table; Table truncated. SQL> explain plan set statement_id = 'q1' for 2 select object_name from test where object_name like 'T%'; Explained. SQL> SELECT LPAD(' ',2*(level-1)) || operation || ' ' || options || ' ' || 2 object_name || ' ' || DECODE(id,0,'Cost = ' || position) AS "Query Plan",other 3 FROM plan_table 4 START WITH id = 0 5 AND statement_id='q1' 6 CONNECT BY PRIOR ID = PARENT_ID 7* AND statement_id = 'q1' Query Plan OTHER -------------------------------------------------- -------------------------------------------------- SELECT STATEMENT Cost = INDEX RANGE SCAN TEST_NAME_IDX

I added an index to the table. Before I issue another EXPLAIN PLAN, I truncate the contents of my PLAN_TABLE to prepare for the new plan. Then I query the PLAN TABLE. Notice that this time I'm using an index (TEST_NAME_IDX) that I created!! Hopefully, this query will run faster now that it has an index to use. But this may not always be the case.

The Oracle8i Designing and Tuning for Performance manual provides much more information than I could cover in this limited space. Please take time to read it!

 

For More Information


For news, advice and other information about database administration, click here.

This was first published in May 2001

Dig deeper on Oracle database administration

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close