Dictionary managed objects

[dsd7038]

1) whats the recommended formulae to calculate which tablespace the relevant table or index should be created in for new tables which dont contain data?
2) for existing tables and indexes I thought I will look at current no of extents and size and order accordingly.


Three tablespaces will exist for creating tables and three will exist for indexes as per below

initial 128K next 1024K PCT Inc 0
initial 512K next 4096K PCT Inc 0
initial 5M next 10M PCT Inc 0

PCTFREE will be 10

NOTE Dictionary managed tablespaces will have to be used not LMT.

Please help

[davey23uk]

set initial=next or you WILL get fragmentation at a tablespace level

[dsd7038]

whats the best way to calculate which tablespace the existing and new table or index's should go into if I use the sizes as per above.

I can look at dbe_segments for current extents and move the greater number of extents into the larger tablespaces.


any other ideas?

[jmodic]

Originally posted by dsd7038
Three tablespaces will exist for creating tables and three will exist for indexes as per below

initial 128K next 1024K PCT Inc 0
initial 512K next 4096K PCT Inc 0
initial 5M next 10M PCT Inc 0

How in the world have you come to those silly numbers for INITIAl and NEXT parameters for your tablespace?

Think about what davey23uk has advised you, then think again what storage parameters will you set for your tablespaces and only then start thinking about which segment should go into which tablespace.

[slimdave]

Why are you splitting tables and indexes into different tablespaces? There's no performance advantage, you know -- that's an urban legend.

[dsd7038]

Sizes were obtained from http://www.bytelife.com/storage.htm. The article appeared to quite good. See for yourself.

I am dividing Tables and Indexes as customer (DBA's)requires this. I have also read various documents which state that by separating tablespaces for table an indexes allows for easier maintenance. Not sure how right or wrong this is.

As we produce on the shelf packages any sizes I recommend will apply to small, maedium and large databases which makes my task more difficult as different customers have different sizes per table and index. I require a general calculation.

[slimdave]

Sizes were obtained from http://www.bytelife.com/storage.htm. The article appeared to quite good. See for yourself.

No, that's a quite bad article. The advice is terrible

Here's some new rules for you with DMT's.

i) PCTINCREASE=0. Always.
ii) INITIAL=NEXT. Always.
iii) For each TS with increasing INITIAL size, make the sizes increase by a factor equal to the DB_FILE_MULTIBLOCK_READ_COUNT -- typically, 32.

I am dividing Tables and Indexes as customer (DBA's)requires this. I have also read various documents which state that by separating tablespaces for table an indexes allows for easier maintenance. Not sure how right or wrong this is.

Your customer's DBA's are working on the basis of myth and legend. There is no benefit in performance or manageability in separating tables and indexes.

As we produce on the shelf packages any sizes I recommend will apply to small, maedium and large databases which makes my task more difficult as different customers have different sizes per table and index. I require a general calculation.

You could choose your smallest tablespace's initial size based on some database size criteria, but still keep the factor of DMRC between the different TS initial sizes.

eg.
No. of customers < 10000, small TS initial size = 32kb
No. of customers > 10000 & < 50000, small TS initial size = 64kb
No. of customers > 50000, small TS initial size = 128kb

[jmodic]

Originally posted by dsd7038
Sizes were obtained from http://www.bytelife.com/storage.htm. The article appeared to quite good. See for yourself.

The article is more or less a crap. If you realy want to read a *good* article about how to size segments in DMTs, read the article "HOW TO STOP DEFRAGMENTING AND START LIVING: THE DEFINITIVE
WORD ON FRAGMENTATION" http://technet.oracle.com/deploy/ava...pdf/defrag.pdf

[DaPi]

Originally posted by slimdave
Why are you splitting tables and indexes into different tablespaces? There's no performance advantage, you know -- that's an urban legend.

I've thought on and off for a long while about this; tho' I don't expect the advantages to be enormous, how about the following?

1) indexes are (much) smaller than tables, so if you use uniform space allocation they perhaps should go in different TS's.

2) this is one way to spread load across disks (hardware striping is better but we can't all do that).

3) (actually an example of #2) Reading a row via index might require (say) 2 I/O's on the index and 1 on the table - if there is a FTS running at the same time, you win if they are on different disks.

I'm sure someone will enjoy shooting that down!

[adewri]

Originally posted by DaPi
I've thought on and off for a long while about this; tho' I don't expect the advantages to be enormous, how about the following?

1) indexes are (much) smaller than tables, so if you use uniform space allocation they perhaps should go in different TS's.

2) this is one way to spread load across disks (hardware striping is better but we can't all do that).

3) (actually an example of #2) Reading a row via index might require (say) 2 I/O's on the index and 1 on the table - if there is a FTS running at the same time, you win if they are on different disks.

I'm sure someone will enjoy shooting that down!

Me too, i have been thinking about it too...

Agreed that the reads and writes are sequential, during select it will go to index first and then to table, and during inserts, first in table then in index. So seperating them on tablespaces on same disk won't do any good.

But if there are simultanious inserts and selects from many users then i think having indexes and tables on seperate tablespaces on seperate disks will get the benefit.

I have read that thread on google where TK, JL and all great ones are discussing this same issue but i still doubt it as there had been no practical explanation or any example to show who was right...

And morever i feel that by keeping indexes and tables on seperate tablespaces, i can manage them better.

One example that comes to my mind.

Say i have a very highly volatile table (lots of transaction going on all the time) having millions of rows in a tablespace. Now if i create an index on this same tablespace, won't this add up to the i/o being carried out by other online users.

Now if i were to create this index on another tablespace on seperate disk totally, at least it would reduce the inserts (but not the reads) on the same disk as the inserts would go to seperate disks altogether during the index creation...

Someone enlighten me...