Cary Millsap recently published a paper “Mastering Performance with Extended SQL Trace” describing how to use Oracle trace to assist with troubleshooting the performance of database queries. As with all of Cary Millsap’s papers it is superbly written, presenting very detailed information in a clear and understandable way. (and yes I do have a DBA crush ;-)) It discusses how you can automate the tracing of specific sessions on the database, and requiring the application to be appropriately instrumented.

OBIEE comes with a very useful usage tracking feature. For information about it and how to set it up see these links: http://obiee101.blogspot.com/2008/08/obiee-setting-up-usage-tracking.html http://www.oracle.com/technology/obe/obe_bi/bi_ee_1013/usage_tracking/usage_tracking.htm http://108obiee.blogspot.com/2009/07/obiee-usage-tracking-setup-and-cloning.html Usage Tracking captures the logical SQL of queries in a column called QUERY_TEXT in the table S_NQ_ACCT. However, out of the box this column is defined as 1k (1024 bytes) long. In some situations this will limit its usefulness because the text will be truncated if necessary when it’s inserted.