Hi guys,
I have Index tablespace increasing about 100MB per mth....if I perform alter indexes rebuild on all the indexes does it help to reduce tabblespace consumation??
any inputs appreciated.
:)
Hi guys,
I have Index tablespace increasing about 100MB per mth....if I perform alter indexes rebuild on all the indexes does it help to reduce tabblespace consumation??
any inputs appreciated.
:)
No.
Hi adewi,
Thank-u for ur reply...in that case do u know if there's anyway to reduce the growth of tablespace other than resizing the tablespace??
any inputs appreciated :)
Hi,
Yes it could be,
if your application creates new rows and deletes 'old' ones then a rebuild will shrink the size.
If you have bitmaped indexes then they shrink too because they grow due to dml like update-stmt.
Orca
:cool:
I don't see this as a problem. As you said that the size increases by 100M every month, that itself indicates that lots of data inserts is going on. And growth of tablespace due to valid inserts (important records) is what a database is expected to have.Quote:
Originally posted by ngwh
Hi adewi,
Thank-u for ur reply...in that case do u know if there's anyway to reduce the growth of tablespace other than resizing the tablespace??
any inputs appreciated :)
Now the thing to watch here is are there lots of unused indexes.
if yes, then drop them because having unused indexes can lead to unwanted growth in tablespace.
And if you cannot avoid those lots of indexes then you need to rewrite your application querries in such a way that it least hampers your business logic and functionality and use mininimum indexes.
HTH
Yse, sure it will if there are a lot of deleted rows in the base table. In that case, there are holes in the index which can be compacted with a rebuild statement.
In 9i you could do an online rebuild of the index.
Nizar
Right Amar on why the index is growing. Also there might be a lot of deletes which does not claim back tablespace usage.
You couls also COALESCE option with ALTER INDEX if you so not want to rebuild it.
Nizar
pardon me for being a greenhorn...if I may ask...how can I tell which indexes I should rebuild inorder to eliminate to pick out those that are eating up too much tablespace?
thank-u
No use, they will grow again....More ever you need twice the space during rebuild (Old and the New will both be there during the rebuild)Quote:
Originally posted by nabaig
Yse, sure it will if there are a lot of deleted rows in the base table. In that case, there are holes in the index which can be compacted with a rebuild statement.
In 9i you could do an online rebuild of the index.
Nizar
And if you do it online, you'll need additional space to hold the changes that are made during the rebuild.
Hi ,
What is the different between using COALSE and REBUILD for Alter Index... ??
anyone can enlighten?
http://technet.oracle.com/docs/produ...dexes.htm#4443Quote:
Originally posted by ngwh
Hi ,
What is the different between using COALSE and REBUILD for Alter Index... ??
anyone can enlighten?
Coalesce doesn't need additional space (rebuild need the space for the "old index" and for the "new index" before dropping the old one)
Coalesce performs the rebuild of the index doing "partial commits" and "deleting partialy".
Cheers
Of course they will grow again. But why does my company pay me to be a DBA? To monitor indexes, growth patterns, index usage, etc. etc.
Rebuild is one of the things I have to do during off-peak hours, weekends etc.
On a production system, you just cannot ignore those holes in indexes which you are sure are important.
Nizar
hi,
also..what if i ENABLED the compression for Indexes ??
does it help to reduce the tablespace size??
Hope this clears your doubtsQuote:
Originally posted by nabaig
Of course they will grow again. But why does my company pay me to be a DBA? To monitor indexes, growth patterns, index usage, etc. etc.
Rebuild is one of the things I have to do during off-peak hours, weekends etc.
On a production system, you just cannot ignore those holes in indexes which you are sure are important.
Nizar
http://asktom.oracle.com/pls/ask/f?p...:457820220286,
Mr Tom Kyte who is a recognized Oracle expert recogns rebuild index is just another myth, waste of time :o
oh guess we were writing at the same time adewri :o
Hi Pando,
pardon my greenhorn-ness :) ...so if rebuilding index is wasting time..any suggestions given by him as to how we can manage the growth of indexes that eat up the tablespace? for my case the data are insertions and updates..so meaning if this is the operation pattern..my index tablespace for sure will "grow" right?
anyone correct me if I am wrong?
well the index freespace are reused you know... well considering if yo are inserting same range of values you deleted, but if you dont delete data then what´s the point?
Yes pando :)Quote:
Originally posted by pando
oh guess we were writing at the same time adewri :o
and NABAIG if you have been rebuilding your indexes with out using the NEXT clause. Then i think you must have ended using up more space than reducing it, just check the example Mr. TOM has given in his explanation... (Refer to link above)Quote:
Originally posted by nabaig
Of course they will grow again. But why does my company pay me to be a DBA? To monitor indexes, growth patterns, index usage, etc. etc.
Rebuild is one of the things I have to do during off-peak hours, weekends etc.
On a production system, you just cannot ignore those holes in indexes which you are sure are important.
Nizar
So i guess you are not getting paid correctly :)
(Just kidding)
ok guys....so what's the conclusion??
Should I sit and do nothing about indexes that are consuming up my index tablespaces OR is there something I can do like COALESCE or COMPRESSION ...etc??
kindly enlighten a poor chap here :)
hello gurus,
Isnt dropping and recreating the objects the best possible solution for fragmentation(if possible)?
Thank you
All that t.Kyte's posting tells me is if you use initial and next extent then the next extent sizes increase by the pctincrease factor.
I notices Tom's message is dated July 2000 for Oracle 7.3? Does it apply today? No. Why?
With 9i when we have LMT's and segment space management AUTO options, initital, next, pctincrease is history. It sure does makes sense to upgrade right?
Well when you rebuild, you do need to keep next in mind.
And finally, why I think a rebuild is necessary is that given I/O is in blocks and not in rows, then a index block with a 90% free space and one index row sure eats up my cache.
And finally from the Oracle manual:
When you rebuild an index, you use an existing index as the data source. Creating an index in this manner enables you to change storage characteristics or move to a new tablespace. Rebuilding an index based on an existing data source removes intra-block fragmentation. Compared to dropping the index and using the CREATE
INDEX statement, re-creating an existing index offers better performance.
What I mean is T. Kyte is not the end in himself. Different times, different technologies, think about different solutions.
Nizar
ngwh, as I said what´s the point if you dont delete data, you will have to rebuild an index if you delete data otherwise it becomes screwed
nabaig dont tell me you rebuild index every month or every week?!?! without deleting data?
Pando,
My point is moot only if data is deleted. My environment is dynamic; lotsa inserts, deletes etc.
And I have noticed that if I dont rebuild my index regularly; then full table scans take over.
If there are no deletes then why rebuild? Agreed.
Nizar
No i disagree on this also. Deletes does not always need rebuild, only few exceptional cases.Quote:
Originally posted by nabaig
Pando,
My point is moot only if data is deleted. My environment is dynamic; lotsa inserts, deletes etc.
And I have noticed that if I dont rebuild my index regularly; then full table scans take over.
If there are no deletes then why rebuild? Agreed.
Nizar
Consider the following example.
So according to you if the index entries are never reused upon deletes and if i keep inserting and deleting and never reuse a value, this index should grow like crazy.Code:SQL*Plus: Release 9.2.0.3.0 - Production on Wed Mar 26 19:13:03 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
SQL> create user test identified by test default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource,create procedure to test;
Grant succeeded.
SQL> alter user test quota unlimited on users;
User altered.
SQL> grant select on dba_tablespaces to test;
Grant succeeded.
SQL> grant select on dba_segments to test;
Grant succeeded.
SQL> conn test/test
create or replace
procedure show_space
( p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'TABLE',
p_partition in varchar2 default NULL )
authid current_user
as
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') ||
p_num );
end;
begin
for x in ( select tablespace_name
from dba_tablespaces
where tablespace_name = ( select tablespace_name
from dba_segments
where segment_type = p_type
and segment_name = p_segname
and owner = user
and SEGMENT_SPACE_MANAGEMENT <> 'AUTO' )
)
loop
dbms_space.free_blocks
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
freelist_group_id => 0,
free_blks => l_free_blks );
end loop;
dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );
p( 'Free Blocks', l_free_blks );
p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );
end;
/
Procedure created.
SQL> create table t (x int,constraint t_pk primary key(x));
Table created.
SQL> insert into t values (1);
1 row created.
SQL> insert into t values (2);
1 row created.
SQL> insert into t values (9999999999);
1 row created.
SQL> commit;
Commit complete.
SQL> exec show_space('T_PK',user,'INDEX');
Free Blocks.............................
Total Blocks............................8
Total Bytes.............................65536
Total MBytes............................0
Unused Blocks...........................4
Unused Bytes............................32768
Last Used Ext FileId....................5
Last Used Ext BlockId...................96
Last Used Block.........................4
PL/SQL procedure successfully completed.
Let's see.
So, this shows that the space in the index was reused. So no need to rebuild even if there is deletions.Code:SQL> begin for i in 2 .. 999999
2 loop
3 delete from t where x = i;
4 commit;
5 insert into t values (i+1);
6 commit;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> exec show_space('T_PK',user,'INDEX');
Free Blocks.............................
Total Blocks............................8
Total Bytes.............................65536
Total MBytes............................0
Unused Blocks...........................4
Unused Bytes............................32768
Last Used Ext FileId....................5
Last Used Ext BlockId...................96
Last Used Block.........................4
PL/SQL procedure successfully completed.
SQL>
Nabaig just try the above method on your indexes and see what it shows
HTH
PS: The above procedure (works only for 9i LMT tablespaces) and example is given in "expert one on one oracle" by thomas kyte, i have done these tests and shown here to prove the point that deletes also does not always require rebuild.
"ALTER INDEX indexname COALESCE" statement can be used in a 24x7 env where REBUILD is not possible. What it does is: Merges empty and nearly empy (based on pctfree value) blocks into a single block, frees up the blocks (totally empty) for future inserts.
Here is my test:
create table Tamil tablespace psdeveloper
storage (initial 10m next 10m pctincrease 0)
as select rownum runseq , a.* from dba_objects a;
14:54:56 H8DEVW3>select count(*) from tamil ;
COUNT(*)
----------
39367
create index tamil_idx on tamil(runseq)
tablespace psindex storage (initial 256k next 256k pctincrease 0);
Index created.
insert into tamil select (40000+rownum) runseq , a.* from dba_objects a;
39368 rows created.
select count(*) from tamil ;
COUNT(*)
----------
78735
select segment_name, trunc(sum(bytes/1024/1024)) from dba_extents
where segment_name = 'TAMIL'
group by segment_name;
SEGMENT_NAME TRUNC(SUM(BYTES/1024/1024))
-------------------------------- ---------------------------
TAMIL 10
select segment_name, trunc(sum(bytes/1024/1024)) from dba_extents
where segment_name = 'TAMIL_IDX'
group by segment_name;
SEGMENT_NAME TRUNC(SUM(BYTES/1024/1024))
-------------------------------- ---------------------------
TAMIL_IDX 2
analyze index tamil_idx validate structure ;
select BLOCKS , NAME, lf_rows, lf_blks, br_blks, used_space from index_stats;
BLOCKS NAME LF_ROWS LF_BLKS BR_BLKS USED_SPACE
---------- ----------------- ---------- ---------- ---------- ----------
260 TAMIL_IDX 78735 165 1 1250817
select sum(blocks) from dba_extents where segment_name = 'TAMIL_IDX';
SUM(BLOCKS)
-----------
260
delete from tamil where runseq between 5001 and 15000 ;
10000 rows deleted.
delete from tamil where runseq between 35001 and 45000 ;
9367 rows deleted.
delete from tamil where runseq between 65001 and 75000 ;
10000 rows deleted.
commit;
select count(*) from tamil;
COUNT(*)
----------
49368
select blocks,name, lf_rows, lf_blks, br_blks, del_lf_rows, used_space, btree_space
from index_stats;
BLOCKS NAME LF_ROWS LF_BLKS BR_BLKS DEL_LF_ROWS USED_SPACE BTREE_SPACE
---------- ---------- ---------- ---------- ---------- ----------- ---------- -----------
260 TAMIL_IDX 78735 165 1 29367 1250817 1327368
alter index tamil_idx coalesce ;
analyze index tamil_idx validate structure ;
select blocks,name, lf_rows, lf_blks, br_blks, del_lf_rows, used_space, btree_space
from index_stats;
BLOCKS NAME LF_ROWS LF_BLKS BR_BLKS DEL_LF_ROWS USED_SPACE BTREE_SPACE
---------- ---------- ---------- ---------- ---------- ----------- ---------- -----------
260 TAMIL_IDX 49368 106 1 0 785542 855604
Note: After the coalesce on Index the number of Leaf Blocks have come down to 106 from 165.
Hi, what tom said could be right for many cases but if you have a technical growing id in your index
and your applications deletes older entries from time to time
you NEED to rebuild from time to time if you want not to waste to much disk-storage.
And why is it not so bad to have big holes in the index?, how effective is it to read air (empty blocks) instead of hard index-data?
Orca
:cool:
Well, that's what i exactly showed above, deleted space will be reused.Quote:
Originally posted by Orca777
if you have a technical growing id in your index
and your applications deletes older entries from time to time
But yes if we had deleted every other row (even rows) from 1 to 999999, then yes it would leave 499999 holes which would be reused only if we reinsert data that will fit into those blocks. Here in this case rebuild and coalesce is unavoidable.
But if we delete all the rows below 499999 then the blocks that were cleaned out of the index will be put back onto the FREELIST for the index. This space can be totally reused.