Performance Tuning Concepts

Below are the Basics Concepts for Performance tuning Beginners

Blocks changed per Read %:

The % Blocks changed per Read statistic indicates all blocks are retrieved for update.

Blocks Changed per Read % = (Block Changes + 100/ Logical Reads)

Recursive Call %:

Sometimes, in order to execute a SQL statement issued by a user, Oracle must issue additional statements. Such statements are called recursive calls or recursive SQL statements. For example, if you insert a row into a table that does not have enough space to hold that row, then Oracle makes recursive calls to allocate the space dynamically. Recursive calls are also generated when data dictionary information is not available in the data dictionary cache and must be retrieved from disk

1. Redo size: The amount of redo generated during the report.

2. Logical Reads:

 Calculated as

Consistent Gets XE "Consistent Gets" + DB Block Gets XE "DB Block Gets" = Logical Reads

Block changes:

The number of blocks modified during the sample interval.

Physical Reads:

 The number of requests for a block that caused a physical I/O operation.

3. Physical Writes:

 Number of physical writes performed.

User Calls:

Number of user queries generated.

4. Parses: The total of all parses; both hard and soft.

5. Hard Parses:

The parses requiring a completely new parse of the SQL statement. These consume both latches and shared pool area.

6. Soft Parses:

Soft parses are not listed but derived by subtracting the hard parses from parses. A soft parse reuses a previous hard parse; hence it consumes from far fewer resources.

7. Sorts, Logons, Executes and Transactions: All self-explanatory.