Why Isn’t Oracle Allocating More Parallel Slaves?

You have a statement that somebody, without thinking about resources, requested 96 total parallel processes for. They have now come to you, the DBA and want to know why, they aren’t getting the parallel they have requested:

The statement starts out something like this:
create table table_too_large_data
tablespace mart_data1 compress nologging pctfree 0 parallel 16
as
SELECT /*+ use_hash(i itmaff) parallel(i, 16) parallel(itmaff, 16) */
last_name ,…

The developer who wrote it, did not take the parameter PARALLEL_THREADS_PER_CPU into consideration, so they didn’t even realize they have requested pretty much everything above *2..

As a DBA, you should first query the following:
select * from V$PX_PROCESS_SYSSTAT;

Parallel Servers VALUE
Servers In Use 48
Servers Available 0
Servers Highwater 168 <– Max that could ever be allocated

The Highwater will match the parameter PARALLEL_MAX_SERVERS. This value is the max slave processes that can be spawned, but this does not guarantee that there are enough resources to spawn this many slaves, so keep that in mind. the servers in use tell you how many are being utilized currently and available tells you how many can be allocated- which at this time, is 0.

It is important to next see, how much has degradated? Is this the only process or are their others? If your environment utilizes a lot of parallel, it is important to track this information and keep track of how your parallel processing is performing or if it is being “abused”.

SELECT NAME, VALUE FROM GV$SYSSTAT
WHERE UPPER (NAME) LIKE ‘%PARALLEL OPERATIONS%’
OR UPPER (NAME) LIKE ‘%PARALLELIZED%’ OR UPPER (NAME) LIKE ‘%PX%’;

NAME VALUE
queries parallelized 56083
DML statements parallelized 6
DDL statements parallelized 160
DFO trees parallelized 56249
Parallel operations not downgraded 56128
Parallel operations downgraded to serial 951
Parallel operations downgraded 75 to 99 pct 0
Parallel operations downgraded 50 to 75 pct 0
Parallel operations downgraded 25 to 50 pct 119
Parallel operations downgraded 1 to 25 pct 2

As you can see from the above information, there have been almost 1000 processes downgraded to serial- no parallel at all, even though it was requested. Also note how many other have been downgraded by percentage. This number is calculated by the number requested, not the calcuation for PARALLEL_THREADS_PER_CPU, (which is most often set to a value of 2) so realize, degradation may be worse than it appears from the results in this query.

You should then examine what processes are getting parallel, tying up the resources and figure out how best that they can “play nice together”. This means parallel hints are set to a degree that fits the worst case scenario for the load on the database, not as if this were the only process running.