Viewing Instance Activities and Wait Events

 Practice Target

 In this practice, you will perform the following: • View system and session instance activity statistics • View system and session wait event statistics • Use session waiting events to investigate into hung or very slow sessions 

Note: This practice is not to learn about specific activity statistics or wait events. This practice is about retrieving them in various ways. Learning about handling wait events is covered in future lectures.

Preparing for the Practice

 In this section of the practice, you will prepare your environment for this practice. 

 1. Make sure the virtual appliance srv1 is up and running. 

 2. Start Putty, login to srv1 as oracle

 3. In the hosting PC, open a command prompt window and start Swingbench. 

 D: 

 cd swingbench\winbin 

 set PATH=D:\oracle\product\12.1.0\client_1\jdk\jre\bin;%PATH% 
 swingbench.bat

 4. In Swingbench, set the Number of Users field to 10 then click on the Start Benchmark Run button.

Viewing Instance Activity Statistics

In this section of the practice, you will run queries to display the instance statistics at the system level and at the session level. 

5. Invoke SQL*Plus and login to the database as sysdba 

 sqlplus / as sysdba  

6. Run the following query to display the instance activity statistics at the instance level. In most cases, displaying all the time-based instance activity statistics at the system level is meaningless. Those statistics provide more useful indications when they are taken in two different known times and compared to each other. As you will learn later in the course, this approach is best done using AWR and Statspack. 

 There are non-time-based instance activity statistics. We refer to those statistics based on our direction of investigation.

col NAME format A50 
col CLASS format A10 
SELECT NAME,  
DECODE(TO_CHAR(CLASS), 
 '1','User', 
 '2','Redo', 
 '4','Enqueue', 
 '8','Cache', 
 '16','OS', 
 '32','RAC', 
 '33','RAC-User', 
 '40','RAC-Cache', 
 '64','SQL', 
 '72','SQL-Cache', 
 '128','Debug', 
 '192','Debug-SQL', 
 TO_CHAR(CLASS) 
) CLASS, VALUE 
FROM V$SYSSTAT 
ORDER BY CLASS,NAME; 

7. Run the following query to retrieve the statistics of full table scans and index scans in the 

database. 

This is just an example how to retrieve statistics of specific interests.

 

SELECT NAME, VALUE 

FROM V$SYSSTAT 

WHERE (NAME LIKE 'table%' or NAME LIKE 'index%') AND VALUE<>0 

ORDER BY NAME;

Run the following queries to display the sessions with the top parse CPU time. Run the same 

query multiple times and observe whether the statistics get incremented. 

The same queries can be used to retrieve top sessions based on any specific statistic. As you go 

on with the course, you will learn about most common statistics to look at in your investigation. 

Note: observe that you need to link the V$SESSTAT with the V$STATNAME to obtain the statistic 

names. 

/* to list the top sessions of specific statistics: */ 

col USERNAME format a5 

col NAME format a20 

SELECT S.SID, H.USERNAME, T.NAME, S.VALUE 

 FROM V$SESSTAT S, V$STATNAME T, V$SESSION H 

WHERE S.STATISTIC# = T.STATISTIC# AND S.SID = H.SID 

  AND T.NAME = 'parse time cpu' 

  AND H.USERNAME IS NOT NULL 

ORDER BY S.VALUE DESC; 

 

/* to include the statement text in the output: */ 

col SQL_TEXT format a25 

SELECT S.SID, H.USERNAME, T.NAME, S.VALUE, SUBSTR(Q.SQL_TEXT,1,25) SQL_TEXT 

 FROM V$SESSTAT S, V$STATNAME T, V$SESSION H, V$SQL Q 

WHERE S.STATISTIC# = T.STATISTIC# AND S.SID = H.SID AND H.SQL_ID=Q.SQL_ID(+) 

  AND T.NAME = 'parse time cpu' 

  AND H.USERNAME IS NOT NULL 

ORDER BY S.VALUE DESC; 

9. Run the following query to display the same activity statistics of the current session. 

The query code is the same as the query code of the preceding example, except the V$SESSTAT is replaced with V$MYSTAT. 

V$MYSTAT is used when troubleshooting a current client session that we have control on it.

 

SELECT S.SID, H.USERNAME, T.NAME, S.VALUE 

 FROM V$MYSTAT S, V$STATNAME T, V$SESSION H 

WHERE S.STATISTIC# = T.STATISTIC# AND S.SID = H.SID 

  AND T.NAME = 'parse time cpu' 

  AND H.USERNAME IS NOT NULL 

ORDER BY S.VALUE DESC;

Viewing Wait Events 

In this section of the practice, you will run queries to display the wait events at various levels. 

10. Display all the non-idle wait event statistics at the instance level.

Usually wait events at the instance level are useful when comparing them to previously taken baseline statistics. However, some specific wait events should not take large percentage of the total wait time in all cases. You will learn about those events as you progress with the course. 

