So do u still think you need to rebuild :)Quote:
Originally posted by hrishy
Hi Amar
I fully agree with you that those situations are rare..
Printable View
So do u still think you need to rebuild :)Quote:
Originally posted by hrishy
Hi Amar
I fully agree with you that those situations are rare..
Get us the result of this query....and higlight the tablespace where ur Index resides....if the tablespace has reached fragmentation above 90% then u may need to rebuild..............Quote:
Originally posted by V6163
Hi All,
In our database all the index and data tablespaces are LMT tablespaces. We are aware that the LMT tablespaces doesnt have fragmentation. But I have read some article, stating that the index objects will have holes while there are frequent deletions in data, and hence they are fragmented and the only way to correct them is to rebuild the indexes. Even the indexes are in the LMT this will happen.
So just correct me whether my understanding is correct or not.
As my indexes are in LMT, so if I rebuild them, will there be any use out of that?
This query gives all the tablspaces which are fragmented mor than 90%.....
Abhay.Code:rem =====================================================
clear screen
SET ECHO OFF
SET PAGESIZE 1000
SET FEEDBACK OFF
SET LINESIZE 1000
COLUMN Tablespace FORMAT A15 HEADING 'Tablespace Name'
COLUMN Total_Size FORMAT 9,999 HEADING 'Total Space(GB)'
COLUMN Free_Space FORMAT 9,999 HEADING 'Free Space(GB)'
COLUMN Percentage FORMAT 90.0 HEADING '% Used'
COLUMN decode FORMAT A14 HEADING '(Space Gauge)'
COLUMN fragdec FORMAT A14 HEADING '(Frag Gauge)'
COLUMN percfrag FORMAT 90.0 HEADING '% Frag'
COLUMN frags FORMAT 9,999 HEADING 'Frags'
COLUMN bigchunk FORMAT 9,999 HEADING 'Big Chunk(GB)'
COLUMN gap HEADING ' '
COLUMN data_files FORMAT 99 HEADING 'Data Files'
TTITLE 'Tablespace Analysis - Space Management and Fragmentation'
select free.tablespace_name Tablespace, tot.total Total_Size,free.free Free_Space,
100 - ((free.free / tot.total) * 100) Percentage,
decode
((ceil(10-(free.free / tot.total) * 10)),
0,'| .......... |',
1,'| *......... |',
2,'| **........ |',
3,'| ***....... |',
4,'| ****...... |',
5,'| *****..... |',
6,'| ******.... |',
7,'| *******... |',
8,'| ********.. |',
9,'| *********. |',
10,'| **danger** |') decode,
bigchunk, '|' gap, data_files, frags, percfrag,
decode ((ceil(percfrag / 10)) ,
0,'| .......... |',
1,'| *......... |',
2,'| **........ |',
3,'| ***....... |',
4,'| ****...... |',
5,'| *****..... |',
6,'| ******.... |',
7,'| *******... |',
8,'| ********.. |',
9,'| *********. |',
10,'| **danger** |') fragdec
from
(
select tablespace_name, ceil(sum(bytes) / (1048576*1024)) total, count(*) data_files
from sys.dba_data_files
group by tablespace_name
) tot,
(
select tablespace_name, ceil(sum(bytes) / (1048576*1024)) Free,
ceil(max(bytes) / (1048576*1024)) bigchunk, count(*) frags,
100 - (max(bytes) / sum(bytes)) * 100 percfrag
from sys.dba_free_space
group by tablespace_name
) free
where free.tablespace_name = tot.tablespace_name and
percfrag>90
order by free.tablespace_name,Percentage, percfrag
/
CLEAR COLUMNS
TTITLE OFF
BTITLE OFF
SET FEEDBACK ON
SET PAGES 24
CLEAR BREAKS
Just some additional information regarding indexes - in both Locally Managed and Dictionary Managed tablespaces.
Deleting a row only results in a logical deletion of the index entry. The space used by the deleted row is not available for new entries until all entries in the block are deleted.
Updates however result in a logical delete and insert into the index therefore space is reused.
This means that if you have a volatile table where there are many deletes it will be advantageous to rebuild the indexes.
Hope this helps.
Allie
Abhay, haven't you noticed that the original question (and even the subject of the thread) explicitely states that we are talking about LMT. So your query is totaly useless... The fragmentation that is reported by your query is irrelevant for localy managed tablespaces.Quote:
Originally posted by abhaysk
Get us the result of this query....and higlight the tablespace where ur Index resides....if the tablespace has reached fragmentation above 90% then u may need to rebuild..............
This query gives all the tablspaces which are fragmented mor than 90%.....
Quote:
Originally posted by jmodic
Abhay, haven't you noticed that the original question (and even the subject of the thread) explicitely states that we are talking about LMT. So your query is totaly useless... The fragmentation that is reported by your query is irrelevant for localy managed tablespaces.
Jmodic:
I am wrong....i was totaly out of my mind....didnt go thru message thourghly......
Cud u highlight on how abt knowing Tablespsace fragmentation for LMT managed tablespaces...
Abhay.
Tablespace fragmentation does not exist in LMT (at least not in uniform mode LMT). Segments stored in LMTs could suffer block fragmentation though, and that's what other have been talking about when mentioning deleted entries from indexes....
If it were to be auto allocate mode, managed by System?
do u mean for this opt fragmentation can exist?
If S, how do we know?
Abhay.
I recently went sniffing for information regarding autoallocate, and came across this thread...
http://groups.google.com/groups?hl=e....uk%26rnum%3D1
I'll probably help you with the the LMT AUTOALLOCATE.
And while we are at it, let's debunk the theory of REGULAR INDEX REBUILDS are essential.
Go to, http://www.e-dba.net/site/edba.html
Click on What's New and then Connor's Talk About and read the "A RETHINK ON REBUILDS"
Cheers,
Hi All,
Thank you all, noted down your inputs.
Thanks GR:Quote:
Originally posted by grjohnson
I recently went sniffing for information regarding autoallocate, and came across this thread...
http://groups.google.com/groups?hl=e....uk%26rnum%3D1
Cheers,
that was indeed good link to know more abt LMT.
Abhay.