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