Bobby Durrett's Oracle DBA blog
Oracle database administration - Peoplesoft, and tuning focused
Entry for April 24, 2008

Well, I did a bunch of research on dictionary managed tablespaces and converting them to locally managed.  It looks like the biggest problem is described in Note:273468.1 on Metalink.  If you use locally managed tablespaces on a version of Oracle earlier than 9.2.0.5 you can have lowness from certain DBA_ views.  We have seen this on other 8.1.7 databases with LMTs.


I didn't find anything on Metalink about our hang issues, but I did find this discussion:


http://www.dbforums.com/archive/index.php/t-487931.html


This sounds very similar to our issue, except our case is worse.  The wierd thing is that it doesn't happen every time.  Occaisionally a session that is allocating space will hang while the database queries fet$ 30,000+ times, once for each free extent in the tablespace.  It is just an inefficient way to get that data - why doesn't it just query all the rows in one select instead of having a select for each free extent?  I've addressed it by simply increasing the next extent size of the segments to be so large that the tables will rarely grow in size.  Since the hangs are sporatic this should reduce the frequency down to something acceptable until we can upgrade the database to 10.2.0.3 and move to LMTs.


- Bobby




2008-04-24 19:33:25 GMT
1