Pete Finnigan's Oracle Security Forum


    
      
        Pete Finnigan's Oracle Security Forum
        (http://www.petefinnigan.com/forum/yabb/YaBB.cgi)
      

        Oracle General >> General Oracle questions >> Oracle Session Resource Profiler
        
(Message started by: Pete Finnigan on Nov 28^th, 2005, 5:03pm)

Title: Oracle Session Resource Profiler
Post by Pete Finnigan on Nov 28^th, 2005, 5:03pm

Oracle SQL traces with wait events can be of a lot of help to understand certain performance problems, because it allows you to calculate the response time of certain problems in the database. After all, response time = service time + wait time, and in default SQL traces you miss out on the latter.

When you use event 10046 SQL tracing to analyse a (performance) problem, you need some kind of reporting tool to make sense of the data you just gathered and add up all the response time information. There are several tools available that do just that:

tkprof
Available with your Oracle installation. In Oracle9i I find its information a bit limited.

trace analyzer
Available from Oracle Metalink (note 224270.1). It creates a nice report, but to do that you need to load your trace into you database. That is not something you want to do with a 100M trace file. Also it does some miscalculations.

Hotsos Profiler
A commercial product available at http://www.hotsos.com/. After uploading your trace file to their website, it creates a very nice HTML report that allows you to drill down into the problem and is also accurate. You pay per trace file.

Recently I discovered OraSRP (http://oracledba.ru/orasrp/). This tool does a lot that Hotsos Profiler does, except it's free and open source. To run it, it starts a HTTP server on your local machine, after which you can select the trace file (if it's in the proper directory) and generate the report. You don't need to run the http server for very long. Just save the report as html and stop it.

The report of OraSRP allows you to drill down into a performance problem the way you would do with Hotsos Profiler.

I thought you might want to know.