Hi,
I have this procedure implemented which does a gather stats on a partitioned table. Since the performance was degrading gradually, I changed the gather stats to gather at a partition level rather than a table level which worked wonders.
However, we have this test environment which has a lot of data spread across in 590 partitions ( I personally feel a cleanup should be done here). The gather stats on a partition level is taking quite some time here. When I changed the gather stats to gather at a granularity 'partition', the time taken for stats gathering reduced significantly.
This is where I got confused. I checked all the indexes created for that table (many people have worked on it) and noticed that they were all local indexes.
The following query proved my point:-
select INDEX_NAME, LOCALITY from all_part_indexes where table_name='<table_name>' showed the locality of all the indexes as LOCAL.
My understanding of gather stats is that if we do a partition level stats gathering without specifiying the granularity, the gather stats will gather the stats for that partition, build the local indexes and also rebuild the global indexes. Specifying the granularity to partition would simply gather the stats for that partition and build the local indexes.
If my understanding is correct, shouldnt gathering the stats with default granularity and 'partition' granularity take the same time since the table does not have any global indexes?
We use Oracle 10g.
These are the sqls I use:-
For default granularity:-
DBMS_STATS.gather_table_stats( ownname => SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
,tabname => '<table name>'
,partname => '<specific partition>'
,no_invalidate => FALSE );
And for 'partition' granularity:-
DBMS_STATS.gather_table_stats( ownname => SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
,tabname => '<table name>'
,partname => '<specific partition>'
, granularity => 'PARTITION'
,no_invalidate => FALSE );
Thanks in advance.