Database Reference
In-Depth Information
exec dbms_resource_manager.begin_sql_block;
Insert /*+ parallel(10) */ into huge_table
Select /*+ parallel (10) */ col1, col2 .. from oltp_transactions..;
update emp.. ; -- Serial statement
--second parallel statement
insert /*+ parallel (10) */ into summary_table
select /*+ parallel (10) */ ..from huge_table;
commit;
exec dbms_resource_manager.end_sql_block;
With this strategy, multiple parallel statements in the block will be considered as one group, and the waits in
parallel statement queue will be minimal. The downside of this feature is that if there is a delay between two parallel
statement executions in the same parallel statement block, then PX servers can be unnecessarily held up by this
process.
Debugging PX Execution
Debugging PX execution problems in a RAC cluster requires reviewing PX execution environment setup. The PX
tracing event is useful to understand why a query was not parallelized even though the optimizer chose a PX plan.
9
In the following example, I will review a problem of a SQL statement executing serially, even though the
optimizer execution plan indicates a PX plan. I will trace PX execution setting _px_trace event to high, all.
10
alter session set "_px_trace"=high,all;
select /*+ parallel (8) full(h) full(l) */ count(*) from
oe_order_headers_all h, oe_order_lines_all l
where h.header_id = l.header_id and
h.open_flag='N';
Reviewing the trace file generated by _px_trace event, we can walk through the PX server allocation and parallel
execution. This part of the trace file shows the default DOP calculation. In this database, the calculated default
DOP is 168.
kxfrSysInfo [ 12/ 0]
DOP trace -- compute default DOP from system info
# instance alive = 3 (kxfrsnins)
kxfrDefaultDOP [ 12/ 0]
DOP Trace -- compute default DOP
# CPU = 28
Threads/CPU = 2 ("parallel_threads_per_cpu")
default DOP = 56 (# CPU * Threads/CPU)
default DOP = 168 (DOP * # instance)
Default DOP = 168
9
Note that discussion here is not “why is SQL statement not parallelized?”, but rather “why didn't the PX plan use the PX servers?”.
10
Note that _px_trace=high, all creates a huge amount of diagnostics details in PX trace files. You might need to contact Oracle
Support to understand further details printed in a PX trace file.
