The Oracle Instructor

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)
----------
 1623

Next 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)
----------
 295

As 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)
----------
 226

We 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

I just came back from an Inhouse course in Basel (Switzerland) for the Novartis AG. It was about Partitioning in general and about some 11g New Features in this area. The present Novartis IT staff was very experienced (some of them worked with Oracle since version 5) and friendly – so we had a pleasant atmosphere to work in. I would like to share one of my demonstrations from this class with the Oracle Community.

One very useful feature about partitioning that is available since version 8 is the possibility of partition pruning. That means, that the optimizer during the parse phase of a SQL statement is able to exclude certain partitions from scanning because according to the definition of the partitioned table present in the Data Dictionary, the desired rows can’t be in those partitions.

When we for example have a table that is partitioned by range on a date column with one partition for each quarter of the year, it is implicitly clear for a query like

select * from t where time_id=to_date('01-01-2009','dd-mm-yyyy');

that the desired rows can only be in partition Q1. Without the presence of indexes, this query is round 4 times faster (because we have 4 assumed roughly equal filled partitions) than a Full Table Scan. An 11g New Feature is interval partitioning. Before 11g, you have to create a new partition on a range partitioned table manually, if you want to add a new range (for example a new quarter). Interval partitioning does that automatically.

The following demonstration shows the benefit of partition pruning & interval partitioning. I did it on my notebook with Oracle Enterprise Linux and 11.1.0.6 . The two tables with 20 Million rows need round 600 MB space and it took a couple of minutes (less than 15) to create them.

SQL> create table nonpart (id number, name varchar2(50), salary number);
begin
for i in 1..10000000 loop -- insert 10 Million rows
 insert into nonpart values (i, 'John Doe', 9000);
end loop;
commit;
end;
/
SQL> set timing on
SQL>select salary from nonpart where id=4711 -- FTS on 10 Million rows table;

 SALARY
----------
 9000

Elapsed: 00:00:04.13

Now we create the partitioned table with very little coding effort:

SQL> create table part
 (id number, name varchar2(50), salary number)
partition by range (id)
interval (10000) -- one partition each 10000 ids
(partition p1 values less than (10001));

The following insert creates 999 partitions automatically!

SQL> insert into part select * from nonpart nologging;
SQL> commit;

Due to partition pruning, even with this relatively tiny tables we see a dramatic speed up of the query:

SQL> select salary from part where id=4711 -- scan on 10.000 rows partition;

    SALARY
----------
      9000
Elapsed: 00:00:00.05

You can also tell from investigating the execution plan via set autotrace on or explain plan for that the above query made use of partition pruning, but I like to see (and show) that in this fashion a little more obviously