dbms_stats.GATHER_INDEX_STATS : Parallelized in 9.2.0.6 only ?

hello,

I see a behaviour with dbms_stats.gather_index_stats in that it honors the 'degree' parameter in a 9.2.0.6 d/b, but not in a 9.2.0.7 d/b.

All indexes on the (partitioned) table are regular (locally partitioned) indexes.

The only gather_index_stats parameters defined are ownname, indname, partname, estimate_percent (10) and degree=>8

For the 9.2.0.6 d/b, in v$sql i can see the "parallel_index" hint in the query generated by oracle:

select
/*+
cursor_sharing_exact
dynamic_sampling(0)
no_monitoring
no_expand
index_ffs(t,"XX_ADH_ASG_EED_IDX")
parallel_index(t,"XX_ADH_ASG_EED_IDX",8) */
count(*) as nrw,count(distinct sys_op_lbid(12552,'L',t.rowid)) as nlb, count(distinct "EFFECTIVE_END_DATE") as ndk, sys_op_countchg(substrb(t.rowid,1,15),1) as clf from "DPK.MY_TABLE" sample block ( 10.4860794158) t where "EFFECTIVE_END_DATE" is not null

However, in the 9.2.0.7 d/b, oracle inserts the "noparallel_index" hint in the generated sql.

I understand that in earlier versions of oracle we could not parallelize index stats gathering.
If this still holds true in 9i, could the 9.2.0.6 behaviour be caused by some other setting in the d/b?

gather_table_stats (with cascade=TRUE) also causes the index stats gathering to spawn multiple threads in 9.2.0.6 (ie., query in v$sql shows oracle inserting the 'parallel_index' hint. Also, in v$sql, users_opening=users_executing=8 ).
In 9.2.0.7, gather_table_stats spawns parallel threads for the table stats only - when it gets to the indexes, i see the "noparallel_index" hint in v$sql.

( with event 10046 on gather_index_stats, in the 'bdump' directory i could see twice as many .trc files as the value of 'degree' for the 9.2.0.6 run, but none for 9.2.0.7)

The gather_table_stats and gather_index_stats run 6-8 times quicker in 9.2.0.6 than in 9.2.0.7 (most likely due to the parallel threads spawned in 9.2.0.6) so i was looking for ways to replicate the same behaviour in 9.2.0.7 too.

Would appreciate any thoughts on this observation.

thanks
deepak Received on Tue Jul 04 2006 - 12:20:09 CDT