Database Reference
In-Depth Information
Table 11-2.
Performance Test for Use of Reverse Key Indexes with PL/SQL and Pro*C: 2 Users
Reverse PL/SQL
No Reverse PL/SQL
Reverse Pro*C
No Reverse Pro*C
Transaction/second
55.0
55.0
59.1
53.8
CPU time (seconds)
0.80
0.77
1.57
1.55
Buffer Busy Waits number/seconds
823/0
615/0
649/0
1,580/0
Elapsed Time (seconds)
0.79
0.75
6.99
6.90
Log File Sync number/seconds
3/0
3/0
1,229/19
1,227/26
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. For the Pro*C program, 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 PL/SQL
No Reverse PL/SQL
Reverse Pro*C
No Reverse Pro*C
Transaction/second
82.2
82.2
65.6
70.3
CPU time (seconds)
1.93
1.91
3.97
4.36
Buffer Busy Waits number/seconds
1,963/1
2,644/1
2,707/0
9,839/1
Elapsed Time (seconds)
5.26
5.59
22.14
22.17
Log File Sync number/seconds
6/0
6/0
3,061/138
3,202/128
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.
Taking a look at the ten-user test in Table
11-4
, we can see the trend continues.
