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 :
Jonathan Lewis’s post or this one
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…
View original post ещё 304 слова
