Replies to This Discussion

Permalink Reply by Linda Woodhead on August 19, 2013 at 1:30pm

The best way to profile your application in Oracle is sql tracing. The sql trace gives you what statements your application executed, the elapsed time for those, for that they waited on and for how long the waits were. The link I have provided is a good start to read about sql tracing.

• ▶ Reply

Permalink Reply by brian murphy on August 19, 2013 at 1:32pm

Related with profiling, a bit more general, as involves general performance tuning - Automatic Performance Statistics. Even if you don't want to use it, it will give you idea of the important system performance related views and events.

• ▶ Reply

Permalink Reply by David Karplin on August 19, 2013 at 1:34pm

The challenge with most native trace functionality is the performance load they impose, thus making them unadvisable for 24x7 query monitoring of high-volume, mission critical production systems.

There are third-party tools in the market that also provide profile/trace functionality. Some of these tools also impose a performance burden on the system and so will often only take periodic snapshots via polling of the dbms; other tools monitor the database queries non-intrusively (so impose zero/minimal load on the system) and can be used on an "always on" basis.

You need to determine whether the additional performance load is acceptable. If so, then go with the native capability or the cheaper third party tools. If you are working with a high-transaction, mission critical production system, then look at the non-intrusive tools.

• ▶ Reply

Permalink Reply by Peter Wilby on August 19, 2013 at 1:35pm

Also take a look at the enterprise manager. It has a lot of handy tools to dynamically examine and tune your code as it's running. It does have to be configured by your DBA.

• ▶ Reply

▶ Reply to Discussion