My Oracle Support Banner

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

Last updated on APRIL 30, 2013

Applies to:

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


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:
  SET partition_id = (SELECT p_id from node_part
  WHERE vertex_id = start_node_id)';

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


To view full details, sign in with your My Oracle Support account.

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

In this Document

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.