Generate PGA Breakdown
Oracle Database allocates a program global area (PGA) for each server process. The PGA is used to process SQL statements and to hold logon and other session information. For the purposes of memory management, the collection of all PGAs is known as the instance PGA. Using an initialization parameter, you set the size of the instance PGA, and the database distributes memory to individual PGAs as needed.
The content of the PGA memory varies, depending on whether or not the instance is running the shared server option. Generally speaking, the PGA memory is divided into the following areas:
- Session Memory
- Private SQL Area
Session Memory
Session memory is the memory allocated to hold a session's variables (logon information) and other information related to the session. For a shared server, the session memory is shared and not private.
Private SQL Area
The private SQL area contains data such as bind variable values, query execution state information, and query execution work areas. Each session that issues a SQL statement has a private SQL area. Each user that submits the same SQL statement has his or her own private SQL area that uses a single shared SQL area. Thus, many private SQL areas can be associated with the same shared SQL area. Private SQL Area consist of three areas:
- Cursors and SQL Areas
- Private SQL Area Components
- SQL Work Areas
If session consumes a lot of memory it would be due to one of the Private SQL Area.
If your application code does a lot of sorting or open a lot of cursors then it would be an expected to consume a lot of memory
The script attached here can be used to generate the PGA breakdown for a particular session. The script was produced based on Oracle Doc Id: 822527.1
Deployment
Copy the script onto the target server and grant it execute permission using chmod a+x command.
Usage 1:
Log in as oracle and call the script as follows. By default the script sleeps for 180 seconds while waiting for the PGA dump to be generated.
$ ./oracle_pga_breakdown.sh
Usage 2:
Log in as oracle and call the script as follows. If no dump is produced, then consider increasing the sleep time to a larger value in seconds. e.g. 300 will case the script to wait for 5 minutes.
$ ./oracle_pga_breakdown.sh 300
Inputs
- When prompted "Enter value for username : ", Enter the username to obtain a list of open sessions for that particular user.
- When prompted "Enter value for SID : ", Enter the value listed under the SID column for the user whose PGA break down is being generated.
- When prompted "Enter value for PID : ", Enter the value listed under the PID column. Note: There will be multiple entries with the same PID. This is expected.
Note: If you have multiple user sessions, then repeat re-run the script to generate data for each listed user.
Upon completion the output file name is displayed. Download this and pass it on to the relevant development team for further analysis.
Sample Output
Enter value for username : XXXX
USERNAME SID SPID LOGON_TIM PROGRAM PID_REMOTE STATUS Used MB Allocated MB Freeable MB Max MB
-------- ----- ----- --------- ---------------- ----------- -------- -------- ------------ ----------- --------
XXXX 1032 25110 24-APR-14 JDBC Thin Client 1234 INACTIVE 2.3 2.8 .0 3.9
XXXX 1033 28240 22-APR-14 JDBC Thin Client 1234 INACTIVE 10.9 11.7 .1 15.6
Enter value for SID : 1033
----------------------------------------------
Generating PGA component breakdown for SID=1033
----------------------------------------------
PID Category Allocated MB Used MB Max allocated MB
---------- --------------- ------------ ---------- ----------------
51 SQL 8.44 0 12.18
51 PL/SQL .04 .02 .05
51 Freeable .06 0
51 Other 3.14 3.28
Enter value for PID : 51
---------------------------------------
Generating Memeory Details for PID=51
---------------------------------------
Clearing stale PGA_DETAIL
Session altered.
Generating PGA_DETAIL_GET dump
Session altered.
Sleeping for 180s
CATEGORY NAME HEAP_NAME BYTES ALLOCATION_COUNT HEAP_DESCRIPTOR PARENT_HEAP_DESC
--------------- -------------------------- --------------- ---------- ---------------- ---------------- ----------------
Other Fixed Uga pga heap 32664 1 0000000009F7FFE0 00
Other kfk_sbmtptr_a KFK_IO_SUBHEAP 32792 1 00002B57C0A51D20 0000000009F7FFE0
Other kfk_reapptr_a KFK_IO_SUBHEAP 32792 1 00002B57C0A51D20 0000000009F7FFE0
Other kfk_waitptr_a KFK_IO_SUBHEAP 32792 1 00002B57C0A51D20 0000000009F7FFE0
Other kxsFrame16kPage session heap 32880 2 00002B57C0B08090 0000000009F856E0
Other koh-kghu session heap session heap 35352 15 00002B57C0B08090 0000000009F856E0
Other KSXP osd connection handl pga heap 41352 3 0000000009F7FFE0 00
Other KTI persistent buffer callheap 43320 2 0000000009F84600 0000000009F854C0
Other kfkosd_p KFK_IO_SUBHEAP 69656 1 00002B57C0A51D20 0000000009F7FFE0
Other permanent memory session heap 79472 31 00002B57C0B08090 0000000009F856E0
Other kxsFrame4kPage session heap 103800 25 00002B57C0B08090 0000000009F856E0
Other kfioRqTracer pga heap 131104 1 0000000009F7FFE0 00
Other kgh stack pga heap 139768 8 0000000009F7FFE0 00
Other free memory top call heap 195368 5 0000000009F854C0 00
Other free memory pga heap 237592 12 0000000009F7FFE0 00
Other free memory session heap 275200 482 00002B57C0B08090 0000000009F856E0
Other kfk_kfkio_freeq KFK_IO_SUBHEAP 278552 1 00002B57C0A51D20 0000000009F7FFE0
Other miscellaneous 413400 882 00 00
Other permanent memory pga heap 604584 25 0000000009F7FFE0 00
PL/SQL miscellaneous 43400 30 00 00
SQL define var info kxs-heap-p 26664 22 00002B57C1AE30B8 00002B57C0B08090
SQL user uac kxs-heap-p 30904 297 00002B57C1AE30B8 00002B57C0B08090
SQL miscellaneous 118680 381 00 00
SQL permanent memory kxs-heap-i 8563952 8499 00002B57C0E93D70 00002B57C0B08090
24 rows selected.
Done.
----------------------------------------------------------------------
Send "pga_heap_dump_1033.out" file to development for analysis
----------------------------------------------------------------------
This comment has been removed by a blog administrator.
ReplyDelete