Top activity

At any point in time, it is possible to go to Performance | Top Activity and display a view of the top SQL statements as well as top sessions. You can drag the highlighted bar to select the particular time required.

This screen looks similar to the drilldown from the Average Active Sessions section on the performance home page. However, the difference is that all the wait events can be seen against the time in this graph, on the Top Activity page. This data is fully sourced from the ASH information in the database. The text in the following screenshot may not be fully readable, but it has been included for the purpose of illustration:

Another difference is the presence of the Run ASH Report button on this page. This allows you to generate an ASH report for a period as small as one minute. The beauty of ASH is that it allows you to examine database activity that happened in any small period of time you select.

Enterprise Manager Cloud Control 12c also allows you to examine the SQL statements issued by Enterprise Manager itself. This is accessed by going to Performance | SQL | Cloud Control SQL History. You can select the module as either administration, real-time monitoring, or repository to display the most recent 50 statements. You can also enable or disable SQL Trace on this page.

The SQL Access Advisor utility is another important part of the SQL tuning pack. This is accessed by going to Performance | SQL | SQL Access Advisor.

The SQL Access Advisor goes through SQL statements in the database cache or in a SQL tuning set (a collection of SQL statements), and will give you suggestions for creating new database objects or dropping existing objects to improve the performance of the workload.

These suggestions may include the creation of new B-tree indexes or Bitmap indexes, materialized views (for maximum query rewrite usage), materialized view logs (for fast refresh), or the creation of partitions (hash and interval only) for tables, indexes, or materialized views.

This advisor analyzes the entire workload, and not just independent SQL statements to arrive at its conclusions, plus it also considers the impact of the new access structures it recommends on DML operations. For example, a new index may speed up an existing query, but if there are a lot of insertions/updates/deletions happening on the same table, the Oracle database has to maintain the index to cater for the DML and this may actually degrade the overall performance; so this must be taken into consideration.

Storage, creation, and maintenance costs are all considered by the advisor before it gives any recommendations. New indexes or new materialized views are simultaneously considered, as well as a combination of both.