30-01-2006

When the data is read from the hard it is called “Physical Read.”

When the data is read from the memory it is called “Logical Read.”

When the data is read from the Buffer Cache (Memory) + Roll back segment it is called “Consistent Read.”

In case of consistent read the o/p contains original data.

There are two important concepts in Oracle:

1. Data Consistency.

2. Data Integrity.

# Data Consistency: Data consistency is defined as the value of the data as it exits at the time of request.

EG: Suppose a request is made by a client c1 at 11:00 AM and in case it takes 5 min to reach to server (exaggerated). If at time say 11:00 AM the data which c1 demanded was changed by another client c2 at 11:02 AM then client c1 will still get the original data on account of Data Consistency provided server has kept the original data intact.

# Data Integrity: It implies to validity of data with respect to natural and business rules.

EG: The Loan given by Bank will never be more than the loan sanctioned by the bank.

Roll Back reading corresponds to Consistent reading.

*: 18000 brains have worked so far on Oracle in 30 years.

When ever a SQL or PL-SQL statement is fired by a user, it involves approximately following processes:

Statement  Parsing  Planning  Execution

# Parsing: Parsing is a process which resolve the references to object, data types, privileges, syntax etc.

In short in parsing checking of syntax and validity of different parameters is made. Checking is made by making Parsed Tree.

Optimizer makes execution plans. There are two types of optimizers:

1. Cost based.

2. Rule based.

Most times same set of commands are fired. For every statement fired 2/3^rd of energy is used for parsing and planning. Using shared SQL area this can be restricted to much less value.

Shared SQL comes to picture only when two statements are exactly similar i.e. even their cases match.

Using # value SQL differentiates between two statements before parsing. There are two types of parsing:

1. Soft Parsing

2. Hard Parsing

Soft Parsing: Oracle makes every fired statement pass through # function and produces a value called # value. This # value depends on the way statement is written and not the text of statement. If the # value belonging to two statements is same, then it bypasses the second statement i.e. second statement is not required to go through parsing and planning process.

Ready to use statement is stored in cursor area or library cache or cursor cache.

Statements stored in Cursor area are identified by their # value.

Using # value parsing and planning processes are avoided.

If two statements are exactly identical including their cases but just differ by some digit used in the form of bind variable then the second statement is not required to go through planning and parsing process.

# Optimizer uses set of 15 hard coded rules for determining the execution plan. Rule based optimizer was the default optimizer in olden version. Cost based optimizer is more intelligent optimizer which considers statistics before drawing the execution plan to make the decision. How ever cost based optimizer requires that statistics should be current otherwise the decision of cost based optimizer would be affected. Cost based optimizer is default optimizer for the current version. Rule based optimizer will be eliminated in future versions of Oracle.

Rule based optimizer goes by text. It is not intelligent. Where as cost based optimizer is intelligent. It just doesn’t look at text but it also takes into consideration the different ways data could be accessed. It also checks the no of ways paths can be optimized