Real-Time segments statistics

The v$segment_statistics and v$segstat views are a goldmine to extract statistics that are associated with the Oracle segments.

You can see how useful it could be in those posts :

Kevin Closson’s post

Jonathan Lewis’s post or this one

Arup Nanda’s post

But those views are cumulatives, so not so helpful to report real-time information on the segments (Imagine your database is generating a lot of I/O right now and you would like to know wich segments are generating those I/O).

To report real-time statistics on the segments I wrote the segments stats.pl script (click on the link and then on the view source button to copy/paste the source code) that basically takes a snapshot based on the v$segstat view each second (default interval) and computes the differences with the previous snapshot.

Let’s see an example:

./segments_stats.pl

Connecting to the Instance...

07:10:45   INST_NAME	OWNER	OBJECT_NAME	STAT_NAME                VALUE     
07:10:45   BDT1		BDT	BDTTAB          physical read requests   6         
07:10:45   BDT1		BDT	BDTTAB          segment scans            6         
07:10:45   BDT1         BDT     BDTTAB          logical reads            48        
07:10:45   BDT1         BDT     BDTTAB          physical reads           85        
07:10:45   BDT1         BDT     BDTTAB          physical reads direct    85   
--------------------------------------> NEW
07:10:46   INST_NAME	OWNER	OBJECT_NAME	STAT_NAME                VALUE     
07:10:46   BDT1		BDT	BDTTAB          segment scans            19
07:10:46   BDT1		BDT	BDTTAB          physical read requests   28         
07:10:46   BDT1         BDT     BDTTAB          logical reads            48        
07:10:46   BDT1         BDT     BDTTAB          physical reads           285        
07:10:46   BDT1         BDT     BDTTAB          physical reads direct    285

So, as you can see the BDTTAB generated 285 physical reads during the last second.

Let’s see the help:

./segments_stats.pl help

Usage: ./segments_stats.pl [Interval [Count]] [inst] [top=] [owner=] [statname=] [segment=] [includesys=]

        Default Interval : 1 second.
        Default Count    : Unlimited

  Parameter		Comment						     Default    
  ---------		-------					             -------    
  INST=          	ALL - Show all Instance                              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                            10         
  OWNER=		ALL - Show all OWNER                                 ALL        
  STATNAME=		ALL - Show all Stats                                 ALL        
  SEGMENT=		ALL - Show all SEGMENTS                              ALL        
  INCLUDESYS=           Show SYS OBJECTS                                     N          

Example : ./segments_stats.pl segment='AQ%' statname='physical%'
Example : ./segments_stats.pl segment='AQ%' statname='physical writes direct'

As usual:

  • You can choose the number of snapshots to display and the time to wait between snapshots.
  • You can choose to filter on statname, segment and owner (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.

Remark for Exadata: 

As you can filter on the statname, you could choose to filter on the particular ‘optimized physical reads‘ statistic that way:

./segments_stats.pl statname='%optimized%'
About these ads
This entry was posted in Exadata, Perl Scripts, ToolKit and tagged , , . Bookmark the permalink.

One Response to Real-Time segments statistics

  1. Vijay says:

    Bertrand, Thanks for sharing nice scripts , Could you please update with Partition/SubPartition stats .

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s