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.

Print Friendly
November 12th, 2009 by

facebook comments:

  • Danny

    Hi, Kevlar, I meet the same issue in one of our database. We have a max parallel server 160, but only 96 of them are available. If all of the parallel server have been used, then those sql query should run in parallel are running in serial, this has a great influence on our database performance.Do you figure why server available is 0 in your example?

  • Hi Danny,
    If you only have, as your example above states, 96 available and those are used, then you have utilized all CPU resource and none are left. The max parallel server is a “max” setting- it is not a guarantee. You must have the CPU resources behind it to support it. If you want more parallel available to important processes, look at what utilizes the most slaves and for the longest- take those statements and tune them. Also ensure that the degree of parallel, (DOP) that you have allocated to the process is the RIGHT amount. Test out the statement, using a hint, (/*+ PARALLEL(x) */) for DOP of 2, 4. 8 and so on and see what the percentage of increase OR decrease is experienced by each. You may be surprised to find out the fact of Parallel is that MORE parallel is not BETTER performance. There is always a best amount of DOP to be allocated to grant you the most optimal performance any anything more could be better applied to other processes.

    Hope this helps,
    Kellyn

  • Danny

    Hi, Kellyn

    Really appreciate your help, I am going to take a look at the CPU Usage and degree of parallel.

    Thanks again, Have a good day

  • serdar erdurak

    Hi All,

    I have a similar problem I can not work sql parallel.

    At a procedure When I use variables as filter condition it does not get parallel session but when I change them number values it is working fine and taking 32 parallel do u have any idea why does it happen.

    Insert into Table_A
    select * from table_B where timed_id >=20140101 works fine

    But

    Insert Into Table_A
    select * from table_B where timed_id >=v_start_date

  • Facebook
  • Google+
  • LinkedIn
  • Twitter