Re: Managing Dynamic Allocation of Extents?

Thanks to Thomas Kyte for an explanation. While dynamic allocation may add several blocks to an extent, in this case parallel query was a factor; two threads each grabbed 45M extents then shrunk one to 32M.

Richard A Papaj wrote:

> 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 Fri Apr 03 1998 - 00:00:00 CST