0 Replies Latest reply: Mar 21, 2013 11:38 AM by jc4ellucian RSS

    P_Analye_Targets, the degree parameter, and parallelism for Stats gathering

    jc4ellucian
      Some of the mappings we run that load large target tables (3-6 M rows, sometimes more) are taking a while to complete, yet the actual INSERT/MERGE statement seems to have completed - instead, it seems to be spending a lot of time doing a full table scan on the target table -- and looking at the SQL running, it looks like some sort of query from Stats gathering (getting distinct values on each column, etc). Tracing this back, looks like the mapping (PLSQL package) is in the Analyze_Targets procedure, which one assumes is firing off the Stats gatherhing query. Yet in the actual SQL of the stats query, it is explicitly using a "no_parallel" hint:

      SELECT /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad */
      COUNT (
      *),
      COUNT ("col1"),
      COUNT (DISTINCT "col1"),
      SUM (sys_op_opnsize ("col1")),
      SUBSTRB (DUMP (MIN (SUBSTRB ("col1", 1, 32)),
      16,
      0,
      32), 1, 120),

      which is limiting the ability of the parallel query server to take advantage of the 32 CPUs that this particular box has. Does anyone know how to configure the mapping to change this? Is this controlled by the DEGREE setting for a target table? Will play around w/ this some, but just curious if anyone knows this answer off-hand...

      Thanks!
      Jim C.