Locally Managed Oracle Tablespaces

[mjf]

Hi List

I'm doing some research on moving our Oracle database from dictionary to locally managed tablespaces and would like to hear from anybody who has made this move. I am interested to hear of any performance issues with big tables like F0911 and the like. Our F0911 has approx 9 million records and our biggest table - a custom one has 16 million. I have heard of people running OneWorld with locally managed tablespaces but in all cases they have split their business data into 3 tablespaces of small, medium and large and had uniform extent sizing in each. I presume they are doing this by entering OCM override records for the tables they want to split into their medium and large tablespaces. This seems like a pain to manage. My interest is in setting the business data tablespace to autoallocate and let the database handle the extent sizings of all the tables in one tablespace. Your experiences would be greatly appreciated.

Regards
Marty

 

[mschmidt]

Marty,

We have been running locally managed tablespaces for several years, without
any issues. There should be no performance problems, because even with a
large number of table extents, the whole table segment map can be read in
one read in one hit on the database, unlike dictionary managed tablespaces.

We also use different tablespaces, such as small, medium and large. This
allows us to set uniform extent sizes, which overcomes tablespace
fragmentation issues as all free blocks are useable as they are the same
size. If you allow different extent sizes you will find that occasionally
you will have to de-fragment your tablespaces to ensure all space is
useable. We don't have OCM overrides to manage this, we create the table in
the scheme of PRODDTA, but create it in a different tablespace. This
requires no OCM changes, but does require a bit of set-up in Oracle.

Regards,

Michael Schmidt.
The Yalumba Wine Company
OneWorld: Xe SP19.1
Database: Oracle 8.1.4
Enterprise Server: IBM W2K

 

[mjf]

Thanks for this Michael. Actually you were the people I was thinking about.
I remember when you visited that you said you had been looking at this. Just
a couple of questions:

What do you mean when you say "requires a bit of setup in Oracle". Also how
do you get on with generating custom tables into the correct tablespace? I
suppose you could have a couple of dummy datasources not associated with any
environment pointing to the individual tablespaces which you could use only
for generating tables and indexes?

The last point, what extent sizes do you use for the various tablespaces?

Thanks in advance.

Regards

Marty Fleming
Business Analyst
Richmond Limited

Phone: +64 +6 8786464 Ext 8168
Fax : +64 +6 8780959
Mobile: +64 +21 478946
Email: mailto:[email protected]

OneWorld: Xe SP16.1
Database: Oracle 8.1.6.3
Enterprise Server: Compaq Proliant 8500R W2K





OneWorld: Xe SP16.1
Database: Oracle 8i
Enterprise Server: Compaq Proliant 8500R W2K

 

[patrickjolliffe]

Re: RE: Locally Managed Oracle Tablespaces

I have attached a document we found useful.
Executive summary, create LMT with extent sizes of either 128KB, 4MB or 128MB. Put each segment (tables or index) into a tablespaces such that it will take up less than 1000 extents. We tend to stick all our empty tables on 128K, the huge ones (F0911) on 128MB and the rest on 4MB.

Suggest a quick search on http://asktom.oracle.com - an excellent resource. Search on the following terms, fragmentation, extent, lmt.

 

[mjf]

RE: RE: Locally Managed Oracle Tablespaces

Thanks Patrick. I hadn't come across this resource before. Looks pretty
good.

Regards

Marty Fleming
Business Analyst
Richmond Limited

Phone: +64 +6 8786464 Ext 8168
Fax : +64 +6 8780959
Mobile: +64 +21 478946
Email: mailto:[email protected]

OneWorld: Xe SP16.1
Database: Oracle 8.1.6.3
Enterprise Server: Compaq Proliant 8500R W2K





OneWorld: Xe SP16.1
Database: Oracle 8i
Enterprise Server: Compaq Proliant 8500R W2K