MDSYS.SDO_ROUTER_PARTITION Parallel Hints Causing Slow Performance
(Doc ID 1549936.1)
Last updated on APRIL 30, 2013
Oracle Spatial - Version 184.108.40.206 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)'; COMMIT;
-- Possibly from the Java classes (source is not available):