ITPub博客

首页 > 数据库 > Oracle > 位图索引导致update阻塞insert语句

位图索引导致update阻塞insert语句

原创 Oracle 作者:parknkjun 时间:2016-10-23 12:44:00 0 删除 编辑
近日某客户核心系统数据库出现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系统。







来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10271187/viewspace-2126955/,如需转载,请注明出处,否则将追究法律责任。

下一篇: ORACLE 回滚段详解
请登录后发表评论 登录
全部评论

注册时间:2008-02-11

  • 博文量
    113
  • 访问量
    509309