Using hash clusters to solve buffer busy wait problems

Concurrent inserts into a table will often result in crippling buffer busy wait problems as sessions serialize on access to the last block(s) of the table segment. Using hash clusters can remove the issue.

Buffer busy waits can be a dreadful problem that arises with increased scale. An application may perform well single user or in test conditions, but when it goes live with a greater number of concurrent sessions, the whole database can lock up on this wait event. Hash clusters may be one solution. Here's a demonstration.

First, this script will create the database objects for the test:

connect / as sysdba
drop user jw cascade;
grant dba to jw identified by jw;
conn jw/jw

create cluster onetab (keycol number(6,0)) hashkeys 100000 hash is keycol single table;
select blocks from user_segments where segment_name='ONETAB';
create sequence s1 cache 100000 noorder;
create table hashtab(keycol number(6,0), othercol number) cluster onetab (keycol);
create table heaptab(keycol number(6,0), othercol number);

create or replace procedure inshash(n number) as begin
for i in 1..n loop
insert into hashtab values(s1.nextval,1);
end loop;
commit;
end;
/

create or replace procedure insheap(n number) as begin
for i in 1..n loop
insert into heaptab values(s1.nextval,1);
end loop;
commit;
end;
/

The schema JW now has a cluster with (by default)one block of space pre-allocated for each of 100,000 hash keys. Of course this requires tuning: you do need to have some idea of how many rows you will have for each key and how big they will be, and how many distinct keys you expect. If you get this wildly wrong, or choose a key that will not distribute the rows evenly, the hash cluster will still work but its efficiency will degrade. Then create a sequence to generate the hash keys and two tables: HASHTAB is within the cluster, HEAPTAB is not. Finally, two procedures to insert rows.
Then set up and run the concurrency test, using Windows operating system facilities:

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 inshash(1000)
for /l %i in (1,1,100) do start /b concurrent_inserts.bat insheap(1000)

The SQL*Plus script I.SQL does nothing more than execute a procedure. The batch file CONCURRENT_INSERTS.BAT will launch SQL*Plus, calling the script I.SQL with a command line argument that will pass the name of the procedure.
To run the test, the FOR loops call the batch file concurrently in a hundred background sessions, performing a thousand inserts each.

What is the result for buffer busy wait? Here it is:

orclz>
orclz> select object_name,value from v$segment_statistics where owner='JW' and statistic_name='buffer busy waits';

OBJECT_NAME                         VALUE
------------------------------ ----------
HEAPTAB                              3629
HASHTAB                                 0


orclz>

The clustered table has reduced buffer busy wait to zero compared to the heap table. Of course this example is contrived (there will never be a collision in the hash table) but the general point is clear: because of the manner in which concurrent inserts can be distributed throughout the entire segment, a hash clustered table does not suffer from the concurrency issues of heap tables.

But.... all is not perfect. Enabling trace for the sessions shows the full story. First, a trace of the insert into the hash table:

********************************************************************************

SQL ID: 82wy6prt9shrp Plan Hash: 3884345238

INSERT INTO HASHTAB 
VALUES
(S1.NEXTVAL,1)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      100      0.00       0.00          0          2          0           0
Execute 100000     13.75     455.23      83251        117     104109      100000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   100100     13.75     455.24      83251        119     104109      100000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 164     (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=0 pr=1 pw=0 time=157275 us)
         1          1          1   SEQUENCE  S1 (cr=0 pr=0 pw=0 time=58 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                      178        1.01         32.37
  db file sequential read                     83251        0.21        251.02
  latch: shared pool                              2        0.01          0.01
  cursor: pin S                                  65        0.03          0.18
  library cache: mutex X                       1031        0.48         50.01
  resmgr:cpu quantum                          35228        0.03         44.65
  latch free                                     94        0.00          0.16
  free buffer waits                            5588        0.02         49.47
  buffer busy waits                              13        0.92          6.61
  latch: object queue header operation           28        0.00          0.00
  write complete waits                            4        0.61          1.05
  latch: enqueue hash chains                      8        0.00          0.00
  latch: checkpoint queue latch                   1        0.02          0.02
  latch: cache buffers chains                     2        0.00          0.00
  enq: HW - contention                           13        0.93          5.76
********************************************************************************

and this is the trace of an insert into the heap table:

********************************************************************************

SQL ID: c92x55n0wnd9v Plan Hash: 3884345238

INSERT INTO HEAPTAB 
VALUES
(S1.NEXTVAL,1)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      100      0.03       0.02          0         26          0           0
Execute 100000      4.53      14.54       1087       2535     106027      100000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   100100      4.56      14.56       1087       2561     106027      100000

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 164     (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=3 pr=1 pw=0 time=10826 us)
         1          1          1   SEQUENCE  S1 (cr=0 pr=0 pw=0 time=30 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                      1087        0.05          0.91
  Disk file operations I/O                       85        0.28          3.39
  buffer busy waits                            2978        0.14          1.28
  library cache: mutex X                        309        0.06          2.20
  cursor: pin S                                  49        0.00          0.09
  latch free                                     61        0.00          0.00
  latch: enqueue hash chains                     35        0.00          0.00
  enq: HW - contention                           60        0.01          0.14
  resmgr:cpu quantum                           1449        0.01          0.72
  enq: TX - contention                           35        0.00          0.16
  enq: FB - contention                           28        0.09          0.52
  latch: cache buffers chains                     9        0.00          0.00
  latch: redo allocation                          2        0.00          0.00
********************************************************************************

So the hash table does not suffer from buffer busy wait, but I/O related wait events are far worse. Why? Because distributing the work across many blocks loses the benefit of caching blocks for re-use. So, remove one problem and another arises that must be fixed. Swings and roundabouts. As usual.

(All tests done on DB release 12.1.0.1)
--
John Watson
Oracle Certified Master DBA
http://skillbuiders.com