This content has been marked as final. Show 11 replies
You can see the results in DBA_INDEXES data dictionary view.1 person found this helpful
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.
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?
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.1 person found this helpful
*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)
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
Are you looking for the base table of all/dba/user_indexes view?
You can find out from dba_views for this view.
1 person found this helpful
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:
Do not execute this in production. DML enqueue lock held on the table that the index corresponds to, not just the index.
create table idx_stats as select * from index_stats; delete from idx_stats; BEGIN FOR x IN (SELECT index_name FROM user_indexes WHERE index_type = 'NORMAL') LOOP EXECUTE IMMEDIATE 'analyze index ' || x.index_name || ' validate structure'; EXECUTE IMMEDIATE 'insert into idx_stats select * from index_stats'; COMMIT; END LOOP; END; /
Edited by: Anantha on Apr 28, 2009 8:29 AM
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?
GATHER_INDEX_STATS does not populate index_stats.
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 ?
1 person found this helpful
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?
Here's another info.1 person found this helpful
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.