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 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:


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:

./ help

Usage: ./ [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 : ./ 
Example : ./ pool='%shared%'
Example : ./ name='%free%'
Example : ./ 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.