Last week, I was in Frankfurt (Germany) teaching about Performance Tuning & Partitioning in a Private Event for LHS Telecommunication. One demonstration that I developed for that event was about Reducing Buffer Busy Waits. One major reason why the wait event Buffer Busy Waits can occur is if many sessions are inserting at the same time in the same table. Large OLTP installations therefore will see it most likely. This can lead to a performance problem, especially if the site does not make use of Automatic Segment Space Management – a technique introduced in 9i and used by default since 10g. The old fashioned way to determine where to insert a new row in blocks before the high water mark uses Freelists. So if you are going with that „traditional“ technique with freelists (and PCTUSED), then your segment has one freelist by default, which leads to contention easy, because multiple simultaneous sessions that insert look at that one freelist and all pick the same Blocks to insert in. Here is an example:
SQL> create tablespace old datafile '/u01/app/oracle/oradata/orcl/old01.dbf'
size 50m segment space management manual;
Tablespace created.
SQL> create table bbw_freelist (n number, name varchar2(50)) tablespace old;
Table created.
That table now uses (one) freelist and PCTUSED (40 by default) in order to determine what blocks may be used
for insert. The next steps are going to create 10 simultaneous sessions that insert 1 Million rows into the table.
SQL> create or replace procedure bufwait1 as begin for i in 1..100000 loop insert into bbw_freelist values (i, 'BUFFER BUSY WAITS?'); end loop; commit; end; / 2 3 4 5 6 7 8 Procedure created. SQL> create or replace procedure do_bbw1 as v_jobno number:=0;begin FOR i in 1..10 LOOP dbms_job.submit(v_jobno,'bufwait1;', sysdate); END LOOP; commit; end; / 2 3 4 5 6 7 8 Procedure created.SQL> exec do_bbw1 PL/SQL procedure successfully completed.SQL> select count(*) from dba_jobs_running
COUNT(*)
----------
0
One easy way to discover Buffer Busy Waits is v$segment_statistics.Of course, you may see them also in Statspack/AWR reports.
SQL> select sum(value) from v$segment_statistics where OBJECT_NAME='BBW_FREELIST' and statistic_name='buffer busy waits'; SUM(VALUE) ---------- 1623Next we are doing exactly the same inserts with a table that uses Automatic Segment Space Management.
Simply speaking, ASSM achieves a better distribution of the new rows across multiple blocks if multiple
inserts occur at the same time. It is determined at the tablespace level:
SQL> select SEGMENT_SPACE_MANAGEMENT from dba_tablespaces where tablespace_name='USERS'; SEGMEN ------ AUTO SQL> create table bbw_assm (n number, name varchar2(50)) tablespace users; Table created. create or replace procedure bufwait2 as begin for i in 1..100000 loop insert into bbw_assm values (i, 'BUFFER BUSY WAITS?'); end loop; commit; end; / create or replace procedure do_bbw2 as v_jobno number:=0;begin FOR i in 1..10 LOOP dbms_job.submit(v_jobno,'bufwait2;', sysdate); END LOOP; commit; end; / SQL> exec do_bbw2 PL/SQL procedure successfully completed. SQL> select count(*) from dba_jobs_running; COUNT(*) ---------- 10 SQL> select count(*) from dba_jobs_running; COUNT(*) ---------- 0 SQL> select sum(value) from v$segment_statistics where OBJECT_NAME='BBW_ASSM' and statistic_name='buffer busy waits'; SUM(VALUE) ---------- 295As you can see, ASSM reduced strongly the number of Buffer Busy Waits! But still there are some,
and still this may be a performance problem for very large OLTP sites. Right now, the table is
not partitioned, so we have only one „hot extent“ if the table is continually growing, all inserts
occur in the newest allocated extent – where they are distributed nicely via ASSM. If that same table
would be Hash Partitioned, then we could have multiple „hot extents“ further more reducing the contention:
SQL> create table bbw_hash (n number, name varchar2(50))tablespace users partition by hash (n) partitions 256; Table created. SQL> create or replace procedure bufwait3 as begin for i in 1..100000 loop insert into bbw_hash values (i, 'BUFFER BUSY WAITS?'); end loop; commit; end; / 2 3 4 5 6 7 8 Procedure created. SQL> create or replace procedure do_bbw3 as v_jobno number:=0;begin FOR i in 1..10 LOOP dbms_job.submit(v_jobno,'bufwait3;', sysdate); END LOOP; commit; end; / 2 3 4 5 6 7 8 Procedure created. SQL> exec do_bbw3 PL/SQL procedure successfully completed. SQL> select sum(value) from v$segment_statistics where OBJECT_NAME='BBW_HASH' and statistic_name='buffer busy waits'; SUM(VALUE) ---------- 226We observe a (in this case) moderate further decrease of the Buffer Busy Waits. That
is one major reason for hash partitioning: Fighting against contention. Another reason
is – as in other kinds of partitioning – the possibilty for Partiton Pruning, if the
partition key is part of the where-clause of our statement. Then the optimizer
implicitly knows what partitions to scan. Even in my (relatively tiny) two tables, that
effect is visible:
SQL> set timing on SQL> select * from bbw_assm where n=4711; N NAME ---------- -------------------------------------------------- 4711 BUFFER BUSY WAITS? 4711 BUFFER BUSY WAITS? 4711 BUFFER BUSY WAITS? 4711 BUFFER BUSY WAITS? 4711 BUFFER BUSY WAITS? 4711 BUFFER BUSY WAITS? 4711 BUFFER BUSY WAITS? 4711 BUFFER BUSY WAITS? 4711 BUFFER BUSY WAITS? 4711 BUFFER BUSY WAITS? 10 rows selected. Elapsed: 00:00:00.11 SQL> select * from bbw_hash where n=4711; N NAME ---------- -------------------------------------------------- 4711 BUFFER BUSY WAITS? 4711 BUFFER BUSY WAITS? 4711 BUFFER BUSY WAITS? 4711 BUFFER BUSY WAITS? 4711 BUFFER BUSY WAITS? 4711 BUFFER BUSY WAITS? 4711 BUFFER BUSY WAITS? 4711 BUFFER BUSY WAITS? 4711 BUFFER BUSY WAITS? 4711 BUFFER BUSY WAITS? 10 rows selected. Elapsed: 00:00:00.01
#1 von coskan am Juni 3, 2009 - 15:46
Again very clean and easy to understand explanation.
It might be good if you add the missing job execution step to mssm part
Thank you
#2 von Uwe Hesse am Juni 3, 2009 - 19:40
Oops, I forgot to copy & paste the part
exec do_bbw1
in the last version of the posting. Thank you, Coskan, for spotting this!
#3 von Md. Zafar Ahsan am März 29, 2010 - 14:57
Hi,
It is pleasing to see your practical approach to create the problem and its solution.
I am on oracle version 10.1.0.2.0.
dbms_job.submit just submits the job and so it only shows in dba_jobs but not in dba_jobs_running. So do we need to call the dbms_job.run explicitly.
Becuase when i
run the — exec do_bbw3 it just shows the jobs in dba_jobs but they are not running. Or is there any other parameter to set so that when
exec dbms_job.submit , it would start running the jobs in parallel.
Regards,
zafar
#4 von Uwe Hesse am März 29, 2010 - 15:43
Zafar,
I have no 10.1 release at hand to check, but the example should work the same way there. Maybe your parameter JOB_QUEUE_PROCESSES is set to zero?
#5 von Md. Zafar Ahsan am März 31, 2010 - 10:38
Thanks hesse,
After Setting the job_queue_processes parameter i donot need to exec dbms_jobs.run procedure.
I really appriciate this practical approach to create the problem at will. Because it gives the best understanding and control and helps in practical life.
Regards,
zafar
#6 von Uwe Hesse am März 31, 2010 - 10:59
Zafar,
you’re welcome 🙂
Thank you for the nice feedback – I am glad that the posting was helpful for you.