DBMS_STATS.GATHER_TABLE_STATS( 'USRTEST', 'TABLE1', partname=>'PART2' );
I just noticed that gathering statistics on one table partition with above command actually does a full table scan of the whole table (all partitions) instead of gathering only for partition PART2. The command took way too long than it should, which caused me to look at what it was doing.
And it appears that default value of granularity parameter is AUTO which is described in the documentation as “determines the granularity based on the partitioning type”. But on PSOUG i also found info saying that AUTO granularity collects global, partition and subpartition level statistics. So the above statement is pretty misleading, since it doesn’t tell you that by simply looking at it. I for one thought it would only gather partition level stats. (this is a good example why read the documentation and check the default values and behavior of procedures).
Granularity parameter can be set to these values: ALL, PARTITION, SUBPARTITION, DEFAULT. Setting the granularity parameter to PARTITION directs the procedure to process only the partition in the partname parameter.
DBMS_STATS.GATHER_TABLE_STATS( 'USRTEST', 'TABLE1', partname=>'PART2', granularity => 'PARTITION' );
I have yet to confirm that granularity set to ALL with partname parameter set processes only one, specified partition or does it completely ignore the partname parameter.