Gather_table_stats is running for longer time eventhough it has been enabled as parallel
DB version - 10.2.0.5
OS -- AIX
We are running a gather_table_stats procedure with parallel degree 12 as below.
DBMS_STATS.SET_PARAM('DEGREE', 12);
DBMS_STATS.GATHER_TABLE_STATS
(
OwnName => 'GMIS_ODS',
TabName => p_TableName,
Estimate_Percent => DBMS_STATS.Auto_Sample_Size,
Cascade => True
);
The table has 64 partitions however we can see that dbms_stats is not running in parallel.
GMIS_ODS 1023 6760 select substrb(dump(val,16,0,32),1,120) ep, cnt from (select /*+
GMIS_ODS 1023 6760 no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_e
GMIS_ODS 1023 6760 xact use_weak_name_resl dynamic_sampling(0) no_monitoring */max(
GMIS_ODS 1023 6760 "PKSAPLINEOFBUSINESSCODE") val,count(*) cnt from "GMIS_ODS"."AC
We are running a gather_table_stats procedure with parallel degree 12 as below.
DBMS_STATS.SET_PARAM('DEGREE', 12);
DBMS_STATS.GATHER_TABLE_STATS
(
OwnName => 'GMIS_ODS',
TabName => p_TableName,
Estimate_Percent => DBMS_STATS.Auto_Sample_Size,
Cascade => True
);
The table has 64 partitions however we can see that dbms_stats is not running in parallel.
GMIS_ODS 1023 6760 select substrb(dump(val,16,0,32),1,120) ep, cnt from (select /*+
GMIS_ODS 1023 6760 no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_e
GMIS_ODS 1023 6760 xact use_weak_name_resl dynamic_sampling(0) no_monitoring */max(
GMIS_ODS 1023 6760 "PKSAPLINEOFBUSINESSCODE") val,count(*) cnt from "GMIS_ODS"."AC
0