The article is all about locks and wait events and the Application Express application ahould help the user identify locks and the source of waits. Of course the oracle 10g dbconsole can do the same and better, but we believe it is alwasy very useful to understand which system tables can be queried and with what kind of queries are behind any diagram or chart.
|
A lock is artificially caused in the database by updating the same table from two different sessions without committing. In the first page of our application express application the v$session view is queried with the condition WHERE BLOCKING_SESSION IS NOT NULL. The field WAIT_CLASS will tell us that the session is waiting for reasons related to how the application is performing (which is what we should expect in this case). |
Clicking on session 1080 one is led to a graph showing the type of waits this particular session has been experiencing.
We see that the class application is responsible for a large part of the waits, as expected!; we therefore click on its bar in order to see whether this wait class is widespread in the system
We can be interested in drilling down into the class showing the most wait events, in this example application related.
The question is now about the distribution of this particular wait event according to buckets; a high frequency in the longer buckets may signify that thsi wai event is relevant system wide.
What is the system waiting on?
Some queries can be used to tracking system events or wait classes that you suspect are important for your systems
Oracle 10g also offers the view v$ossstat, which contains a few statistics for the operating system of the server where the Oracle instance is running.
SQL> descr v$osstat Name Null? Type ----------------------------------------- -------- ---------------- STAT_NAME VARCHAR2(64) VALUE NUMBER OSSTAT_ID NUMBERAn eye should be kept, among other parameters, on the ratio between system and user CPU usage and the idle time.
 |
 |
SELECT distinct enm.name, ash.WAIT_CLASS FROM v$active_session_history ash, v$event_name enm WHERE ash.event#=enm.event# ORDER BY ash.WAIT_CLASS, enm.name