| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Tablespace management.
This is not a comprehensive test by any means, though I think it should
ally at least a few fears (and it gets me out of housework for half an
hour or so. Comments with a ****
SQL> select initial_EXTENT,next_EXTENT from dba_tablespaces 2 where tablespace_name='XXXXXXX';
INITIAL_EXTENT NEXT_EXTENT
131072 131072
SQL> SELECT ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
2 FROM DBA_FREE_SPACE
3 where tablespace_name='XXXXXXX';
FREE_SPACE
4064.13
SQL> CREATE TABLE TEST_LMT(C1 CHAR(255)) 2 TABLESPACE XXXXXXX; Table created.
SQL> BEGIN
2 FOR I IN 1..100000 LOOP
3 EXECUTE IMMEDIATE 'ALTER TABLE TEST_LMT ALLOCATE EXTENT';
4 END LOOP;
5 END;
6 /
BEGIN
*
ERROR at line 1:
ORA-01653: unable to extend table XXXXXXX.TEST_LMT by 8 in tablespace
XXXXXXX
ORA-06512: at line 3
SQL> SELECT COUNT(*)
2 FROM DBA_EXTENTS
3 WHERE SEGMENT_NAME='TEST_LMT';
COUNT(*)
32513
Shame it wasn't 32768 but never mind
SQL> SET TIMING ON
SQL> DROP TABLE TEST_LMT;
Table dropped.
Elapsed: 00:00:01.04
SQL> SPOOL OFF
So DROP performance seems good up to at least 32k extents, and my guess
is that most objects that actually eat up 4gb or more of space are good
candidates for partitioning.
All of the above 9.2.0.3 on Win2k.
> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com] On Behalf Of
> Cary Millsap
> Sent: 30 May 2003 17:55
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Tablespace management.
>
>
> 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.
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Thomas Day
> INET: tday6_at_csc.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).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Goulet, Dick
> INET: DGoulet_at_vicr.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).
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Goulet, Dick
> INET: DGoulet_at_vicr.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).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Cary Millsap
> INET: cary.millsap_at_hotsos.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).
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield INET: niall.litchfield_at_dial.pipex.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 Sat May 31 2003 - 09:44:38 CDT
 |
 |