Locking , blocking - Queries

 

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. 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

SQL>

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.

Find Detailed Information

5-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

/

SQL>

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;

select event, state, seconds_in_wait siw from v$session_wait where sid = 81;

EVENT                                     STATE        SIW

---------------------------------------- ----------- ------

enq: TX - row lock contention            WAITING       976

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

…

SQL>

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

…

SQL>

SQL> 

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;

SQL> 

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

SQL>

The output shows that the session with the SID 68 is waiting for a row lock that’s held by another

transaction.

5-9. 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;

5-12. 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 )

5-13. Identifying Blocked and Blocking Sessions

----------------------------------------------------

SQL> 

select decode(request,0,'Holder: ','Waiter: ')||sid sess,

id1, id2, lmode, request, type

from v$lock

where (id1, id2, type) in

(select id1, id2, type from v$lock where request>0)

order by id1, request;

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

SQL>

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);

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;

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;

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;

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;

5-20. 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.

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;

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';

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#;

Once you have identified queries (using the prior queries in this section), then consider the

following factors, which can cause a SQL statement to consume inordinate amounts of I/O:

• Poorly written SQL

• Improper indexing

• Improper use of parallelism (which can cause excessive full table scans)

You’ll have to examine each query and try to determine if one of the prior items is the cause of poor performance as it relates to I/O.