My Oracle Support Banner

How To Find Where The Memory Is Growing For A Process (Doc ID 822527.1)

Last updated on APRIL 06, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 to 12.1.0.2 [Release 10.2 to 12.1]
Oracle Database - Standard Edition - Version 12.1.0.1 to 12.1.0.1 [Release 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Information in this document applies to any platform.

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

Goal

How to use V$PROCESS_MEMORY and V$PROCESS_MEMORY_DETAIL to identify where the memory is growing.

In Oracle 10.2 and higher exist 2 new views that can be used to find where the memory continue to grow. This views can be used instead of heap dump to find where the memory is growing:

 - V$PROCESS_MEMORY:

     This view displays dynamic PGA memory usage by named component categories for each Oracle process. This view will contain up to six rows for each Oracle process, one row for:
      - Java
      - PL/SQL
      - OLAP
      - SQL
      - Freeable
      - Other

 - V$PROCESS_MEMORY_DETAIL
    Contain break down of memory allocation for each component.
    - To activate this view can one of following commands executed:
       SQL> alter session set events'immediate trace name PGA_DETAIL_GET level <PID>';
       From ORADEBUG:
       SQL> ORADEBUG SETMYPID;
       SQL> ORADEBUG DUMP PGA_DETAIL_GET <PID>;
    - To remove all rows in the view run following command:
       SQL> alter session set events'immediate trace name PGA_DETAIL_CANCEL level <PID>';
       From ORADEBUG:
       SQL> ORADEBUG DUMP PGA_DETAIL_CANCEL <PID>;

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
 Recordings
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.