Featured Database Articles

Oracle's 'Parallel Query Allocate Buffer' Latch

By David Fitzjarrell

In an Oracle forum recently the following question was posted:

hi Friends,
I see this wait event latch: parallel query alloc buffer, when a job meant for doing some cleanup ran this query.
Why does this wait event come happen? , i searched google,MOS no exact hit for explanation of the exact same event.
Looking at query does it happen because of the incorrect use of parallel hint i.e. no object mentioned and also it is not specified like parallel(5) .
It is not causing any big issues, just want to know for my understanding sake.
Regd,
Sachin

There were also queries, query results and parallel execution plans posted but those weren't included. The main issue here is the lack of information on the following event:

latch: parallel query alloc buffer

Parallel query slaves and coordinators allocate message buffers; what may be surprising is the sheer number of these message buffers that can be allocated for a given parallel execution. There's a formula that can be used to calculate the number of buffers required based upon the parallel degree calculated or specified; let's call that value p. Given that information the buffers required would be calculated as: p(p+1)*3 for non-RAC databases and: p(p+1)*4 for RAC installations. Let's go through some calculations for non-RAC systems to see how many buffers could be required for various degrees of parallelism. We'll start with something simple and consider a query executed with a parallel degree of 4; using the first formula provided we get:

4(4+1) * 3 -> 4(5) * 3 -> 20 * 3 -> 60

So, for a relatively small-scale parallel execution Oracle will need 60 message buffers for the parallel query coordinator and its slaves. Let's consider a parallel degree of 16; since the degree is 4 times the size of the original query (4) one might expect that the number of buffers might be 240 but that would be considerably less than Oracle would require:

16(16+1) * 3 -> 16(17) * 3 -> 272 * 3 -> 816

Related Articles

By running at a parallel degree of 16 Oracle now requires 816 message buffers, 13.6 times the number of buffers for the parallel degree 4 execution. Since it's not uncommon with today's servers, let's run one more calculation, this time at a parallel degree of 128:

126(128+1) * 3 -> 128(129) * 3 -> 16512 * 3 -> 45936

Now Oracle is allocating (or attempting to, anyway) 45,936 message buffers. Given that number of buffers to allocate, it's not difficult to understand why such a latch wait could be in the top waits for a given database. One factor affecting the length of time it takes Oracle to allocate such a large number of message buffers is where the PX message pool ('PX msg pool') is located; the shared pool or the large pool are common areas where this pool can be found. To find out where in your database configuration the 'PX msg pool' is found, the following query can be run:

break on pool skip 1
column name format a32
select * from v$sgastat where pool = 'large pool' or name like '%PX%' or name like '%para%' order by pool, name;

In my database, the 'PX msg pool' is found in the large pool, a desirable location since it's probably easier to allocate such buffers there than in the shared pool; using the shared pool could cause the 'latch: parallel query alloc buffer' wait to have wait times that could put it as a top-10 wait in an AWR or Statspack report due to contention for available resources.

Looking at another database, this time using version 11.2.0.3, with that same query produces:

Again, we see the 'PX msg pool' located in the large pool, but don't take that for granted as earlier releases may put that pool in the shared pool which could create long waits on the 'latch: parallel query alloc buffer' event.

Let's now go through some calculations for RAC systems to see how those numbers change. We'll use the same parallel degrees found in the first set of calculations:

4(4+1) * 4 -> 4(5) * 4 -> 20 * 4 -> 80

So, for a relatively small-scale parallel execution Oracle will need 80 message buffers for the parallel query coordinator and its slaves for a RAC configuration, 20 more than the non-RAC example. Let's consider a parallel degree of 16:

16(16+1) * 4 -> 16(17) * 4 -> 272 * 4 -> 1088

By running at a parallel degree of 16 in a RAC configuration, Oracle now requires 1088 message buffers. As Exadata and ODA sytems are more common now than they were a few years ago, a parallel degree of 128 is not unheard of, even with Auto DOP. For such a query execution, we find that Oracle will need:

126(128+1) * 4 -> 128(129) * 4 -> 16512 * 4 -> 66048

Now Oracle is allocating 66,048 message buffers, a large number, indeed. On 'lesser' systems (read that as 'commodity servers') such a buffer allocation could easily and quickly generate long event wait times for the 'parallel query alloc buffer' latch; such long waits could still be possible on Oracle's engineered systems if the overall utilization is high.

It may not be a top-10 wait in your database but it's good to be aware of what that latch wait event represents and why it can possibly generate long wait times, especially with a parallel degree of 16 or higher, because the number of message buffers Oracle requires can skyrocket.