Post a Comment On: Oracle Help

Its common to encounter issues related to Performance of the custom PLSQL Programs. Best approach
for performance tuning has been to generate Explain Plan for complex SQL queries and then tune them using hints, add missing joins or rewrite the queries. This article is for beginners to understand
different aspects/terms of this process.

Explain Plan in oracle helps to find out the path/cost taken by SQL Query fired on to the Database. It is an Oracle function that analyzes SQL statements for performance issues. The Explain Plan determines the execution plan Oracle follows when executing a specified SQL statement.

Explain Plan indicates:
1. Order that Oracle will search and join tables
2. Types of index search used or Full table scans
3. Names of indexes used.


Generate Explain Plan in Toad:
Navigate to Toad Options>Oracle>General
Setup the Explain Plan Table
If you do not want to store previous Explain Plan results, disable the option Save previous Plan results in the Toad Options|Oracle|General window

The Explain Plan  button is fourth position from left top corner in form of ambulance sign on the Editor toolbar. Results are then displayed in the Explain Plan tab below the editor.

By Left clicking  on the results in Explain Plan tab we can change the representation of the results in
different Format such as Tree, Plain English and Graphic.

Optimizer:
The optimizer determines the most efficient way to execute a SQL statement after considering many factors.  Optimizer generates a set of potential plans for the SQL statement based on available access paths and hints.The optimizer estimates the cost of each plan based on statistics in the data dictionary for the data distribution and storage characteristics of the tables, indexes, and partitions accessed by the statement.

Oracle uses two types of Optimizer, now days it uses only Cost base optimizer
1. Rule-Based Optimizer (RBO) - It uses a list of rules Oracle should follow to generate an execution plan. Even after the cost-based optimizer was introduced, this method was used if the server had no internal statistics relating to the objects referenced by the statement, or if explicitly requested by a hint or instance/session parameter. This optimizer was made obsolete, then deprecated in later versions of the database.

2. Cost-Based Optimizer (CBO) - The CBO uses database statistics to generate several execution plans, picking the one with the lowest cost, where cost relates to system resources required to complete the operation.

 
Cost:
This is estimated value of resources used for execution of the SQL query.The optimizer calculates the cost of access paths and join orders based on the estimated computer resources, which includes I/O, CPU, and memory.
Though Cost is important in terms of performance tuning, performance tuning based only on the cost
may not get the expected results.
Oracle Applications have defaulted to use the Rule-Based Optimizer where as in 11i this has been moved to use of Cost-Based Optimizer (CBO). CBO uses statistics to decide the execution of SQL statements and internally FND_STATS is a vital part of collecting this data.

Proper configuration of your Oracle server requires that you gather statistics for the objects in your database. Recommendation is that you gather new statistics after the completion of an upgrade or installation, upon loading large amounts of data into the database, and thereafter gather statistics on a regular basis. If you run into a performance issue, gathering schema statistics would be the recommended action to take.

Gather schema Statistics:
This is a concurrent Program that generates the statistics which is used by the optimizer to generate
the Explain Plans.

If you are executing heavy intensive task like Journal Import, etc...its better to run the gather schema Statistics before running the program.If you use Oracle Applications, it is encouraged that you should run the concurrent programs for gathering statistics.

Navigation:
a) Responsibility = System Administrator
b) List > Request > Run.
c) Enter the parameters. This can be run for specific schemas by specifying the schema name or entering 'ALL' to gather statistics for every schema in the database.
d)Submit the gather schema statistics program.

Hints:
Hints let you override the decisions made by optimizer to follow a certain execution plan.  Hints provide a mechanism to instruct the optimizer to choose a certain query execution plan based on the specific criteria.

If you known that using specific index the query will execute more efficiently, then it possible to use
hints in the query to instruct optimizer to use the specific index.


EXAMPLE

SELECT /*+ ALL_ROWS */ DEPT_NAME,DEPT_NUM
FROM DEPARTMENTS
WHERE DEPT_id = 101;
 
The FIRST_ROWS hint instructs Oracle to choose the plan that returns the first n rows most efficiently. For integer, specify the number of rows to return.
 
SELECT /*+ FIRST_ROWS(1) */ DEPT_NAME,DEPT_NUM
FROM DEPARTMENTS
WHERE DEPT_id = 101;


Cardinality:
This is the number of rows that Oracle expects that step in the plan to evaluate. It simply indicates
the number of values that will be necessary to be evaluated for Query execution.

Explain Plan will give you cardinality and bytes at each step of query execution along with the Cost required for execution of the query. Based on this information the Performance tuning of the SQL Queries can be done. Though this requires a certain level of expertise and understanding, but with above basics you are ready to go.