In order to gather statistics on an index we may use the Oracle supplied package DBMS_STATS:
SQL> Execute DBMS_STATS.GATHER_INDEX_STATS (‘HR’,’LOC_COUNTRY_IX’);
or gather statistics at index creation time:
SQL> Create index hr.loc_country_ix … compute statistics;
or gathering statistics when rebuilding an index:
SQL> Alter index hr.loc_country_ix rebuild compute statistics;
But after 10g even you do not mention to collect, statistics are collected by default;
Code listing 66a : Automatic Index Statistics Gathering with 10g Example
Indexing strategies are very important for OLTP applications. But over indexing affects DML operations. Oracle, if possible uses an already created index for primary or unique keys to minimize over indexing;
Code listing 66b : Minimizing Over Indexing Example
Also you may identify unused indexes beginning with Oracle9i. Statistics about the usage of an index can be gathered and displayed in V$OBJECT_USAGE. If the information gathered indicates that an index is never used after some time, the index can be dropped. Each time the MONITORING USAGE clause is specified, V$OBJECT_USAGE is reset for the specified index. The previous information is cleared, and a new start time is recorded;
Code listing 66c : Minimizing Over Indexing with Index Monitoring and V$OBJECT_USAGE
Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0
Refences Used :
Oracle® Database Performance Tuning Guide 10g Release 2 (10.2)-Chapter 2 Designing and Developing for Performance
Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog 
Good to see you online again!
Patrick
Thanks for this brief example of gathering index statistics.
For gathering stats There is an explanation of WHY, Is there an exact explanation about WHEN ?
We have to collect statistics for CBO to make the right decision of access paths.
But when it is necessary ?
In my way; I query the table ALL_TAB_MODIFICATIONS for gathering the change on data and make the decision from the result set. (If there is a change of 10 percent for insert update and delete i gather the stats of index and table). This table is very useful for understanding what is happening to my data ?. This table is started to be populated after the last statistics collection and holds all the change on the table.
like the query below
select dt.table_name,dt.owner,num_rows from all_TAB_MODIFICATIONS tb,dba_tables dt where
dt.TABLE_NAME=tb.TABLE_NAME
and (dt.num_rows*10/100
abi merhaba,
örneğin üzerinden denemeler yaptım, indeks oluşturuldugu anda analiz yapılıyor.Ancak anladıgım kadarıyla sonrasında otomatik bir analiz yok.(Herhlade bunu beklememeliydim :)
Çok yogun insert ler yaptıktan sonra baktım, analiz tarihi oluşturuldugu andaki tarih ve num_rows alanı yine ilk andaki ile aynı..Ne zamanki analyze index komutu verdim, bilgiler güncellendi.
Patrick hi, good to be back again :)
Coskan hi, when to gather statistics is a very challenging question especially after 10g RBO is obsolute. Here in my company DBA prefer to use DBMS_STATS’s STALE(10% changed) statistic option. But a research on asktom and lewis’s blog could be usefull of course :)
Hakki hi, after 10g there is a job scheduled for this purpose;
SELECT owner, job_name, enabled
FROM dba_scheduler_jobs
WHERE job_name = ‘GATHER_STATS_JOB’
This job is needed because RBO is dead, and your index will be analyzed when this job starts at its scheduled time if it is in STALE status. Also new created segments without statistics will be gathered.
Also when it comes to Datawarehousing and ETL, we may also advise that CASCADE option as TRUE on table or partitions is unnecessary since the bitmap indexes subpartitions will be re-gathered during rebuilds.
I tried to build a small example here;
http://tonguc.yilmaz.googlepages.com/dbms_stats_cascade_needed.txt