Oracle Parallel Processing: ‘New’ and Improved

In Release 11.2, Oracle has provided three improvements to the earlier attempts at controlling parallel execution. These improvements make this feature more manageable, more scalable, and less likely to saturate server resources, such as memory and CPU, than earlier releases of the database. The first of those improvements that we will discuss is parallel statement queuing.

Getting into the Queue

Parallel query has been available in Oracle releases since version 7, and when used with care can significantly improve statement performance. On the other hand, when overused, it can bring the database server to its knees. Multiple users, all choosing parallel execution, can saturate CPU resources to the point that the benefits of parallel query become the curse of parallel query. Until Release 11.2, there was no way to control parallel execution by multiple users on the same system. That changed with parallel statement queuing. With parallel statement queuing, Oracle has a way to rein in parallel statements that otherwise would be too resource-intensive for a multiuser system.

Old School

To give some perspective on how parallel statement queuing has improved parallel query performance, it is necessary to examine the tool Oracle provided to manage this feature in earlier releases. The parallel_adaptive_multi_user parameter is quite a powerful tool, but it can create varying performance by deciding, at runtime, whether to parallelize a statement or not. It also determines how many slaves should be employed for a parallelized query. With this mechanism, the parallelism can go down dramatically, depending on the system resources in use at the moment the decision is made, which is the time the execution starts.

Statements can go from having 16 or more parallel slaves for one execution to having absolutely none the next time the same statement is executed. Remember that once the degree of parallelism (DOP) is set, it cannot be altered; the statement must run to completion at the assigned DOP, which may be one. The statement also cannot benefit from resources freed during its execution; if ten parallel query slaves become available during the serial execution of that statement, they cannot be repurposed to provide a higher DOP and, as a result, a shorter execution time. As an example, you have a statement that was assigned 24 parallel query slaves the last time it was run, with a resulting execution time of 1 minute. A short spike in activity occurs at the time this statement starts its next execution, downgrading it to a serial operation. Several seconds later, this spike is over; the statement is stuck, executing serially for possibly 24 minutes or longer. With no apparent rhyme or reason for this seemingly erratic behavior (from the user’s perspective), such inconsistent performance can make for unhappy users and very confused, unhappy developers.

The New Method

Parallel adaptive multi-user was a good start at controlling parallel execution, based on available resources, but version 11.2 introduces parallel statement queuing to the mix, allowing parallel execution to be placed on hold, until sufficient resources become available.

Note: It is best to set parallel_adaptive_multi_user to FALSE to prevent these two features from conflicting with each other. When both are enabled, we have seen parallel queries take much longer to execute.

How it works is simple, really. Enable the feature by setting parallel_degree_policy to AUTO. Next, set parallel_servers_target to the number of parallel slaves you want. The easy part is that you run your queries; Oracle takes care of the rest for you. In basic terms, a statement will be queued if it will require more parallel slaves than there are currently available. Once the required number of slaves are freed, the statement executes.

The V$SQL_MONITOR and GV$SQL_MONITOR views report on the execute status of submitted statements; basically, these views report that a statement is DONE (in some form) EXECUTING, or if parallel statement queuing is enabled, QUEUED. Given that information, it isn’t difficult to see which statements are waiting in the parallel statement queue. The standard EMP table from the demobld.sql script was used, although it was loaded with a very large number of records:

SSQL> select count(*)
2 from emp;

COUNT(*)
-------------
15032385536

SQL>

We chose to select the average salary from our large version of the EMP table from 12 different sessions. We then queried V$SQL_MONITOR to see how many of those sessions ended up in the queue. The query produced the following output:

SSQL> select sid, sql_id, sql_exec_id, sql_text
2 from v$sql_monitot monitor
3 where status = 'QUEUED'
4 order by 3;

SID  SQL_ID          SQL_EXEC_ID SQL_TEXT
---- ------------- ------------- ----------- ------------------------------------
1532 5du23va3p3ad0      16777216 select avg(sal) from emp
1059 5du23va3p3ad0      16777217 select avg(sal) from emp
1628 5du23va3p3ad0      16777218 select avg(sal) from emp
865  5du23va3p3ad0      16777219 select avg(sal) from emp
205  5du23va3p3ad0      16777220 select avg(sal) from emp
2199 5du23va3p3ad0      16777221 select avg(sal) from emp
1542 5du23va3p3ad0      16777222 select avg(sal) from emp
159  5du23va3p3ad0      16777223 select avg(sal) from emp
1888 5du23va3p3ad0      16777224 select avg(sal) from emp
1234 5du23va3p3ad0      16777225 select avg(sal) from emp
705  5du23va3p3ad0      16777226 select avg(sal) from emp

