Real-Time SGA component monitoring

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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.