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

To evaluate these programs and their behavior in a multiuser environment, I opted to use Statspack to gather the
metrics, as follows:

1. Execute a Statspack snapshot to gather the current state of the system.

2. Run N copies of the program, having each program INSERT into its own database table so as to

avoid the contention associated with having all programs trying to insert into a single table.

3. Take another snapshot immediately after the last copy of the program finishes.

Then it is a simple matter of printing out the Statspack report and finding out how long it took N copies of the
program to complete, how much CPU was used, what major wait events occurred, and so on.

■ Why not use aWr (automatic Workload repository) to perform this analysis? the answer to that is because
everyone has access to statspack, everyone. It might have to be installed by your DBa, but every oracle customer has
access to it. I want to present results that are reproducible by everyone.

Note

These tests were performed on a dual-CPU machine with hyperthreading enabled (making it appear as if there
were four CPUs). Given that there were two physical CPUs, you might expect very linear scaling here—that is, if one
user uses 1 unit of CPU to process her inserts, then you might expect that two users would require 2 units of CPU.
You'll discover that this premise, while sounding plausible, may well be inaccurate (just how inaccurate depends
on your programming technique, as you'll see). It would be correct if the processing we were performing needed no
shared resource, but our process will use a shared resource, namely the shared pool. We need to latch the shared pool
to parse SQL statements, and we need to latch the shared pool because it is a shared data structure, and we cannot
modify it while others are reading it and we cannot read it while it is being modified.

■ I've performed these tests using Java, pL/sQL, pro*C, and other languages. the end results are very much the
same every time. this demonstration and discussion applies to all languages and all interfaces to the database. I chose
Java for this example as I find Java and Visual Basic applications are most likely to not use bind variables when working
with the oracle database.

Note

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