col EVENT format a40 

col WAIT_CLASS format a11 

SELECT EVENT, AVERAGE_WAIT,  

TO_CHAR(ROUND(TIME_WAITED/100),'999,999,999') TIME_SECONDS, WAIT_CLASS 

FROM V$SYSTEM_EVENT 

WHERE TIME_WAITED>0 AND WAIT_CLASS<>'Idle' 

ORDER BY TIME_WAITED;

11. Display total wait time by wait event class.

col WAIT_CLASS format a25 

col TIME_SECONDS format a25 

SELECT WAIT_CLASS, TO_CHAR(ROUND(TIME_WAITED/100),'999,999,999') TIME_SECONDS FROM V$SYSTEM_WAIT_CLASS WHERE TIME_WAITED>0 AND WAIT_CLASS<>'Idle' ORDER BY TIME_WAITED; 

/* to display the percentage for each wait class */ 

col PCT format a5

SELECT WAIT_CLASS, TO_CHAR(ROUND(TIME_WAITED/100),'999,999,999') TIME_SECONDS, '%' || ROUND(RATIO_TO_REPORT(TIME_WAITED) over ()*100) PCT FROM V$SYSTEM_WAIT_CLASS WHERE TIME_WAITED>0 AND WAIT_CLASS<>'Idle' ORDER BY TIME_SECONDS; 

Display the current sessions with the total wait time of the wait event 'log file sync' in each session. Repeat running the same query for a few times and notice the wait time is incremented. 

 V$SESSION_EVENT is used to retrieve the accumulated wait time for each session since the session logon time. The query below is a model example that can be tailored as per the investigation direction. 

set linesize 180
col EVENT format a25 
 TO_CHAR(ROUND(E.TIME_WAITED/100),'999,999,999') TIME_SECONDS,  
 E.WAIT_CLASS 
FROM V$SESSION_EVENT E, V$SESSION S 
WHERE E.SID=S.SID AND ROUND(E.TIME_WAITED/100)>0 
  AND S.USERNAME='SOE' AND E.EVENT='log file sync' 
ORDER BY TIME_WAITED;

SELECT E.SID, S.USERNAME,  E.EVENT,  

13. Display the current sessions which are currently waiting for the event 'log file sync'. 

Current wait events can be retrieved from V$SESSION. Observe that the query below sometimes 

retrieves some rows and sometimes it retrieves no row. It depends on the status of the sessions 

at the time of running the query. 

Note: You can query the view V$SESSION_WAIT for the same purpose.

col USERNAME format a4

col WAIT_CLASS format a10 

SELECT SID, USERNAME, EVENT, WAIT_TIME, WAIT_CLASS FROM V$SESSION WHERE USERNAME='SOE' AND EVENT='log file sync' ORDER BY WAIT_TIME; 14. Stop the run in Swingbench and exit from it.

Use Case: Using Session Waiting Events with Hung or Very Slow Sessions 

In this section of the practice, you will use the session wait event views to investigate into hung or 

very slowly sessions. The target is to have further understanding on using the wait events in 

performance troubleshooting. You will also learn how to use Oracle documentation to look up the 

meaning of a wait event. 

15. Open two new Putty sessions, and in each of them, invoke SQL*Plus and login as metals 

sqlplus metals/metalsdb

16. Run the following query in each session. The second run should hang. 

UPDATE EMP SET SALARY=SALARY WHERE EMP_NO=104; 

17. In the sysdba session, display the SID and the current event of the hung session. 

In our scenario, we expect the hung session to be in WAITING state. If it is not, find another way to identify the session. 

Note: this section is not about studying the event 'enq: TX - row lock contention'. This event will be covered in more details later in the course. 

SELECT SID, EVENT 

FROM V$SESSION 

WHERE STATE='WAITING' AND USERNAME ='METALS' AND WAIT_CLASS<>'Idle'; 

18. Display the wait event information of the hung session from the V$SESSION and from the 

V$SESSION_EVENT. Run the queries multiple times and observe the increments in waiting time. 

Observe that when the status is WAITING, the WAIT_TIME is zero and the SECONDS_IN_WAIT displays the waiting time. 

col SESSION_WAITS format a100 

SELECT 'SID: '|| SID||  

 CHR(10)||'USERNAME: '|| USERNAME|| 

 CHR(10)||'STATE: '|| STATE|| 

 CHR(10)||'EVENT: '|| EVENT||  

 CHR(10)||'WAIT_TIME: '|| WAIT_TIME||  

 CHR(10)||'SECONDS_IN_WAIT: '|| SECONDS_IN_WAIT|| 

 CHR(10)||'WAIT_CLASS: '|| WAIT_CLASS||  

 CHR(10)||'P1TEXT: '|| P1TEXT|| 

 CHR(10)||'P1: '|| P1|| 

 CHR(10)||'P2TEXT: '|| P2TEXT|| 

 CHR(10)||'P2: '|| P2 || 

 CHR(10)||'P3TEXT: '|| P3TEXT|| 

 CHR(10)||'P3: ' || P3 AS SESSION_WAITS 

