Database Reference
In-Depth Information
Setting Up for the Test
In order to test, we'll need a schema (set of tables) to work with. We'll be testing with multiple users and want to
measure the contention due to latching most of all, meaning that we're not interested in measuring the contention
you might observe due to multiple sessions inserting into the same database table. So, we'll want a table per user to be
created and we'll name these tables
T1
. . .
T10
. For example:
SCOTT@ORA12CR1> connect scott/tiger
Connected.
SCOTT@ORA12CR1> begin
2 for i in 1 .. 10
3 loop
4 for x in (select * from user_tables where table_name = 'T'||i )
5 loop
6 execute immediate 'drop table ' || x.table_name;
7 end loop;
8 execute immediate 'create table t' || i || ' ( x int )';
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed.
We'll run this script before each iteration of the test to follow in order to reset our schema and to force hard
parsing to take place if we run a test more than once. During our testing, we'll follow these steps:
1.
Run
statspack.snap
.
2.
Immediately start N of our Java routines, where N will vary from 1 to 10, representing 1 to
10 concurrent users.
3.
Wait for all N to complete.
4.
Run
statspack.snap
.
5.
Generate the Statspack report for the last two Statspack IDs.
The numbers presented for the following test runs were collected using this technique.
■
the scripts to automate the test described in this section are available for download from the apress web site for
this topic. in the
ch06
folder there are two subdirectories:
nobinds
and
binds
. Within those directories the
run.sql
script calls the required code to execute this test. You'll need to modify the code to reflect your database connection
information, where appropriate. and, of course, you'll also have to compile the Java program on your server.
Tip