07 Performance Improvement Consideration

Use UNION instead of OR

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

Continue reading


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

Continue reading

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

Continue reading

03 Accessing Tables

What is my Address?

In Oracle RowID is the physical address of a row. Oracle knows uses ROWID to identify the location of  record in the database. RowID’s are binary addresses of the location where the row is stored.

Example: Select rowid, s.salesorder from sales_history s

Continue reading

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
  • Statistics, Last Analyzed date
  • Pecent of Dsitinct Values on coulmn
  • All indexes on the table (including statistics)
  • All constraints on the table.

Continue reading