| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Managing Dynamic Allocation of Extents?
A copy of this was sent to Richard A Papaj <papaj_at_acsu.buffalo.edu> (if that email address didn't require changing) On Thu, 02 Apr 1998 15:16:12 -0500, you 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)
>;
its the
parallel (degree 2)
thats doing it. parallel index creates cause each thread in the index creation to grab their own INITIAL and NEXT extents. All of the initial extents are trimmed if possible after they are used.
So, in your case, the 2 threads each grabbed 45m and one of them shrunk it back to 32meg after it was done.
From the Oracle7.3 server tuning manual, chapter 6 on parallel operations:
<quote>
Attention: When creating an index in parallel, the
STORAGE clause refers to the storage of each of the
subindexes created by the query server processes.
Therefore, an index created with an INITIAL of 5M and
a PARALLEL DEGREE of 12 consumes at least 60M of
storage during index creation because each process
starts with an extent of 5M. When the query coordinator
process combines the sorted subindexes, some of the
extents may be trimmed, and the resulting index may
be smaller than the requested 60M.
</quote>
> 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:
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Apr 03 1998 - 00:00:00 CST
 |
 |