Q
Problem solve Get help with specific problems with your technologies, process and projects.

Interpreting tkprof/explain plan output

I have a problem in SQL tuning. When I see the output of tkprof/explain plan, what values should I monitor (before...

and after tuning)? This is a loaded question. There are training classes designed to go over how to interpret tkprof and explain plan. I strongly recommend you get hold of a tuning book and go over them.

Tkprof actually lets you analyze trace file that dumps all I/O information regarding a particular SQL statement. On the other hand, explain plan tells you how the Oracle cost-based optimizer is planning to run your query. The result of the explain plan will vary if the schema that you are using the SQL statement against is analyzed or not.

Therefore, please ensure that you analyze the schema using DBMS_STATS.GATHER_SCHEMA_STATS procedure.

You can run the following query against the plan table to see exactly what your query does. Please ensure you set your query for the plan table using the following command.

 EXPLAIN PLAN SET STATEMENT_ID = 'ASIF_HALIYAL_QUERY_1' FOR <YOUR SQL STATEMENT>

Now querying the plan table using the following SQL statement will let you know exactly what the CBO is planning to do with your SQL. Many things to look at for instance if the index you want the query to use is being used, is there a full table scan (note sometimes a full table scan may be desirable) and if so why, etc.

 SELECT ID, PARENT_ID, cardinality "Rows", LPAD(' ', 1*(LEVEL-1)) || LTRIM(' ', 1*(LEVEL-1)) || LTRIM(RTRIM(OPERATION)) || ' ' || LTRIM(RTRIM(OPTIONS)) || ' ' || LTRIM(RTRIM(OBJECT_NAME)) || ' ' || LTRIM(RTRIM(REMARKS)) EXECUTION_PLAN FROM PLAN_TABLE WHERE STATEMENT_ID = 'ASIF_HALIYAL_QUERY_1' CONNECT BY PRIOR ID = PARENT_ID AND PRIOR STATEMENT_ID = STATEMENT_ID AND STATEMENT_ID = 'ASIF_HALIYAL_QUERY_1' START WITH ID = 0 AND STATEMENT_ID = 'ASIF_HALIYAL_QUERY_1' ORDER BY POSITION


This was last published in March 2004

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close