Performance Tuning (Wait Event & Statistic & Classes)

Wait Event


Acollection of wait events provides information about the session that had to wait or must wait for different reasons.

It's incremented by a server process or thread to indicate that it had to wait for an event to complete before being to able to continue processing.Wait events data reveals som problem that might be affecting performanc like (latch contention,buffer contention, and I/O contention).this vies V$EVENT_VIEW to found the full list of events.

TIME_STATISTIC parameter is set TRUE.

MICRO SECOND TIMINGS

* MICRO_SECOND TO SECOND convert  (4818649763 / 10 raise to power -6)
   = 4818649763 / 1000000



*MICRO_SECOND TO CENTI-SEC (4818649763 / 10 raise to power -4)
= 4818649763 / 10000




*MICRO_SECOND TO MILLI-SEC (4818649763 / 10 raise to power -3)
= 4818649763 / 1000



* MICRO_SECOND TO MINT convert  (4818649763 / 10 raise to power -6 / 60)
   = 4818649763 / 1000000 / 60





---Column (TIME_WAITED_MICRO)                 (CPU_TIME,ELAPSED_TIME)
v$session_wait                                                                    v$SQL
v$system_event                                                                   v$SQLAREA
v$session_event


---Column (WAIT_TIME)                                         (ACITVE_TIME)
v$LATCH                                                                      v$SQL_WORKAREA
v$LATCH_PARENT                                                     v$SQL_WORKAREA_ACTIVE
v$LATCH_CHILDREN

V$SESSION: lists session information for each current session. It lists either the event currently being waited for, or the event last waited for on each session. This view also contains information about blocking sessions, the wait state, and the wait time.




V$SESSION_WAIT: displays the events for which sessions have just completed waiting or are currently waiting.
--Column
* WAIT_TIME
                >0  The session's last wait time .i.e( how much last time  for the session)
                =0 It means session currently waiting.
               =-1 The vaule is less than 1/100 of second.
               =-2 The system cannot provide timing information.

* Second_In_wait: Number of seconds the eent wait
* State: Waiting, waiting unknown time,waited short time ( < 0.01 Second) or Waited Known time.


V$SESSION_WAIT_HISTORY: lists the last 10 wait events for each current session and the associated wait time.



Wait Class


V$SESSION_WAIT_CLASS: displays the time spent in various wait event operations on a per-session basis.

V$SYSTEM_WAIT_CLASS: displays the instance-wide time totals for each registered wait class.

V$SERVICE_WAIT_CLASS:displays aggregated wait counts and wait times for each wait statistic. An aggregation of these wait classes is used when thresholds are imported.


V$EVENT_NAME:




Wait Event Statistic


V$SYSTEM_EVENT: displays the total number of times all the sessions have waited for the events in that view.

V$SESSION_EVENT: is similar to V$SYSTEM_EVENT, but displays all waits for each session.


Select sid,event 
     from v$session_wait 
           where wait_time=0 and wait_class#=6 and event!='SQL*Net message from client';


Note:- Ignore event "SQL*Net message from client" .The wait event shown above are idel wait class events that always appear. They don't indicate a problem.There are more than 60 such idle events and belog to the "Idel" wait class (wait class number 6);




V$SERVICE_EVENT:displays the services in the database.



select service_name,event,average_wait,time_waited
                from v$service_event 
                      where time_waited > 0;

• Wait Event Class:-
  
  
  select distinct wait_class#,wait_class from v$event_name order by 1
  select distinct name, wait_class#,wait_class from v$event_name order by 1

SOME WAIT EVENT V$EVENT_NAME	WAIT EVENT	AREA
	Buffer Busy & Waits	Buffer cache,DBWR
	Free Buffer Waits	Buffer cache,DBWR,I/O
	DB File Scattered read	I/O ,SQL Tuning
	DB File Sequential read	I/O ,SQL Tuning
	Enqueue Waits (enq:)	LOCKS
	Library cache waits	Latches
	Log buffer space	Log buffer I/O
	Log file sync	Over Commit + rollback ,I/O

V$WAITSTAT 

The V$WAITSTAT view lists details about the block contention. This view updates the wait statistics from the buffer cache when timed statistics are enabled. This statistic can be used along with the 'buffer busy waits' wait information. To get finer details on the waits per file basis, the following query can be used.

To display buffer waits per file (run as SYS)

SELECT count, time, name

FROM v$datafile a, X$KCBFWAIT b

WHERE b.indx+1 = a.file#;

select class,count,time from v$waitstat;

Data Blocks - Usually occurs when there are too many modified blocks in the buffer cache; reduce contention by adding DBWR processes.

Free List - May occur if multiple data loading programs run simultaneously.

Segment Header - May occur when may full table scans execute simultaneously with data loading processes; aggravated by the parallel options. Reschedule data loading jobs to reduce contention;

Sort Block - Rarely seen except when the Parallel Query option is used; reduce contention by reducing the degree of parallelism or decreasing the SORT_AREA_SIZE init.ora parameter setting.

Undo Block - Very rarely occurs; may be caused by multiple users updating records in the same data block at a very fast rate; contention can usually be resolved by increasing the PCTFREE of the tables being modified.

Undo Header - May occur if there are not enough rollback segments to support the number of concurrent transactions.

System Statistic Classes

STATISTIC		ID	Class Name
V$SYSSTAT		1	User
V$SESSTAT	All Session	2	Redo
V$STATNAME		4	Enqueue
V$MYSTAT	Current Session	8	Chache
V$SERVICE_NAME	Instance Startup	16	OS
		32	RAC
		64	SQL
		128	Debug

Eq:-Consume 30,000 bytes of PGA


select username,name,round(value)/1024||' KB' ,class
from v$statname n,v$session s,v$sesstat t
where s.sid=t.sid
and n.statistic#=t.statistic#
and s.type='USER'
and s.username is not null
and n.name='session pga memory'
and t.value > 30000
/