Extended Oracle agent - Total PGA Used Memory monitoring

Answer

Customized SQL feature of Extended Oracle agent can be used to monitor Total PGA Memory Used metrics as described in the discussion below.

Following SQL returns 1) total PGA Memory Used by processes in MB, and 2) systimestamp, which is the time when the SQL was run by the agent.

select systimestamp,sum(round(pga_used_mem/1024/1024,2)) pga_used_mem from v$process

To monitor the SQL results, define it as customized SQL as follows:

1) Add the SQL to agent configuration file, krzcussql.properties :

cd /opt/IBM/ITM/config

Edit krzcussql.properties. Insert following lines at the end of the file:

[SELECTSQL]
SQL_ID=totalpgausedMB
SQL_TEXT=select systimestamp,sum(round(pga_used_mem/1024/1024,2)) total_pga_used_mem from v$process 

Save changes.

There is no need to restart the agent, the krzcussql.properties file is read dynamically.

2) View SQL Results as follows:

2.1 Access Tivoli Enterprise Portal (TEP) workspace "Customized SQLs".

2.2 Right click the query with SQL ID totalpgausedMB, select "SQL Result" link.

SQL Result workspace displays the result set of the SQL:

3) Creating situation for the customized SQL:

Create situation based on KRZ RDB CUSTOMIZED SQL DETAIL attribute group, include SQL ID attribute in the situation.

Following example shows steps to create a situation which will alert when the result returned by totalpgausedMB customized SQL is > 10 MB.

Note: Use appropriate threshold and SQL for your environment after discussing with the DBA.

For further discussion on Customized SQL feature, refer to Extended Oracle Agent User Guide Chapter 2. Agent installation and configuration, section "Defining and running customized SQL statements".