How can we get email whenever PGA memory exceeds a limit in Oracle database

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
• How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
• How can we get email whenever PGA memory exceeds a limit in Oracle database - 1 March 2024

When an Oracle database is heavily used by many connections / sessions, one or some of them may consume a large percentage of memory (RAM). If there is a problem and this is not dealt with due to lack of memory, the operating system and Oracle may start to kill sessions by themselves indiscriminately even until the entire database instance goes down.

So in this article we will see a way that as soon as the memory (RAM) and specifically the memory called PGA in Oracle exceeds a limit, it sends a warning email containing the size of the memory and the information of the session that occupies it more.

What is PGA memory?

THE Program Global Area or PGA is the non-shared memory that each individual connection / session occupies in the base. The PGA is used to store session information and process SQL queries for operations such as sorting.

The footsteps

In previous article we had seen how we can set one up Linux servers (Oracle Linux based on RHEL specifically) so that we receive an email whenever an error occurs in the Alert log of the database instance. Accordingly now we will see the steps to receive email when PGA memory exceeds some limit.

Create an SMTP relay with Google's gmail

If we have our own relay on the exchange server with configured smtp that will send the emails, we don't need this step.

But now we will see what we do in case we want to send the emails through Gmail.

First we need to connect to gmail account security.

There we should activate 2 factor authentication and then create an application password.

After getting the code from gmail, go to our server and add to the mail configuration our email along with the code we got:

[oracle@dp-gr ~]$ sudo vi /etc/mail.rc 

account gmail {
    set smtp-use-starttls
    set ssl-verify=ignore
    set smtp-auth=login
    set smtp=smtp://smtp.gmail.com:587
    set from="info@dataplatform.gr"
    set smtp-auth-user=dataplatform.gr@gmail.com
    set smtp-auth-password="edw_vazoume_to_app_password"
    set ssl-verify=ignore
    set nss-config-dir=/etc/pki/nssdb
}

We are ready to test that we can send email:

[oracle@dp-gr ~]$ echo "Test" | /usr/bin/mail -A gmail -s "dokimastiko" ToEmailMas@outlook.com

How to find the total PGA memory of the instance and the session that consumes the most

We create the following script that outputs a warning message with the total PGA memory occupied by all the sessions of the instance and the information of the session that currently occupies the most:

[oracle@dp-gr ~]$ vi check_pga.sql

set echo off
set pagesize 0
set feedback off
select 'Warning: Session''s Program Page Area (PGA) Memory on '|| (select instance_name from v$instance) || '@' ||(select host_name from v$instance)||' is too high!!!       ---> '  ||to_char(pga_sum_gb, '999g999g990d00', 'NLS_NUMERIC_CHARACTERS=,.')|| ' GB' as PGA_Response 
 FROM (select sum(pga_alloc_mem/1024/1024/1024) as pga_sum_gb from v$process)
where pga_sum_gb > 0.01; -- DONT FORGET TO SWITCH
SELECT 'Highest PGA Session: ' 
   || ' PGA:' || to_char(p.pga_alloc_mem/1024/1024/1024, '999g999g990d00', 'NLS_NUMERIC_CHARACTERS=,.')||' GB '
   || ' ---- LOGON: ' || to_char(h.LOGON_TIME,'YYYY/MM/DD HH:MI:SS') 
   || ' ---- SID: ' || h.SID
   || ' ---- SERIAL: ' || h.SERIAL#
   || ' ---- PROCESS: ' || p.SPID
   || ' ---- USERNAME: ' || u.username
   || ' ---- OSUSER: ' || h.osuser
   || ' ---- MACHINE: ' || h.machine
   || ' ---- PROGRAM: ' || h.program
   || ' ---- MODULE: ' || h.module
   || ' ----               SQL TEXT: ' || s.sql_text
   FROM
   gv$session h
LEFT JOIN gv$SQLAREA s ON h.sql_hash_value = s.hash_value and h.sql_ADDRESS = s.ADDRESS and h.inst_id = s.inst_id 
LEFT JOIN DBA_USERS u ON h.USER# = u.USER_ID
LEFT JOIN gv$process  p ON p.ADDR = h.PADDR and p.inst_id = h.inst_id 
WHERE  1=1
and (select sum(pga_alloc_mem/1024/1024/1024) as pga_sum_gb from v$process) > 0.01 -- DONT FORGET TO SWITCH
and rownum <=1
ORDER BY p.pga_alloc_mem DESC;
quit;
/

*In the two fields in lines 6 and 25, we define the limit at which the query result will be returned from this total PGA memory and above. For the example I put 0.01 GB. If we wanted it to show us only whenever it exceeds 30 GB we would change it to 30.

How do we send emails when PGA passes the threshold we set

Now we will create an executable that will run the above query, exporting the results to a text file. In case the file is more than 3 lines then it will send an email with the title “Oracle DB Alert ORCL” in the emails we have defined with a warning as text with the total PGA and the information of the session that occupies the most.

The reason that the process only completes if the file contains more than 3 lines is so that it does not send an email if there is no alert and the text file is empty.

So we create the executable with the following code:

[oracle@dp-gr ~]$ vi check_pga_live.sh

#!/bin/bash
sqlplus -S "/as sysdba" @/home/oracle/check_pga.sql > /home/oracle/check_pga.log
cnt=$(wc -l </home/oracle/check_pga.log)
echo $cnt
if [ $cnt -ge "4" ]
then
        /usr/bin/mail -A gmail -s "Oracle DB high PGA on ORCL" \
                -r info@dataplatform.gr \
                patatakia@outlook.com, dokimi@outlook.com \
         < /home/oracle/check_pga.log
fi

We can alternatively run it remotely on another machine with the SYS user via ezconnect:

#!/bin/bash
sqlplus -S "sys/Kwdikos@hostname:1521/ORCL as sysdba" @/home/oracle/check_pga.sql > /home/oracle/check_pga.log
cnt=$(wc -l </home/oracle/check_pga.log)
echo $cnt
if [ $cnt -ge "4" ]
then
        /usr/bin/mail -A gmail -s "Oracle DB Alert ORCL" \
                -r info@dataplatform.gr \
                patatakia@outlook.com, dokimi@outlook.com \
         < /home/oracle/check_pga.log
fi

We don't forget to have given execute permission before executing it:

[oracle@dp-gr ~]$ chmod +x check_pga_live.sh
[oracle@dp-gr ~]$ ./check_pga_live.sh

How do we schedule it to check every 5 minutes for Alerts

Since our executable is ready, we can add a line to the crontab in which we will set it to run every 5 minutes:

[oracle@dp-gr ~]$ crontab -e

#crontab
0,5,10,15,20,25,30,35,40,45,50,55 * * * * . ~/.bash_profile && (/home/oracle/check_pga_live.sh  >> /dev/null 2>&1)

The result

Now we will see that whenever the PGA exceeds the threshold we have set (for example I had set 0.01 GB) we will receive an email.

This email will contain a message of the type:

Warning: Session's Program Page Area (PGA) Memory on DBNAME@HOSTNAME is too high!!!
     --->            0,09 GB

And the following information of the session consuming the most PGA memory at the moment:

• PGA
• Logon time
• SID
• Serial
• Process
• Username
• OS user
• Machine
• Program
• Module
• SQL text

Sources:

•  Program Global Area