MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

 » Database Journal Home » Database Articles » Database Tutorials MS SQL Oracle DB2 MS Access MySQL » RESOURCES SQL Scripts & Samples Tips » Database Forum » Slideshows
 Database Journal |DBA Support |SQLCourse |SQLCourse2

## Oracle

Posted September 2, 2019

# What is the Latch Event and Why Does It Occur?

By David Fitzjarrell

Posted in an Oracle forum some time back was a question on the `latch` event:

```
latch: parallel query alloc buffer
```

It was noted by the person posting the question that there's not much, if any, information on the `latch` event. The question posed in that forum was, essentially, just WHAT is that latch event and why does it occur? To answer this it will be necessary to understand some of the 'ins-and-outs' of parallel processing according to Oracle.

Parallel query processing allocates slaves to do the bulk of the work; as a result of that parallel coordinators allocate message buffers to handle intermediate results. Depending on the system configuration and the load the parallel slaves must handle a surprisingly large number of message buffers can be allocated. There are two equations that can be used to compute the number of buffers that will be required; those equations use the parallel degree value that is either calculated by Oracle or specified in a hint; for simplicity that value will be represented by `pd`. For a non-RAC Oracle database the number of buffers required would be calculated as:

```
pd(pd+1)*3
```

RAC adds a bit of complexity and multiple instances and, as a result, the equation is modified to be:

```
pd(pd+1)*4
```

On non-RAC systems various degrees of parallelism will be used to calculate the estimated number of buffers Oracle could allocate. Consider a query executed with a parallel degree set to 4; the first equation produces the following results:

```
4(4+1) * 3
```

becomes:

```
4(5) * 3
```

and simplifying:

```
20 * 3 = 60
```

Four parallel slaves can allocate 60 buffers. Now consider setting a parallel degree of 16. It's important to know that this buffer calculation is not proportional; a degree of 16 is four times the original setting, but the result is far from four times the original result:

```
16(16+1) * 3
```

Combining like terms produces:

```
16(17) * 3
```

which reduces to:

```
272 * 3 = 816
```

A parallel execution using 16 slaves requires a total of 816 message buffers, almost 14 times the number required by the execution at a parallel degree of 4. Given the computing power in modern servers, it is not a ridiculous thought to set a parallel degree of 128. Performing that calculation produces the following result:

```
126(128+1) * 3
```

Continuing as in previous calculations:

```
128(129) * 3
```

The result is:

:

```
16512 * 3 = 45936
```

This is a considerable increase over the buffers allocated for a parallel degree 4 execution and far larger than what would be expected if the result from the degree 4 execution is multiplied by 32. Having the "`latch: parallel query alloc buffer`" wait as one of the top waits isn't a far-fetched idea after all. A determining factor affecting this wait is where the PX message pool ('PX msg pool') resides; both the large pool and the shared pool are locations where this pool is usually found. The query provided below can be executed to learn where it located in a given database configuration:

```
break on pool skip 1
column name format a32

select
pool,
name,
bytes
from
v\$sgastat
where
pool = 'large pool'
or
name like '%PX%'
or
name like '%para%'
order by
pool, name;
```

An example of the results of this query follows:

```
BING @ quanghoo > break on pool skip 1
BING @ quanghoo > column name format a32
BING @ quanghoo >
BING @ quanghoo > select
2         pool,
3         name,
4         bytes
5 from
6         v\$sgastat
7 where
8         pool = 'large pool'
9    or
10         name like '%PX%'
11    or
12         name like '%para%'
13 order by
14         pool, name;

POOL               NAME            BYTES
------------ -------------------------------- ----------
large pool  PX msg pool         491520
free memory         7897088

shared pool   PX QC deq stats         1696
PX QC msg stats         3080
PX list of chunk lists  1088
PX msg pool struct      56
PX scan rate stats      1080
PX server deq stats     1696
PX server msg stats     3080
PX subheap              156344

POOL         NAME                   BYTES
------------ -------------------------------- ----------
shared pool  PX subheap desc            256
parallel kcbibr               32
parallel kcbibr dbwr bitv     16
parallel_max_servers          14720
param hash values             15912
parameter blocks              31824
parameter handle              137584
parameter string values       291240
parameter table block         1050648
parameter text value          9400
parameter value memory        1416

21 rows selected.

BING @ quanghoo &gt
```

From the example above it can be seen that the 'PX msg pool', for this database, is in the large pool. Versions of Oracle older than 11.2 (there are still some running) could be using the shared pool; this could result in the "`latch: parallel query alloc buffer`" wait to experience exceptionally long wait times as the result of contention for the available resources.

For RAC configurations the equation changes slightly but that does not mean that the calculated results follow that trend. Using the same parallel degree settings from the first round of calculations the differences start small but soon increase dramatically:

```
4(4+1) * 4
```

reduces to:

```
4(5) * 4
```

and produces a final result of:

```
20 * 4 = 80
```

an increase of 33% simply because this is Real Application Clusters. Moving on to a parallel degree of 16 the equation generates:

```
16(16+1) * 4
```

resulting in:

```
16(17) * 4
```

giving a final value of:

```
272 * 4 = 1088
```

This is 272 more buffers as a result of a clustered database configuration.

Engineered systems, from Oracle and from other vendors, make it possible to have a parallel degree set to 128 or higher. Considering parallel execution at a degree of 128 the computed allocation becomes:

```
128(128+1) * 4
```

Combining like terms:

```
128(129) * 4
```

produces:

```
16512 * 4 = 66048
```

66,048 is a large number of message buffers. Even 10 years ago a parallel degree of 128 could have crippled a transactional database and possibly some smaller data warehouses or data marts with waits for the 'parallel query alloc buffer' latch. Depending on the overall resource utilization (disk, memory, swap) some smaller engineered systems could still fall victim to this wait.

If parallel execution plans are rare in a database configuration this latch wait may be of little or no concern. If there is a reasonable chance that parallel execution can occur, then being aware that parallel buffer allocation can be an issue can make DBA life a bit less surprising and may shorten problem investigation and resolution times. This can all lead to a bit less stress on the DBA.

# # #

See all articles by David Fitzjarrell

Oracle Archives

 Latest Forum Threads Oracle Forum Topic By Replies Updated Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM no matching unique or primary key rcanter 1 April 25th, 12:32 PM Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM