11 Replies Latest reply: Apr 28, 2009 8:30 AM by 591186 RSS


      Hi All, I am using the dbms_stats package to gather index statistics info.

      My questions:

      1- In analyze index package, results were stored in a table. What is the table for analyse index and what is the table for dbms_stats?
      2- What should the degree be? I am setting my degree to 2.

      Below is one index compute statistics that I am using:

      exec dbms_stats.GATHER_INDEX_STATS('WM','Employee_IDX1',degree=>2);
        • 1. Re: GATHER_INDEX_STATS question
          Mahesh Menon
          You can see the results in DBA_INDEXES data dictionary view.
          The results can be stored in a user specified table also by specifying stattab attribute of the procedure.
          degree attribute is where you specify the degree of parallelism.
          • 2. Re: GATHER_INDEX_STATS question
            What I meant by question 1 is: usually the result of the analyze index is stored in a table. what is the name of that table? Is it index_stats?

            If yes, it is always blank on my db, even after running an analyse index command. Any idea why?
            • 3. Re: GATHER_INDEX_STATS question
              Mohammed Mehraj Hussain
              Oracle Database collects the following statistics for an index. Statistics marked with an asterisk are always computed exactly. For conventional indexes, the statistics appear in the data dictionary views USER_INDEXES, ALL_INDEXES, and DBA_INDEXES in the columns shown in parentheses.


              *Depth of the index from its root block to its leaf blocks (BLEVEL)

              Number of leaf blocks (LEAF_BLOCKS)

              Number of distinct index values (DISTINCT_KEYS)

              Average number of leaf blocks for each index value (AVG_LEAF_BLOCKS_PER_KEY)

              Average number of data blocks for each index value (for an index on a table) (AVG_DATA_BLOCKS_PER_KEY)

              Clustering factor (how well ordered the rows are about the indexed values) (CLUSTERING_FACTOR)
              • 4. Re: GATHER_INDEX_STATS question
                Mahesh Menon
                GATHER_INDEX_STATS will populate results in DBA_INDEXES only by default...if you want to store the result to a separate table then you can create one stat table to store statistics using
                create_stat_table procedure of dbms_stats and assign that table in the query using stattab attribute.

                ANALYZE INDEX <index_name> VALIDATE STRUCTURE;
                The above command populates values to the INDEX_STATS table...

                Edited by: Oracle Hawk on Apr 28, 2009 4:23 PM
                • 5. Re: GATHER_INDEX_STATS question
                  Are you looking for the base table of all/dba/user_indexes view?

                  You can find out from dba_views for this view.
                  • 6. Re: GATHER_INDEX_STATS question
                    Is it index_stats?
                    yes. its index_stats. the index_stats is session specific and you can only use to validated one index at a time.
                    that is only the session that did the validate can see the data. Also, there is only ever one row in index_stats, and that row will be for the last index that you did the validate on.

                    If you want to retain the index_stats information, you can do the following:
                    create table idx_stats as select * from index_stats;
                    delete from idx_stats;
                    FOR x IN (SELECT index_name
                    FROM user_indexes
                    WHERE index_type = 'NORMAL')
                    EXECUTE IMMEDIATE 'analyze index ' || x.index_name || ' validate structure';
                    EXECUTE IMMEDIATE 'insert into idx_stats select * from index_stats';
                    END LOOP;
                    Do not execute this in production. DML enqueue lock held on the table that the index corresponds to, not just the index.

                    Edited by: Anantha on Apr 28, 2009 8:29 AM
                    • 7. Re: GATHER_INDEX_STATS question
                      Dear Anantha,
                      Thank you for your post. In fact I was trying your query 5 min ago (i found it in one of the posts)

                      To my recollection, 1 record is populated in index_stats, when analyze index is run.
                      In my case index_stats is always blank.

                      Any hints why?
                      • 8. Re: GATHER_INDEX_STATS question
                        GATHER_INDEX_STATS does not populate index_stats.
                        • 9. Re: GATHER_INDEX_STATS question
                          Dear Mohd,
                          I know that BLEVEL affects the performance if the depth > 3.(so in this case some DBAs rebuild the indexes)

                          How does LEAF_BLOCKS and AVG_LEAF_BLOCKS_PER_KEY affect the index performance?

                          How about the Clustering factor ?

                          • 10. Re: GATHER_INDEX_STATS question
                            How about the Clustering factor ?
                            [Clustering Factor A Consideration in Concatenated Index Leading Column Decision|http://richardfoote.wordpress.com/2008/02/15/clustering-factor-a-consideration-in-index-lead-column-decision-sweet-thing/]

                            following will help:

                            Re: When I should rebuild the index

                            which index rebuild?
                            • 11. Re: GATHER_INDEX_STATS question
                              Here's another info.

                              You can analyze the index using online at the end.
                              analyze index scott.emp_pkl validate structure online;
                              But this will not populate index_stats table. its just used for structure validation. Whereas, if you use,
                              analyze index scott.emp_pkl validate structure;
                              The index_stats table gets populated which is session specific.