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
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