From the V$SGA_RESIZE_OPS view, you observed that your database is doing frequent resize.
You want to know what’s going on and you decided to query the v$sgastat view at regular interval to understand which component of the sga is growing.
The sgastat.pl script (click on the link and then on the view source button to copy/paste the source code) do it for you : It takes a snapshot based on the gv$sgastat view each second (default interval) and computes the differences with the previous snapshot.
Let’s see an example:
./sgastat.pl
16:48:54 INST_NAME POOL NAME BYTES
16:48:54 BDT_1 shared pool QSMQUTL summar 32
16:48:54 BDT_1 shared pool kzull 512
16:48:54 BDT_1 shared pool kksss 1744
16:48:54 BDT_1 shared pool kksss-heap 4280
16:48:54 BDT_1 shared pool parameter handle 7192
16:48:54 BDT_1 shared pool library cache 10936
16:48:54 BDT_1 shared pool PCursor 12544
16:48:54 BDT_1 shared pool parameter table block 51896
16:48:54 BDT_1 shared pool CCursor 449688
16:48:54 BDT_1 shared pool sql area 6715208
--------------------------------------> NEW
16:48:55 INST_NAME POOL NAME BYTES
16:48:55 BDT_1 shared pool kzull 128
16:48:55 BDT_1 shared pool KTCCC OBJECT 144
16:48:55 BDT_1 shared pool kksss 416
16:48:55 BDT_1 shared pool kksss-heap 1040
16:48:55 BDT_1 shared pool parameter handle 1776
16:48:55 BDT_1 shared pool parameter table block 12920
16:48:55 BDT_1 shared pool library cache 35904
16:48:55 BDT_1 shared pool PCursor 67856
16:48:55 BDT_1 shared pool CCursor 942688
16:48:55 BDT_1 shared pool sql area 8876448
So as you can see, the sql area component grow by about 8MB during the last second.
Let’s see the help:
./sgastat.pl help
Usage: ./sgastat.pl [Interval [Count]] [inst=] [top=] [pool=] [name=]
Default Interval : 1 second.
Default Count : Unlimited
Parameter Comment Default
--------- ------- -------
INST= ALL - Show all Instance(s) ALL
CURRENT - Show Current Instance
INSTANCE_NAME,... - choose Instance(s) to display
<< Instances are only displayed in a RAC DB >>
TOP= Number of rows to display ALL
POOL= ALL - Show all POOL (wilcard allowed) ALL
NAME= ALL - Show all NAME (wilcard allowed) ALL
Example : ./sgastat.pl
Example : ./sgastat.pl pool='%shared%'
Example : ./sgastat.pl name='%free%'
Example : ./sgastat.pl pool='%shared%' name='%sql%'
As usual:
- You can choose the number of snapshots to display and the time to wait between snapshots.
- You can choose to filter on pool and name (by default no filter is applied).
- This script is oracle RAC aware : you can work on all the instances, a subset or the local one.
- You have to set oraenv on one instance of the database you want to diagnose first.
- The script has been tested on Linux, Unix and Windows.
You can found a very good study about shared pool management in Coskan’s post.