07 Performance Improvement Consideration


Use UNION instead of OR

Consider using UNION when you have to use OR condition on 2 index columns

07 Performance Improvement Consideration

Advertisements

06 Index Suppression


Reasons for Index Suppression (Accidental)

There are many reasons why an index might not be used even though you have an index on the column you are searching for.

  • Use of <> operator on an indexed column
  • Use o a SUBSTR function on an indexed column
  • use of arithmetic operators (+,_,*,/) on an indexed column
  • Use of TRUNC function on an indexed column
  • Use of || operator on an indexed column
  • Data type conversion on an indexed column
  • Use of IS NULL/ IS NOT NULL on an indexed column

06 Index Suppression

04 Explain Plan


eExecution Plan

Oracle execution plan sample
Oracle execution plan sample
  • An execution plan is a hierarchical listings of steps.
  • Each step is one of a few basic data access operations known to the database server.
  • The most complex statement can be broken down into a series of basic operations
  • To execute any  SQL statement oracle has to derive an ‘Execution Plan’
  • It is nothing but a tree which contains the order of steps and a relationship between them.

What should you look for in the execution plan?

You should be able to look at the execution plan and assess if the optimizer has made any mistakes in its estimations or calculations, leading to a sub-optimal plan.

  • Cardinality : Estimate the number of rows coming out of each operations.
  • Access Method:  The way in which data is being accessed , via either a table scan or index access (full table scan, index unique scan, range scan etc)
  • Join Method: The method (e.g. hash, sort-merge join , nested loops, sort join, etc) used to join tables with each other.
  • Join Type: The type of join (inner, outer, semi, anti…)
  • Join Order : The order in which the tables are joined to each other.
  • Partition Pruning: Are only the necessary partitions being accessed to answer the query?
  • Parallel Execution : In case of parallel execution, is each operation in the plan being conducted in parallel? Is the right data redistribution method being used

04 Explain Plan

02 Oracle Optimizer and its execution plan


Cost based optimization

What is cost based optimization?
Optimization is the selection of the best plan to un a SQL.

Oracle cost based optimizer is designed to determine the most efficient way to carry out an
SQL statement. but it can’t do this without good, up to date statistical information on
the data being accessed.

With the cost based approach, the optimizer factors in statistical information about the contents of the particular schema objets (tables, clusters, or indexes) being accessed. statistical information includes data on some of the items listed below

  • Columns, datatypes
  • Row count, Row length, Bytes, Percentage free
  • DB_BLOCK_SIZE
  • Statistics, Last Analyzed date
  • Pecent of Dsitinct Values on coulmn
  • All indexes on the table (including statistics)
  • All constraints on the table.

02 Oracle Optimizer and its execution plan