Skip to Main Content
  • Questions
  • SQL Tuning basics for PL/SQL Developer's

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Anbazhagan.

Asked: April 21, 2010 - 9:55 pm UTC

Last updated: April 22, 2010 - 9:28 am UTC

Version: 10.2.0.1.0

Viewed 1000+ times

You Asked

Hi Tom,

I am a PL/SQL developer; I want to learn about basics of SQL tuning. How to identity that my SQL query is really need to tune. What are all the steps should need to follow to optimize the query? How to read and understand explain plan output?

Basically, I am looking for a step by step document that explains all about SQL tuning and it would be helpful if you provide me with some case study, so that i cantry practically with some demo tables and learn better.

Thanks in Advance,
Anbu

and Tom said...

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

step by step document that explains SQL tuning

Duke Ganote, April 22, 2010 - 1:50 pm UTC

Try any of method-R's papers, for example

"For Developers: Making Friends with the Oracle Database - Cary Millsap"
http://method-r.com/downloads/cat_view/38-papers-and-articles

Karen Morton's use of:
SQL>select /*+ gather_plan_statistics */ * from ord where order_no = 256769 ;
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));


"Managing Statistics for Optimal Query Performance - Karen Morton"

in the same folder... very useful. Better than EXPLAIN PLAN, since it's "actuals" not "estimates".

Tuning

A reader, April 22, 2010 - 8:03 pm UTC