if the index is on a sequence - rebuilding with a higher pctfree would simply make it such that the existing blocks that will never get another value (the left hand side of the index) go from about 90% full to about 60% full. It would make the index bigger.
The buffer busy waits are likely coming from the right hand side block, where the new inserts are happening. If that is the case (very likely), then the following extract from my forthcoming book might be of interest.
<quote>
Reverse Key Indexes
Another feature of a B*Tree index is the ability to “reverse” its keys. At first you might ask yourself, “Why would I want to do that?” B*Tree indexes were designed for a specific environment and for a specific issue. They were implemented to reduce contention for index leaf blocks in “right-hand-side” indexes, such as indexes on columns populated by a sequence value or a timestamp, in an Oracle RAC environment.
Note We discussed RAC in Chapter 2.
RAC is a configuration of Oracle in which multiple instances can mount and open the same database. If two instances need to modify the same block of data simultaneously, they will share the block by passing it back and forth over a hardware interconnect, a private network connection between the two (or more) machines. If you have a primary key index on a column populated from a sequence (a very popular implementation), everyone will be trying to modify the one block that is currently the left block on the right-hand side of the index structure as they insert new values (see Figure 11-1, which shows that higher values in the index go to the right, and lower values go to the left). Modifications to indexes on columns populated by sequences are focused on a small set of leaf blocks. Reversing the keys of the index allows insertions to be distributed across all the leaf keys in the index, though it could tend to make the index much less efficiently packed.
Note You may also find reverse key indexes useful as a method to reduce contention, even in a single instance of Oracle. Again, you will mainly use them to alleviate buffer busy waits on the right-hand side of a busy index, as described in this section.
Before we look at how to measure the impact of a reverse key index, letÂ’s discuss what a reverse key index physically does. A reverse key index simply reverses the bytes of each column in an index key. If we consider the numbers 90101, 90102, and 90103, and look at their internal representation using the Oracle DUMP function, we will find they are represented as follows:
ops$tkyte@ORA10GR1> select 90101, dump(90101,16) from dual
2 union all
3 select 90102, dump(90102,16) from dual
4 union all
5 select 90103, dump(90103,16) from dual
6 /
90101 DUMP(90101,16)
---------- ---------------------
90101 Typ=2 Len=4: c3,a,2,2
90102 Typ=2 Len=4: c3,a,2,3
90103 Typ=2 Len=4: c3,a,2,4
Each one is 4 bytes in length and only the last byte is different. These numbers would end up right next to each other in an index structure. If we reverse their bytes, however, Oracle will insert the following:
ops$tkyte@ORA10GR1> select 90101, dump(reverse(90101),16) from dual
2 union all
3 select 90102, dump(reverse(90102),16) from dual
4 union all
5 select 90103, dump(reverse(90103),16) from dual
6 /
90101 DUMP(REVERSE(90101),1
---------- ---------------------
90101 Typ=2 Len=4: 2,2,a,c3
90102 Typ=2 Len=4: 3,2,a,c3
90103 Typ=2 Len=4: 4,2,a,c3
Note REVERSE is an undocumented function and, as such, should be used carefully. I do not recommend using REVERSE in “real” code, as its undocumented nature implies that it is not supported.
The numbers will end up “far away” from each other. This reduces the number of RAC instances going after the same block (the rightmost block) and reduces the number of block transfers between RAC instances. One of the drawbacks to a reverse key index is that you cannot use it in all cases where a regular index can be applied. For example, in answering the following predicate, a reverse key index on X would not be useful:
where x > 5
The data in the index is not sorted by X before it is stored, but rather by REVERSE(X), hence the range scan for X > 5 will not be able to use the index. On the other hand, some range scans can be done on a reverse key index. If I have a concatenated index on (X, Y), the following predicate will be able to make use of the reverse key index and will “range scan” it:
where x = 5
This is because the bytes for X are reversed, and then the bytes for Y are reversed. Oracle does not reverse the bytes of (X || Y), but rather stores (REVERSE(X) || REVERSE(Y)). This means all of the values for X = 5 will be stored together, so Oracle can range scan that index to find them all.
Now, assuming you have a surrogate primary key on a table populated via a sequence, and you do not need to use range scanning on this index—that is, you don’t need to query for MAX(primary_key), MIN(primary_key), WHERE primary_key < 100, and so on—then you could consider a reverse key index in high insert scenarios even in a single instance of Oracle. I set up two different tests, one in a pure PL/SQL environment and one using Pro*C to demonstrate the differences between inserting into a table with a reverse key index on the primary key and one with a conventional index. In both cases, the table used was created with the following DDL (we will avoid contention on table blocks by using ASSM so we can isolate the contention on the index blocks):
create table t tablespace assm
as
select 0 id, a.*
from all_objects a
where 1=0;
alter table t
add constraint t_pk
primary key (id)
using index (create index t_pk on t(id) &indexType tablespace assm);
create sequence s cache 1000;
whereby &indexType was replaced with either the keyword REVERSE, creating a reverse key index, or with nothing, thus using a “regular” index. The PL/SQL that would be run by 1, 2, 5, 10, or 15 users concurrently was
create or replace procedure do_sql
as
begin
for x in ( select rownum r, all_objects.* from all_objects )
loop
insert into t
( id, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED,
LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY )
values
( s.nextval, x.OWNER, x.OBJECT_NAME, x.SUBOBJECT_NAME,
x.OBJECT_ID, x.DATA_OBJECT_ID, x.OBJECT_TYPE, x.CREATED,
x.LAST_DDL_TIME, x.TIMESTAMP, x.STATUS, x.TEMPORARY,
x.GENERATED, x.SECONDARY );
if ( mod(x.r,100) = 0 )
then
commit;
end if;
end loop;
commit;
end;
/
Now, since we discussed the PL/SQL commit time optimization in Chapter 9, I wanted to run a test that was using a different environment as well, so as to not be misled by this commit time optimization. I used Pro*C to emulate a data warehouse extract, transform, load (ETL) routine that processed rows in batches of 100 at a time between commits:
exec sql declare c cursor for select * from all_objects;
exec sql open c;
exec sql whenever notfound do break;
for(;;)
{
exec sql
fetch c into :owner:owner_i,
:object_name:object_name_i, :subobject_name:subobject_name_i,
:object_id:object_id_i, :data_object_id:data_object_id_i,
:object_type:object_type_i, :created:created_i,
:last_ddl_time:last_ddl_time_i, :timestamp:timestamp_i,
:status:status_i, :temporary:temporary_i,
:generated:generated_i, :secondary:secondary_i;
exec sql
insert into t
( id, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED,
LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY )
values
( s.nextval, :owner:owner_i, :object_name:object_name_i,
:subobject_name:subobject_name_i, :object_id:object_id_i,
:data_object_id:data_object_id_i, :object_type:object_type_i,
:created:created_i, :last_ddl_time:last_ddl_time_i,
:timestamp:timestamp_i, :status:status_i,
:temporary:temporary_i, :generated:generated_i,
:secondary:secondary_i );
if ( (++cnt%100) == 0 )
{
exec sql commit;
}
}
exec sql whenever notfound continue;
exec sql commit;
exec sql close c;
The Pro*C was precompiled with a PREFETCH of 100, making this C code analogous to the PL/SQL code in Oracle 10g.
Note In Oracle 10g Release 1 and above, a simple FOR X IN ( SELECT * FROM T ) in PL/SQL will silently array fetch 100 rows at a time, whereas in Oracle9i and before, it fetches just a single row at a time. Therefore, if you want to reproduce this example on Oracle9i and before, you will need to modify the PL/SQL code to also array fetch with the BULK COLLECT syntax.
Both would fetch 100 rows at a time and then single row insert the data into another table. The following tables summarize the differences between the various runs, starting with the single user test in Table 11-1.
Table 11-1. Performance Test for Use of Reverse Key Indexes with PL/SQL and Pro*C: Single User
Reverse No Reverse Reverse No Reverse
PL/SQL PL/SQL Pro*C Pro*C
Transaction/second 38.24 43.45 17.35 19.08
CPU time (seconds) 25 22 33 31
Buffer busy waits number/time 0/0 0/0 0/0 0/0
Elapsed time (minutes) 0.42 0.37 0.92 0.83
Log file sync number/time 6/0 1,940/7 1,940/7
From the first single-user test, we can see that PL/SQL was measurably more efficient than Pro*C in performing this operation, a trend weÂ’ll continue to see as we scale up the user load. Part of the reason Pro*C wonÂ’t scale as well as PL/SQL will be the log file sync waits that Pro*C must wait for, but which PL/SQL has an optimization to avoid.
It would appear from this single-user test that reverse key indexes consume more CPU. This makes sense because the database must perform extra work as it carefully reverses the bytes in the key. But, weÂ’ll see that this logic wonÂ’t hold true as we scale up the users. As we introduce contention, the overhead of the reverse key index will completely disappear. In fact, even by the time we get the two-user test, the overhead is mostly offset by the contention on the right hand side of the index, as shown in Table 11-2.
Table 11-2. Performance test for use of reverse key indexes with PL/SQL and Pro*C: 2 users
Reverse No Reverse Reverse No Reverse
PL/SQL PL/SQL Pro*C Pro*C
Transaction/second 46.59 49.03 20.07 20.29
CPU time (seconds) 77 73 104 101
Buffer busy waits number/time 4,267/2 133,644/2 3,286/0 23,688/1
Elapsed time (minutes) 0.68 0.65 1.58 1.57
Log file sync number/time 19/0 18/0 3,273/29 2,132/29
As you can see from this two-user test, PL/SQL still outperforms Pro*C, but the use of the reverse key index is showing some positive benefits on the PL/SQL side and not so much on the Pro*C side. That too is a trend that will continue. The reverse key index is solving the buffer busy wait problem we have due to the contention for the rightmost block in the index structure; however, it does nothing for the log file sync waits that affect the Pro*C program. This was the main reason for performing both a PL/SQL and a Pro*C test: to see the differences between these two environments. This begs the question, why would a reverse key index apparently benefit PL/SQL but not Pro*C in this case? It comes down to the log file sync wait event. PL/SQL was able to continuously insert and rarely had to wait for the log file sync wait event upon commit, whereas Pro*C was waiting every 100 rows. Therefore, PL/SQL in this case was impacted more heavily by buffer busy waits than Pro*C was. Alleviating the buffer busy waits in the PL/SQL case allowed it to process more transactions, and so the reverse key index positively benefited PL/SQL. But in the Pro*C case, the buffer busy waits were not the issue—they were not the major performance bottleneck, so removing the waits had no impact on overall performance.
LetÂ’s move on to the five-user test, shown in Table 11-3.
Table 11-3. Performance test for use of reverse key indexes with PL/SQL and Pro*C: 5 users
Reverse No Reverse Reverse No Reverse
PL/SQL PL/SQL Pro*C Pro*C
Transaction/second 43.84 39.78 19.22 18.15
CPU time (seconds) 389 395 561 588
Buffer busy waits number/time 19,259/45 221,353/153 19,118/9 157,967/56
Elapsed time (minutes) 1.82 2.00 4.13 4.38
Log file sync number/time 691/14 6,655/73 5,391/82
We see more of the same. PL/SQL, running full steam ahead with few log file sync waits, was very much impacted by the buffer busy waits. With a conventional index and all five users attempting to insert into the right-hand side of the index structure, PL/SQL suffered the most from the buffer busy waits and therefore benefited the most when they were reduced.
Now, taking a look at the ten-user test in Table 11-4, we can see the trend continues.
Table 11-4. Performance test for use of reverse key indexes with PL/SQL and Pro*C: 10 users
Reverse No Reverse Reverse No Reverse
PL/SQL PL/SQL Pro*C Pro*C
Transaction/second 45.90 35.38 17.88 16.05
CPU time (seconds) 781 789 1,256 1,384
Buffer busy waits number/time 26,846/279 456,231/1,382 25,871/134 364,556/1,702
Elapsed time (minutes) 3.47 4.50 8.90 9.92
Log file sync number/time 2,602/72 11,032/196 12,653/141
PL/SQL, in the absence of the log file sync wait, is very much helped by removing the buffer busy wait events. Pro*C is experiencing more buffer busy wait contention now but, due to the fact it is waiting on log file sync events frequently, is not benefiting. One way to improve the performance of the PL/SQL implementation with a regular index would be to introduce a small wait. That would reduce the contention on the right-hand side of the index and increase overall performance. For space reasons, I will not include the 15- and 20-user tests here, but I will confirm that the trend observed in this section continued.
We can take away two things from this demonstration. A reverse key index can help alleviate a buffer busy wait situation, but depending on other factors you will get varying returns on investment. In looking at Table 11-4 for the ten-user test, the removal of buffer busy waits (the most waited for wait event in that case) affected transaction throughput marginally, but it did show increased scalability with higher concurrency levels. Doing the same thing for PL/SQL had a markedly different impact on performance: we achieved a measurable increase in throughput by removing that bottleneck.