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:
$ sqlplus eoda/foo@PDB1
SQL> beginfor i in 1 .. 10loopfor x in (select * from user_tables where table_name = ‘T’||i ) loop
execute immediate ‘drop table ‘ || x.table_name; end loop;execute immediate ‘create table t’ || i || ‘ ( x int )’; end loop;end;/
PL/SQL procedure successfully completed.
Note Statspack must be installed to run the examples in this section. Ensure that you review the material in the “Setting Up Your Environment” section of the Introduction of this book. It contains instructions for installing Statspack.
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 PL/SQL code, where N will vary from one to ten, representing one to ten 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.
Leave a Reply