近日某客户核心系统数据库出现hang住情况，AWR报告top 5中”enq: TX - row lock contention“占DB Time 97%以上，如下：
trace文件中显示两个session互相阻塞，并且阻塞了大量sessions
client details:
O/S info: user: sunflow, term: unknown, ospid: 1234
machine: pdccsfwma0app18.site program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
Current Wait Stack:
0: waiting for 'enq: TX - row lock contention'
name|mode=0x54580004, usn<<16 | slot=0x29f000d, sequence=0xa984
wait_id=212839 seq_num=16236 snap_id=1
wait times: snap=8 min 39 sec, exc=8 min 39 sec, total=8 min 39 sec
wait times: max=infinite, heur=8 min 39 sec
wait counts: calls=1041 os=1041
in_wait=1 iflags=0x15a0
There is at least one session blocking this session.
Dumping 1 direct blocker(s):
inst: 1, sid: 30, ser: 121----这个session被sid 30阻塞了
Dumping final blocker:
inst: 1, sid: 30, ser: 121
There are 905 sessions blocked by this session.----这个session block了905个session
Dumping one waiter:
inst: 1, sid: 30, ser: 121
wait event: 'enq: TX - row lock contention'
p1: 'name|mode'=0x54580004
p2: 'usn<<16 | slot'=0xe8a0011
p3: 'sequence'=0x57f00
row_wait_obj#: 7298, block#: 0, row#: 0, file# 0 -------等待对象7298
min_blocked_time: 501 secs, waiter_cache_ver: 55039
Wait State:
fixed_waits=0 flags=0x2a boundary=0x700000638ce1030/0
另外一个trace文件显示sid 30被上面session block了，同样sid 30阻塞了908个会话
查看ADDM报告，显示object id为7298是一个位图索引，并且发现两个session的sql语句：
Action
Significant row contention was detected in the INDEX
" JZH.BM_JZH_STATE_INDEX " with object ID 7298. Trace the cause of row
contention in the application logic using the given blocked SQL.
Related Object
Database object with ID 7298.
Rationale
The SQL statement with SQL_ID "6h0cug179f9ms" was blocked on row locks.
Related Object
SQL statement with SQL_ID 5h0cug179f9ms.
INSERT INTO JZH_TRADE VALUES(:B6 ,:B5 ,:B4 ,:B3 ,:B2 ,:B1 )
Rationale
The SQL statement with SQL_ID "71bwjd6fy7xp5" was blocked on row locks.
Related Object
SQL statement with SQL_ID 61bwjd6fy7xp5.
UPDATE JZH_TRADE SET STATE=:B2 WHERE FLOWID=:B1
Rationale
The session with ID 7133 and serial number 45 in instance number 1 was
the blocking session responsible for 99% of this recommendation's
benefit.
查看表JZH_TRADE的定义发现索引BM_STATE_INDEX是一个位图索引，并且索引字段正是state，我们知道bitmap index与b-tree索引不同，bitmap index索引键值指向多行，因此当update时，oracle会锁定索引包含的多行记录
官文档有如一段解释：
Assume that a session updates the job ID of one employee from Shipping Clerk to Stock Clerk. In this case, the session requires exclusive access to the index key entry for the old value (Shipping Clerk) and the new value (Stock Clerk) . Oracle Database locks the rows pointed to by these two entries—but not the rows pointed to by Accountant or any other key—until the UPDATE commits.
oracle会以"X"排它模式锁定索引条目指向的update的新值与原值。
那bitmap index适用于什么样的场景？
In a bitmap index, the database stores a bitmap for each index key. In a conventional B-tree index, one index entry points to a single row. In a bitmap index, each index key stores pointers to multiple rows.
Bitmap indexes are primarily designed for data warehousing or environments in which queries reference many columns in an ad hoc fashion. Situations that may call for a bitmap index include:
The indexed columns have low cardinality, that is, the number of distinct values is small compared to the number of table rows.
The indexed table is either read-only or not subject to significant modification by DML statements.
For a data warehouse example, the sh.customer table has a cust_gender column with only two possible values: M and F. Suppose that queries for the number of customers of a particular gender are common. In this case, the customer.cust_gender column would be a candidate for a bitmap index.
Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid, so the bitmap index provides the same functionality as a B-tree index although it uses a different internal representation.
If the indexed column in a single row is updated, then the database locks the index key entry (for example, M or F) and not the individual bit mapped to the updated row. Because a key points to many rows, DML on indexed data typically locks all of these rows. For this reason, bitmap indexes are not appropriate for many OLTP applications.
Oracle官方文档解释位图索引是为数据仓库或环境设计，适用于1.低基数，distinct值小于表的行数，越小越好，2.只读表或不做DML操作，bitmap index不适用OLTP系统。
Top 5 Timed Foreground Events
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
enq: TX - row lock contention 6,553 816,378 124581 97.75 Application
DB CPU 5,890 0.71
log file sync 1,471,039 4,709 3 0.56 Commit
db file sequential read 528,348 755 1 0.09 User I/O
latch free 323 281 869 0.03 Other
Top 5 Timed Foreground Events
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
enq: TX - row lock contention 6,553 816,378 124581 97.75 Application
DB CPU 5,890 0.71
log file sync 1,471,039 4,709 3 0.56 Commit
db file sequential read 528,348 755 1 0.09 User I/O
latch free 323 281 869 0.03 Other
Top 5 Timed Foreground Events
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
enq: TX - row lock contention 6,553 816,378 124581 97.75 Application
DB CPU 5,890 0.71
log file sync 1,471,039 4,709 3 0.56 Commit
db file sequential read 528,348 755 1 0.09 User I/O
latch free 323 281 869 0.03 Other
Top 5 Timed Foreground Events
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
enq: TX - row lock contention 6,553 816,378 124581 97.75 Application
DB CPU 5,890 0.71
log file sync 1,471,039 4,709 3 0.56 Commit
db file sequential read 528,348 755 1 0.09 User I/O
latch free 323 281 869 0.03 Other
Top 5 Timed Foreground Events
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
enq: TX - row lock contention 6,553 816,378 124581 97.75 Application
DB CPU 5,890 0.71
log file sync 1,471,039 4,709 3 0.56 Commit
db file sequential read 528,348 755 1 0.09 User I/O
latch free 323 281 869 0.03 Other
trace文件中显示两个session互相阻塞，并且阻塞了大量sessions
client details:
O/S info: user: sunflow, term: unknown, ospid: 1234
machine: pdccsfwma0app18.site program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
Current Wait Stack:
0: waiting for 'enq: TX - row lock contention'
name|mode=0x54580004, usn<<16 | slot=0x29f000d, sequence=0xa984
wait_id=212839 seq_num=16236 snap_id=1
wait times: snap=8 min 39 sec, exc=8 min 39 sec, total=8 min 39 sec
wait times: max=infinite, heur=8 min 39 sec
wait counts: calls=1041 os=1041
in_wait=1 iflags=0x15a0
There is at least one session blocking this session.
Dumping 1 direct blocker(s):
inst: 1, sid: 30, ser: 121----这个session被sid 30阻塞了
Dumping final blocker:
inst: 1, sid: 30, ser: 121
There are 905 sessions blocked by this session.----这个session block了905个session
Dumping one waiter:
inst: 1, sid: 30, ser: 121
wait event: 'enq: TX - row lock contention'
p1: 'name|mode'=0x54580004
p2: 'usn<<16 | slot'=0xe8a0011
p3: 'sequence'=0x57f00
row_wait_obj#: 7298, block#: 0, row#: 0, file# 0 -------等待对象7298
min_blocked_time: 501 secs, waiter_cache_ver: 55039
Wait State:
fixed_waits=0 flags=0x2a boundary=0x700000638ce1030/0
另外一个trace文件显示sid 30被上面session block了，同样sid 30阻塞了908个会话
查看ADDM报告，显示object id为7298是一个位图索引，并且发现两个session的sql语句：
Action
Significant row contention was detected in the INDEX
" JZH.BM_JZH_STATE_INDEX " with object ID 7298. Trace the cause of row
contention in the application logic using the given blocked SQL.
Related Object
Database object with ID 7298.
Rationale
The SQL statement with SQL_ID "6h0cug179f9ms" was blocked on row locks.
Related Object
SQL statement with SQL_ID 5h0cug179f9ms.
INSERT INTO JZH_TRADE VALUES(:B6 ,:B5 ,:B4 ,:B3 ,:B2 ,:B1 )
Rationale
The SQL statement with SQL_ID "71bwjd6fy7xp5" was blocked on row locks.
Related Object
SQL statement with SQL_ID 61bwjd6fy7xp5.
UPDATE JZH_TRADE SET STATE=:B2 WHERE FLOWID=:B1
Rationale
The session with ID 7133 and serial number 45 in instance number 1 was
the blocking session responsible for 99% of this recommendation's
benefit.
查看表JZH_TRADE的定义发现索引BM_STATE_INDEX是一个位图索引，并且索引字段正是state，我们知道bitmap index与b-tree索引不同，bitmap index索引键值指向多行，因此当update时，oracle会锁定索引包含的多行记录
官文档有如一段解释：
Assume that a session updates the job ID of one employee from Shipping Clerk to Stock Clerk. In this case, the session requires exclusive access to the index key entry for the old value (Shipping Clerk) and the new value (Stock Clerk) . Oracle Database locks the rows pointed to by these two entries—but not the rows pointed to by Accountant or any other key—until the UPDATE commits.
oracle会以"X"排它模式锁定索引条目指向的update的新值与原值。
那bitmap index适用于什么样的场景？
In a bitmap index, the database stores a bitmap for each index key. In a conventional B-tree index, one index entry points to a single row. In a bitmap index, each index key stores pointers to multiple rows.
Bitmap indexes are primarily designed for data warehousing or environments in which queries reference many columns in an ad hoc fashion. Situations that may call for a bitmap index include:
The indexed columns have low cardinality, that is, the number of distinct values is small compared to the number of table rows.
The indexed table is either read-only or not subject to significant modification by DML statements.
For a data warehouse example, the sh.customer table has a cust_gender column with only two possible values: M and F. Suppose that queries for the number of customers of a particular gender are common. In this case, the customer.cust_gender column would be a candidate for a bitmap index.
Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid, so the bitmap index provides the same functionality as a B-tree index although it uses a different internal representation.
If the indexed column in a single row is updated, then the database locks the index key entry (for example, M or F) and not the individual bit mapped to the updated row. Because a key points to many rows, DML on indexed data typically locks all of these rows. For this reason, bitmap indexes are not appropriate for many OLTP applications.
Oracle官方文档解释位图索引是为数据仓库或环境设计，适用于1.低基数，distinct值小于表的行数，越小越好，2.只读表或不做DML操作，bitmap index不适用OLTP系统。
Top 5 Timed Foreground Events
| Event | Waits | Time(s) | Avg wait (ms) | % DB time | Wait Class |
|---|---|---|---|---|---|
| enq: TX - row lock contention | 6,553 | 816,378 | 124581 | 97.75 | Application |
| DB CPU | 5,890 | 0.71 | |||
| log file sync | 1,471,039 | 4,709 | 3 | 0.56 | Commit |
| db file sequential read | 528,348 | 755 | 1 | 0.09 | User I/O |
| latch free | 323 | 281 | 869 | 0.03 | Other |
Top 5 Timed Foreground Events
| Event | Waits | Time(s) | Avg wait (ms) | % DB time | Wait Class |
|---|---|---|---|---|---|
| enq: TX - row lock contention | 6,553 | 816,378 | 124581 | 97.75 | Application |
| DB CPU | 5,890 | 0.71 | |||
| log file sync | 1,471,039 | 4,709 | 3 | 0.56 | Commit |
| db file sequential read | 528,348 | 755 | 1 | 0.09 | User I/O |
| latch free | 323 | 281 | 869 | 0.03 | Other |
Top 5 Timed Foreground Events
| Event | Waits | Time(s) | Avg wait (ms) | % DB time | Wait Class |
|---|---|---|---|---|---|
| enq: TX - row lock contention | 6,553 | 816,378 | 124581 | 97.75 | Application |
| DB CPU | 5,890 | 0.71 | |||
| log file sync | 1,471,039 | 4,709 | 3 | 0.56 | Commit |
| db file sequential read | 528,348 | 755 | 1 | 0.09 | User I/O |
| latch free | 323 | 281 | 869 | 0.03 | Other |
Top 5 Timed Foreground Events
| Event | Waits | Time(s) | Avg wait (ms) | % DB time | Wait Class |
|---|---|---|---|---|---|
| enq: TX - row lock contention | 6,553 | 816,378 | 124581 | 97.75 | Application |
| DB CPU | 5,890 | 0.71 | |||
| log file sync | 1,471,039 | 4,709 | 3 | 0.56 | Commit |
| db file sequential read | 528,348 | 755 | 1 | 0.09 | User I/O |
| latch free | 323 | 281 | 869 | 0.03 | Other |
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
enq: TX - row lock contention 6,553 816,378 124581 97.75 Application
DB CPU 5,890 0.71
log file sync 1,471,039 4,709 3 0.56 Commit
db file sequential read 528,348 755 1 0.09 User I/O
latch free 323 281 869 0.03 Other
Top 5 Timed Foreground Events
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
enq: TX - row lock contention 6,553 816,378 124581 97.75 Application
DB CPU 5,890 0.71
log file sync 1,471,039 4,709 3 0.56 Commit
db file sequential read 528,348 755 1 0.09 User I/O
latch free 323 281 869 0.03 Other
Top 5 Timed Foreground Events
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
enq: TX - row lock contention 6,553 816,378 124581 97.75 Application
DB CPU 5,890 0.71
log file sync 1,471,039 4,709 3 0.56 Commit
db file sequential read 528,348 755 1 0.09 User I/O
latch free 323 281 869 0.03 Other
Top 5 Timed Foreground Events
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
enq: TX - row lock contention 6,553 816,378 124581 97.75 Application
DB CPU 5,890 0.71
log file sync 1,471,039 4,709 3 0.56 Commit
db file sequential read 528,348 755 1 0.09 User I/O
latch free 323 281 869 0.03 Other
Top 5 Timed Foreground Events
Event Waits Time(s) Avg wait (ms) % DB time Wait Class
enq: TX - row lock contention 6,553 816,378 124581 97.75 Application
DB CPU 5,890 0.71
log file sync 1,471,039 4,709 3 0.56 Commit
db file sequential read 528,348 755 1 0.09 User I/O
latch free 323 281 869 0.03 Other
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10271187/viewspace-2126955/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/10271187/viewspace-2126955/
1349
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
