حل مشکل buffer busy wait problems در oracle database - هفت خط کد انجمن پرسش و پاسخ برنامه نویسی

وبـــلاگ هــفت خــط کــد


آموزش های برنامه نویسی
۱۷۳ نفر آنلاین
۰ عضو و ۱۷۳ مهمان در سایت حاضرند

حل مشکل buffer busy wait problems در oracle database

+2 امتیاز
47 بازدید

چگونه با استفاده از reverse key index  مشکلات buffer busy wait را در oracle database حل کنیم؟

سوال شده شهریور 14, 1393  بوسیله ی k1 (امتیاز 231)   6

1 پاسخ

+4 امتیاز

هدف این سوال جنبه آموزشی آن است.

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

پاسخ داده شده شهریور 14, 1393 بوسیله ی k1 (امتیاز 231)   6
...