I need a quick guide or how-to on reading an Execution Plan output. I am not familiar with analyzing different parameters, such as COST, Cardinality, Nested Loops, Merge Joins, Hash join, etc. I have just purchased your book on it and am going through it, but this is sort of urgent. If you could explain about the different parameters of the execution plan output, it would be a great help.
This is quite a tall order. Tuning SQL statements, with or without an explain plan can be tricky. Generally you read from the bottom up, inside to outside. A good formatting query can be a big help to eliminate unneeded data from the table.
Set lines 132 pages 47 COLUMN QUERY_PLAN FORMAT A80 column statement_id format a11 heading ID spool ex_plan..lis SELECT statement_id, LPAD( ' ' , 2*LEVEL)||OPERATION||' '||options||' '||OBJECT_NAME||' cost: '||cost ||' goal:'||optimizer QUERY_PLAN FROM &&owner..PLAN_TABLE CONNECT BY PRIOR ID = PARENT_ID START WITH ID=0; spool off truncate table &&owner..plan_table; ed ex_plan..lis undef id undef owner
Once you have a formatted the explain plan, look at the highest cost lines to determine what can be done to fix it.
Dig Deeper on Oracle DBA jobs, training and certification
Related Q&A from Mike Ault
How to find the definition or structure of a dropped table? I know the table's name but I don't know the columns and datatypes. It no longer exists. Continue Reading
I am trying to remove carriage returns at the end of clob fields in SQL*Plus. This just nulls out the field in the table. What do I need to change to... Continue Reading
I want to find the length of a numeric datatype field in my table. How can I find it? Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.