TKPROF's output - how to interpret

Hi List,

I need your advice concerning sql-trace interpreting. Today I received a trace file (tkprof output)
from one of our customers from an other country. I found a bad sql-statement and rewrote it. Initialy,
the statement checked that there is only one record (under some conditions) via count(*) in one
of joined tables (simplified). The second trace file shows that the number of query blocks and fetch
time have been greatly reduced, i.e. disk blocks 15000 -> 0, query blocks 670000 -> 7,
elapsed time 24.75 -> 0.

It is ok, but the trace file also shows that the execution time one of the procedures have been
greatly increased (2.38 -> 28.60 sec). There are no any logical relation between the previous
tuning and the procedure. This procedure doesn't contain any problem sql code inside
(I couldn't find any considerable increases of execution/fetch times of the statements).   

The simplified workflow (sequence of calls) is ....-> the tunned procedure
-> ......... ->

the problem procedure -> .......

So I don't expect that my tunning could make worse the execution time of the second procedure.
The customer checked all things at a test schema which is located as well as a production schema
at a production server. The customer insists that it observed a considerable increase of CPU
loading during the second test which was made after my tunning. There was no any other activity
and so there was no table locks at the test schema, but of course, users did their work at the production schema.
The overall CPU loading of the server was middle

... long explanation, but I hope someone could understand my English

Questions:

1. What things would you check first ?
2. TKPROF's execution times of procedures... Are there any niceties, specialities (not obvious details) of interpreting/treating them?

I understand that probably I didn't give you much info. I don't expect exact answers, but any idea would be very very appreciated. Received on Fri Nov 24 2000 - 12:35:29 CST