I would like to add one of my other observation on the query. Posted by Unknown at It is also designed to help analyze the trace files generated by SQL tracing. Even the World Wide Web comes up with little information on using this tool and troubleshooting its installation. Click here The quickest way to capture the SQL being processed by a session is to switch on SQL trace Set the event for a representative period of time. The above book excerpt is from: Several of the key improvements are discussed as follows:

You are commenting using your WordPress. This brings us to the most important point in our guess work. You are commenting using your WordPress. Given the relatively small amount of work reported in the traec file, the extreme peaks of in the disk times suggest problems due to sharing a disk cabinet, and the peak in the latch wait time suggest intermittent CPU starvation exaggerating the effect of sleeps. Comment by rakeshchemikala — July 24, 2: There are cases where we can observe current mode during select statements but not at this high number. Hi Mohamed, I would like to add one of my other observation on the query.

Ideally you have to parse 1 and execute many. As a “general” rule, it is not necessary to backup online redo logs. Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. Stripe disk space over sufficient disks.

What if you try a different arraysize set arraysize Could you please suggest what might have caused this scenario. Newer Posts Older Posts Home. So, to the list of candidate scenarios where index rebuilds can be useful, I would add SLAs. So, once again, tkprof is “lying” in that it is not presenting the complete information.


The next 15 executions would have a different Execution Plan from the first 5 executions. Without any hesitation, I will take into account your suggestion. You’ve done a very large purge of records on a table that is frequently undergoing FullTableScans. We want fetches to be less than the number of rows as this will mean we have done less work array fetching.

tkprof case study

One key consideration is that the “perceived” cost of Index Block Splits is not significant. All rights reserved by Burleson.

So, have you noticed how much of information we can diagnose simply by looking at those 4 lines of trace file? Here’s the tkprof of the second slice.

Here are use of various events during tracing. Notify me of new comments via email. Unfortunately, this tool seems to be largely unknown or at least unused given the lack of hits when searching MOSC. First of all what can we diagnose just with the following bit of information extracted from the tkprof trace?

tkprof case study

Tkprrof, Rakesh Comment by rakeshchemikala — July 25, 2: I agree that there are lot of full scansI am also checking for proper indexes for these tables, but none of the full table scans have taken significant time as they are smaller stidy.


As a rule, I, too, dislike a general rebuild dtudy indexes unless the requirements above are met. The first fundamental remark I can point out is that you are doing a parse for each execution. Please let me know your observations on having lot of fetches though we have tried arraysize 15 default sqlplus and as well. Several of the key improvements are discussed as follows: One of our end users complains about a query that was suffering a performance pain.


Comment by rakeshchemikala — July 25, 2: Index block splits are for DBAs and computer science classes. It is clear that reviewing those two table scans will help enhancing the performance of your query. These indicate signficant activity. This is a clear symptom of and a pointer to the presence of aggressive full table scans that should be investigated 2.

Such a case would be where tkprof is “lying” to me — but it is really only providing me partial data. Tracing was executed for an application server process running for 30minutes, probably running the same 40 SQL statements 20times a total of execution in those 30minutes.

This Oracle documentation was created as a support and Oracle training reference for use by studt DBA performance tuning consulting professionals.

tkprof case study