Do You Have a Wait Problem?
It’s easy to find out the percentage of time a database has spent waiting for resources instead of actually executing.
1) Issue the following query to find out the relative percentages of wait times and actual CPU processing in the database:
SQL> select metric_name, value
from v$sysmetric
where metric_name in ('Database CPU Time Ratio',
'Database Wait Time Ratio') and
intsize_csec =
(select max(INTSIZE_CSEC) from V$SYSMETRIC);
METRIC_NAME VALUE
————————————------------ -----------
Database Wait Time Ratio 11.371689
Database CPU Time Ratio 87.831890
Note:- If the query shows a very high value for the Database Wait Time Ratio, or if the Database Wait Time Ratio is much greater than the Database CPU Time Ratio, the database is spending more time waiting than processing and you must dig deeper into the Oracle wait events to identify the specific wait events causing this.
2) Identifying SQL Statements with the Most Waits
----------------------------------------------------------------
Problem
You want to identify the SQL statements responsible for the most waits in your database.
Solution
Execute the following query to identify the SQL statements that are experiencing the most waits in your
database:
SQL>
select ash.user_id,
u.username,
s.sql_text,
sum(ash.wait_time +
ash.time_waited) ttl_wait_time
from v$active_session_history ash,
v$sqlarea s,
dba_users u
where ash.sample_time between sysdate - 60/2880 and sysdate
and ash.sql_id = s.sql_id
and ash.user_id = u.user_id
group by ash.user_id,s.sql_text, u.username
order by ttl_wait_time
/
The preceding query ranks queries that ran during the past 30 minutes, according to the total time waited by each query.
SQL> select event, count(*) from v$session_wait group by event;
SQL> select event, state, seconds_in_wait siw from v$session_wait where sid = 81;
EVENT STATE SIW
---------------------------------------- ----------- ------
enq: TX - row lock contention WAITING 976
3) Examining Wait Events by Class
---------------------------------------
SQL>
select wait_class, name
from v$event_name
where name LIKE 'enq%'
and wait_class <> 'Other'
order by wait_class
/
WAIT_CLASS NAME
-------------------- --------------------------
Administrative enq: TW - contention
Concurrency enq: TX - index contention
4) To view the current waits grouped into various wait classes, issue the following query:
SQL>
select wait_class, sum(time_waited), sum(time_waited)/sum(total_waits)
sum_waits
from v$system_wait_class
group by wait_class
order by 3 desc;
WAIT_CLASS SUM(TIME_WAITED) SUM_WAITS
---------------- ---------- ----------------- -----------------
Idle 249659211 347.489249
Commit 1318006 236.795904
Concurrency 16126 4.818046
User I/O 135279 2.228869
Application 912 .0928055
Network 139 .0011209
select a.event, a.total_waits, a.time_waited, a.average_wait
from v$system_event a, v$event_name b, v$system_wait_class c
where a.event_id=b.event_id
and b.wait_class#=c.wait_class#
and c.wait_class in ('Application','Concurrency')
order by average_wait desc;
select a.sid, a.event, a.total_waits, a.time_waited, a.average_wait
from v$session_event a, v$session b
where time_waited > 0
and a.sid=b.sid
and b.username is not NULL
and a.event='enq: TX - row lock contention';
SID EVENT TOTAL_WAITS time_waited average_wait
---------- ------------------------------ ------------ ----------- ------------
68 enq: TX - row lock contention 24 8018 298
The output shows that the session with the SID 68 is waiting for a row lock that’s held by another transaction.
5. Minimizing read by other session Wait Events
--------------------------------------------------------
Problem
Your AWR report shows that the read by other session wait event is responsible for the highest number of waits.
You’d like to reduce the high read by other session waits.
Solution
The main reason you’ll see the read by other session wait event is that multiple sessions are seeking to read the same data blocks, whether they are table or index blocks, and are forced to wait behind the session that’s currently reading those blocks.
You can find the data blocks a session is waiting for by executing the following command:
SQL>
select p1 "file#", p2 "block#", p3 "class#" from v$session_wait where event = 'read by other session';
You can then take the block# and use it in the following query, to identify the exact segments (table or index) that are causing the read by other session waits.
SQL>
select relative_fno, owner, segment_name, segment_type
from dba_extents
where file_id = &file
and &block between block_id
and block_id + blocks - 1;
6. Finding Out Who’s Holding a Blocking Lock
------------------------------------------------------
In order to find out whether a session is being blocked by the locks being applied by another session, you can execute the following query:
SQL>
select 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 s2
where 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.id2 ;
BLOCKING_STATUS
--------------------------------------------------------------------
HR@MIRO\MIROPC61 ( SID=68 ) is blocking SH@MIRO\MIROPC61 ( SID=81 )
SQL> select sid,type,lmode,request,ctime,block from v$lock;
SID TY LMODE REQUEST CTIME BLOCK
---- -------- ----------- ----------- -------- -------
127 MR 4 0 102870 0
81 TX 0 6 778 0
191 AE 4 0 758 0
205 AE 4 0 579 0
140 AE 4 0 11655 0
68 TM 3 0 826 0
68 TX 6 0 826 1
The key column to watch is the BLOCK column—the blocking session will have the value 1 for this column.
In our example, session 68 is the blocking session, because it shows the value 1 under the BLOCK column.
Thus, the V$LOCK view confirms our initial finding in the “Solution” section of this recipe.
The blocking session, with a SID of 68, also shows a lock mode 6 under the LMODE column, indicating that it’s holding this lock in the exclusive mode—this is the reason session 81 is “hanging,” unable to perform its update operation.
The blocked session, of course, is the victim—so it shows a value of 0 in the BLOCK column.
It also shows a value of 6 under the REQUEST column, because it’s requesting a lock in the exclusive mode to perform its update of the column. The blocking session, in turn, will show a value of 0
for the REQUEST column, because it isn’t requesting any locks—it’s already holding it.
If you want to find out the wait class and for how long a blocking session has been blocking others,
you can do so by querying the V$SESSION view, as shown here:
SQL>
select blocking_session, sid, wait_class,
seconds_in_wait
from v$session
where blocking_session is not NULL
order by blocking_session;
BLOCKING_SESSION SID WAIT_CLASS SECONDS_IN_WAIT
----------------- -------- ------------- ----------------
68 81 Application 7069
The query shows that the session with SID=68 is blocking the session with SID=81, and the block started 7,069 seconds ago.
SQL> alter system kill session '68, 1234';
5-17. Identifying Recently Locked Sessions
--------------------------------------------------
All locks held in the database during the previous five minutes.
SQL>
select to_char(h.sample_time, 'HH24:MI:SS') TIME,h.session_id,
decode(h.session_state, 'WAITING' ,h.event, h.session_state) STATE,
h.sql_id, h.blocking_session BLOCKER
from v$active_session_history h, dba_users u
where u.user_id = h.user_id
and h.sample_time > SYSTIMESTAMP-(2/1440);
7. To find the most important wait events in the last 15 minutes, issue the following query:
-----------------------------------------------------------------------------------
SQL>
select event,
sum(wait_time +
time_waited) total_wait_time
from v$active_session_history
where sample_time between
sysdate – 30/2880 and sysdate
group by event
order by total_wait_time desc;
8. To find out which of your users experienced the most waits in the past 15 minutes, issue the following query:
SQL>
select s.sid, s.username,
sum(a.wait_time +
a.time_waited) total_wait_time
from v$active_session_history a,
v$session s
where a.sample_time between sysdate – 30/2880 and sysdate
and a.session_id=s.sid
group by s.sid, s.username
order by total_wait_time desc;
9. Execute the following query to find out the objects with the highest waits.
---------------------------------------------------------------------
SQL>select a.current_obj#, o.object_name, o.object_type, a.event,
sum(a.wait_time +
a.time_waited) total_wait_time
from v$active_session_history a,
dba_objects d
where a.sample_time between sysdate – 30/2880 and sysdate
and a.current_obj# = d.object_id
group by a.current_obj#, d.object_name, d.object_type, a.event
order by total_wait_time;
10. You can identify the SQL statements that have been waiting the most during the last 15 minutes with this query.
---------------------------------------------------------------------
SQL>
select a.user_id,u.username,s.sql_text,
sum(a.wait_time + a.time_waited) total_wait_time
from v$active_session_history a,
v$sqlarea s,
dba_users u
where a.sample_time between sysdate – 30/2880 and sysdate
and a.sql_id = s.sql_id
and a.user_id = u.user_id
group by a.user_id,s.sql_text, u.username;
11. Minimizing Latch Contention
----------------------------------------
SQL>
select event, sum(P3), sum(seconds_in_wait) seconds_in_wait
from v$session_wait
where event like 'latch%'
group by event;
The previous query shows the latches that are currently being waited for by this session.
To find out the amount of time the entire instance has waited for various latches, execute the following SQL statement.
SQL>
select wait_class, event, time_waited / 100 time_secs
from v$system_event e
where e.wait_class <> 'Idle' AND time_waited > 0
union
select 'Time Model', stat_name NAME,
round ((value / 1000000), 2) time_secs
from v$sys_time_model
where stat_name not in ('background elapsed time', 'background cpu time')
order by 3 desc;
WAIT_CLASS EVENT TIME_SECS
-------------------- ---------------------------- -----------------
Concurrency library cache pin 622.24
Concurrency latch: library cache 428.23
Concurrency latch: library cache lock 93.24
Concurrency library cache lock 24.20
Concurrency latch: library cache pin 60.28
Contention due to the library cache latches as well as shared pool latches is usually due to applications not using bind variables.
If your application can’t be recoded to incorporate bind variables,
all’s not lost. You can set the CURSOR_SHARING parameter to force Oracle to use bind variables, even if your application hasn’t specified them in the code. When you set the CURSOR_SHARING parameter to FORCE, Oracle converts all literals to bind variables.
12. You can also directly query data dictionary views such as V$SQL to determine which SQL statements are using excessive I/O—for example:
----------------------------------------------------
SELECT *
FROM
(SELECT
parsing_schema_name
,direct_writes
,SUBSTR(sql_text,1,75)
,disk_reads
FROM v$sql
ORDER BY disk_reads DESC)
WHERE rownum < 20;
13. To determine which sessions are currently waiting for I/O resources, query V$SESSION:
-----------------------------------------------------------------------------------
SELECT
username
,program
,machine
,sql_id
FROM v$session
WHERE event LIKE 'db file%read';
14. To view objects that are waiting for I/O resources, run a query such as this:
---------------------------------------------------------------------------------
SELECT
object_name
,object_type
,owner
FROM v$session a
,dba_objects b
WHERE a.event LIKE 'db file%read'
AND b.data_object_id = a.row_wait_obj#;
No comments:
Post a Comment