Real Time SQL Monitoring

here is a feature that I'm excited about:

Real Time SQL Monitoring

http://askdba.org/weblog/2009/09/11gr2-monitoring-real-time-sql-plan-execution-from-oracle-enterprise-manager/

Active Session History has added fields that can track the execution id of a statement as well as the row source (ie line in the explain plan) that the query is executing. For long running queries this can be a way to see where the query is at as well as seeing which row sources are more expensive and for short running queries one can build up a statistical picture of what row sources are expensive.

11g ASH has added the fields

SQL_EXEC_ID

SQL_EXEC_START

that can track and distinguish different executions of the same sql.

11g ASH has added the fields

SQL_PLAN_LINE_ID

SQL_PLAN_OPERATION

SQL_PLAN_OPTIONS

That can track the particular line in the execution plan that is executing

ashmasters.com

for all questions on the site ashmasters.com, please asked them in the comment section on this blog post. Due to the enormously high amount of spam on that site, I am no longer responding to the posts at ashmasters.com (yes there is filter software I can set up but till I do ... )

ASH explodes in Oracle 11gR2

ASH is now at 93 fields in 11gR2, starting from an original 30 in 10gR1

Here is a spread sheet across 10.1.0, 10.2.0.1, 10.2.0.3,11.1, 11.2

http://spreadsheets.google.com/pub?key=t7c5YZ1hD_I25jLa2D6DdLg&output=html

For more information on ASH see http://ashmasters.com

Finding the problem is the hard part

Interesting blog on how finding performance problems takes 80% of the mean time to resolution.

Sybase ASE Performance and Tuning

This is why I like DB Optimizer so much. DB Optimizer let's me find the root problem in seconds on a database.

Correlating SQL to Procedures

Starting in Oracle 10.2.0.3 (ie not 10.2.0.2 and below) it's easy to track what package and/or procedure a SQL execution came from using v$session and or v$active_session_history (ASH) which have the new fields

COUNT(*) SQL_ID calling_code
-------- ---------- ----------------------------------
     2 1xxksrhwtz3zf OE.NEWORDER => DBMS_RANDOM.VALUE
     2 1xxksrhwtz3zf OE.NEWORDER => DBMS_LOCK.SLEEP
    13 1xxksrhwtz3zf OE.NEWORDER
    76 dw2zgaapax1sg OE.NEWORDER
   131 75621g9y3xmvd OE.BROWSEANDUPDATEORDERS
   163 0uuqgjq7k12nf ORDERENTRY.NEWORDER

using SQL like ( http://www.perfvision.com/ash/ashpl2.sql)