dcsimg
Database Journal
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
Free Newsletters:



News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

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