TKPROF-Peformance Tunning(Example2,3)-4

STEP 2 - Examine statements using high resourcee
===============================================
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] shows that this query is visiting 297 blocks to find the rows
to update[I] shows that only 3 blocks are visited performing
the update[J] shows that only 1 row is updated.
297 block to update 1 rows is a lot. Possibly there is an index missing?


STEP 3 - Look for over parsing
select ...
call--------count------cpu-------elapsed------disk----query----current---- rows--
--------------------------------------------------------------------------------------
Parse------[M] 2 ----- [N] 221--- 329---------0-------45---------0--------- 0--
Execute---- [O] 3----- [P]9-------17----------0--------0--------- 0----------0--
Fetch------- 3----------6----------8-----------0-------[L] 4-------0-------- [K] 1
Misses in library cache during parse: 2 [Q]

[K] is shows that the query has returned 1 row.
[L] shows that we had to read 4 blocks to get this row back.This is fine.
[M] show that we are parsing the statement twice - this is not desirable especially as the cpu usage is high [N] in comparison to the execute
figures : [O] & [P]. [Q] shows that these parses are hard parses.

If [Q] was 1 then the statemnent would have had 1 hard parse followed by a soft parse (which just looks up the already parsed detail in the library cache).
This is not a particularly bad example since the query has only been executed a few times.

However excessive parsing should be avoided as far as possible by:
o Ensuring that code is shared:
- use bind variables - make shared pool large enough to hold query definitions in memory long enough to be reused.