Shulou(Shulou.com)06/01 Report--

At around 7: 00 on March 17, the customer's set of 11gRAC suddenly showed an increase in active conversations, the waiting event was too high, and the cpu utilization rate was close to zero. Let's restore the process of solving this problem as much as possible:

Query wait event:

Select inst_id, event#, event,count (*) from gv$session

Where wait_class# 6

Group by inst_id, event#,event

Order by 1,4 desc

A large number of read by other session wait events were found. At the same time with db file sequential read.

# # introduction # #

What is read by other session?

This wait event occurs when we are trying to access a buffer in the buffer cache but we find that the buffer is currently being read from disk by another user so we need to wait for that to complete before we can access it. In previous versions, this wait was classified under the "buffer busy waits" wait-event. However, in Oracle 10.1and higher, the wait time is now broken out into the "read by other session" wait event.

When b session accesses buffer cache and pin resides in the corresponding block, it finds that a session also needs these data blocks and is reading related data blocks from the hard disk to buffer cache. At this time, b session must wait for a session to finish reading, and b session has a read by other session wait event at this time. In versions prior to 10.1, this wait event was classified as a buffer busy waits wait event. Wait for the event to be isolated after 10.1.

From the above definition, it is not difficult to see that the reason for this waiting event is:

1) Hot block contention: contention is caused by a large number of sessions accessing the same block almost at the same time.

2) there is a problem with disk IO performance: disk IO still needs to take a look, although this reason is less likely than the one above. What if the elephant rushes into the data center and causes a hardware failure? Of course, if parallelism is used, it also depends on whether there is too much dbwr slave process:sho parameter dbwr_io_slaves open.

See the meaning of the p1 p2 p3 value of the wait event:

SQL > SELECT NAME, PARAMETER1 P1, PARAMETER2 P2, PARAMETER3 P3

2 FROM V$EVENT_NAME

3 WHERE NAME ='& event_name'

Enter value for event_name: read by other session

Old 3: WHERE NAME ='& event_name'

New 3: WHERE NAME = 'read by other session'

NAME P1 P2 P3

Read by other session file# block# class#

Parameters:

P1 = file# Absolute File# (AFN)

P2 = block#

P3 = class# Block class

Class# Block class#

This is the class of block being waited on. In particular:

Class 1 indicates a "data block", which could be table or index

Class 4 indicates a "segment header"

Class > = 15 indicate undo blocks

Query p1 p2 p3 for waiting events:

SELECT p1 "file#", p2 "block#", p3 "class#"

FROM v$session_wait WHERE event ='& event_name'

After you find three values, you can determine what the hotspot object is:

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

View the sql caused by the hotspot block:

Select sql_id,sql_text

From v$sqltext a

(select distinct a.owner, a.segment_name, a.segment_type

From dba_extents a

(select dbarfil, dbablk

From (select dbarfil, dbablk from x$bh order by tch desc)

Where rownum < 11) b

Where a.RELATIVE_FNO = b.dbarfil

And a.BLOCK_ID b.dbablk) b

Where a.sql_text like'%'| | b.segment_name | |'%'

And b.segment_type = 'TABLE'

Order by a.hash_value, a.address, a.piece

# # #

Determine the session and sql information for read by other session:

Select sid

S.username

S.program

S.action

Logon_time

Q.sql_text

Q.SQL_FULLTEXT

Q.sql_id

From v$session s

Left join v$sql Q on s.sql_hash_value = q.hash_value

Where s.sid in (select sid

From v$session_wait

Where event='read by other session')

At this time, I found that the sql is basically some similar sql statements:

The sql id of the select statement changes frequently, but the sql is similar.

PLAN_TABLE_OUTPUT

- -

SQL_ID 44v32y42t480h, child number 0

-

