Recently we had a challenge where staging table drop and truncates were causing "RO Enqueue" wait events, the problem was magnified by a huge buffer cache on a 3 node RAC Instance. We worked with Support for several weeks and later found that in 9i Instance when you do a drop/truncate of a table which is one of extent and the extent size is not more than half the size of buffer pool then drop/truncate does by flush of objectd, if the number of extents is more than one then it does a full buffer pool scan.
The concurrent program was using a tablespace to create staging tables which had low initial extent storage parameters causing the staging table created to be a multi extent table. by chaging to a tablespace which had bigger initial extent and could accomodate the staging table in one extent, the difference was unbelievable,
Try it out on your development instance by droping/truncating a multi extent table and a single extent table with huge buffer pool (50Gig??)
Comments (1)
Hey Murali, I felt this was a good post. Giving time related data plus some output would help illustrate the case's observations better.
Posted by gaurav verma | March 31, 2007 4:34 PM
Posted on March 31, 2007 16:34