-
Manual Locking and User-Defined Locks- Locking and Latching
So far, we have looked mostly at locks that Oracle places for us transparently. When we update a table, Oracle places a TM lock on it to prevent other sessions from dropping that table (or performing most DDL, in fact). We have TX locks that are left on the various blocks we modify so others…
-
With Bind Variables- Locking and Latching
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…
-
Setting Up for the Test- Locking and Latching
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,…
-
Performance/Scalability Comparison- Locking and Latching
Table 6-1 summarizes the CPU usage by each implementation, as well as the latching results as we increase the number of users beyond two. As you can see, the solution using fewer latches (binds) will scale much better as the user load goes up. Table 6-1. CPU Usage Comparison with and Without Bind Variables The…
-
Without Bind Variables- Locking and Latching
In the first instance, our PL/SQL code will not use bind variables, but rather will use string concatenation to insert data: To automate this, the prior code was placed in a text file named nb.sql. Then the next bit of SQL automatically generates a shell script to call the prior PL/SQL as well as to…
