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%'