4 Replies Latest reply: Jan 30, 2013 10:00 AM by Jonathan Lewis RSS

    used_space column in index_stats

    User477708-OC
      alter index <xxx> validate structure to populate index_stats.

      http://docs.oracle.com/cd/E14072_01/server.112/e10820/statviews_5131.htm

      USED_SPACE      NUMBER           Total space that is currently being used in the B-Tree

      Can someone explain "Total space that is currently being used in the B-Tree" and how that differs from the bytes used in dba_segments for the index. I have an index thats using 21gb of segments but when i validate the structure, the used_space is only showing 3gb in the index_stats. probably something simple.

      thanks.
        • 1. Re: used_space column in index_stats
          Jonathan Lewis
          961469 wrote:
          alter index <xxx> validate structure to populate index_stats.

          http://docs.oracle.com/cd/E14072_01/server.112/e10820/statviews_5131.htm

          USED_SPACE      NUMBER           Total space that is currently being used in the B-Tree

          Can someone explain "Total space that is currently being used in the B-Tree" and how that differs from the bytes used in dba_segments for the index. I have an index thats using 21gb of segments but when i validate the structure, the used_space is only showing 3gb in the index_stats. probably something simple.
          used_space the sum of the lengths of all the index entries in the leaf and branch blocks. It's a reasonable approximation of the size the index would be if you rebuilt it at pctfree 0.
          What's your block size, and the values reported by index_stats for pct_used and lf_blk_len ?

          Regards
          Jonathan Lewis
          • 2. Re: used_space column in index_stats
            sb92075
            961469 wrote:
            alter index <xxx> validate structure to populate index_stats.

            http://docs.oracle.com/cd/E14072_01/server.112/e10820/statviews_5131.htm

            USED_SPACE      NUMBER           Total space that is currently being used in the B-Tree

            Can someone explain "Total space that is currently being used in the B-Tree" and how that differs from the bytes used in dba_segments for the index. I have an index thats using 21gb of segments but when i validate the structure, the used_space is only showing 3gb in the index_stats. probably something simple.

            thanks.
            it is difference between USED & ALLOCATED.

            DBA_SEGMENTS does not differentiate between USED & ALLOCATED.
            did underlying table have massive DELETE done against it?
            • 3. Re: used_space column in index_stats
              User477708-OC
              used_space the sum of the lengths of all the index entries in the leaf and branch blocks. It's a reasonable approximation of the size the index would be if you rebuilt it at pctfree 0.
              What's your block size, and the values reported by index_stats for pct_used and lf_blk_len ?
              block = 8192
              pct_used = 150
              lf_blk_len = 3988

              I rebuilt the index.
              now dba_segments is showing 3.68gb down from 21gb, validate structure used_space is now showing 2.64gb. most likely deletes in the first run as sb suggests.

              Im investigating a performance query and noticed the discrepancies in size. I dont think its related to my query but was wondering why the discrepancy anyway. At least I dont think its related just yet, have a bit to go investigating it yet before I can completely rule it out.
              • 4. Re: used_space column in index_stats
                Jonathan Lewis
                961469 wrote:
                used_space the sum of the lengths of all the index entries in the leaf and branch blocks. It's a reasonable approximation of the size the index would be if you rebuilt it at pctfree 0.
                What's your block size, and the values reported by index_stats for pct_used and lf_blk_len ?
                block = 8192
                pct_used = 150
                lf_blk_len = 3988

                I rebuilt the index.
                now dba_segments is showing 3.68gb down from 21gb, validate structure used_space is now showing 2.64gb. most likely deletes in the first run as sb suggests.

                Im investigating a performance query and noticed the discrepancies in size. I dont think its related to my query but was wondering why the discrepancy anyway. At least I dont think its related just yet, have a bit to go investigating it yet before I can completely rule it out.
                You've got the symptoms I've described in a series of articles about concurrent index activity colliding with an Oracle defect. The catalogue is here: http://jonathanlewis.wordpress.com/2009/09/19/index-itls/ (The clues iare the pct_used > 100 and lf_blk_len being down to half the block size for an 8KB block). You may also be suffering from trailing deletes as well.

                If you're licensed for the partitioning option then you might want to consider globally hash parititioning the index.

                Regards
                Jonathan Lewis