I order to check for the performance, I normally check for the disk reads and the milliseconds columns of the trace file.
These are the only 2 things I am looking so far. But would like to learn from you
1) How to interpret this trace file and understand all the sql statements shown in the trace file.
also at the end of the trace files, it shows totals for all recursive sql..
2) What are these recursive sql's and non-recursive sqls'
After all the sql statement you may find the following values
COUNT==>How many times the statement was parsed or executed and the no of fetch calls issued for the part stat.
CPU==>Process time for each phase in seconds.
if the stat was found in the shared pool it is 0 for the parse phase.
ELAPSED==>This is not much useful since other process also affect ellapsed time.
QUERY==>Log Buffers retrieved for Consistent read normally for a select stat.
CURRENT==>L buffers retri in current mode.
ROWS==>Rows processed by the outer stat.Select stat this is shown for the fetch phase and for DML stat it is shown in the execute phase.
DISK==>Phy data blocks read from the DB files.
This stst may be very low if the data was buffered.
Libr cache misses==>this states that no of times the stat was not found in the Lib Cache in the parse and execute phase.if there was no miss then this statistcis wont appear.
User==>Id of the last user to parse the statement.
Execution Plan==> when you specify the EXPLAIN parameter with TKPROF it then works out the access path for each sql statement traced and included in the output.
Optimizer Hint==>used to indicate the Optim hint which was used during the execution of the stat.If there was no hint it will show optimizer mode was used.
Recursive SQL==> From Oracle Docs
Sometimes in order to execute a SQL statement issued by a user, Oracle must issue additional statements. Such statements are called recursive calls or recursive SQL statements. For example, if you insert a row into a table that does not have enough space to hold that row, Oracle makes recursive calls to allocate the space dynamically. Recursive calls are also generated when data dictionary information is not available in the data dictionary cache and must be retrieved from disk.
If recursive calls occur while the SQL trace facility is enabled, TKPROF produces statistics for the recursive SQL statements and marks them clearly as recursive SQL statements in the output file. You can suppress the listing of recursive calls in the output file by setting the SYS statement-line parameter to NO. The statistics for a recursive SQL statement are included in the listing for that statement, not in the listing for the SQL statement that caused the recursive call. So when you are calculating the total resources required to process a SQL statement, you should consider the statistics for that statement as well as those for recursive calls caused by that statement.
Now you know the meaning of Non recursive right ?
If you still have doubt please post it.
Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it