11 rows selected.

SQL>

The sql_exec_id is, basically, a run number indicating the statement’s position in the queue. It is also possible for the sql_exec_id to be the same for all queued statements (indicating simultaneous execution), if the sum of the parallel resources required for execution doesn’t exceed the maximum available for the database.

Control Issues

There may be times when you want to have some control over parallel statement queuing behavior. If you simply turn it on and let it run, it’s a first-in, first-out (FIFO) queue. It’s possible to bypass the queue using a hint. It’s also possible to use parallel statement queuing, even if the feature isn’t enabled at the database level. Two parameters are available that can affect parallel statement queuing, and the foremost of those is parallel_servers_target. The value for this parameter sets the number of parallel server processes Oracle can run before placing statements in the queue. There is a formula for computing the default value, as follows:

S((4*cpu_count)*parallel_threads_per_cpu)*active number of instances

Given a system with the following settings:

Scpu_count 24
parallel_threads_per_cpu 4

and two active instances, the default setting would be computed as follows:

S((4*24)*4)*2 = 768

This is a value that is much higher than you’d ever want to set; the calculation, as written, will compute a value intended to utilize all of the available resources for parallel query processes. Also, a setting that high would seriously impact OLTP transactions, as they could possibly starve, should a long-running, resource-intensive parallel query commandeer the available CPU and memory.

Like pga_aggregate_target, this parameter sets a target value, not a limit. There can be more parallel server processes running than the parameter initially configures. This can occur because the number of parallel slaves assigned to a statement may be up to twice the DOP setting. As an example, a system has parallel_servers_target set to 4 and Auto DOP set a degree of parallelism of 4. The statement could have up to 8 parallel query processes attached to it, which is greater than the target setting. Parallel query processes are assigned, in part, based on available resources, so occasionally, exceeding the parallel_servers_target setting isn’t a problem. The statement may end up in the parallel statement queue, but it won’t fail to run, because it’s using more parallel query slaves than parallel_servers_target would lead you to believe are allowed.

The second parameter to affect parallel statement queuing is a hidden parameter, _parallel_statement_queuing. The values for this parameter are TRUE and FALSE, with TRUE being the default setting when parallel_degree_policy is set to AUTO. It can be set independently of parallel_degree_policy, to enable and disable this feature. Even when parallel statement queuing is active it’s possible to bypass the queue entirely, with a hint. The NO_STATEMENT_QUEUING hint allows you to immediately execute parallel queries that otherwise would have been placed into the queue. Checking the queue for them is fruitless, as they do not appear with a status of “QUEUED.” Querying V$SQL/GV$SQL joined to V$SESSION/GV$SESSION will reveal that they are, indeed, running. Not only do they jump the queue, they also can spawn parallel query slaves in excess of the setting for parallel_servers_target. If you don’t have parallel statement queuing enabled, you can still use it with another hint: STATEMENT_QUEING. This will queue statements, should parallel resoures be scarce at the time the statement starts execution.

Necessary Settings

In addition to parallel statement queuing, Oracle has provided automatic degree of parallelism (Auto DOP) and in-memory parallel execution. All three are enabled when parallel_degree_policy is set to AUTO. Auto DOP allows the database to calculate the degree of parallelism on a query-by-query basis; this will be discussed in more detail in a separate section. This is the one improvement that requires a bit of effort to get working, as Oracle won’t activate this feature, if the I/O system has not been calibrated and I/O calibration is not a task to be executed during business hours. I/O calibration is just one part of parallel query processing configuration; several database initialization parameters control some aspect of parallel query execution. These parameters, their settings, and which features or aspects of parallel query execution they affect are listed in Table 1.

Parallel statement queuing and in-memory_parallel execution rely on the parallel_servers_target and the parallel_min_time_threshold parameter settings.

My Friend Auto

Auto DOP is a change in parallel operations introduced in Oracle Release 11.2. In prior releases of the database, parallel operations relied on hints at the query level or on the DEGREE and INSTANCES settings for the object in use. It is an unfortunate reality that a single DOP setting is rarely appropriate for all queries, at all times, for a given object. Arriving at usable values for the DOP that makes the query execution more efficient than serial execution and doesn’t adversely impact other queries and statements is a time-consuming trial-and-error process. It requires the development team to understand the platform in use and the workload that the system will be experiencing when the statement is executed. Another issue with the DOP: it cannot change during statement execution. Auto DOP was designed to overcome such problems, and it’s done its job admirably.

Calibrate Me

Auto DOP, when enabled and activated, causes Oracle to evaluate each statement, to determine if it should run in parallel. The DOP is set once this decision has been made.

Note: It is important to note that simply enabling Auto DOP doesn’t get it working; it needs to be activated by running an I/O calibration in each database where you want Auto DOP available.

The optimizer evaluates serial execution time and, in general, if that estimated time exceeds the setting for parallel_min_time_threshold, the statement will be executed in parallel. By default, this parameter is set to AUTO, providing a threshold value of 10 seconds. This parameter can be altered at the system level or at the session level, to implement a lower or higher parallel triggering threshold.

A supplied package, DBMS_RESOURCE_MANAGER, provides the CALIBRATE_IO procedure that is used to generate a random read-only workload across all RAC instances on the cluster. The procedure takes five parameters, two input values (number of disks and maximum estimated disk latency), and three output variables to receive the computed values for maximum I/Os per second, the maximum megabytes per second, and the calculated latency. An anonymous PL/SQL block can be used to call the procedure and return the computed metrics, as follows:

Set serveroutput on size 1000000
Declare
Calc_lat number;
Calc_iops number;
Calc_mbps number;
Begin
Dbms_resource_manager.calibrate_io(&dsks,&maxlat, Calc_iops, Calc_mbps, Calc_lat);
Dbms_output.put_line('Max IOPS : '||Calc_iops);
Dbms_output.put_line('Max mbytes-per-sec: '||Calc_mbps);
Dbms_output.put_line('Calc. latency : '||Calc_lat);
End;
/

As stated earlier, before Auto DOP is activated, the I/O system has to be calibrated.

Note: It cannot be stressed enough that I/O calibration is a resource-intensive operation and should not be run on a system experiencing heavy workloads. This is an after-hours operation that will take about 15 minutes or more to run.

Your patience will be rewarded when Auto DOP is functional.

A view, V$IO_CALIBRATION_STATUS, reports whether or not this calibration has been run. A database in need of calibration will show these results when V$IO_CALIBRATION_STATUS is queried:

SQL> select *
2 from v$io_calibration_status;

STATUS CALIBRATION_TIME
------------- -----------------------------------------------------------
NOT AVAILABLE

SQL>

Queries that would be executed in parallel on calibrated systems run serially on databases that have not had I/O calibration run. The execution plan returns an informative message about the Auto DOP calculations, immediately visible after query execution when autotrace is enabled:

SQL> select count(*)
      2 from dba_objects
      3 /

COUNT(*)
----------
369952

Execution Plan
----------------------------------------------------------
Plan hash value: 3660875064
----------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name        | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |    1 |       |    1099 (2)| 00:00:14 |
|   1 |   SORT AGGREGATE                      |             |    1 |       |            |          |
|   2 |     VIEW                              | DBA_OBJECTS |  320K|       |    1099 (2)| 00:00:14 |
|   3 |       UNION-ALL                       |             |      |       |            |          |
|*  4 |         FILTER                        |             |      |       |            |          |
|*  5 |       HASH JOIN                       |             |  320K|    21M|    1092 (2)| 00:00:14 |
|   6 |         INDEX FULL SCAN               | I_USER2     |  845 |  3380 |       5 (0)| 00:00:01 |
|*  7 |       HASH JOIN                       |             |  320K|    20M|    1085 (2)| 00:00:14 |
|   8 |         INDEX FULL SCAN               | I_USER2     |  845 | 18590 |       5 (0)| 00:00:01 |
|*  9 |    TABLE ACCESS STORAGE FULL          | OBJ$        |  320K|    13M|    1078 (2)| 00:00:13 |
|  10 |      NESTED LOOPS                     |             |    1 |    30 |       4 (0)| 00:00:01 |
|* 11 |         INDEX RANGE SCAN              | I_OBJ4      |    1 |    10 |       3 (0)| 00:00:01 |
|* 12 |         INDEX RANGE SCAN              | I_USER2     |    1 |    20 |       1 (0)| 00:00:01 |
|* 13 |      HASH JOIN                        |             |   29 |   232 |      7 (15)| 00:00:01 |
|  14 |         INDEX FULL SCAN               | I_LINK1     |   29 |   116 |       1 (0)| 00:00:01 |
|  15 |         INDEX FULL SCAN               | I_USER2     |  845 |  3380 |       5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND ("U"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' OR "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND "O2"."OWNER#"="U2"."USER#" AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))))
5 - access("O"."SPARE3"="U"."USER#")
7 - access("O"."OWNER#"="U"."USER#")
9 - storage("O"."LINKNAME" IS NULL AND "O"."TYPE#"<>10 AND BITAND("O"."FLAGS",128)=0 AND "O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_')
filter("O"."LINKNAME" IS NULL AND "O"."TYPE#"<>10 AND BITAND("O"."FLAGS",128)=0 AND "O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_')
11 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88)
12 - access("O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
filter("U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
13 - access("L"."OWNER#"="U"."USER#")

Note
-----

- automatic DOP: skipped because of IO calibrate statistics are missing

Statistics
----------------------------------------------------------
       17 recursive calls
     2 db block gets
     4788 consistent gets
     4772 physical reads
        0 redo size
      528 bytes sent via SQL*Net to client
      524 bytes received via SQL*Net from client
        2 SQL*Net roundtrips to/from client
        0 sorts (memory)
        0 sorts (disk)
        1 rows processed

SQL>

Running the calibration process is simple and straightforward, passing to the script the number of disks and an expected latency of 10. The procedure returns the actual latency, along with the maximum IOPs and the maximum megabytes per second the storage system can support:

SQL> @calibrate_io
Enter value for dsks: 36
Enter value for maxlat: 10
old 7: dbms_resource_manager.calibrate_io (&dsks, &maxlat, Calc_iops, Calc_mbps, Calc_lat);
new 7: dbms_resource_manager.calibrate_io (36, 10, Calc_iops, Calc_mbps, Calc_lat);
Max IOPS : 1022
Max mbytes-per-sec: 2885
Calc. latency : 11

PL/SQL procedure successfully completed.

Elapsed: 00:07:10.57

SQL>

Once this calibration has completed, a query of V$IO_CALIBRATE_STATUS reports a different result than it did previously:

SQL> select *
      2 from v$io_calibration_status;

STATUS        CALIBRATION_TIME
------------- -----------------------------------------------------------
READY         08-APR-13 11.24.49.977 AM

SQL>

If you query V$IO_CALIBRATION_STATUS during an I/O calibration run, the view will report results similar to the following output:

SQL> select *
      2 from v$io_calibration_status;

STATUS CALIBRATION_TIME
------------- -----------------------------------------------------------
IN PROGRESS 01-APR-12 09.34.13.083 AM

SQL>

After I/O calibation has been successfully completed, if the query or statement in question meets or exceeds the serial execution time represented by the parallel_min_time_threshold parameter, then Auto DOP will set the degree of parallelism, regardless of whether any of the objects are explicitly set for parallel execution. You may find that once I/O calibration is completed, some tasks may take longer to complete because they are queued. This may occur because the parallel resources allocated may not be sufficient for all the queries and statements that qualify for parallel execution. There is a Metalink Note, document id 1393405.1, that explains how to delete the I/O calibration statistics. In summary, there is a single table, RESOURCE_IO_CALIBRATE$, that the two views are based on. Deleting the data from this table clears the I/O calibration statistics, so that Auto DOP will no longer function. Statements in the queue at the time the statistics are deleted will remain in the queue until executed; no additional statements will be queued after the I/O calibration statistics are deleted. This is not as drastic a measure as it may appear at first, because DBMS_RESOURCE_MANAGER.CALIBRATE_IO deletes any existing I/O calibration values before it generates the current statistics, using this same statement, as a portion of the tkprof output shows:

SQL ID: bzhku92rujah0 Plan Hash: 256968859
delete from resource_io_calibrate$
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.00 0 1 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 DELETE RESOURCE_IO_CALIBRATE$ (cr=1 pr=0 pw=0 time=26 us)
0 0 0 TABLE ACCESS FULL RESOURCE_IO_CALIBRATE$ (cr=1 pr=0 pw=0 time=21
us cost=2 size=0 card=1)
********************************************************************************

Make It So

I/O calibration isn’t the only factor in enabling Auto DOP, as the parameter parallel_degree_policy must be set to AUTO or LIMITED. By default, it is set to MANUAL, which disables the three parallel features of Oracle Release 11.2: Auto DOP, parallel statement queuing, and in-memory parallel execution.

Another parameter, this one hidden, that affects one of the new features is _parallel_statement_queueing. When set to TRUE (the default), queuing is enabled, which allows Oracle to decide if a parallel statement can be executed at runtime or if it needs to wait in the queue until sufficient resources are available. And yet another hidden parameter, _parallel_cluster_cache_policy, controls whether in-memory parallel execution is available or not.

With parallel_degree_policy set to AUTO, it doesn’t matter if objects are created with a DEGREE greater than one, as the database decides which statements to run in parallel, how many parallel slaves to execute, and even how many RAC nodes to involve in the process. This is the beauty and power of Auto DOP.

As you can see Oracle has made significant improvements to parallel processing in 11.2.0.2 and later releases, making it less likely for parallel statements to commandeer the resources and starve other processes that also need to run. It isn’t the easiest system to configure but it’s not rocket science, either. Your patience will be rewarded.

See all articles by David Fitzjarrell