هدف این سوال جنبه آموزشی آن است.
buffer busy wait و وقایع مرتبط می تواند عملکرد insert های همزمان را فلج کند. این مساله در یک single instance database بد است و در یک RAC بسیار بدتر . اغلب دلیل این مشکل ناشی از تولید primary key از sequence می باشد و معکوس سازی شاخص می تواند این مشکل را حل کند.
رقابت برای بلوک شاخص هنگام وارد کردن داده افزایش می یابد که می تواند یک برنامه را به طور کامل قطع کند . دلیل این است که شاخص b-tree بر روی یک کلید به صورت یکنواخت افزایش می یابد حتی اگر هرگز وجود نداشته باشد قفلی بر روی سطر تمام کلید های درج شده قرار خواهند گرفت در همان بلوک در حاشیه ی شاخص .
شاخص کلید معکوس این مساله را حل خواهد کرد. اگر شما شاخص گذاری کنید , برای مثال , 19- 20-21 همه ی این سه کلید در یک بلوک شاخص قرار خواهند گرفت . در عوض شما می بایستی به عنوان مثال 91-2-12 شاخص گذاری کنید . بنابراین مقادیر متوالی در شاخص در مجاورت هم نخواهند بود . آنها در تمام عرض شاخص توزیع خواهند شد. شما می توانید این را به صورت برنامه نویسی انجام دهید . اما اوراکل معکوس سازی کلید شاخص را دقیقا برای همین منظور تدارک دیده است.
به این مثال توجه کنید:
connect / as sysdba
drop user jw cascade;
grant dba to jw identified by jw;
conn jw/jw
create table t1 (c1 number);
create index normal_i on t1(c1);
create table t2 (c1 number);
create index reverse_i on t2(c1) reverse;
create sequence s1 cache 100000 noorder;
create or replace procedure ins_normal(n number) as begin
for i in 1..n loop
insert into t1 values(s1.nextval);
end loop;
commit;
end;
/
create or replace procedure ins_reverse(n number) as begin
for i in 1..n loop
insert into t2 values(s1.nextval);
end loop;
commit;
end;
/
اسکیما jw اکنون دو جدول دارد , یکی شاخص گذاری با نرمال b-tree و دیگری معکوس کلید b-tree , و یک sequence برای تولید کلید ها و دو پروسیجر برای وارد کردن سطرها.
اکنون یک آزمایش همزمان را ترتیب بدید , با استفاده از windows shell script :
copy con i.sql
exec &1
exit
^Z
copy con concurrent_inserts.bat
sqlplus jw/jw @i.sql %1
^Z
for /l %i in (1,1,100) do start /b concurrent_inserts.bat ins_normal(10000)
for /l %i in (1,1,100) do start /b concurrent_inserts.bat ins_reverse(10000)
اسکریپت I.SQL هیچ کاری بیش از اجرای یک پروسیجر نمی کند . batch فایل concurrent-insert.bat برنامه sql*plus را اجرا خواهد کرد و فراخوانی اسکریپت I.SQL همراه با آرگومان command line که نام پروسیجر را می فرستد .
برای اجرای آزمایش , حلقه for فراخوانی می کند فایل batch را به صورت همزمان در صد session پس زمینه , هر یک ده هزار insert را انجام می دهد.
نتیجه ی buffer busy wait را با هم ببینیم :
orclz>
orclz> select object_name,value from v$segment_statistics
2 where owner='JW' and object_type='INDEX' and statistic_name='buffer busy waits';
OBJECT_NAME VALUE
------------------------------ ----------
NORMAL_I 161347
REVERSE_I 8983
orclz>
معکوس کلید شاخص کاهش داده bufffer busy wait نزدیک به 95% .
شگفت زده شدید ؟! منم امیدوارم همین طور باشد .
این مهم تر از محیط RAC , جایی که buffer busy wait به صورت عمومی تعریف شده است .
من نمی گویم که تمام شاخص ها بایستی معکوس باشند . شما نیاز دارید بشناسید داده تان را و اینکه چگونه مورد دسترسی قرار خواهد گرفت. در مثال پایین trace را مشاهده می کنید . ابتدا insert برای normal index و سپس reverse index.
********************************************************************************
SQL ID: 1bmscqsn9h63b Plan Hash: 3884345238
INSERT INTO T1
VALUES
(S1.NEXTVAL)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 100 0.00 0.00 0 0 0 0
Execute 1000000 134.68 5421.65 1208 16471886 5103248 1000000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1000100 134.68 5421.66 1208 16471886 5103248 1000000
Misses in library cache during parse: 0
Optimizer mode: FIRST_ROWS
Parsing user id: 128 (recursive depth: 1)
Number of plan statistics captured: 100
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL (cr=62 pr=0 pw=0 time=14931 us)
1 1 1 SEQUENCE S1 (cr=0 pr=0 pw=0 time=38 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 316 0.31 4.67
db file sequential read 1264 0.37 3.22
buffer busy waits 513840 3.20 2198.53
latch free 2582 0.03 1.63
library cache: mutex X 2035 0.09 25.87
enq: TX - index contention 182675 0.13 900.54
enq: SQ - contention 128 0.00 0.43
buffer deadlock 105187 0.11 1163.93
latch: cache buffers chains 3039 0.01 0.79
enq: TX - allocate ITL entry 101 0.04 0.42
latch: enqueue hash chains 133585 0.09 922.55
resmgr:cpu quantum 57296 0.00 7.89
cursor: pin S 136 0.06 1.20
read by other session 270 0.37 4.45
log file switch completion 65 0.11 2.96
latch: undo global data 53 0.00 0.00
enq: TX - contention 11 0.00 0.02
log file switch (checkpoint incomplete) 49 3.18 35.50
reliable message 100 0.01 0.09
control file sequential read 560 0.01 0.27
Data file init write 28 0.00 0.02
db file single write 28 0.00 0.00
control file parallel write 84 0.00 0.01
rdbms ipc reply 28 0.08 0.29
undo segment extension 1 0.02 0.02
enq: US - contention 14 0.00 0.02
enq: HW - contention 11 0.00 0.00
enq: FB - contention 3 0.00 0.00
latch: redo allocation 1 0.00 0.00
********************************************************************************
********************************************************************************
SQL ID: b00stmqya8601 Plan Hash: 3884345238
INSERT INTO T2
VALUES
(S1.NEXTVAL)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 100 0.00 0.00 0 0 0 0
Execute 1000000 61.89 948.89 2572 302421 4145886 1000000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1000100 61.89 948.89 2572 302421 4145886 1000000
Misses in library cache during parse: 0
Optimizer mode: FIRST_ROWS
Parsing user id: 128 (recursive depth: 1)
Number of plan statistics captured: 100
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL (cr=1 pr=1 pw=0 time=312356 us)
1 1 1 SEQUENCE S1 (cr=0 pr=0 pw=0 time=1120 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 304 0.25 15.28
db file sequential read 2638 0.43 12.93
read by other session 318 0.46 8.26
latch free 504 0.12 7.84
resmgr:cpu quantum 16738 0.21 196.22
enq: SQ - contention 112 0.07 3.03
library cache: mutex X 2892 0.16 73.54
buffer busy waits 13740 4.97 278.00
latch: redo allocation 48 0.07 0.48
reliable message 206 0.06 0.40
cursor: pin S 289 0.07 2.90
log file switch completion 154 0.25 12.49
latch: cache buffers chains 21 0.07 0.24
enq: US - contention 556 4.51 95.12
control file sequential read 660 0.06 1.63
Data file init write 36 0.12 0.22
db file single write 33 0.00 0.01
control file parallel write 99 0.00 0.03
rdbms ipc reply 33 0.71 1.56
enq: HW - contention 324 4.44 40.96
enq: CF - contention 6 0.08 0.21
enq: TX - index contention 974 0.66 46.81
buffer deadlock 538 0.11 13.31
latch: object queue header operation 4 0.02 0.08
log file switch (checkpoint incomplete) 50 1.43 44.59
enq: TX - contention 10 0.01 0.05
latch: enqueue hash chains 451 0.07 1.18
enq: TX - allocate ITL entry 60 0.20 2.29
undo segment extension 210 1.00 14.99
log buffer space 115 0.24 6.51
latch: redo copy 7 0.06 0.29
enq: FB - contention 3 0.01 0.01
latch: row cache objects 4 0.00 0.00
latch: undo global data 2 0.00 0.00
latch: checkpoint queue latch 1 0.00 0.00
********************************************************************************
All tests done on DB release 12.1.0.1