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 ten-user test without bind variables took 130 CPU seconds, whereas with using bind variables it took only 4 CPU seconds! The interesting observation is that ten users using bind variables (and very few latch requests as a result) use about the same amount of hardware resources (CPU) as one user that does not use bind variables (i.e., that overuse a latch or process more than they need to). It’s quite dramatic when you examine the results for ten users.
You see that nonuse of bind variables uses orders of magnitude more hardware resources than when compared with the use of bind variables. The more users are added over time, the longer each user spends waiting for these latches. However, the bind variable implementation avoided overuse of the latch suffered no ill effects as it scaled up.
As a developer, the results of this test really drive home the impact of using bind variables. Nonuse of bind variables in your code can cripple application performance. As a DBA, you need to be aware of how to spot the nonuse of bind variables and work with application teams to ensure bind variables are always used.
Mutexes
A mutex is a serialization device much like a latch is; in fact, the name mutex stands for mutual exclusion. It is another serialization tool used by the database. It is used in place of traditional latches in many places in the server.
A mutex differs from a latch in that it is even more lightweight in its implementation. It requires less code to implement, approximately one-fifth of the instructions (which results in less CPU to request in general), and it requires less memory, approximately one-seventh of the size, to implement.
A mutex, in addition to being lighter weight, is a little less functional in some respects. Just like an enqueue lock is much heavier than a latch, a latch is heavier than a mutex. But, like the enqueue to latch comparison, the latch can do more than a mutex in some cases (like an enqueue can do more than a latch in some cases).
This means that not every latch will be, or should be, replaced by a mutex, just as every enqueue lock will not be, or should not be, replaced by a latch.
When reading about mutexes in various reports, just remember that they are lighter- weight serialization devices. They enable possibly more scalability than a latch (just as latches are more scalable than enqueues), but they are still a serialization device. If you can avoid doing something that requires a mutex, in general, you should, for the same reason you would avoid requesting a latch if possible.
Leave a Reply