Database Reference
In-Depth Information
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 10
g
. For example, say you have the prior Pro*C code stored in a file named
t.pc
, then the Pro*C compiler
command looks like this:
$ proc iname=t.pc MODE=ORACLE PREFETCH=100
■
In Oracle 10
g
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 Oracle9
i
and before, it fetches just a single row at a time. therefore, if you want to
reproduce this example on Oracle9
i
and before, you will need to modify the pL/sQL code to also array fetch with the
BULK COLLECT
syntax.
Note
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 Case
Reverse PL/SQL
No Reverse PL/SQL
Reverse Pro*C
No Reverse Pro*C
Transaction/second
41.5
41.5
42.2
45.5
CPU time (seconds)
0.38
0.37
0.73
0.69
Buffer Busy Waits number/seconds
0/0
0/0
0/0
0/0
Elapsed Time (seconds)
0.38
0.34
3.31
3.09
Log File Sync number/seconds
2/0
2/0
650/4
650/4
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 slightly 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
.
