Tkprof to the Rescue

The DBA Manager asked me to give him the SQL for a certain operation in our application. Luckily I knew a lot about it. So I dug into our requirements, looked at the code, and explained what the query did. Then I broke down a simple example of which SQL statements were executed by the application. I was proud of myself.

Then I got another request. The DBA Manager wanted all the SQL for a given report. This was a bit more complicated. The application creates a temporary table. Then it calls an Oracle report which is just a driver for another Oracle report. I knew the type of information this setup retrieved. But it would take a while to extract the SQL. And right now I don't have a lot of time.

The DBA Manager got back to me and told me to run a trace and get the SQL results quickly that way. This was easier said than done. I had some type of bug in my Oracle Report Builder, such that I could not compile in the commands to enable session level tracing. And my database account did not have permission to enable session level tracing either. I got some help from a DBA and an Oracle Reports developer. So I had Oracle generate a trace for a session in which I only ran the report.

Unfortunately I did not have access to the machine that hosted the Oracle database. But a DBA hooked me up with the trace file. This was the first time I had done this. I studied up on the tkprof command, but ended up executing it with all the default options. I was amazed at how simple it was to let the database capture all the SQL. But my instinct told me some SQL from the report was missing. The DBA Manager told me I could just give him the tkprof output. He would take it from there.

I think I need to get a little more practice running traces. And I certainly need to get my Oracle Reports installation fixed so I get make reports changes. Perhaps I can also obtain access to the UNIX box where we run our main Oracle database. Then I can run traces on my own and not need a DBA to get me the trace file each time. I have the feeling that me and tkprof are going to get along very well.