As an example, we’ll study the cost of latching the shared pool. We’ll compare a well- written program (one that uses bind variables) and a program that is not so well written (it uses literal SQL or unique SQL for each statement). To do this, we’ll use a very small PL/SQL program that simply logs into Oracle and executes 25,000 unique INSERT statements in a loop. We’ll perform two sets of tests: our PL/SQL program will not use bind variables in the first set, and in the second set, it will.
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.
Note 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.
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 one unit of CPU to process their inserts, then you might expect that two users would require two 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.
Note 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.
Leave a Reply