--buffer busy waitsselect sw.P1 "FILD ID",sw.P2 "Block ID",sw.P3 "Class ID" from V$session_Wait sw where sw.EVENT='buffer busy waits'SELECT s.ROW_WAIT_OBJ# FROM V$SESSION s where event = 'buffer busy waits'select do.OWNER,do.OBJECT_NAME,do.SUBOBJECT_NAME,do.OBJECT_TYPE from dba_objects dowhere do.DATA_OBJECT_ID = &ROW_WAIT_OBJ#/*db file scattered read multiblock read => a fast full scan(of an index)a full table scan*/select sw.P1 "obsolute file number",sw.P2 "block begin read",sw.P3 "the number of blocks" from V$session_Wait swwhere sw.EVENT = 'db file scattered read'-- select s.sql_ADDRESS,s.sql_HASH_VALUE from v$session s where s.EVENT = 'db file%read'SELECT s.ROW_WAIT_OBJ# FROM V$SESSION swhere event = 'db file scattered read'select do.OWNER,do.OBJECT_TYPE from dba_objects dowhere do.DATA_OBJECT_ID = &ROW_WAIT_OBJ#--db file sequential read a single-blockselect sw.P1 "obsolute file number",sw.P3 "the number of blocks" from V$session_Wait swwhere sw.EVENT = 'db file scattered read'/* db file sequential read (single block read into one SGA buffer)* db file scattered read (multiblock read into many discontinuous SGA buffers)* db direct read (single or multiblock read into the PGA,bypassing the SGA)*/-- db file direct path and direct read tempselect sw.P1 "file_id",sw.P2 "start_block_id",sw.P3 "the number of blocks" from V$session_Wait swwhere sw.EVENT = 'db file direct path write'--causees/* The sorts are too large* parallel slaves are used for scanning data */--db file direct write select sw.P1 "file_id",sw.P3 "the number of blocks" from V$session_Wait swwhere sw.EVENT = 'db file direct path write'/* sorts are too large to fit in memory and are written to disk * parallel DML are issued to create/populate objects * direct path loads*/ /*enqueue(enq):waits This event iddiates that the session is waiting for a lock * that is held by another session,such as following related TX types: * enq: TX-allocate ITL entry * enq: TX-contention * enq: TX-index contention * enq: TX-row lock contention*/ select sw.P1 "LOCK TYPE",P2 "Resource identifier ID1",p3 "Resource identifier ID3" from v$session_wait sw where sw.EVENT like 'enq: TX - row lock contention' select * from v$lock l where l.REQUEST>0 SELECT DECODE(REQUEST,'Holder: ','Waiter:') || sid sess,id1,id2,lmode,request,type from v$lockwhere (id1,type) in (select id1,type from v$lock where request > 0)order by id1,request--blocking sessionselect s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from v$lock l1,v$session s1,v$lock l2,v$session s2where s1.sid = l1.sid and s2.sid = l2.sid and l1.BLOCK = 1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2select a.SERIAL# from v$session a where a.SID=211alter system kill session '211,485';--free buffer waits/*This wait event indicates that a server process was unable to find a free buffer and hasposted the database writer to make free buffers by writing out dirty buffers. A dirtybuffer is a buffer whose contents have been modified. Dirty buffers are freed for reusewhen DBWR has written the blocks to disk.CausesDBWR may not be keeping up with writing dirty buffers in the following situations:■ The I/O system is slow.■ There are resources it is waiting for,such as latches.■ The buffer cache is so small that DBWR spends most of its time cleaning outbuffers for server processes.■ The buffer cache is so big that one DBWR process is not enough to free enoughbuffers in the cache to satisfy requests.*/select * from v$filestat;select * from V$DB_CACHE_ADVICE;--latch events/*A latch is a low-level internal lock used by Oracle Database to protect memorystructures. The latch free event is updated when a server process attempts to get alatch,and the latch is unavailable on the first attempt.There is a dedicated latch-related wait event for the more popular latches that oftengenerate significant contention. For those events,the name of the latch appears in thename of the wait event,such as latch: library cache or latch: cache bufferschains . This enables you to quickly figure out if a particular type of latch isresponsible for most of the latch-related contention. Waits for all other latches aregrouped in the generic latch free wait event.*//*Check the following V$SESSION_WAIT parameter columns:■ P1 : Address of the latch■ P2 : Latch number■ P3 : Number of times process has slept,waiting for the latch*/select sw.EVENT,sum(sw.P3) SLERPS,sum(sw.SECONDS_IN_WAIT) seconds_in_wait from v$session_wait swwhere sw.EVENT like 'latch%'group by sw.EVENTselect se.EVENT,se.TIME_WAITED_MICRO,round(se.TIME_WAITED_MICRO * 100 / s.DBTIME,1) PCT_DB_TIME from v$system_event se,(select stm.VALUE DBTIME from v$sys_time_model stm where stm.STAT_NAME = 'DB time') swhere se.EVENT like 'latch%'order by PCT_DB_TIME ASCSELECT E.EVENT,E.WAIT_CLASS,E.TIME_WAITED_MICRO,ROUND(TIME_WAITED_MICRO * 100 / S.DBTIME,1) PCT_DB_TIME FROM V$SYSTEM_EVENT E,V$EVENT_NAME N,(SELECT VALUE DBTIME FROM V$SYS_TIME_MODEL WHERE STAT_NAME = 'DB time') SWHERE E.EVENT_ID = N.EVENT_ID AND N.WAIT_CLASS NOT IN ('Idle','System I/O')ORDER BY PCT_DB_TIME ASC;--shared pool ibrary cacheSELECT sql_TEXT FROM V$sqlSTATS WHERE EXECUTIONS < 4 ORDER BY sql_TEXT;SELECT SUBSTR(sql_TEXT,1,60),COUNT(*) FROM V$sqlSTATSWHERE EXECUTIONS < 4GROUP BY SUBSTR(sql_TEXT,60)HAVING COUNT(*) > 1;SELECT sql_TEXT FROM V$sqlSTATSWHERE PLAN_HASH_VALUE IN (SELECT PLAN_HASH_VALUE FROM V$sqlSTATS GROUP BY PLAN_HASH_VALUE HAVING COUNT(*) > 4)ORDER BY PLAN_HASH_VALUE;SELECT pa.SID,pa.VALUE "Hard Parses",ex.VALUE "Execute Count" FROM V$SESSTAT pa,V$SESSTAT exWHERE pa.SID = ex.SID AND pa.STATISTIC# = (SELECT STATISTIC# FROM V$STATNAME WHERE NAME = 'parse count (hard)') AND ex.STATISTIC# = (SELECT STATISTIC# FROM V$STATNAME WHERE NAME = 'execute count') AND pa.VALUE > 0;---chaxselect c.OBJECT_NAME,a.ROW_WAIT_OBJ#,a.ROW_WAIT_FILE#,a.ROW_WAIT_BLOCK#,a.ROW_WAIT_ROW#,dbms_rowid.rowid_create(1,c.OBJECT_ID,a.ROW_WAIT_ROW#) rid from v$session a,v$enqueue_lock b,dba_objects cwhere a.sid = b.SID and b.type = 'TX' and a.ROW_WAIT_OBJ# = object_id(+)