Questions On Parallel Degree For SQL Statements (Doc ID 2643108.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

• High load is associated with stats jobs for each schema that run from the scheduler
• Job is  running at the same time as the staggering of start times is not large enough
• Each schema has a job to connect full stats as follows:
  GATHER_FULL_STATS_JOB
  begin dbms_stats.gather_schema_stats (ownname => '',
  method_opt => 'for all columns size auto',
  estimate_percent => 100,
  cascade => true,
  degree=> DBMS_STATS.DEFAULT_DEGREE,
  options => 'gather'); end; 
   
• There are many queries in AWR showing following:
  select /*+ parallel_index(t, "_IDX1", 384) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad no_expand index_ffs(t, "_IDX1") */ count(*) as nrw, count(distinct sys_op_lbid(364661, 'L', t.rowid)) as nlb, count(distinct "SYS_NC00015$") as ndk, sys_op_countchg(substrb(t.rowid, 1, 15), 1) as clf from...
   

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.