JL Computer Consultancy

Some rarely used features of V$ (Oracle

February 1999

Oracle is a very large, complex, product and one of the problems facing any person or organisation that tries to be a centre of excellence is the difficulty of keeping up with the changes.

This note highlights a few of the more recent changes in some of the v$ monitoring views. One of the features of such small changes is that they don't get into exciting press releases or product launches, so they frequently take months, or even years, to appear in the commercially available monitoring tools.

In fact, one of the reasons why I wander the world with my own little stack of scripts rather than investing in a pretty little GUI tool is that the tools never seem to keep up with what's possible - back when Oracle 8.0 had been in Beta release for just a few months I remember the representative from one company proudly telling me that they had been in the early release program for several months and had proved that their product still worked with 8.0 ... they hadn't actually enhanced their product to handle anything new in 8.0, they had merely satisfied themselves that the bits that used to work still did.

I have to say I don't really find it necessary to monitor every little aspect of Oracle when trying to keep a database healthy (as you may have guessed from my 'three views for everything' note) but it is nice to keep an eye on what goes on, and learning about the v$ performance monitoring views can give you an interesting insight into how Oracle really works.

Back to Main Index of Topics

Table - Column






The number second that this lock has been in this lmode. This figure is updated every 3 seconds, and can be a good indicator of a stuck process








If you have set an optimal size for the rollback segment, this column tells you how many times the rollback segment has shrunk (not necessarily all the way back to the optimal). Combined with aveshrink, extends, and wraps this can give you an indication of how suitable your rollback optimal size is.



If you have set an optimal size of the rollback segment, this column tells you the average space freed when the rollback segment has had a chance to shrink. If the number of shrinks is small relative to wraps your choice of optimal is probably fairly good. If shrinks and extends are nearly the same size then your optimal could be just a bit larger. If shrinks is significantly smaller than extends (and aveshrink will be much larger than a single extent) then you probably have a few very expensive transactions that might benefit from having their own special size rollback segment.



I have often found that setting optimal to around twice aveactive with about 10 extents is a good starting point. The aveactive is the average amount of rollback segment in use when there are any active transactions on that rollback segment.



If you want to watch how rapidly a rollback segment is being used watch the current extent changing.



And if you want to watch very closely how rapidly a rollback segment is being used, watch the current block changing.








This is a real implementation oddity. Every time it is accessed it will have increased by 1 if the session has executed a database call since the last time anyone looked at it. So if you have a monitoring routine that keeps track of this column value then you only need to worry about chasing down sessions where the number has changed since the last pass.



The Oracle manual may not agree with this, but I believe this is the amount of elapsed time since the last call was made to the database kernel. The figure is updated every 3 seconds. For the background processes (such as DBWR) this is the time (to the nearest 3 seconds) that the database has been up. (Thanks to Karl Craven for correcting me on this point)..



If this is non-zero, then the session is waiting for a row lock to be released. The number is the obj# in sys.obj$.



These three columns allow you to work out the rowid of the object for which the session is waiting. You may have to fiddle around a bit to translate the file# into a value that you can use in the rowid.



Well written applications can call the dbms_application_info package to write location tracking information into these 5 columns. This will eventually be the topic of a further page.








People often seem to ask how they can work out how much rollback space a transaction will need. The simplest answer is to try it and see. used_ublk is the number of rollback (undo) blocks used by the transactions. An interesting insight into the way Oracle works is to watch this change as you insert, then update, then delete 100 rows.



This is the number of rollback (undo) records written by the transaction. It is not 100% accurate since some 'recursive' undo records seem to be missed (perhaps because they are recorded against a separate recursive transaction), but is very close.



Matches the block_gets of v$sess_io, but for one transaction.



Matches physical_reads of v$sess_io, but for one transaction.



Matches consistent_gets of v$sess_io, but for one transaction.



Matches consistent_changes of v$sess_io, but for one transaction.





Users of Oracle parallel server will now find columns in these views which tell them how badly they are suffering from non-PCM lock-conflict problems


Every user should be allowed to see this. It is a 'local' image of v$sessstat for their session. Strangely even AUTOTRACE does not use it, instead using v$sesstat..


Oracle 7.3 allows for 'proper' temporary segments. This views holds one row per temporary tablespace per instance recording the state and cumulative statistics of the single segment from that tablespace that is allocated to that instance until the instance is shut down.


v$latch can now be broken down into parent latches and child latches. About 1 latch in 5 has children. Some child latches can only be acquired whilst holding a parent, some are independent. If you want to worry about some really exotic tuning this is one place to look.

Back to Main Index of Topics