Shulou(Shulou.com)05/31 Report--

This article focuses on "how to solve the ORACLE 12C: 'acknowledge over PGA limit' Wait Event problem", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "how to solve the ORACLE 12C: 'acknowledge over PGA limit' Wait Event problem"!

Users report that the database in 19c production environment is slow, and it only takes 2s to run a simple statement uat, but it takes 40s for prod to check the database.

First of all, check the awr report of the database and find that the exception waiting for acknowledge over PGA limit takes up a lot.

Top 10 Foreground Events by Total Wait Time

EventWaitsTotal Wait Time (sec) Avg Wait% DB timeWait Classacknowledge over PGA limit5,2551050.3199.87ms90.2Schedulercursor: pin S wait on X672.612.10 s6.2ConcurrencyDB CPU

4.6

. 4

When querying mos documents, it is found that this kind of problem is due to the fact that the PGA size reaches the value of PGA_AGGREGATE_LIMIT to prevent ORA-4036 errors. Later processes need to wait for other processes to release pga when assigning pga.

12c: 'acknowledge over PGA limit' Wait Event (Doc ID 2138882.1)

CAUSE

The "acknowlege over PGA limit" is a new wait event that was introduced with PGA_AGGREGATE_LIMIT in 12.1

And it will force a process that wants more PGA to wait a bit if the instance is getting close to hitting the limit.

The hope is some other process will release memory and avoid the ORA-4036 error.

SOLUTION

1. Set PGA_AGGREGATE_LIMIT=0 (as SYS user) to revert to 11g behavior of PGA memory management using PGA_AGGREGATE_TARGET parameter and the wait event will be alleviated.

ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=0 SID='*' SCOPE=BOTH

2. Set PGA_AGGREGATE_LIMIT to higher than the default value (as SYS user), which was calculated based on the PGA_AGGREGATE_TARGET, PROCESSES or "_ pga_limit_target_perc" setting and that will also help to reduce this wait event.

ALTER SYSTEM SET PGA_AGGREGATE_LIMIT= & new_value SID='*' SCOPE=BOTH

Oracle suggests setting PGA_AGGREGATE_LIMIT=0 or increasing the value of this parameter to solve the problem

ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=0 SCOPE=BOTH

However, this method is only a temporary solution. What caused it? we need to continue to check the use of pga.

First of all, determine whether the pga setting of the application is reasonable, that is, the number of connections and the size of pga. You can query the number of process and the size of pga. If

Obviously, if process is small and pga is heavily used, it may be caused by some application process or database process exception. You can query "yes" by the following statement

Which process exception caused

1. List the processes that take up the most pga

Select pid,spid,substr (username,1,20) "USER", program,PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,PGA_MAX_MEM

From v$process

Where pga_alloc_mem= (select max (pga_alloc_mem) from v$process

Where program not like'% LGWR%')

two。 View the details of the pga occupied by all sid in the database session

Set linesize 120

Set pagesize 120

Column spid heading 'OSpid' format A8

Column pid heading 'Orapid' format 999999

Column sid heading 'Sess id' format 99999

Column serial# heading 'Serial#' format 999999

Column status heading 'Status' format A8

Column pga_alloc_mem heading 'PGA alloc' format 999999999999

Column pga_used_mem heading 'PGA used' format 999999999999

Column username heading 'oracleuser' format A12

Column osuser heading'OS user' format A12

Column program heading 'Program' format A20

SELECT

P.spid

P.pid

S.sid

S.serial#

S.status

P.pga_alloc_mem

P.PGA_USED_MEM

S.username

S.osuser

S.program

FROM

V$process p

V$session s

WHERE s.paddr (+) = p.addr

And p.BACKGROUND is null / * Remove if need to monitor background processes * /

Order by p.pga_alloc_mem desc

3. Check pga over time

Select snap_id,round (value/1024/1024,0) from dba_hist_pgastat where name='total PGA allocated' order by snap_id

4. Check the pga assigned by the application process, and query for those larger than 50m

Select a. Sid from v$session a. Status where a.paddr=b.addr and a. Sqlgift _ and b.pga_alloc_mem/1024/1024 a. Lastworthy callbacks _

5. View current pga usage

Select * from v$pgastat

Through 1, we can find out whether there are abnormal database processes occupying a large pga,2, we can find out whether there are abnormal sessions taking up a large pga,3, we can query the growth of pga in the recent period of time, and we can judge when the exception began to occur. 4, we use Pga greater than 50m to check the application process.

If the database process is abnormal, you need to check the trc file corresponding to the system process for analysis.

If it is an application process exception, analyze it through the sid and spid, heapdump the process that is not released, see what is stored in memory, and then solve it with the application

At this point, I believe you have a deeper understanding of "how to solve the ORACLE 12C: 'acknowledge over PGA limit' Wait Event problem". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.