Database activity can also be traced through tools native to the database software. SQL Server Profiler or Extended Events can be used to capture expensive queries from either the OnBase Client or Web Client. Hyland Software, Inc. considers any query requiring more than 10,000 reads against the database as a candidate for review. The events RPC:Completed, SQL: StmtCompleted, and SP:StmtCompleted can be used to capture the actual SQL query that was executed by the application. Use the Filters option to limit the trace to queries greater than 10,000 reads.
Long-running queries can then be copied into SQL Server Management Studio and executed in order to capture the I/O statistics and Execution Plan. This information will help determine the Query Plan chosen by the Query Optimizer. Analysis of this data can determine the root cause of the poorly performing query. It may be necessary to consult Hyland Software Database Services Group regarding possible solutions to this type of issue.
For Oracle, an extended SQL trace (10046 Event) can be configured to trace SQL statements. The TKPROF utility can be used to filter this output and allow easier analysis for expensive queries. Oracle's Enterprise Management tools also offer built-in capabilities to review SQL queries generating the most I/O, longest duration, and highest CPU.