Then again, why query DBA_EXTENTS ? Pretty much
anything that its got you can get from DBA_SEGMENTS,
the exception being the file/block_id/blocks info.
I would contend that the reason we want that level of
information is when mapping segments to file position
to determine I/O rates, which we can get from segment
level stats in 9i anyway.
And of course, everyone is already on 9i by now, as
they start getting ready for a migation to 10
:-)
- Kirtikumar Deshpande
<kirtikumar_deshpande_at_yahoo.com> wrote: > 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).
>
Connor McDonald
web:
http://www.oracledba.co.uk
web:
http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"
Yahoo! Plus - For a better Internet experience
http://uk.promotions.yahoo.com/yplus/yoffer.html
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
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 Sat May 31 2003 - 11:04:37 CDT
