Performance Tunning

Performance Tunning:-
Its flexibility allows you to make small adjustments that affect database performance.
By tuning your system, you can manage its performance to best meet your needs.

What we tune and what we do:-

•  Performance Planning
•  Instance Tuning
•  SQL Tuning
•  Network Tunning
• Application Tunning

What is execution plan?

It the access path for a query execution.

When a query is executed from the client, it does three things.

1. Parsing
2. Exection
3. fetching

1. PARSING
Parsing has 6 stages, they are

1. systax checking
     - Just checks the syntax
2. Semantic checking
     - Checks the objects are available and accessible.
3. View Merging
     - rewrites the query so that it uses tables instead if views.
4. Statement transformation
     - Breaks the query to simple queries.
5. Optimization
     - Check the best access path with use of CBO or RBO.
6. Query Execution Plan Generation

2. EXECUTION
   Query Execution Plan Execution

3. FETCHING

   Reads the data. It may be physical reads, logical reads and consistent reads.

How does oracle reads data?
There are two ways.
1. sigle blocks read
2. multi-block read

Blocks are read using the following techniqs.

Full tables scan
Index scan
Rowid

What is driving table and probing table?
  Driving table is the one that will be scanned first and the key rows are retrieved. The result will be compared to  the probing table to get the exact result.

for example.

SQL> select e.empno,d.deptno  from    emp e,dept d  where    e.deptno=d.deptno;


if the table emp is scanned first by using the predicate e.deptno and the result is used to look for data in dept table using d.deptno.
Here emp is driving table and dept is probing table.

Optimizer in oracle:-

1.RBO
2.CBO

Overview of SQL Processing

SQL processing uses the following main components to execute a SQL query:

• The Parser checks both syntax and semantic analysis.
• The Optimizer uses costing methods, cost-based optimizer (CBO), or internal rules, rule-based optimizer (RBO), to determine the most efficient way of producing the result of the query.
• The Row Source Generator receives the optimal plan from the optimizer and outputs the execution plan for the SQL statement.
• The SQL Execution Engine operates on the execution plan associated with a SQL statement and then produces the results of the query.

OPTIMIZER_MODE Parameter Values

CHOOSE

• The optimizer chooses between a cost-based approach and a rule-based approach, depending on whether statistics are available. This is the default value.
• If the data dictionary contains statistics for at least one of the accessed tables, then the optimizer uses a cost-based approach and optimizes with a goal of best throughput.
• If the data dictionary contains only some statistics, then the cost-based approach is still used, but the optimizer must guess the statistics for the subjects without any statistics. This can result in suboptimal execution plans.

ALL_ROWS

•    optimizer uses a cost-based approach for all SQL statements in the session regardless of the        presence of statistics and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).

FIRST_ROWS_n

• The optimizer uses a cost-based approach, regardless of the presence of statistics, and optimizes with a goal of best response time to return the first n number of rows; 

                 n can equal 1, 10, 100, or 1000.

FIRST_ROWS

• The optimizer uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows.
• Note: Using heuristics sometimes leads the CBO to generate a plan with a cost that is significantly larger than the cost of a plan without applying the heuristic.
•       FIRST_ROWS is available for backward compatibility and plan stability.

RULE

• The optimizer chooses a rule-based approach for all SQL statements regardless of the presence of statistics.

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_1;

Join Methods:

• Nested Loop Joins
• Hash Joins
• Sort Merge Joins
• Cartesian Joins