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.
Printable View
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]