Last updated on APRIL 30, 2013
Applies to:Oracle Spatial - Version 22.214.171.124 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 slave processes, and all of them have to wait for the coordinator to continue their work.
cpu_count = 64
parallel_threads_per_cpu = 2
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms