Oracle Database - PARALLEL_MIN_PERCENT parameter

> Database > Oracle Database

1 - About

This initialization parameter is to guarantee a minimal DOP in a adaptive mode to a parallel query.

This parameter controls the minimal percentage of parallel server processes that must be available to start the operation; it defaults to 0, meaning that Oracle will always execute the statement, irrespective of the number of available parallel server processes.

3 - Example

If you want to ensure to get at least 50% of the requested parallel server processes for a statement:

SQL> ALTER SESSION SET parallel_min_percent=50 ;
SQL> SELECT /*+ parallel(s,128) */ COUNT(*)
FROM sales s ;
SELECT /*+ parallel(s,128) */ COUNT(*) FROM sales s
*
ERROR at line 1:
ORA-12827: insufficient parallel query slaves
available

If there are insufficient parallel query servers available – in this example less than 64 parallel servers for a simple SQL statement (or less than 128 slaves for a more complex operation, involving producers and consumers) - you will see ORA-12827 and the statement will not execute. You can capture this error in your code and retry later.

Advertising