Re: Managing Dynamic Allocation of Extents?

From: Richard A Papaj <papaj_at_acsu.buffalo.edu>
Date: 1998/04/02
Message-ID: <3523F20C.80462022@acsu.buffalo.edu>#1/1

Here are the create statements that I used for the tablespace & index in my example and the resulting rows from the dba_extents view which may (or may not) be helpful:

CREATE TABLESPACE DARINDX DATAFILE
'/oracle/data03/adminp0/darindx01.dbf' SIZE 100M REUSE

 DEFAULT STORAGE (INITIAL     10240
    NEXT        10240

    MINEXTENTS 1
    MAXEXTENTS 99
    PCTINCREASE 1)
ONLINE
;
CREATE INDEX DAR.NON_UB_CREDIT_K ON DAR.NON_UB_CREDIT_T (PERSON_NUMBER,INSTID,INSTCD,DPMASK,YEAR_TERM,COURSE_ID,SEQUENCE_NO) INITRANS 2 MAXTRANS 255 PCTFREE 10
  TABLESPACE DARINDX

 STORAGE (INITIAL     45M
    NEXT        600K

    MINEXTENTS 1
    MAXEXTENTS 121
    PCTINCREASE 0 )
unrecoverable
parallel (degree 2)
;

 OWNER                             SEGMENT_NAME
SEGMENT_TYPE                      TABLESPACE_NAME
EXTENT_ID                         FILE_ID
BLOCK_ID                          BYTES                             BLOCKS
 --------------------------------- ---------------------------------
--------------------------------- ---------------------------------
--------------------------------- ---------------------------------
--------------------------------- ---------------------------------
---------------------------------

DAR NON_UB_CREDIT_K INDEX DARINDX 1 72 2 32436224 15838 DAR NON_UB_CREDIT_K INDEX DARINDX 0 72 23042 47185920 23040 2 rows affected
Script Completed: APR-02-1998 03:05:18

Richard A Papaj wrote:

> I couldn't understand why the value I chose for a next extent was being
> overridden until I
> read that Oracle7 may dynamically modify size of extents based on
> rounding criteria and
> available storage space. I can see advantages with Oracle doing this
> (e.g. maximizing
> reuse of extents). On the other hand, wouldn't there be times when you
> may want to
> override these dynamic sizes?

> Note the following example: Within an empty tablespace of 100M (1
> datafile), I created
> an index with an initial extent size of 45M and next extent 600K. As a
> result, I was
> expecting an initial extent of 45M and about forty 600K extents (I
> realize this may not be
> optimum sizing). Instead, I got an initial of 45M and one next extent
> of 32M! That's a
> big jump in next extent size and one that I don't want. It's probably a
> result of the
> tablespace being empty at the time but I did plan on creating more
> segments in there.

> Is there any way to override dynamic sizing? Or must I keep trying
> different extent sizes
> until I get acceptable dynamic sizes as a result? I'm a relatively new
> DBA so maybe I'm
> missing something here. It just seems that it would be nice to have
> more control over this
> sizing at times.

> Any feedback is much appreciated...

> Rick Papaj
> papaj_at_acsu.buffalo.edu
> State University of New York at Buffalo
Received on Thu Apr 02 1998 - 00:00:00 CST