How to view long-running SQL Processes with Oracle 10g Enterprise Manager

To see the log running queries:

• Open the Oracle Enterprise Manager 10g Web-interface (typically found at http://DBServer:1158/em/console)
• Click on the Performance Tab
• At the bottom of the page, there is a Top Activity link
• In that page, there will be a Top SQL link. If you click on the SQL ID, it will bring up the statement that was executed.
• If it is a select, there will be a Plan tab, it will show how expensive the statement is.
• If it looks like a TRIRIGA query, either coming from a manager, portal, or GUI query section, the query should be refactored and/or redesigned to allow for better performance.
• The report can normally be easily identified by the tables or Module-views it selects from, so for example, if the statement is question looks like:

SELECT ROW1, ROW2, ROW3

FROM T_TRIBUSINESSOBJECT

WHERE ROW1 like ':1'

• Look in the report manager for a report/query/graph, etc, that is looking at the TRIBusinessObject BO.
• Test different reports by opening the report, and clicking the "Export SQL" link. The sql can be verified against what Oracle is reporting back

For more information on how to trace long SQL from long running queries, please refer to my earlier post: http://www.tririga.info/2008/01/analyzing-long-running-sql-queries.html

Gathering Database Statistics

In order to improve performance TRIRIGA recommends gathering database statistics on a daily basis. Normally this is done as part of the daily cleanup jobs. If you notice poor system performance on a day with particularly heavy usage or during a data load you can manually gather statistics by running the following scripts.

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('TRIDATA',DBMS_STATS.AUTO_SAMPLE_SIZE);

If 2.1.9.X is being used, also run:
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('TRIRIGA',DBMS_STATS.AUTO_SAMPLE_SIZE);