TKPROF-Peformance tunning(Example1)-3

Examples:
Step 1 - Look at the totals at the end of the tkprof output===========================================================
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows

-----------------------------------------------------------------

Parse [A] 7 1.87 4.53 385 [G] 553 22 0

Execute [E] 7 0.03 0.11 [P] 0 [C] 0 [D] 0 [F] 0

Fetch [E] 6 1.39 4.21 [P] 128 [C] 820 [D] 3 [F] 20

--------------------------------------------------------------------------

Misses in library cache during parse: 5

Misses in library cache during execute: 1

8 user SQL statements in session.

12 internal SQL statements in session

.[B] 54 SQL statements in session.

3 statements EXPLAINed in this session.
1. Compare [A] & [B] to spot over parsing.

In this case we have 7 parses for 54 statements which is ok.
2. You can use [P], [C] & [D] to determine the hit ratio.
Hit Ratio is logical reads/physical reads:
Logical Reads = Consistent Gets + DB Block Gets

Logical Reads = query + current

Logical Reads = Sum[C] + Sum[D]

Logical Reads = 0+820 + 0+3

Logical Reads = 820 + 3

Logical Reads = 823
Hit Ratio = 1 - (Physical Reads / Logical Reads)

Hit Ratio = 1 - (Sum[P] / Logical Reads)

Hit Ratio = 1 - (128 / 823)

Hit Ratio = 1 - (0.16)

Hit Ratio = 0.84 or 84%

3. We want fetches to be less than the number of rows as this will mean we have done less work (array fetching).

To see this we can compare [E] and [F].
[E] = 6 = Number of Fetches[F] = 20 = Number of Rows
So we are doing 6 fetches to retrieve 20 rows - not too bad.

If arrayfetching was configured then rows could be retrieved with less fetches.
Remember that an extra fetch will be done at the end to check thatthe end of fetch has been reached.
4. [G] Shows reads on the Dictionary cache for the statements.
- this should not be a problem on Oracle7.

In this case we have done 553 reads from the Library cache.