Access articles and resources written by some of the best Oracle brains in the world.
How to Gather SQL Resource Consumption Metrics in Oracle
Presented by Karen Morton
Tues 8th May 2012
This webinar has now finished, but feel free to click the button above to watch the recording.
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 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 follows the presentation.
Oracle ACE, member of the OakTable Network, Oracle performance optimization (SQL tuning and the optimizer) specialist, co-author of "Beginning Oracle SQL", "Expert Oracle Practices: Database Administration" from the Oak Table and "Pro Oracle SQL". Expert on All Things Oracle.