Contents Lab128 - Tools for Advanced Oracler Tuning and Monitoring. Reference Guide.

Workload Repository SQL report

In Oracle 10g or higher, the AWR SQL report can be generated for an individual SQL statement and selected time interval. The report can be requested using Main menu Report | SQL Statement | AWR SQL Report. If this menu item is not enabled, then make sure that SQL statement and time interval is selected and the focus is on the screen that has those selections. For example, in Activity Explorer or SQL Explorer, the time interval can be selected in ordinary way on the graphical charts. Then you can select SQL statement making this report accessible. The report can also be requested using popup menus right-clicking on SQL ID (or SQL Hash/SQL Address), then opening Reports submenu. The report can be generated using two formats: text and HTML.

The report is generated by Oracle package dbms_workload_repository, function awr_sql_report_html(). This function needs snapshot IDs supplied as parameters. Lab128 chooses snapshots based on selected time interval picking closest snapshot before and after time boundaries. The discrepancy between user's requested interval and actual one in AWR is reported in the header. The default time resolution of AWR is 1 hour therefore the discrepancy may be substantial. If you need more detailed data about SQL statement, use SQL Statement Details window.