MDSYS.SDO_ROUTER_PARTITION Parallel Hints Causing Slow Performance
(Doc ID 1549936.1)
Last updated on JUNE 26, 2021
Applies to:Oracle Spatial and Graph - Version 188.8.131.52 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:
EXECUTE IMMEDIATE 'UPDATE /*+ PARALLEL (edge) */ edge
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 worker processes, and all of them have to wait for the coordinator to continue their work.
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