Example of using a tkprof format file

Example: SQL tuning with formatted output file

The first step:-now see the final section of the formatted output file, the summary section

===========================================================

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] 182 | [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.

Internal SQL statements in session.

[B] SQL statements in session.

3 statements explained in the session.

(1). By comparing [A] with [B], we can find out whether there are excessive parsing phenomena. In the example above, we can see that 54 statements were executed in the session, but only 7 times parses, so this is more normal, there is no excess parse phenomenon.

(2). Use [P], [C] & [D] To determine the rate of hit on the database cache

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 the fetch to be smaller than rows, that is, a fetch can fetch multiple rows of data (array fetching),

We can get the query data more efficiently.

This can be done by comparing [E] with [F].

[E] = 6 = number of fetches

[F] = + = number of Rows

We can see from the above information, 6 times fetch total 20 rows of data, the result is not very bad. If you use a well configured arrayfetching, you can get the same amount of data with fewer fetch times, and better performance.

(4). [G] Indicates the number of times the data dictionary tells the cache in order to parse the statement

-This parameter has little effect on performance and is generally not concerned. And this statistic is generally not what we can control.

Step Two – Check statements that consume a lot of resources

===============================================

Update ...

where ...

| Call | Count | CPU | Elapsed |  Disk | Query |  Current | Rows |

|--------- |------- |----- |---------  |------ |--------  |--------- |-------- |

|   Parse |  1 |   7 |  122 |    0 |   0 |   0 | 0 |

|   Execute | 1 |   75 |  461 | 5 |  [H] 297 |  [I] 3 | [J] 1 |

|   Fetch |  0 |    0 |  0 |    0 |   0 |   0 | 0 |

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

[H] indicates that 297 blocks of data need to be accessed to find the data we need to modify.

[I] indicates that our modifications only modify the data in the 3 data blocks

[J] indicates that we have only modified one row of data (other data blocks should be modified for undo, redo information) to search for 297 blocks in order to modify one row of data. Consider whether you need to build an index on the list of the checks!

Step three-see if there is an excess parse phenomenon

==============================

Select ...

| Call |   Count | CPU | Elapsed | Disk | Query | Current | Rows |

|--------- |------ -|---------  |--------- |----- -|-------  -|-------- -|------  -|

| Parse |  [M] 2 |  [N] 221 |  329 |   0 |   45 |   0 | 0 |