FROM V$SESSION 

WHERE USERNAME='METALS' AND EVENT LIKE 'enq: TX%' 

ORDER BY WAIT_TIME;

 

Practice 3 - Viewing Instance Activities and Wait Events Page | 8 

 

Oracle Database Performance Tuning, a course by Ahmed Baraka 

 

col EVENT for a30 

SELECT E.EVENT,  

 TO_CHAR(ROUND(E.TIME_WAITED/100),'999,999,999') TIME_SECONDS 

FROM V$SESSION_EVENT E, V$SESSION S 

WHERE E.SID=S.SID AND S.USERNAME='metals' AND E.EVENT LIKE 'enq: TX%' 

ORDER BY TIME_WAITED; 

19. Retrieve information about the wait event from the V$SESSION_WAIT_HISTORY. 

V$SESSION_WAIT_HISTORY view retrieves into the last 10 wait events in the current sessions. 

Observe that the wait event is not registered in this view. As you will see soon, wait events are 

registered in this view only after they expire.

 

SELECT COUNT(*) FROM V$SESSION_WAIT_HISTORY  

WHERE EVENT LIKE 'enq: TX%'; 

The query from V$SESSION returns the wait event parameter values but their meanings may not be 

clear to us. Let’s look up the wait event in the documentation. 

20. In Oracle reference documentation, go to appendix C and look for what phrase “enq: TX - row 

lock contention”. 

If you do not have a copy of Oracle 12c R2 reference documentation You will see a 

description of the wait event parameters. 

Note: not all wait events are documented.

 

21. From the blocking session, rollback the transaction. 

rollback; 

22. Display the wait events from the V$SESSION for all the metals sessions. 

Observe that the enqueue wait event does not appear any more in the V$SESSION view because the session is not currently waiting for the event. 

col EVENT format a40 

col WAIT_CLASS format a10 

SELECT SID, EVENT, WAIT_CLASS  

FROM V$SESSION  

WHERE USERNAME='METALS'; 

Practice 3 - Viewing Instance Activities and Wait Events

 

Oracle Database Performance Tuning, a course by Ahmed Baraka 

 

23. Display the wait events from the V$SESSION_EVENT for all the metals sessions. 

Observe that the view reports the wait event that has been waited by the session. 

SELECT E.EVENT,  

 TO_CHAR(ROUND(E.TIME_WAITED/100),'999,999,999') TIME_SECONDS 

FROM V$SESSION_EVENT E, V$SESSION S 

WHERE E.SID=S.SID AND S.USERNAME='METALS' AND E.EVENT LIKE 'enq: TX%' ORDER BY TIME_WAITED; 

24. Retrieve information about the wait event that has just expired from the 

V$SESSION_WAIT_HISTORY. 

After the wait is expired, it is reported by the view. 

col SESSION_WAITS format a100 

SELECT 'SID: '|| SID||  

 CHR(10)||'EVENT: '|| EVENT||  

 CHR(10)||'WAIT_TIME: '|| WAIT_TIME||  

 CHR(10)||'P1TEXT: '|| P1TEXT|| 

 CHR(10)||'P1: '|| P1|| 

 CHR(10)||'P2TEXT: '|| P2TEXT|| 

 CHR(10)||'P2: '|| P2 || 

 CHR(10)||'P3TEXT: '|| P3TEXT|| 

 CHR(10)||'P3: ' || P3 AS SESSION_WAITS 

FROM V$SESSION_WAIT_HISTORY 

WHERE EVENT LIKE 'enq: TX%' 

ORDER BY WAIT_TIME; 

25. Exit from the metals sessions 

26. Verify that the wait events cannot be reported from V$SESSION_WAIT_HISTORY after the sessions 

are disconnected. 

Of course, they will not be seen in V$SESSION and V$SESSION_EVENT as well. 

SELECT COUNT(*) FROM V$SESSION_WAIT_HISTORY WHERE EVENT LIKE 'enq: TX%'; 

27. To cleanup, exit from all the opened Putty sessions. 

Practice 3 - Viewing Instance Activities and Wait Events 

Summary 

• V$SYSSTAT and V$SESSTAT views are used to retrieve instance activity statistics at the instance 

level and at the session level respectively. 

• V$MYSTAT is used to retrieve instance activity statistics on the current session. 

• V$SYSTEM_EVENT, V$SESSION_EVENT views are used to retrieve wait event statistics at the 

instance level (since instance last startup) and at the session level (for current sessions). 

• The wait event statistics are gone from the V$SESSION_EVENT when the sessions are 

disconnected.