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

Last updated on APRIL 30, 2013

Applies to:

Oracle Spatial - Version 11.1.0.7 and later
Information in this document applies to any platform.

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 slave 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


Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms