Re: Examining the space allocation within individual Extents

To Joel Gary:

Howdo Joel,

The following link you supplied provided me with what I needed:

http://www.orafaq.com/scripts/sql/freesp.txt

In a nutshell, I just needed to use the UNUSED_SPACE procedure from the DBMS_SPACE package. I've now managed to obtain all the information I needed, and can see I'm going to have to rethink the NEXT extent sizes. Think I'm going to drop it to 7MB, which will still divide evenly into the tablespace INITIAL and NEXT extent sizes of 112MB.

Thanks very much for your help.

To Terry Dykstra:

Hi Terry,

The main DBA team here have been doing some web-based 'research' on various forums into LMT's in 8i, and have decided not to go for it. I don't know the full details of what they found, but I think the gist of it was that there was a performance hit in the database over using DMT's in that version of Oracle. I know that's not supposed to be the case with LMT's in theory, and they should result in a performance improvement by bypassing the whole freelists processing hit on the data dictionary, but that's just what I've heard from the guys here.

Admittedly, I should do my own research to form my own judgements, but as I'm principally a developer, not a DBA, this is not currently a priority with me (Reading Tom Kyte's latest book is ;o)).

Just to give you an idea of how much activity our OLTP/OLAP hybrid database sees, there is one particular table that receives an average insert of 22 rows per second, every second of a 24 hour period, 365 days per year. The total database size is 8 terrabytes. I know that's not such a big deal in 10g, but it was pushing the envelope in 8i.

I think the guys here just don't want to take the risk with LMT's in 8i at this point in time. T'd be curious to get some insight into your 8i system that is using LMT's though. How big is the database overall? Is it primarily an OLTP or OLAP environment, or a hybrid of the two? How may people/clients are using it at peak periods, etc.

Cheers.

James Received on Wed Dec 14 2005 - 12:19:41 CST