1. What is Oracle Parallel Execution?
Parallel Execution allows Oracle to perform specific database actions in parallel.
2. Which sub types of Parallel Execution exist?
The following types of Parallel Execution are most important:
Parallel Query
Parallel Query allows to parallelize certain components of selections, e.g.:
Full table scans
Index fast full scans
Access to inner tables of nested loop joins
Parallel DML
Parallel DML allows to parallelize DML operations:
UPDATE
DELETE
INSERT
Parallel DDL
Parallel DDL allows to parallelize DDL operations, e.g. :
CREATE INDEX
ALTER INDEX REBUILD
CREATE TABLE AS SELECT
Parallel Recovery
Parallel Recovery can be used to parallelize recovery activities:
RECOVER DATABASE
The most important Parallel Execution type is Parallel Query. Therefore this note focuses on the Parallel Query type. Nevertheless the technical details apply in the same way also to the other Parallel Execution types.
3. What are the advantages of Parallel Execution?
Based on Parallel Execution you are able to use more system resources like CPU or I/O at the same time for a specific operation. As a consequence the total runtime can significantly decrease.
4. What are restrictions and disadvantages of Parallel Execution?
The following disadvantages are possible:
Resource bottlenecks
Executing an operation in parallel involves increased resource usage (e.g. CPU or I/O). In the worst case a bottleneck situation can be the consequence and the whole system performance is impacted.
High parallelism in case of DEFAULT degree and many CPUs
If a Parallel Execution is performed with DEFAULT degree, a very high parallelism is possible. In order to avoid this it is usually recommended not to use the DEFAULT degree.
Wrong CBO decisions
Activated Parallel Query can significantly impact the calculations of the Cost Based Optimizer (see note 750631). In the worst case a good (sequential) index access can turn into a long running (parallel) full table
scan.
No RBO support
Parallel Query requires the CBO because the Rule Based Optimizer isn't able to handle it. If the CBO is used for an access on tables without statistics, this can cause performance problems.
Parallel DDL activates segment parallelism
If a parallel DDL operation like ALTER INDEX REBUILD PARALLEL is performed, the parallelism degree for the index remains even after the DDL operation is finished. As a consequence unintentionally parallel query
might be used. In order to avoid problems you have to make sure that you reset the parallelism degree of the concerned segments to 1 after the DDL operation. The BR*TOOLS perform this activity automatically after
parallelized DDL operations.
Wrong result sets with 10g
Due to the Oracle bug described in note 985118 it is possible that Parallel Executions return a wrong result set.
Insufficient explain information
With older SAP releases parallel query is not taken into account in the explain functionality in transaction ST04 or ST05 (see note 723879). As a consequence misconceptions and irritations are possible.
Shared pool allocation
Parallel query can consume significant amounts of shared pool memory for communication purposes. Particularly in the case of high values for PARALLEL_MAX_SERVERS and
PARALLEL_EXECUTION_MESSAGE_SIZE up to several GB of shared pool memory can be allocated. A consequence can be ORA-04031 errors (see note 869006).
No parallel DML with IOTs
It is not possible to use parallel DML in combination with (unpartitioned) Index Organized Tables (IOTs).
5. How is Parallel Execution performed on a technical layer?
The following processes are used to perform a Parallel Execution:
Parallel Execution Coordinator
The Parallel Execution Coordinator (later on referred to as "coordinator") is the control process that distributes the work across the Parallel Execution Slaves.
Up to Oracle 9i the coordinator splits SQL statements into smaller pieces (in terms of the working set) and passes the "smaller" SQL commands to the Parallel Execution Slaves. Typical SQL statements that
are transformed by the coordinator look like:
SELECT /*+ CIV_GB */ A1.C0,AVG(SYS_OP_CSR(A1.C1,0)), MAX(SYS_OP_CSR(A1.C1,1)) FROM :Q490001 A1 GROUP BY A1.C0
SELECT /*+ PIV_GB */ A1.C1 C0,SYS_OP_MSR(AVG(A1.C0),MAX(A1.C0)) C1 ...
... /*+ ORDERED NO_EXPAND USE_HASH(A3) */ ...
... /*+ Q354359000 NO_EXPAND ROWID(A4) */ ...
... PX_GRANULE(0, BLOCK_RANGE, DYNAMIC) ...
SELECT /*+ PIV_SSF */ SYS_OP_MSR(MAX(A1.C0)) FROM ...
As of 10g the original SQL statement is passed to the Parallel Execution Slave without any transformation.
Parallel Execution Slaves (in the following named "slaves")
There are two types of Parallel Execution Slaves:
Producers
The producers read the source data and pass it to the consumers or the coordinators.
Consumers
The consumers take the data from the producers and process it. Consumers are only needed if the data from the producers needs to be post-processed (e.g. sorted).
The communication and data transfer between the processes is based on queues. The communication structures are part of the shared pool:
PX msg pool
Communication area for the parallel query processes
Size depends mainly on PARALLEL_EXECUTION_MESSAGE_SIZE, PARALLEL_MAX_SERVERS and the actually used parallel query slaves
PX subheap
Memory structure with additional parallel query related information
Usually small compared to "PX msg pool"
Blocks that are read via Parallel Execution are always read directly from disk bypassing the Oracle buffer pool. For more information about this "direct path" operation see the "direct path read" section in note
619188.
In order to make sure that the most recent data is read from disk a segment specific checkpoint is performed before the "direct path read" operations are started.
6. Which parameters exist in relation to Parallel Execution?
The following Oracle parameters are most important with regards to Parallel Execution:
CPU_COUNT
CPU_COUNT is a parameter that reflects the number of CPUs on the database server. It is used for several Oracle internal purposes and also influences the DEFAULT parallelism used by Oracle (see below).
As CPU_COUNT influences Oracle at a lot of locations, the default value should not be changed.
PARALLEL_EXECUTION_MESSAGE_SIZE
This parameter determines the size of the communication buffer between the differen Parallel Execution processes.
In order to avoid a bottleneck it's recommended to set this parameter to 16384.
PARALLEL_INSTANCE_GROUP
This parameter can be used in RAC environments in order to restrict Parallel Executions to a sub set of the existing RAC instances.
PARALLEL_MAX_SERVERS
With this parameter the maximum number of simultaneously active slave processes can be specified. If more slaves are requested at a certain time the requests are downgraded and a smaller parallelism is
used.
SAP recommends setting this parameter to 10 times the number of CPUs available for the Oracle database in OLAP and Oracle 10g environments.
PARALLEL_MIN_SERVERS
This parameter specifies how many slave processes are created during database startup. If more slaves are needed, they are created on demand.
It's recommended to keep this parameter on the default of 0 in order to avoid unnecessarily started slave processes.
PARALLEL_THREADS_PER_CPU
This parameter influences the DEFAULT parallelism degree (see below).
In order to avoid overload situations with DEFAULT parallelism it's recommended to set this parameter to 1.
PARALLEL_ADAPTIVE_MULTI_USER
When this parameter is set to TRUE (what is default for Oracle 10g) parallel executions may be downgraded by Oracle even before PARALLEL_MAX_SERVERS is reached in order to limit the system load. If
you need to guarantee maximum parallelization (like during reorganizations or system copies) it is advisable to set this parameter to FALSE. During normal operation the value TRUE should be okay because resource
bottlenecks can be avoided.
7. How are activation and degree of parallelism determined?
The following points have to be taken into account in order to determine if and to what extent Parallel Query is used:
The operation must be parallelizable (e.g. full table scan, scan of partitioned index). An index range scan on a non-partitioned index e.g. can't be performed in parallel.
The parallelism must be activated on segment or on statement level.
Segment level
Parallelism on segment level can be activated with the following command:
ALTER INDEX PARALLEL ;
In order to check the parallelism for a certain segment, you can use the following selection:
SELECT DEGREE FROM DBA_TABLES WHERE TABLE_NAME = ''; SELECT DEGREE FROM DBA_INDEXES WHERE INDEX_NAME = '';
Statement level
On SQL statement level the following hint can be used to activate parallelism (see note 772497):
If is a positive number, up to producers and consumers can be used for a parallel query (-> 2 * slaves).
If DEFAULT is specified for , CPU_COUNT * PARALLEL_THREADS_PER_CPU producers and CPU_COUNT * PARALLEL_THREADS_PER_CPU consumers can be used for a parallel query.
If the PARALLEL_MAX_SERVERS limit is exceeded taking into account all simultaneously active Parallel Executions, the degree of parallelism is reduced.
8. To what extent does SAP use parallelism on segment and statement level?
Per default no segments with activated parallelism are delivered by SAP.
Parallelism on statement level (-> PARALLEL hint) is frequently used in BW environments (e.g. in case of aggregate rollup). In OLTP environments the PARALLEL hint is used only in exceptional situations.
9. How can segments with activated parallelism be determined?
With the following selection all tables and indexes can be determined that have a parallelism degree > 1 or DEFAULT:
SELECT
TABLE_NAME SEGMENT_NAME,
DEGREE,
INSTANCES
FROM DBA_TABLES
WHERE
OWNER LIKE 'SAP%' AND
(DEGREE != ' 1' OR INSTANCES != ' 1')
UNION
SELECT
INDEX_NAME SEGMENT_NAME,
SUBSTR(DEGREE, 1, 10) DEGREE,
SUBSTR(INSTANCES, 1, 10) INSTANCES
FROM DBA_INDEXES
WHERE
OWNER LIKE 'SAP%' AND
INDEX_TYPE != 'LOB' AND
(DEGREE != '1' OR INSTANCES NOT IN ('0', '1'));
10. How can the parallelism degree of segments be modified?
With the following commands the parallelism for a table or index can be deactivated:
ALTER TABLE PARALLEL (DEGREE 1 INSTANCES 1); ALTER INDEX PARALLEL (DEGREE 1 INSTANCES 1);
In order to set the parallel degree to a certain value, you can use:
ALTER INDEX PARALLEL ;
11. How can you determine to what extent the parallelism is downgraded?
The following selection returns how many Parallel Executions are downgraded to what extent:
SELECT NAME, VALUE FROM V$SYSSTAT
WHERE NAME LIKE 'Parallel operations%';
12. Which wait events exist with regards to Parallel Execution?
The following are the main wait events in the area of Parallel Execution (see note 619188 for more information on Oracle wait events). As it is difficult to distinguish between idle events and busy events in the area of
parallel execution, these two types are not distinguished.
PX Idle Wait
A slave waits for acquiration by a coordinator. This is a real idle event.
PX Deq: Execute Reply
The coordinator waits for slaves to finish their work.
PX Deq: Table Q Normal
A consumer waits for data from its producer.
PX Deq: Execution Msg
A slave waits for further instructions from the coordinator.
PX Deq Credit: send blkd
A producer wants to send something to its consumer, but the consumer has not finished the previous work, yet. The same is valid for a slave that want to send something to its coordinator.
In order to tune this wait event you have to check why the consumer / coordinator is not able to process the producer data fast enough. For example deactivated parallelism for DML operations can result in high "PX
Deq Credit: send blkd" waits for parallelized activities containing DML operations (e.g. INSERT ... SELECT). In this case "ALTER SESSION ENABLE PARALLEL DML" can optimize the parallelization.
If an SAP process fetches record by record from the database using parallel query and performs some processing on ABAP side in between, parallel execution slaves wait for "PX Deq Credit: send blkd" during the
ABAP processing. In this case huge wait times for this wait event and huge elapsed times for the SQL statement can be seen although the real execution time on database side is very small. In this scenario "PX Deq
Credit: send blkd" can be treated as an idle event.
PX qref latch
A consumer has to wait for its communication queue to be available. Make sure that PARALLEL_EXECUTION_MESSAGE_SIZE is set to 16384 in order to avoid many small communications and reduce this kind of
contention.
Another possible reason for "PX qref latch" waits is the fact that the consumer (or coordinator) processes the data slower than the producer generates it. In this case further optimization is hardly possible.
PX Deq: Signal ACK
PX Deq: Join ACK
PX Deq Credit: need buffer
These wait events are related to process communication: The coordinator waits for an acknowledgement of a control message ("PX Deq: Signal ACK") or join request ("PX Deq: Join ACK") from a slave or processes
need a queue buffer in order to send data ("PX Deq Credit: need buffer"). It is normal that these events occur (and so Oracle treats them partially as idle events), but if you have doubts that too much time is spent for
communicating, you should check for overall resource bottlenecks (e.g. CPU) and unnecessary parallelization of small tasks.
PX Deq: Parse Reply
The coordinator waits until the slaves have parsed their SQL statements. In case of increased waits for "PX Deq: Parse Reply" you should check if the shared pool is sized sufficiently (note 789011) and if there are
some parallel query SQL statements with a high parse time (note 712624 (24)).
reliable message
The coordinator waits for a reply from another instance in RAC environments. This can e.g. happen if Oracle parameters are set system wide using ALTER SYSTEM.
To a minor extent waits for "reliable message" can also show up in non RAC systems.
latch free (for "query server process" latch)
The "query server process" latch is allocated if an additional slaves are created. This is necessary if more slaves then defined with PARALLEL_MIN_SERVERS are needed. In order to optimize this kind of latch wait
you should check on the one hand side if Parallel Execution is activated by accident (e.g. on segment level) and switch it off in this case. Alternatively you can consider setting PARALLEL_MIN_SERVERS to a higher
value.
13. Where can I find more information about Parallel Execution activites on the system?
The following selection from V$PX_SESSION can be used to monitor the current activities of Parallel Execution processes:
SELECT
PS.SID,
DECODE(SERVER_SET, NULL, 'COORDINATOR', 1, ' CONSUMER',
' PRODUCER') ROLE,
DECODE(SW.WAIT_TIME, 0, SW.EVENT, 'CPU') ACTION,
SQ.SQL_TEXT
FROM
V$PX_SESSION PS,
V$SESSION_WAIT SW,
V$SQL SQ,
V$SESSION S,
AUDIT_ACTIONS AA
WHERE
PS.SID = SW.SID AND
S.SID = PS.SID AND
S.SQL_ADDRESS = SQ.ADDRESS (+) AND
AA.ACTION = S.COMMAND
ORDER BY PS.QCSID, NVL(PS.SERVER#, 0), PS.SERVER_SET;
In addition the following Parallel Execution related views exist:
V$PQ_SYSSTAT
The view V$PQ_SYSSTAT contains general information about Parallel Execution activities like number of Parallel Queries, Parallel DML and Parallel DDL or currently active sessions.
V$PQ_SLAVE
This view contains information about the existing parallel query slaves.
V$PQ_TQSTAT
Statistics for different parallel queries (separated by DFO_NUMBER)
14. How can the progress of a parallelized full table scan be monitored?
Create the following fuction in the schema of a user who has the the the privileges
grant analyze any to ;
grant select any table to ;
directly (not via role) granted. Usually not even the user 'SYS' has these privileges granted directly.
create or replace function sap_fts_progress_parallel
(owner varchar2, tab_name varchar2, sess number, blocks number)
RETURN char IS
stat_nr number;
tot_blk number;
unu_blk number;
dummy number;
query_act number;
perc number;
hwm number;
begin
if blocks=-1 then
dbms_space.unused_space(owner, tab_name, 'TABLE', tot_blk,
dummy, unu_blk, dummy, dummy, dummy, dummy, dummy);
hwm := tot_blk-unu_blk;
else
hwm := blocks;
end if;
select statistic# into stat_nr from v$statname
where name='physical reads direct';
select count(1) into query_act from v$px_sesstat
where statistic#=stat_nr
and qcsid=decode(sign(sess),1,sess,qcsid);
if query_act=0 then
return 'Query not active';
else
select sum(value)/decode(hwm,0,1,hwm)*100 into perc
from v$px_sesstat where statistic#=stat_nr
and sid <>qcsid
and qcsid=decode(sign(sess),1,sess,qcsid);
return to_char(perc,'999.9')||'% of '||to_char(hwm)||' Blocks';
end if;
end;
/
Doublecheck if the character '|' is copied when you cut and paste the function definition.
Execute the function with
select sap_fts_progress_parallel('','',,-1) fromdual;
when your parallel query is running to get the percentual progress and the total number of blocks to be read (example output: '43.7% of 6973 Blocks'). .
is the object on which the query runs.
specifies the session id which initiated the query. For larger tables you can increase performance if you specify the total number of blocks given back from the first query at the second and all further executions:
select sap_fts_progress_parallel('','
',,)from dual;
If you are sure that there is no other parallel query running concurrently then you can also set equal '-1'. If - against the prerequisite - another query runs then the percentual value is undefined.
15. Which error can show up in relation to Parallel Execution?
The following errors are important in the area of Parallel Execution:
ORA-12801: error signaled in parallel query server
This error indicates that a parallel query process had to terminate due to an error situation. As described in note 636475, the ORA-12801 is a secondary Oracle error code. You have to check for the real error message
that accompanies the ORA-12801 error.
ORA-12805: parallel query server died unexpectedly
This error indicates that a parallel query process terminated in a hard way. In this case you have to analyze trace files and the alert log for more information.
16. How does the INSTANCES setting influence the parallelism?
The INSTANCES storage parameter is mainly intended for RAC environments and has a similar effect like the PARALLEL storage parameter. In order to avoid confusion it is recommended not to use the INSTANCES
parameter setting.
17. How can parallelism be activated on session level?
You can enable the different types of parallelism on session level using: ALTER SESSION ENABLE PARALLEL QUERY;
ALTER SESSION ENABLE PARALLEL DDL;
ALTER SESSION ENABLE PARALLEL DML;
While PARALLEL QUERY and PARALLEL DDL are activated per default, PARALLEL DML has to be activated explicitly if required. This can e.g. be useful in case of parallelized BRSPACE online reorganizations. Per
default only the reading of the source table is parallelized while the writing into the target table is done sequentially. By activating PARALLEL DML the writing can be parallelized.
Additionally it is also possible to force parallelism on session level even if no PARALLEL hint or segment parallelism is used:
ALTER SESSION FORCE PARALLEL QUERY PARALLEL ;
ALTER SESSION FORCE PARALLEL DDL PARALLEL ;
ALTER SESSION FORCE PARALLEL DML PARALLEL ;
