Re: LOCALLY MANAGED EXTENT PERFORMANCE

> 
> Ever heard "3 Party App" where you have no access to the code?
>

Gee, no.  What's that?  :-)

C'mon, of course I have.  Few (if any) 3rd-party apps bother to address
extent allocation mechanisms for tablepaces; even fewer insist on having
things one way or the other.  In most cases, 3rd-party app knows the concept
of a tablespace and that's it.  Some might provide sample code for CREATE
TABLESPACE statements, but few are willing to give working code because it
is so dependent on local standards and platform.

And you always have access to that "code" because it is DDL;  it's the
application code within the 3rd-party app that you rarely have access to...

> 
> I was also temped by the theory that number of extents don't matter much
> but I changed my mind after testing LMT with uniform extents for a 3.
> party app in which there are several tables that jump to 3-4G from 0
> bytes during day..all users started screaming and we had to go to LMT
> with auto allocate to calm down the users...

You're saying that transactional insert performance was affected by extent
allocation?  Gee, I don't think so...

Let's run the numbers.  If that 3-4G table grows to consist of 1000 extents,
then you're talking about an extension once every 60-70 seconds during the
day.  Which is once every couple thousand transactions, no?

So, to summarize, an operation (i.e. LMT extent allocation) that takes
milli-seconds per execution, which occurs once every 60-70 seconds or so, is
thus the root-cause and primary-cause of a performance problem?

Highly doubtful...

Less doubtful and more likely, is that in rebuilding the table in question
to another tablespace, several other important modifications to storage
parameters were also made, such as increasing INITRANS, increasing FREELISTS
or using ASSM, or any number of possible changes to indexes on the table,
perhaps?

Each of those factors is far more likely to contribute to a performance
problem on transactional inserts than extent allocation, unless you ended up
with something like 86,400 extents (i.e. adding one extent per second during
the day), which implies an extent size less than 64Kb which is too silly to
contemplate, no?

> 
> You keep repeating yourself, telling well-known facts making it like I
> oppose them. I'm not saying uniform LMT is a bad thing. All I'm saying
> is it should be used if you know estimated size of tables.

Forgive me, repetition seems to be an old man's disease an old man's disease
an old man's disease...

Ah, but then you keep mentioning this 27G table and how autoallocate helped
it.  If there is ever situation that screams for uniform-sized extents, it
would be that one.  Certainly, you know quite well how much space that thing
is consuming day-by-day as it grows.  So why would you need to cede control
over extent sizing to autoallocate?

> And even though LMTs reduces the extent allocation cost dramatically,
> there are some cases where thousands of extents may cause some headache...

Cases such as...?

> 
> Even popular Oracle paper, "How to Stop Defragmenting and Start Living:
> The Definitive Word on Fragmentation", warns DBAs about excessive
> extents. Below is a clip from the paper:
> ...
> 2.1.4     Monitor and Potentially Relocate Segments Having More Than
> 1024 Extents
> Oracle supports an unlimited number of extents in a segment.  The
> performance for DML operations is largely independent of the number of
> extents in the segment. However, certain DDL operations such as dropping
> and truncating of segments are sensitive to the number of extents.
> Performance measures for these operations have shown that a few thousand
> extents can be supported by Oracle without a significant impact on
> performance. A reasonable maximum has been determined to be 4096.
> ..
> 
> So if number of extents is such a benign thing, then why would Oracle
> warn DBAs about it and recommend monitoring tables/indexes with more
> than 4096 extents ?

Please note in the first section of the paper:

    "This paper is targeted at an audience of experienced
     database administrators. It is based on Oracle7 release
     7.3 and also covers new features introduced in Oracle8
     version 8.0."

LMT was not introduced until version 8.1.  So, the advice in the paper is
for DMT only, which is all that existed at the time it was written.

Does that "tempt" you to rethink the "theory" that number of extents don't
matter again?

--
http://www.freelists.org/webpage/oracle-l

Other related posts: