- 最后登录
- 2023-8-16
- 在线时间
- 1686 小时
- 威望
- 2135
- 金钱
- 50532
- 注册时间
- 2011-10-12
- 阅读权限
- 200
- 帖子
- 5207
- 精华
- 39
- 积分
- 2135
- UID
- 2
|
13#
发表于 2012-2-17 14:05:10
ODM Data
Bitmap Indexes and Deadlocks: Deadlocks on Insert Statements [ID 171795.1]
:- PURPOSE
- -------
- The purpose of this article is to explain the occurrence of deadlocks
- when the only DML activity is insert statements against a table with
- a bitmap index. This applies from version 8 to 11.
- SCOPE & APPLICATION
- -------------------
- Database administrators and Application developers involved in
- application design.
- BITMAP INDEXES: THE HIDDEN DEADLOCK THREAT
- ------------------------------------------
- The limitations of bitmap indexes as this:
- Extract of documentation:
- "DML and DDL statements, such as UPDATE, DELETE, DROP TABLE, affect bitmap
- indexes the same way they do traditional indexes: the consistency model is
- the same. A compressed bitmap for a key value is made up of one or more bitmap
- segments, each of which is at most half a block in size (but may be smaller).
- The locking granularity is one such bitmap segment. This may affect performance
- in environments where many transactions make simultaneous updates. If numerous
- DML operations have caused increased index size and decreasing performance for
- queries, then you can use the ALTER INDEX ... REBUILD statement to compact the
- index and restore efficient performance.
- A B*-tree index entry contains a single rowid. Therefore, when the index entry
- is locked, a single row is locked. With bitmap indexes, an entry can potentially
- contain a range of rowids. When a bitmap index entry is locked, the entire range
- of rowids is locked. The number of rowids in this range affects concurrency.
- As the number of rowids increases in a bitmap segment, concurrency decreases.
- Locking issues affect DML operations, and may affect heavy OLTP environments.
- Locking issues do not, however, affect query performance. As with other types
- of indexes, updating bitmap indexes is a costly operation. Nonetheless, for
- bulk inserts and updates where many rows are inserted or many updates are made
- in a single statement, performance with bitmap indexes can be better than with
- regular B*-tree indexes."
- **************
- What is not mentioned is the fact that the same architectural feature that
- locks a range of rowid's also means that its possible to get a deadlock within
- the bitmap when updating rows in the underlying table. This deadlock is not in
- the table itself, as one might suspect, but rather in the bitmap index blocks.
- This kind of deadlock is easily diagnosable by the deadlock trace file, which
- has an entry that looks like the example below:
- The following deadlock is not an ORACLE error. It is a
- deadlock due to user error in the design of an application
- or from issuing incorrect ad-hoc SQL. The following
- information may aid in determining the deadlock:
- Deadlock graph:
- ---------Blocker(s)-------- ---------Waiter(s)---------
- Resource Name process session holds waits process session holds waits
- TX-00080027-0000d2a1 12 37 X 15 35 S
- TX-000a0016-0000d6d2 15 35 X 12 37 S
- session 37: DID 0001-000C-00000002 session 35: DID 0001-000F-00000002
- session 35: DID 0001-000F-00000002 session 37: DID 0001-000C-00000002
- Rows waited on:
- Session 35: no row
- Session 37: no row
- The piece of information that leads us to a bitmap deadlock is the "no row"
- value in the session information. If we had encountered a deadlock in the
- underlying table, the Session line would give us row information so that we
- could track down the exact point of failure. Without a row, it would seem that
- we are at a dead end. Even more mysterious is when we get this deadlock on
- inserts, where we are inserting only new rows and therefore it would seem
- impossible to get a deadlock. No one should be requesting a row that someone
- else holds locked.
- There are no solutions to this kind of problems, except not using bitmap indexes
- when having an application where you can't control when the DML are issued against
- the tables with bitmap indexes. Bitmaps are normally intended for datawarehouse
- applications that are loading data via batches and that users are only querying.
- The following testcase can be used to see the results of this type of problem.
- We will create a table called CAR_TYPE, which holds information about cars,
- including the car's color. We will build a bitmap index on the COLOR column.
- After doing so, we will populate the table with data. After the initial insert,
- we will open two sessions of the same user, and run simultaneous inserts into
- the CAR_TYPE table.
- TESTCASE:
- =====================================
- =====================================
- create table car_type (
- make varchar2(20),
- model varchar2(20),
- color varchar2(20),
- VIN number(15) primary key,
- year number(4));
- create bitmap index car_type_bm_idx on car_type(color);
- create sequence car_type_seq
- start with 35001
- increment by 1
- nocache
- nocycle;
- declare
- v_CarMake varchar2(20) := 'Audi';
- v_CarModel varchar(20) := 'Quattro';
- v_CarColor varchar(20) := 'Gold';
- v_CarVin binary_integer :=1;
- begin
- loop
- insert into car_type (make,model,color,VIN,year)
- values (v_CarMake, v_CarModel, v_CarColor, v_CarVin, '2002');
- v_CarVin := v_CarVin + 1;
- exit when v_CarVin > 5000;
- end loop;
- end;
- /
- commit;
- declare
- v_CarMake varchar2(20) := 'Toyota';
- v_CarModel varchar(20) := 'Camry';
- v_CarColor varchar(20) := 'Red';
- v_CarVin binary_integer :=5001;
- begin
- loop
- insert into car_type (make,model,color,VIN,year)
- values (v_CarMake, v_CarModel, v_CarColor, v_CarVin, '2002');
- v_CarVin := v_CarVin + 1;
- exit when v_CarVin > 10000;
- end loop;
- end;
- /
- commit;
- declare
- v_CarMake varchar2(20) := 'Audi';
- v_CarModel varchar(20) := 'Quattro';
- v_CarColor varchar(20) := 'Blue';
- v_CarVin binary_integer :=10001;
- begin
- loop
- insert into car_type (make,model,color,VIN,year)
- values (v_CarMake, v_CarModel, v_CarColor, v_CarVin, '2002');
- v_CarVin := v_CarVin + 1;
- exit when v_CarVin > 15000;
- end loop;
- end;
- /
- commit;
- declare
- v_CarMake varchar2(20) := 'Toyota';
- v_CarModel varchar(20) := 'Camry';
- v_CarColor varchar(20) := 'Silver';
- v_CarVin binary_integer :=15001;
- begin
- loop
- insert into car_type (make,model,color,VIN,year)
- values (v_CarMake, v_CarModel, v_CarColor, v_CarVin, '2002');
- v_CarVin := v_CarVin + 1;
- exit when v_CarVin > 20000;
- end loop;
- end;
- /
- commit;
- declare
- v_CarMake varchar2(20) := 'Audi';
- v_CarModel varchar(20) := 'Quattro';
- v_CarColor varchar(20) := 'Green';
- v_CarVin binary_integer :=20001;
- begin
- loop
- insert into car_type (make,model,color,VIN,year)
- values (v_CarMake, v_CarModel, v_CarColor, v_CarVin, '2002');
- v_CarVin := v_CarVin + 1;
- exit when v_CarVin > 25000;
- end loop;
- end;
- /
- commit;
- declare
- v_CarMake varchar2(20) := 'Audi';
- v_CarModel varchar(20) := 'Quattro';
- v_CarColor varchar(20) := 'Black';
- v_CarVin binary_integer :=25001;
- begin
- loop
- insert into car_type (make,model,color,VIN,year)
- values (v_CarMake, v_CarModel, v_CarColor, v_CarVin, '2002');
- v_CarVin := v_CarVin + 1;
- exit when v_CarVin > 30000;
- end loop;
- end;
- /
- commit;
- declare
- v_CarMake varchar2(20) := 'Toyota';
- v_CarModel varchar(20) := 'Camry';
- v_CarColor varchar(20) := 'White';
- v_CarVin binary_integer :=30001;
- begin
- loop
- insert into car_type (make,model,color,VIN,year)
- values (v_CarMake, v_CarModel, v_CarColor, v_CarVin, '2002');
- v_CarVin := v_CarVin + 1;
- exit when v_CarVin > 35000;
- end loop;
- end;
- /
- commit;
- ===============================
- ===============================
- After this initial creation, cut the following script into a .sql file, and
- then execute it simultaneously from two sessions:
- ===============================
- ===============================
- insert into car_type values (
- 'Toyota','Camry','White',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','Red',car_type_seq.nextval,'2002');
-
- insert into car_type values (
- 'Toyota','Camry','Silver',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Black',car_type_seq.nextval,'2002');
- commit;
- insert into car_type values (
- 'Audi','Quatro','Gold',car_type_seq.nextval,'2002');
-
- insert into car_type values (
- 'Audi','Quatro','Blue',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Green',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','White',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','Red',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','Silver',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Black',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Gold',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Blue',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Green',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','White',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','Red',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','Silver',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Black',car_type_seq.nextval,'2002');
- commit;
- insert into car_type values (
- 'Audi','Quatro','Gold',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Blue',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Green',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','White',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','Red',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','Silver',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Black',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Gold',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Blue',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Green',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','White',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','Red',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','Silver',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Black',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Gold',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Blue',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Green',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','White',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','Red',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','Silver',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Black',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Gold',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Blue',car_type_seq.nextval,'2002');
- commit;
- insert into car_type values (
- 'Audi','Quatro','Green',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','White',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','Red',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','Silver',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Black',car_type_seq.nextval,'2002');
-
- insert into car_type values (
- 'Audi','Quatro','Gold',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Blue',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Green',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','White',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','Red',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','Silver',car_type_seq.nextval,'2002');
-
- insert into car_type values (
- 'Audi','Quatro','Black',car_type_seq.nextval,'2002');
-
- insert into car_type values (
- 'Audi','Quatro','Gold',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Blue',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Green',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','White',car_type_seq.nextval,'2002');
-
- insert into car_type values (
- 'Toyota','Camry','Red',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','Silver',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Black',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Gold',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Blue',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Green',car_type_seq.nextval,'2002');
-
- insert into car_type values (
- 'Toyota','Camry','White',car_type_seq.nextval,'2002');
-
- insert into car_type values (
- 'Toyota','Camry','Red',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','Silver',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Black',car_type_seq.nextval,'2002');
- commit;
- insert into car_type values (
- 'Audi','Quatro','Gold',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Blue',car_type_seq.nextval,'2002');
-
- insert into car_type values (
- 'Audi','Quatro','Green',car_type_seq.nextval,'2002');
-
- insert into car_type values (
- 'Toyota','Camry','White',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','Red',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','Silver',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Black',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Gold',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Blue',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Green',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','White',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','Red',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','Silver',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Black',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Gold',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Blue',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Green',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','White',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','Red',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','Silver',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Black',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Gold',car_type_seq.nextval,'2002');
- commit;
- insert into car_type values (
- 'Audi','Quatro','Blue',car_type_seq.nextval,'2002');
-
- insert into car_type values (
- 'Audi','Quatro','Green',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','White',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','Red',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Toyota','Camry','Silver',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Black',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Gold',car_type_seq.nextval,'2002');
-
- insert into car_type values (
- 'Audi','Quatro','Blue',car_type_seq.nextval,'2002');
- insert into car_type values (
- 'Audi','Quatro','Green',car_type_seq.nextval,'2002');
- commit;
- ========================================
- ========================================
- The result will be occasional deadlock errors:
- insert into car_type values (
- *
- ERROR at line 1:
- ORA-00060: deadlock detected while waiting for resource
- The trace file will show the tell-tale 'No Row' message:
- Rows waited on:
- Session 11: no row
- Session 10: no row
|
|
发表于 2012-2-17 11:28:00
|
查看: 8607|
回复: 14
|手机版|Archiver|Oracle数据库数据恢复、性能优化
IP卡
狗仔卡
分享
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
显身卡
