Database Tuning (MOSC)

MOSC Banner

Gather_table_stats is running for longer time eventhough it has been enabled as parallel

edited Apr 18, 2011 10:45PM in Database Tuning (MOSC) 3 commentsAnswered
 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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center