Understanding the results of Execute Explain Plan in Oracle SQL Developer

Question

I m trying to optimize a query but don t quite understand some of the information returned from Explain Plan  Can anyone tell me the significance of the OPTIONS and COST columns  In the OPTIONS column  I only see the word FULL  In the COST column  I can deduce that a lower cost means a faster query  But what exactly does the cost value represent and what is an acceptable threshold

User · Answer

FULL is probably referring to a full table scan, which means that no indexes are in use. This is usually indicating that something is wrong, unless the query is supposed to use all the rows in a table.

Cost is a number that signals the sum of the different loads, processor, memory, disk, IO, and high numbers are typically bad. The numbers are added up when moving to the root of the plan, and each branch should be examined to locate the bottlenecks.

You may also want to query v$sql and v$session to get statistics about SQL statements, and this will have detailed metrics for all kind of resources, timings and executions.

User · Answer

The CBO builds a decision tree  estimating the costs of each possible execution path available per query  The costs are set by the CPU cost or I O cost parameter set on the instance  And the CBO estimates the costs  as best it can with the existing statistics of the tables and indexes that the query will use   You should not tune your query based on cost alone   Cost allows you to understand WHY the optimizer is doing what it does   Without cost you could figure out why the optimizer chose the plan it did    Lower cost does not mean a faster query   There are cases where this is true and there will be cases where this is wrong   Cost is based on your table stats and if they are wrong the cost is going to be wrong   When tuning your query  you should take a look at the cardinality and the number of rows of each step   Do they make sense   Is the cardinality the optimizer is assuming correct   Is the rows being return reasonable   If the information present is wrong then its very likely the optimizer doesn t have the proper information it needs to make the right decision   This could be due to stale or missing statistics on the table and index as well as cpu-stats    Its best to have stats updated when tuning a query to get the most out of the optimizer   Knowing your schema is also of great help when tuning   Knowing when the optimizer chose a really bad decision and pointing it in the correct path with a small hint can save a load of time

User · Answer

In recent Oracle versions the COST represent the amount of time that the optimiser expects the query to take  expressed in units of the amount of time required for a single block read   So if a single block read takes 2ms and the cost is expressed as  250   the query could be expected to take 500ms to complete   The optimiser calculates the cost based on the estimated number of single block and multiblock reads  and the CPU consumption of the plan  the latter can be very useful in minimising the cost by performing certain operations before others to try and avoid high CPU cost operations   This raises the question of how the optimiser knows how long operations take  recent Oracle versions allow the collections of  system statistics   which are definitely not to be confused with statistics on tables or indexes  The system statistics are measurements of the performance of the hardware  mostly importantly    How long a single block read takes How long a multiblock read takes How large a multiblock read is  often different to the maximum possible due to table extents being smaller than the maximum  and other reasons   CPU performance   These numbers can vary greatly according to the operating environment of the system  and different sets of statistics can be stored for  daytime OLTP  operations and  nighttime batch reporting  operations  and for  end of month reporting  if you wish   Given these sets of statistics  a given query execution plan can be evaluated for cost in different operating environments  which might promote use of full table scans at some times or index scans at others   The cost is not perfect  but the optimiser gets better at self-monitoring with every release  and can feedback the actual cost in comparison to the estimated cost in order to make better decisions for the future  this also makes it rather more difficult to predict   Note that the cost is not necessarily wall clock time  as parallel query operations consume a total amount of time across multiple threads   In older versions of Oracle the cost of CPU operations was ignored  and the relative costs of single and multiblock reads were effectively fixed according to init parameters

User · Answer

The output of EXPLAIN PLAN is a debug output from Oracle s query optimiser  The COST is the final output of the Cost-based optimiser  CBO   the purpose of which is to select which of the many different possible plans should be used to run the query  The CBO calculates a relative Cost for each plan  then picks the plan with the lowest cost    Note  in some cases the CBO does not have enough time to evaluate every possible plan  in these cases it just picks the plan with the lowest cost found so far   In general  one of the biggest contributors to a slow query is the number of rows read to service the query  blocks  to be more precise   so the cost will be based in part on the number of rows the optimiser estimates will need to be read   For example  lets say you have the following query   SELECT emp id FROM employees WHERE months of service   6     The months of service column has a NOT NULL constraint on it and an ordinary index on it    There are two basic plans the optimiser might choose here    Plan 1  Read all the rows from the  employees  table  for each  check if the predicate is true  months of service 6   Plan 2  Read the index where months of service 6  this results in a set of ROWIDs   then access the table based on the ROWIDs returned    Let s imagine the  employees  table has 1 000 000  1 million  rows  Let s further imagine that the values for months of service range from 1 to 12 and are fairly evenly distributed for some reason   The cost of Plan 1  which involves a FULL SCAN  will be the cost of reading all the rows in the employees table  which is approximately equal to 1 000 000  but since Oracle will often be able to read the blocks using multi-block reads  the actual cost will be lower  depending on how your database is set up  - e g  let s imagine the multi-block read count is 10 - the calculated cost of the full scan will be 1 000 000   10  Overal cost   100 000   The cost of Plan 2  which involves an INDEX RANGE SCAN and a table lookup by ROWID  will be the cost of scanning the index  plus the cost of accessing the table by ROWID  I won t go into how index range scans are costed but let s imagine the cost of the index range scan is 1 per row  we expect to find a match in 1 out of 12 cases  so the cost of the index scan is 1 000 000   12   83 333  plus the cost of accessing the table  assume 1 block read per access  we can t use multi-block reads here    83 333  Overall cost   166 666   As you can see  the cost of Plan 1  full scan  is LESS than the cost of Plan 2  index scan   access by rowid  - which means the CBO would choose the FULL scan   If the assumptions made here by the optimiser are true  then in fact Plan 1 will be preferable and much more efficient than Plan 2 - which disproves the myth that FULL scans are  always bad    The results would be quite different if the optimiser goal was FIRST ROWS n  instead of ALL ROWS - in which case the optimiser would favour Plan 2 because it will often return the first few rows quicker  at the cost of being less efficient for the entire query

User · Answer

Here is a reference for using EXPLAIN PLAN with Oracle  http   download oracle com docs cd B19306 01 server 102 b14211 ex plan htm   with specific information about the columns found here  http   download oracle com docs cd B19306 01 server 102 b14211 ex plan htm i18300    Your mention of  FULL  indicates to me that the query is doing a full-table scan to find your data   This is okay  in certain situations  otherwise an indicator of poor indexing   query writing   Generally  with explain plans  you want to ensure your query is utilizing keys  thus Oracle can find the data you re looking for with accessing the least number of rows possible   Ultimately  you can sometime only get so far with the architecture of your tables   If the costs remain too high  you may have to think about adjusting the layout of your schema to be more performance based

[oracle] Understanding the results of Execute Explain Plan in Oracle SQL Developer

Examples related to oracle

Examples related to oracle-sqldeveloper

Examples related to sql-execution-plan