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.

Gathering Statistics

• The optimizer statistics are crucial to SQL tuning.
• If these statistics do not exist or are no longer accurate, then the optimizer
  cannot generate the best plan.
• Other data relevant to SQL performance include structure of table and views that the statement accessed, and defenitions of any indexes available to the statement

To compute statistics we have 2 methods

Analyze table sales compute statistics
exec dbms_stats.gather_table_stats
(
user, ‘sales_history”, cascade=> true
— cascade=true means gather the statistsics for all constraints, indexes etc related to this table
— Typically this takes a longer time to execute than method 1
);

Table statistics are stored in the table ALL_TAB_STATISTICS.

EX: Select * from all_tab_statistics where table_name in (‘Sales’,’sales_history’)
This will return a numbe of columns, but the pertinent omce being num of rows & blocks occupied by the table

Execution Plan

An execution plan is the list o steps that Oracle will follow in order to execute an SQL statement. Each step is one of a finite number of basic operations known to the database server.

The execution plan shows three actions.
1. do index range scan using the index sales_Ord_IDX and find the row_id
2. Send the row_ID to the operation called table Access by index rowid, then it goes to that particluar row and gets the values
3. and gives to the select statement

SQL Tuning Tools

The following tools are used to display execution plans.

Explain Plan : This SQL statement enables you to view the execution plan that the optimizer would use to execute a SQL statement without actually executing the statement. This is the most commonly used tool to understand the execution of SQL statement.

AutoTrace: The auto-trace command in SQL*Plus generates the execution plan and statistics about the performance of a query. This command provides statistics such as disk reads and memory reads. The information provided also relates to the hardware

v$SQL PLAN: These views contain information about executed statements, and their execution plans, that are still in then shared pool. You can use the DBMS_XPLAN package methods to display the execution plan generated by the explain plan command and query
of v$SQL_PLAN. This can be used to figure out all the queries that are executing really bad

Running Explain Plan

Example:

Explain plan for
Select Orde_ID, Sales_date, Total_Amount, product_name from sales_history s where order_ID= 12345;

The results are stored in the table  DBMS_xplan.display

Select * from table (DBMS_xplan.display)

the resulst are the same as in the image above

For all your application development needs, visit www.verbat.com for a fiscally conscious proposal that meets your needs ( So I can keep this blog going as well!!!!)

Alternatively click through the link   if you found this article interesting. (This will help the company with google  Search engine rankings)