Tuesday, August 12, 2008

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.

4 comments:

Sridevi Koduru said...
This comment has been removed by the author.
Sridevi Koduru said...
This comment has been removed by the author.
Sridevi Koduru said...
This comment has been removed by the author.
Sridevi Koduru said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.