Lock Types - Oracle Database Transactions and Locking Revealed - page 45

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.

Without Bind Variables
In the first instance, our program will not use bind variables, but rather will use string concatenation to insert data
(you will obviously have to use your own connect string for your system):

import java.sql.*;
public class instest
{
static public void main(String args[]) throws Exception
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection
conn = DriverManager.getConnection
("jdbc:oracle:thin:@heesta:1521:ORA12CR1","scott","tiger");
conn.setAutoCommit( false );
Statement stmt = conn.createStatement();
for( int i = 0; i < 25000; i++ )
{
stmt.execute
("insert into "+ args[0] +
" (x) values(" + i + ")" );
}
conn.commit();
conn.close();
}
}