Additional downside item:
- Queries against DBA_EXTENTS will take a bit longer to return.
- Kirti
- Cary Millsap <cary.millsap_at_hotsos.com> wrote:
> Wow.
>
> Maybe someone on the list has the time and motive to construct a test to
> determine how many extents for a segment in a ULMT are "bad." My guess from
> some tests we did a couple of years ago is that it will take hundreds of
> thousands of extents before even DROP performance will suffer. And I can't
> think of *anything* that would make having even hundreds of millions of
> extents a bad idea for INSERTs, UPDATEs, MERGEs, or DELETEs. The only
> possible downsides of huge numbers of extents that I can think of are
> perhaps:
>
> * During the INSERT, UPDATE, or MERGE, what is the overhead of the actual
> allocation of the ULMT extent? (This actually may have nothing to do with
> how many extents are already there.)
>
> * During checkpoints on RAC systems, does the number of extents matter the
> way it did when Jonathan Lewis showed a problem with DMT and OPS a few years
> ago?
>
> * Does a huge bitmap section in the head of a data file cause any
> performance problems for backup and recovery?
>
> Aside from that, I can't imagine any more downside of huge numbers of ULMT
> extents than there is from having the Unix filesystem extents that most of
> us have right now and never notice.
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
>
> Upcoming events:
> - Hotsos Clinic 101 in Reykjavik, Ottawa, Dallas, Washington, Denver, Sydney
> - Visit www.hotsos.com for schedule details...
>
>
> -----Original Message-----
> Sent: Friday, May 30, 2003 8:50 AM
> To: Multiple recipients of list ORACLE-L
>
> Jared,
>
> It's rather simple. If you follow the rules of third normal form
> you have a table with a certain number of rows, a second with a certain
> number of rows for each row in the first table. Obviously the second table
> needs more space than the first. Now if you use Dictionary management you
> can set the storage parameters of each table individually. But if your
> using local management they both have the same extent sizes. This leads one
> to having the extent sizes smaller to accommodate the first table and large
> numbers of extents for the second table. True fragmentation, namely those
> small useless extents that land between larger used extents, is eliminated
> in local management but then I have not had those problems with dictionary
> management either, unless someone makes the case for moving a table but
> that's very rare.
>
> Dick Goulet
> Senior Oracle DBA
> Oracle Certified 8i DBA
>
> -----Original Message-----
> Sent: Thursday, May 29, 2003 8:25 PM
> To: ORACLE-L_at_fatcity.com
> Cc: Goulet, Dick
> Importance: High
>
>
> Dick,
>
> I'm trying to follow your line of thought, but I think I missed the path.
>
> Objects may not have the same storage requirements, but what does that
> matter?
>
> The only way I can make sense of what you say is if trying to have all
> objects
> occupy a single extent, and there's not much point in that.
>
> Jared
>
>
>
>
>
>
> "Goulet, Dick" <DGoulet_at_vicr.com>
> Sent by: root_at_fatcity.com
> 05/29/2003 03:51 PM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> cc:
> Subject: RE: Tablespace management.
>
>
> Thomas,
>
> With the exception of temp and rollback tablespaces I
> have not user locally managed tablespaces just because all objects must
> have the same sized extents. I do not see most tables sharing an equal
> need for storage and using dictionary management allows one to do that, at
> a cost I'll admit, but one that is much easier to swallow.
>
> Dick Goulet
> Senior Oracle DBA
> Oracle Certified 8i DBA
>
> -----Original Message-----
> Sent: Thursday, May 29, 2003 3:25 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> After reading the documents I've recommended using LOCAL, UNIFORM, AUTO as
> the options for tablespace management. Does anyone have any bad
> experiences with these? AUTOALLOCATE seems to come up with extents that
> are much smaller than I want and MANUAL segment management requires the
> use
> of FREELISTs (and I know that there are problems with freelists freeing up
> space correctly, especially in a parallel environment).
>
> I can't find any basis for making a decision between UNDO and ROLLBACK
> SEGMENTS. Does anyone have any experience or recommendations about UNDO
> usage?
>
> The database will be a materialize view replication of a transaction
> master
> that is being used for decision support and has a 15 minute update/refresh
> cycle. Basically, people can run queries against the snapshot without
> impacting the master.
>
>
>
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Kirtikumar Deshpande
INET: kirtikumar_deshpande_at_yahoo.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Fri May 30 2003 - 12:44:58 CDT
