I'm hoping to avoid some work on my part. (I suggested it, the dbas want it, so by default I'll wind up building it if it doesn't exist...)

First some background. You have an application, for instance a website, that runs against Oracle. The DBAs can turn on, per Oracle connection, detailed logging. They do NOT want to turn on detailed logging on the whole site (the database would turn into a smoking hole in the ground), but doing it for one connection at a time is fine. The logfile is in a very detailed but documented format which a variety of standard tools know how to read and give interesting figures on.

We want to do something non-standard.

In our application each Oracle connection is used for one page at a time. The idea is that if we issue a silly command like SELECT :1 AS PAGE_START FROM DUAL at the start of each page and then SELECT NULL AS PAGE_END FROM DUAL at the end of each page, then we could parse the log and get detailed information per page. Here is some of the information that we might want to get:

• What database events are users actually waiting on? (Statspack gives aggregate statistics for waits, but that doesn't differentiate between things that users waited on and things that background Oracle processes waited on. Logfile sync, for instance, is a big wait event but users don't notice it.)


• How long are specific pages taking to return? Where is that time going?


• When certain pages ran slower than normal, what database events were they bottlenecked on? (We sometimes get reports that things are slow, and then later that they are fast, and we have no idea what changed. Aggregate statistics don't help pinpoint that, and by the time the report gets to us the problem is usually long gone.)


• Which pages happen to hold locks for long times? (We can tell now when pages are blocked for long times, but we have no tools to figure out what did the blocking.)

I'm sure that if I build it, people will dream up other things that they might want to know once there is a way to get detailed information like this. (For instance as application developers we might ask what fraction of the time caches avoided the database, and where we spend lots of time in the application between queries.)

I'm sure that if someone else has built it, it would save me a lot of work and they are likely to have thought of things that you can get from a tool like this that we'll take a while to figure out. (They'll also have thought about how to present the data, which I haven't.)

As for starting points, I've seen Oracle::Trace on CPAN. According to the author it doesn't handle accounting for recursive queries properly. That doesn't sound like a major problem from my perspective.

Cheers,
Ben

Each database connection is reused for many different pages. (One at a time, sequentially.) DBAs can turn on logging per database connection. The logs give very detailed information. Too much to hope to read by hand.

Standard tools exist for extracting aggregate information out of the log. They tell you things like, "Which queries used the most CPU in this time period?" They give insight at the level of individual SQL statements, not at the level of application performance. For instance if a particular query is executed very often, they have no clue about whether that query is on a frequently accessed page (reasonable) or is on a less often accessed page that happens to call it a ton of times (might be something to improve). Or if a particular query is usually fast but sometimes is very slow, you'll just see a moderate average and not see the evidence that sometimes it is bad, and that this tends to happen when a particular cron job runs.

There exist other tools to collect aggregate information about the database without logging each connection. Correlating what this says with application performance is even more of a crapshoot.

I'm talking about a tool that addresses that. It looks for hints from the application about when it starts/stops producing a page, and then extracts a picture of what causes perceived application performance. And extracts it in a form that can be related to what everyone else talks about.

Cheers,
Ben

What you're talking about hand-carving does not give a detailed profile of what is happening in the database.

As for the portal tools, I haven't investigated, but I would be surprised if they came with the same kind of profiling that I'm talking about. Certainly if they do it with Oracle they are only going to get some of that information if they tightly integrate with Oracle. (eg When are locks held, what database events are you waiting on.) And they can't turn this on by default because if they do and hit load, the database will melt.

Regards,
Ben

For the record, I was not just interested in free tools. I think we'd be willing to pay for something like this. Particularly if that got us a tool that thought of things I haven't and that saves me time.

Anyways I'm going ahead and trying to figure out how to build it, and what interface to use to present information.

Cheers,
Ben

I've seen them refer to them upon need. I see signs that they are ongoing training. They seem to talk intelligently about what is happening in the database. What they say fits what I know. When there are problems, they seem to be able to troubleshoot them successfully. When application people want to learn more about how Oracle works, they happily recommend books that seem to have lots of good information.

OK, this applies to some of the DBAs. Some are obviously less competent than others, but the one that this project would be for fits the above description very well.

I lack the knowledge to tell more than that. It would take considerable effort for me to learn enough to really test whether they know what they are doing. I have other responsibilities.

I have read a couple of the books that they recommend though. And judging from what I learned there, they seem to be doing the kinds of things that they should be doing.

Beyond that, as long as I don't want to become a DBA myself, I have to assume. This assumption seems reasonable to me.

Cheers,
Ben