How to Gather SQL Resource Consumption Metrics in Oracle

The following video and set of resources are from the webinar broadcast by Red Gate on 8 May 2012. The video above is accompanied by the following downloadable resources: Presentation slides (.pdf) Resource Consumption Metrics Examples (.txt) Workload Repository SQL Report (awrsqlrpt_1_16411_16412.html) SQL Monitoring Report (0-6x2sh0kvzac0t-2.html) ASH Report for BIUAT/biuat1 (ashrpt_1_0505_1117.html) Download all resources (.zip) SQL is utilized to return

The following video and set of resources are from the webinar broadcast by Red Gate on 8 May 2012.

The video above is accompanied by the following downloadable resources:

SQL is utilized to return data via our applications to service user requests. Whether it’s a single customer lookup or a huge month end summary report, the SQL we write must gather the correct data and return it to the user.

Ensuring that the SQL you write can do this in a timely and efficient manner, both now and in the future, requires that you measure and evaluate what resources your query must use.

In this webinar, we’ll cover several methods for how to collect data that show you precisely how your SQL consumes resources:

  • Using AUTOTRACE
  • Using DBMS_XPLAN.DISPLAY_CURSOR
  • Using Report SQL Monitor
  • Using ASH (Active Session History) and AWR (Automatic Workload Repository) data

You’ll learn how to utilize the data to understand the work your SQL is doing and the resources it consumes; identify currently evident performance issues as well as areas that could be problematic in the future; and modify your SQL to use less resources more efficiently while still returning the desired results.

A live Q+A session with Karen Morton will follow the presentation.