Select * from (select B. as row_index from as row_index from (select PRO)

D_ID,SO_DATE,INSTALL_ADDR,SO_STAFF_NAME,CHANNEL_NAME,SO_ORDER_ID

, SO_CO_SERIAL,ORDER_ID,SPELINENUMBER,TML_CODE,ACCESS_NUMB,EXT_NU

MB,CUST_NAME,OWNER_ORG_ID,GROUP_CODE,ACT_ID,DEAL_PRIORITY,STATE

PROD_OFFER_NAME,ONU_PORT_NAME,COMMUNITY_NAME,PROD_BANDWIDTH,OPER

_ TYPE,ALARM_DELAY_FLAG,PRE_INSTALL_START_TIME,PRE_INSTALL_END_TI

ME,CHANNEL_CODE,SLA_CODE,IS_KZY,REGION_ID from (SELECT a.order_i

D, a.so_order_id, a.so_co_serial, a.order_grp_id, a.channel_type

, a.channel_code, a.channel_name, a.pre_install_start_time, a.pr

E_install_end_time, a.limit_time, a.fee_flag, a.so_staff_id, a.s

O_staff_code, a.so_staff_name, a.so_date, a.so_org_id, a.owner_o

Rg_id, a.owner_district, a.order_kind, a.priority, a.prod_ins_id

, a.rel_prod_ins_id, a.prod_id, a.act_id, a.pay_way, a.pay_name

A.access_numb, a.ext_numb, a.bind_access_numb, a.tml_code, a.sl

A_id, a.is_main_prod, a.install_addr, a.standard_addr_id, a.stan

Dard_addr_name, a.create_date, a.execute_date, a.delay_date, a.a

Larm_date, a.alarm_delay_flag, a.deal_priority, a.sla_code, a.wf

_ template_code, a.src_system, a.template_id, a.state, a.remarks

A.region_id, (SELECT oa.attr_val FROM SF_O_ATTR_A oa WHERE a.or

Der_id = oa.order_id AND oa.attr_code = 'community_name' AND ROW

NUM = 1) AS community_name, a.pay_name AS SPELINENUMBER, NVL ((S

ELECT ra.attr_val FROM SF_O_RESSRV_REL_A orr, SF_RESSRV_RES_REL_

A rrr, SF_RES_ATTR_A ra WHERE ra.attr_code = 'port_name' AND ra.

Res_ins_id = rrr.res_ins_id AND rrr.ressrv_ins_id = orr.ressrv_i

Ns_id AND orr.order_id = a.order_id AND orr.state ='N' AND ROWN

UM = 1), (SELECT ra.attr_val FROM SF_O_RESSRV_REL_A orr, SF_RESS

RV_RES_REL_A rrr, SF_RES_ATTR_A ra WHERE ra.attr_code = 'port_na

Me' AND ra.res_ins_id = rrr.res_ins_id AND rrr.ressrv_ins_id = o

Rr.ressrv_ins_id AND orr.order_id = a.order_id AND orr.state ='

O'AND ROWNUM = 1)) AS onu_port_name, (SELECT oa.attr_val FROM S

F_O_ATTR_A oa WHERE a.order_id = oa.order_id AND oa.attr_code =

'prod_offer_name' AND ROWNUM = 1) AS prod_offer_name, (SELECT o

A.attr_val FROM SF_O_ATTR_A oa WHERE a.order_id = oa.order_id AN

D oa.attr_code = 'prod_bandwidth' AND ROWNUM = 1) AS prod_bandw

Idth, op.oper_id, decode. (there are many in the back, which are kept secret and omitted)

Plan hash value: 3873006668

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | 18 (100) | | |

| | * 1 | VIEW | | 1 | 2963 | 18 (6) | 00:00:01 |

| | 2 | COUNT | | |

| | 3 | VIEW | | 1 | 2950 | 18 (6) | 00:00:01 |

| | 4 | SORT ORDER BY | | 1 | 344 | 18 (6) | 00:00:01 |

| | * 5 | FILTER | | |

| | 6 | NESTED LOOPS | | |

| | 7 | NESTED LOOPS | | 1 | 344 | 17 (0) | 00:00:01 |

| | 8 | NESTED LOOPS | | 1 | 314 | 11 (0) | 00:00:01 |

| | 9 | NESTED LOOPS | | 1 | 244 | 8 (0) | 00:00:01 |

| | 10 | NESTED LOOPS | | 1 | 227 | 6 (0) | 00:00:01 |

| | * 11 | TABLE ACCESS BY INDEX ROWID | SF_TASK_A | 1 | 80 | 4 (0) | 00:00:01 |

| | * 12 | INDEX RANGE SCAN | I_SF_TASK_2_A | 2 | | 3 (0) | 00:00:01 |

| | * 13 | TABLE ACCESS BY INDEX ROWID | SF_ORDER_A | 1 | 147 | 2 (0) | 00:00:01 |

| | * 14 | INDEX UNIQUE SCAN | PK_SF_ORDER_A | 1 | | 1 (0) | 00:00:01 |

| | 15 | TABLE ACCESS BY INDEX ROWID | SF_O_CUST_A | 1 | 17 | 2 (0) | 00:00:01 |

| | * 16 | INDEX UNIQUE SCAN | PK_SF_O_CUST_A | 1 | | 1 (0) | 00:00:01 |

| | * 17 | TABLE ACCESS BY INDEX ROWID | SF_TASK_AUTH_A | 1 | 70 | 3 (0) | 00:00:01 |

| | * 18 | INDEX RANGE SCAN | I_SF_TASK_AUTH_1_A | 1 | | 2 (0) | 00:00:01 |

| | * 19 | INDEX RANGE SCAN | I_SF_TASK_OPER_1_A | 3 | | 3 (0) | 00:00:01 |

| | * 20 | TABLE ACCESS BY INDEX ROWID | SF_TASK_OPER_A | 2 | 60 | 6 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

1-filter ("ROW_INDEX" = 1)

5-filter (TO_DATE (: 4MYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYMMMUDD HH24:MI:SS' HH24:MI:SS') AND) = TO_DATE

T. CREATE_DATE.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.