Now I’d like to look at the same situation as presented in the previous section, but this time using a program that uses significantly less latches during its processing. We’ll take that PL/SQL program and code it using bind variables.
To accomplish this, we’ll simply replace the hard-coded variable with a bind variable:
declare begin for i in 1 .. 25000 loop
beginexecute immediate’insert into t1 values(:i)’ using i; exceptionwhen no_data_found then null;end;end loop;end;/
All of the code for this test is placed in a file called bind.sql (you can download this code from the GitHub website), and run it as follows:
$ sqlplus eoda/foo@PDB1
SQL> @bind.sql 1
Let’s look at the single- and dual-user Statspack reports, as we did for the no bind variable example. We’ll see dramatic differences here. Here is the single-user report:
Elapsed: 0.03 (mins) Av Act Sess: 0.5
DB time: 0.02 (mins) DB CPU: 0.02 (mins)
Load Profile Per Second Per Transaction Per Exec Per Call
The difference between no binding and binding is quite dramatic! We went from 11 CPU seconds in the no bind variable example to 1 CPU second here.
From 1796 hard parses per second to about 1.5 per second (and based on my knowledge of how Statspack works, most of those were from running Statspack).
Even the elapsed time was dramatically reduced from about 0.23 minutes (14 seconds) down to 0.03 minutes (2 seconds).
When not using bind variables, we spent much of our CPU time parsing SQL. This was not entirely latch related, as much of the CPU time incurred without bind variables was spent parsing and optimizing the SQL.
Parsing SQL is very CPU intensive, but to expend most of our CPU doing something (parsing) that doesn’t really do useful work for us—work we didn’t need to perform—is pretty expensive.
When we get to the two-user test, the results continue to look much better for the test using bind variables:
sqlplus eoda/foo@PDB1
SQL> @bind.sql 2
Elapsed: 0.03 (mins) Av Act Sess: 0.7
DB time: 0.02 (mins) DB CPU: 0.02 (mins)
The amount of CPU time is about the same as reported by the single-user test case.
Note Due to rounding, the one CPU second is really anywhere from zero to two, and the three is really anywhere from two to four seconds.
Further, the amount of CPU used by two users with bind variables is far less than half the amount of CPU a single user not using bind variables required!
When I looked at the latch report in this Statspack report, I found there was so little contention for the shared pool and library cache that it was not even worth reporting.
In fact, digging deeper turned up the fact that the shared pool latch doesn’t even register shared pool requests, whereas the no-bind two-user test logged well over 2.2 million requests.
Leave a Reply