What's waiting in my Oracle database?

Needed a script to see what sql statements were waiting and what database object(s) they were waiting on.

Here it is:

select a.sid, a. serial#, a. username, a. status, a. seconds_in_wait, c.object_name dbobject, B. sql_text as statement

from v$session A, v$sql B, dba_objects C
where A. SQL_ID = B. SQL_ID
and C. data_object_id = A.ROW_WAIT_OBJ#
and a. STATUS = 'ACTIVE'
order by a. seconds_in_wait desc;





Configure E-mail Alerts for Oracle Blocking

Been troubleshooting a nasty case of blocking at the grind and wanted a way to know immediately when it occurred.

The following code sample can be set up as an Oracle scheduled job and let you know when blocking occurs and a session is waiting for a configurable amount of time. In the code sample to follow I chose to be alerted on waiting events of five minutes or more.

When setting up e-mail alerts it's good to include information about where the e-mail is coming from (so someone can shut it off), the urgency of the problem, and how to resolve.

Personally I don't care if someone kills the session to get the blocking resolved, but I do want to know the statement causing it so that I can tune it later if it becomes a repeat offender.


Optimize joins on large tables and reduce contention caused by reports

  • Optimize joins on large tables
  • Use as SQL "temp table" equivalents
  • Prevent locking on critical tables
  • Replace costly cursors
  • Optimize long-running reports
We have several reports and nightly processes that don't need up-to-the-second data.  They're run only a couple times a day, but are really bogging down our database.  They're joining several large tables and causing significant waits as they contend for the data with our critical applications.  In researching how to minimize the contention and optimize joins between large tables in Oracle I came across the concept of CLUSTERS and Index Organized Tables.

A CLUSTER in Oracle is an object that houses an Index Organized Table.  It's not a team of database servers.  That was news to me.

The CLUSTER tables all share a common index key which you specify when you create the cluster.  Say I'm joining the ORDERNUMBER column between my ORDERHEADER and ORDERDETAILS tables often and both tables are quite large.  I would then define my index key as ORDERNUMBER - having the same data type as it does in both tables participating in the join.

You build your Oracle CLUSTER, specify an index key on the CLUSTER, then start adding tables which use this key.  You can tweak the size of the key and define your own hash, however the performance benefits of CLUSTER's can be realized immediately without going to this level of effort.

The CLUSTER tables are located in the same physical data block.  So retrieval in terms of disk I/O is very efficient.

Unlike a BITMAP index (also useful for large table joins) there is no requirement for a unique constraint on the column you define as your index key (aka the equijoin column).

I was disappointed that CLUSTER tables cannot be dynamic in nature.  If you're looking for that you may wish to take a look at materialized views in Oracle.  Or look at a TRIGGER.  Even so if your contention issues are caused by day end reporting you can wipe and repopulate your CLUSTER tables on a schedule.  Often this will be less costly then locking data for the duration of lengthy reports and other operations.

The first step is to build your cluster.  For this example I have a join on two large tables:  ORDERHEADER and ORDERDETAILS.  I'm creating my cluster and associated index within the MYDATA and MYINDEX tablespaces respectively under the TALLARICO schema.

Next define your index key. In this case it's the ORDERNUMBER.

Finally start adding tables to your cluster. Each CLUSTER table must have the key you defined included.

My performance issues are caused by end-of-day reporting, so I schedule a job to truncate the cluster tables before populating them again. You'll want to leave your CLUSTER and associated INDEX in place. Use the following to create a DBMS Job that truncates all cluster tables and repopulates them:

Again - Oracle CLUSTER's are efficient ways of storing frequently joined columns between large tables. They have less overhead in terms of Disk I/O for retrieval and often relieve contention because, obviously, they're stashed elsewhere.

In the case of reports that run only a few times a day it may be beneficial to grab the data you need and populate your clustered tables with a scheduled job. You can then run reports or other costly operations against the data in the clustered tables and leave your source data for your mission-critical applications.

I believe you'll find too that traditional UPDATE statements using join's on the clustered tables, given their reduced subset of data and optimized I/O, will outperform cursor-based solutions on the large tables.