Real-Time segments statistics

bdt's blog

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

./ 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 слова