How to find the common problems from the trace file

1. didn't use the bind variable
A number of very similar statements that execute only once (Executions = 1)

2. Ratio between logical IOs and processed rows is big (greater than 1000)
Normally this is evidence of an inferior execution plan and could also indicate a big number of throwaways due to poor selectivity from the driving table.
Excessive buffer gets consume lots of CPU.
SQL needs to be rewritten to tune the access path. For example, adding additional indexes may help, or changing the statement to use the existing may help in producing an
optimal plan.

3. didn't use the bulk, we need to use the bulk to tun it.
The number of fetch calls equals the number of processed rows

4. High parse time (for example, more than 2 seconds)
break the SQL statement to simpler statement.

5. Ratio between logical IOs and disk reads is low
(less than 2)
SQL needs to be tuned to reduce disk I/Os by improving the access path.

logical I/Os (query + current)

count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.08       0.07          0          0          0           0
Fetch        5     10.30      37.66       8472     890304          0         431
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7     10.38      37.74       8472     890304          0         431

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13975809/viewspace-1390704/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13975809/viewspace-1390704/