Printable View
Guys,
Right now, I am only able to justify the strength of using data dictionary tablespace is just for backward compatibility which is not strong enough.
I wonder if there are other advantages of using dictionary managed tablespaces over locally managed tablespaces.
Sometimes I do feel confused myself over this thing especially for oracle8i and later.
Please help. Thanks in advance.
Ric
You should always use Locally Managed Tablespaces where possible. It is highly likely that Oracle10i will not allow dictionary management at all. The only reason to stay with dictionary management is if you're living in the dark ages. Oracle9i Rel 2 allows the SYSTEM tablespace to be local also.
When using Oracle9i you should also use Automatic Segment Free Space Management.
I am not so sure about Automatic Segment Free Space Management. It´s been proved by some known peep that this feature is not as good as it sounds. In fact slows down queries and DMLs in non-rac environment
I have seen problems reported on Metalink forums about this feature as well
Experts,
Sorry perhaps I am not clear enough in explaining what happens. Right now our client is using Oracle8.1.7 due to JDE OneWorld limitation. So I am asking this in the light of 8.1.7.
What I understand is Oracle never strongly recommended using locally managed tabs in Oracle 8i where they strongly recommended people to use one in Oracle9i. I want to know whether, from your expertise, there are problems or limitations on using this feature in Oracle8.1.7.
Thanks.
Ric
IMHO, you are wasting your time with Dictionary Managed tablespaces. I have been using LMTs in 8.1.5 and 8.1.7 databases for over 2 years without one single problem.
Yeah I agree with Jeff, you really should jump on the LMT band wagon.
Oh and Pando, that's a bit concerning about the Auto Segment Management slowing down queries. I'm in the process of migrating a large 8.0.4 datawarehouse to Oracle 9i, I wonder how much of a difference it'll have...
Later,
Do a simple test to check the oracle release is bug free with the LMT.
I found a bug in LMT in 9i.
Create a LMT with initial and next size 16K.
Create a table on this new tablespace.
Insert rows using a PL/SQL block so that number of extents can reach around 30000. Then truncate the table. Wait and watch how much time Oracle needs to truncate the table.
If the table is truncated within 5 to 10 minutes, then you are safe and you can use LMT. If not, do not use LMT.
With all due respect, this is one of the most silly tests I've ever heard of.Quote:
Originally posted by tamilselvan
Do a simple test to check the oracle release is bug free with the LMT.
I found a bug in LMT in 9i.
Create a LMT with initial and next size 16K.
Create a table on this new tablespace.
Insert rows using a PL/SQL block so that number of extents can reach around 30000. Then truncate the table. Wait and watch how much time Oracle needs to truncate the table.
If the table is truncated within 5 to 10 minutes, then you are safe and you can use LMT. If not, do not use LMT.
First, I don't see what bug you haver found with LMT in 9i. At least not from the above description. Can you be more specific about that bug of yours?
Second, with LMT you don't specify INITIAL and NEXT at all, you either specify UNIFORM extent size or AUTOALlOCATE.
Third, no one in his right mind will have segments of 30.000 extents, neither in LMT nor in DMT. So what is the point of that test?
Fourth, just out of curriosity, have you tried to truncate a table of 30.0000 extents in dictionary managed tablespace? What was the timing? What is the whole big deal with those magic facts/numbers (LMT + 30.000 extents + TRUNCATE + 5-10 minutes)?
Fifth, even if the above test proves there is/there is not a specific bug (that I can't see) with LMT, it does not proove that "then you are safe and you can use LMT" at all - there could be tens of bugs related with LMT that this test doesn't even touch! But this is not a point - there could be tens of bugs related to DMT that noone of us is avare of too! So Sricky, don't waste your time, switch to LMT wherever possible and whenever you are sure that known LMT bugs are not applicable to your specific situation/bussiness rules.
[Edited by jmodic on 08-27-2002 at 05:00 PM]
Yes. I tested LMT and Dictionary managed tablespace after creating 30000 extents for a table.
Oracle 9i took more than 12 hours to truncate a table. Where as in DMT, it took 10 minutes.
A test is a test. There is no silly test in IT.
Yoda nad vendechukp should definitelly put that into their library of famous quotes :D.Quote:
Originally posted by tamilselvan
A test is a test. There is no silly test in IT.
and unfortunately some tests lead us to a concusion that may not be true.
I'm not sure of anyone who'd create a segment with 30,000 extents... unless they seriously stuffed up.
Oracle suggests that LMT won't suffer any performance issues until a segments extents > 1000. I don't think in ANY of my databases have segment > 100 extents.. never mind 30,000.
Later
That means LMTs are not efficient if # of extents exceeds 1000 ? If this is the limitation how LMTs are better than DMTs ?Quote:
Originally posted by grjohnson
Oracle suggests that LMT won't suffer any performance issues until a segments extents > 1000.
*Curious*
That limitation is kinda valid limitation on even DMTs, so what am I gonna buy switching to LMTs ? Anyway AUTOALLOCATE is not an option for many from consideration of big and small tables in one tablespace... Even AUTOALLOCATE option is not perfect if you have unevenly distributed data across partitions. Say I have an LMT with Unform extent size 1M, and my table partitions have data unevenly distributed (100rec in P1,1000rec in P2 and 1Million rec in P3). So, in P1 and P2 space is wasted.
In whatway LMTs are efficient if no of extents is limitation ?
When you extents get to 1000, create a new LMT tablespace with a larger UNIFORM block size....Quote:
Originally posted by sreddy
In whatway LMTs are efficient if no of extents is limitation ?
Worried about space wastage.. why not use the followingQuote:
Originally posted by sreddy
Say I have an LMT with Unform extent size 1M, and my table partitions have data unevenly distributed (100rec in P1,1000rec in P2 and 1Million rec in P3). So, in P1 and P2 space is wasted.
1. Use HASH paritioning
2. Create your table paritions in different UNIFORM sized LMT's
CREATE TABLE TEST (
ID VARCHAR2 (40),
DAYC NUMBER (2))
PARTITION BY RANGE (DAYC)
(PARTITION DAY1 VALUES less than (2)
TABLESPACE record_data_sml PCTUSED 60 PCTFREE 1 STORAGE (PCTINCREASE 0),
PARTITION DAY2 VALUES less than (3)
TABLESPACE record_data_lge PCTUSED 60 PCTFREE 1 STORAGE (PCTINCREASE 0));
Table created.
SQL> drop table cfa_test;
Table dropped.
I don't see the real problem here..
[Edited by grjohnson on 08-27-2002 at 11:20 PM]
well 1000 is prob not the limit because I have 3 or 4 datamart staging tables with 5000 ~ 8000 extents with no problems
In such a case (and if you are worried about wasting space, you would have two LMTs - one with uniform extent size of 1M and another wits smaller extent size, say 64K or 128K. So you would put your partition P3 in the first LMT, while P1 and P2 would be in the second LMT. That is common sence.Quote:
Originally posted by sreddy
Say I have an LMT with Unform extent size 1M, and my table partitions have data unevenly distributed (100rec in P1,1000rec in P2 and 1Million rec in P3). So, in P1 and P2 space is wasted.
About the recomendation that segments with a very large number of extents (1.000 is often quoted as a "magic limit", even in docs) should be avoided in LMT (I would say the same recomendation is valid also in DMT, if not even more!) - this is because of the fact that the space reserved in segment header for the extent bitmaps becomes insufficient and additional "bitmap blocks" must be allocated in some other extent (or in more of them) "somewhere in the middle" of the segment, thus inducing more complex bitmap management and additional I/Os.
But I'm stressing it again, we are talking about extremes here. Segments with such number of extents in a production environment would simply indicate that physical design was probbably screwed for those segments and that they would need to be rebuild.
[Edited by jmodic on 08-28-2002 at 03:21 AM]
Thanks for the input. I know, I can tak care of these issues by creating multiple tablespaces with relevant extent size. All I was stressing here on so called magic # 1000 and the performance degradation if extents are couple of grands. How efficiently system manages complex bitmapping if the # of extents exceed 1000 and say 4000 or so. Anybody have any experiences with LMTS having more than 5000 or 6000 ??
Pando: do you see anything downside in performance in your system having few thousands of extents ?
hi sreddy
they are staging/temporary tables, basically around 10 million rows is loaded (takes around 10 minutes) then aggreated data are extracted then before next load the table is truncated. To be honest I dont see any performance problems
I can't say I can give any real-life experience comments regarding the impact of large number of extents in LMT on performance, but as far as I understand the concepts behind it, my guess would be:
- Performance impact on queries: I can't see any. The number of extents can have no performance impact on queries, neither in LMT nor in DMT. That is a well prooven fact.
- Performance impact on DMLs: I can't see any, *except for few rare ocasions*. Those rare ocasions are, when insert/update causes Oracle to allocate a new extent to accomodate a new/changed row. I would consider this as a negligible impact.
- Performance impact on DDLs: The performance impact on ths field is of course concentrated on the performance of DROP/TRUNCATE (or probably only on TRUNCATE?) such a segment. And, yes, there will probbably be performance impact when doing that. But then again, how often do you realy drop/truncate a segment with such a huge number of extents in praxis?
[Edited by jmodic on 08-28-2002 at 10:40 AM]
Thanks Pando, thinking about OLTP environment. I better limit the number of extents... here I'm talking about 100 + million row tables...
Excerpt from the following Oracle Doc links:
Avoid dynamic extension in dictionary-mapped tablespaces. For dictionary-mapped tablespaces, do not let the number of extents exceed 1,000. If extent allocation is local, then do not have more than 2,000 extents. Having too many extents reduces performance when dropping or truncating tables. I don't think this is an issue from Pando experience... having many extents for staging tables.
http://technet.oracle.com/docs/produ...20_io.htm#3454
http://technet.oracle.com/docs/produ...20_io.htm#3480
http://technet.oracle.com/docs/produ...0_io.htm#24620
I guess this 1000/2000 is just a number we got go by, there might not be noticable perforamance degradation :) if its not too many...5000 or 10000+
[QUOTE]Originally posted by jmodic
About the recomendation that segments with a very large number of extents (1.000 is often quoted as a "magic limit", even in docs) should be avoided in LMT (I would say the same recomendation is valid also in DMT, if not even more!) - this is because of the fact that the space reserved in segment header for the extent bitmaps becomes insufficient and additional "bitmap blocks" must be allocated in some other extent (or in more of them) "somewhere in the middle" of the segment, thus inducing more complex bitmap management and additional I/Os.
And I absolutly agree with Jriy.
Exists only ONE limitation on a number of extents --> DB_BLOCK_SIZE
or more simple, how many blocks oracle have to allocate for a segment header.
Becuase server cannot allocate a segment header in neast blocks never. It have to
create linked list from segment header blocks and, as result, increase number of I/O
opeartion for works with the table/partition/index (segment).
--------------------------------------------------------------------------
Note: doesn't matter we use LMT or DMT.
--------------------------------------------------------------------------
[Edited by Shestakov on 08-28-2002 at 02:10 PM]
Quote:
Originally posted by Shestakov
And I absolutly agree with Jriy.
Exists only ONE limitation on a number of extents --> DB_BLOCK_SIZE
or more simple, how many blocks oracle have to allocate for a segment header.
Becuase server cannot allocate a segment header in neast blocks never. It have to
create linked list from segment header blocks and, as result, increase number of I/O
opeartion for works with the table/partition/index (segment).
--------------------------------------------------------------------------
Note: doesn't matter we use LMT or DMT.
--------------------------------------------------------------------------
So, performance degradation (if at all there is) boils down to howmany trips(I/O) oracle needs to make towards extent bitmap to find the free space/free contiguous blocks available. I don't think it needs to be considered an issue for performance.(unless there are too many deletes, truncates, drops of objects on the tablespace).
1) I wrote on only about DATAFILE HEADER BLOCKS,
2) i wrote (mostly) about SEGMENT HEADER BLOCKS.
In first case we probably can see perfomance degradation only via microscope, you right.
but in second case, this is question. SEGMENT HEADER BLOCK keep
master freelist structure and we may (i don't say must) have problems with
insert/delete... operations, if we have long linked list of header blocks.
There are no more tablespaces in 10i, so we don't have to worry about this concept anymore.
and log_archive_start parameter is deprecated in 10i
And UTF8 too.Quote:
Originally posted by pando
and log_archive_start parameter is deprecated in 10i
Your turn :-)
No more tablespaces? wow...that is news. wonder what is next on Oracle list of new features. No more DBA's ???
and SQLPLUSW.Quote:
Originally posted by julian
And UTF8 too.
Your turn :-)
Only support is finished but it will be shipped with the product.Quote:
Originally posted by patel_dil
and SQLPLUSW.
Something like that... There will be one parameter for the SGA_SIZE, we don't have to bother how much to allocate for the LARGE POOL, for the SHARED_POOL, etc.Quote:
Originally posted by patel_dil
No more tablespaces? wow...that is news. wonder what is next on Oracle list of new features. No more DBA's ???
Hmmm UTF8 a feature? You must be joking dont you?!?! Oracle already tell ppl not to use UTF8 in 9iQuote:
Originally posted by julian
And UTF8 too.
Your turn :-)
No, I am not joking. UTF is present in 9i, but not in 10i.Quote:
Originally posted by guru_heaven
Hmmm UTF8 a feature? You must be joking dont you?!?! Oracle already tell ppl not to use UTF8 in 9i
A character set is not a feature is it...?
UTF8 is already being deprecated in 9i
No, it isn't. You can still create databases in 9i with UTF8 CS.Quote:
Originally posted by guru_heaven
A character set is not a feature is it...?
UTF8 is already being deprecated in 9i
read carefully. I said BEING not BEENQuote:
Originally posted by julian
No, it isn't. You can still create databases in 9i with UTF8 CS.
Something is either depricated or not. Is there such a thing as "a little bit pregnant" :-)Quote:
Originally posted by guru_heaven
read carefully. I said BEING not BEEN
"deprecated" just means disapproved-of. Doesn't mean you can't use it.