Re: Index Information used_space and dba_segments
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Wed, 1 May 2013 08:02:12 -0700 (PDT)
Message-ID: <276b5617-4820-4293-ac7f-83d3bb41d590_at_googlegroups.com>
On Monday, April 29, 2013 11:59:07 AM UTC-4, Jonathan Lewis wrote:
> <basis_consultant_at_hotmail.com> wrote in message news:cd6f7a8c-bf39-4686-a896-3f1a172f43aa@googlegroups.com... | Hi, | | In Oracle 11g, I analyzed an index and checked index_stats. | | dba_segments indicates that the size of an index was about 420MB. | | used_space in index_stats indicated that the index used about 30MB, | and that a compression of 2 columns would save about 15%. | | I then compressed the index (Rebuild..compress 2), | expecting that the size of the index in dba_segments | to decrease. However, it did not (It was about | 430MB after the compression). | | Leaving aside the issue of compressing the index..I thought | that the fact that used_space was much lower than the index's | space in dba_segments indicated that the index was likely | sparse. | | If so, I expected rebuilding this index to decrease its size | in dba_segments. Why did it not? | | Regarding the argument that analyzing an index is outdated...I | found that index_stats is generally accurate in predicting the | amount of space a compression will save (At least, for an | estimation of a space savings of some 40%+ or more for a | compression). | | | Nothing wrong with using analyze index to find the optimum compression count - though you can probably work it out logically if you're very familiar with the data. After the rebuild with compression, what was the space_used figure when you re-analyzed. Could you give us the full before and after figures from index_stats. What do you have set for pctfree for the index ? There were a couple of anomalies with a mismatch between dba_extents and dba_segments in some versions of Oracle - related in particular to parallel rebuilds I think - have you cross checked the two views ? -- Regards Jonathan Lewis http://jonathanlewis.wordpress.com/all-postings Author: Oracle Core (Apress 2011) http://www.apress.com/9781430239543
Date: Wed, 1 May 2013 08:02:12 -0700 (PDT)
Message-ID: <276b5617-4820-4293-ac7f-83d3bb41d590_at_googlegroups.com>
On Monday, April 29, 2013 11:59:07 AM UTC-4, Jonathan Lewis wrote:
> <basis_consultant_at_hotmail.com> wrote in message news:cd6f7a8c-bf39-4686-a896-3f1a172f43aa@googlegroups.com... | Hi, | | In Oracle 11g, I analyzed an index and checked index_stats. | | dba_segments indicates that the size of an index was about 420MB. | | used_space in index_stats indicated that the index used about 30MB, | and that a compression of 2 columns would save about 15%. | | I then compressed the index (Rebuild..compress 2), | expecting that the size of the index in dba_segments | to decrease. However, it did not (It was about | 430MB after the compression). | | Leaving aside the issue of compressing the index..I thought | that the fact that used_space was much lower than the index's | space in dba_segments indicated that the index was likely | sparse. | | If so, I expected rebuilding this index to decrease its size | in dba_segments. Why did it not? | | Regarding the argument that analyzing an index is outdated...I | found that index_stats is generally accurate in predicting the | amount of space a compression will save (At least, for an | estimation of a space savings of some 40%+ or more for a | compression). | | | Nothing wrong with using analyze index to find the optimum compression count - though you can probably work it out logically if you're very familiar with the data. After the rebuild with compression, what was the space_used figure when you re-analyzed. Could you give us the full before and after figures from index_stats. What do you have set for pctfree for the index ? There were a couple of anomalies with a mismatch between dba_extents and dba_segments in some versions of Oracle - related in particular to parallel rebuilds I think - have you cross checked the two views ? -- Regards Jonathan Lewis http://jonathanlewis.wordpress.com/all-postings Author: Oracle Core (Apress 2011) http://www.apress.com/9781430239543
On our 10.2.0.5 environments the extent information in dba_segments is often different from what is shown via dba_extents which seems to be correct. I do not have a bug number since we have never bothered to report the issue to support, but I am pretty sure this was fixed by 11.2
What kind of tablespace storage allocation is in use: dictionary, uniform extents, or auto-allocate? What degree of parallelism was used on the index rebuild? Use of parallel rebuilds can cause an index allocation to grow well beyound what would be used via a non-parallel rebuild operation.
HTH -- Mark D Powell -- Received on Wed May 01 2013 - 17:02:12 CEST