General
cancel
Showing results for 
Search instead for 
Did you mean: 

problem of allocate of the extents

SOLVED
Go to solution
dngaya
Advisor

problem of allocate of the extents


hi ,
some time ago I have write a script which allows me to get the segments which have more than 1000 extents in a base oracle 7.3.4 on HP Unix. I have just noted that there are one indice which, each week, the number of extent increase and in more there is much block, here below the result:

name of the index: OAS_DOCLINE_IND1
blocks: 117400
semaine1: 1135
semaine2: 1165
semaine3: 2232
semaine4: 2286

When I execute the follow request:
SQL > select index_name, initial_extent, next_extent, min_extents, max_extents, pct_free from dba_indexes where index_name=' OAS_DOCLINE_IND1 ' and owner=' CODPRD';
I receive the following results:

index_name = OAS_DOCLINE_IND1
initial_extent=5529600
next_extent=102400
min_extents=1
max_extents=2147483645 (unlimited)
pct_free=10

I will like to know taking into account this evolution, is what there is not a disadvantage which will be able can be to have problems on the performances and how then-to make for reorganize this index.
thank you for your assistance.
7 REPLIES
Jean-Luc Oudart
Honored Contributor

Re: problem of allocate of the extents

Hi dngaya,

You can rebuild your index with different storage parameters.

alter index
storage ( initial 4096K next 4096K )

and any other parameter you like to change.

Rgds,
Jean-Luc
PS : if you rebuild in same tablespace ensure you have enough space.
fiat lux
Hari Kumar
Trusted Contributor

Re: problem of allocate of the extents

Performing this under Oracle 7.3 then be sure to specify the destination tablespace, ie:
Alter index rebuild tablespace ;

Otherwise the index will be moved to the temporary tablespace, Care to be taken about the tablespace size too.
Information is Wealth ; Knowledge is Power
Graham Cameron_1
Honored Contributor
Solution

Re: problem of allocate of the extents

Your initial extent is 5M, but your next is only 100K, which may expalin why you are getting so many extents.
DBA practice here is to always have initial=next with pctincrease 0, and I would suggest and rebuild the index with these storage parameters.
Use ALTER INDEX ... REBUILD, else the existing fragmentation will still be present.
-- Graham
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
Graham Cameron_1
Honored Contributor

Re: problem of allocate of the extents

Sorry, didn't spot you were on oracle 7.
Not sure if ALTER INDEX REBUILD was available then.
You may have to drop and recreate it.
-- Graham
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
Hari Kumar
Trusted Contributor

Re: problem of allocate of the extents

I feel happy if you give the feed back that
u have followed the suggestions or not?, if then what was the result, any suggestions from your side e.t.c
Information is Wealth ; Knowledge is Power
Jean-Luc Oudart
Honored Contributor

Re: problem of allocate of the extents

What is wrong with my solution ?

Rgds,
Jean-Luc
fiat lux
Yogeeraj_1
Honored Contributor

Re: problem of allocate of the extents

hi,
*
The answer provided by Jean-Luc is also a valid one.
*
Is it time that you migrate to 8i or 9i? and benefit from locally managed tablespaces?
*
regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)