MDSYS.SDO_ROUTER_PARTITION Parallel Hints Causing Slow Performance (Doc ID 1549936.1)

Last updated on JUNE 26, 2021

Applies to:

Symptoms

The MDSYS.SDO_ROUTER_PARTITION package has a few parallel hints that are causing significant waits due to parallel query coordination (PX Deq Credit: send blkd). The hints are using DEFAULT degree of parallelism, which degrades performance. Also, it looks like some of the underlying java calls are also using parallel hints.

For example, from the MDSYS.SDO_ROUTER_PARTITION package, the partition_router procedure is where the processing is held up, namely in the following spots:

-- Line 743:
stmt := 'UPDATE /*+ PARALLEL (node_part) */ node_part
  SET outedges = mdsys.sdo_router_partition.node_outedges(vertex_id, :e_part),
  inedges = mdsys.sdo_router_partition.node_inedges(vertex_id, :e_part)';

EXECUTE IMMEDIATE stmt USING 'edge_part', 'edge_part';

-- Line 786:
EXECUTE IMMEDIATE 'UPDATE /*+ PARALLEL (edge) */ edge
  SET partition_id = (SELECT p_id from node_part
  WHERE vertex_id = start_node_id)';
COMMIT;

-- Possibly from the Java classes (source is not available):

--SQL ID: 9nh0k0qxw0dwf
SELECT /*+ PARALLEL(restricted_nodes) */
  inedges, outedges FROM restricted_nodes


These statements result in 128 parallel worker processes, and all of them have to wait for the coordinator to continue their work.

For reference:
cpu_count = 64
parallel_threads_per_cpu = 2

Changes

Cause

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