Database Reference
In-Depth Information
Table 11-4.
Performance Test for Use of Reverse Key Indexes with PL/SQL and Pro*C: 10 Users
Reverse PL/SQL
No Reverse PL/SQL
Reverse Pro*C
No Reverse Pro*C
Transaction/second
88.3
91.2
96.8
88.1
CPU time (seconds)
3.83
4.06
8.57
10.01
Buffer Busy Waits number/seconds
2,897/28
6,831/7
5,284/7
34,312/25
Elapsed Time (seconds)
25.6
26.04
116.62
124.70
Log File Sync number/seconds
11/0
119/0
6,051/301
6,441/352
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.
■
You can download the source code for the reverse key index performance example from
Apress.com
.
In the
Chapter 11 scripts folder, there are several
demo3*
files (as well as the
t.pc
file) that automate the running of this
entire test suite.
Tip
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.
Descending Indexes
Descending indexes were introduced in Oracle8
i
to extend the functionality of a B*Tree index. They allow for a column
to be stored sorted in descending order (from big to small) in the index instead of ascending order (from small to big).
Prior releases of Oracle (pre-Oracle8
i
) always supported the
DESC
(descending) keyword
syntactically
, but basically
ignored it—it had no effect on how the data was stored or used in the index. In Oracle8
i
and above, however, the
DESC
keyword changes the way the index is created and used.
Oracle has had the ability to read an index backward for quite a while, so you may be wondering why this feature
is relevant. For example, if we use a table
T
:
EODA@ORA12CR1> create table t
2 as
3 select *
4 from all_objects
5 /
Table created.
