Database Reference
In-Depth Information
Table 3-1.
CPU Usage Comparison with and Without Bind Variables
Users
CPU (sec)/Elapsed Time (min.)
Shared Pool Latch Requests
Latch Wait Count/Latch Wait Time (sec.)
No Binds
Binds
No Binds
Binds
No Binds*
Binds
1
11/0.22
1/0.04
0
0
0/0
0/0
2
23/0.44
3/0.07
>2.2 million
>50 thousand
15.0k/0
0/0
3
35/0.65
4/0.10
>3.5 million
>75 thousand
19.1k/1
0/0
4
47/0.89
4/0.13
>4.7 million
>95 thousand
33.9k/1
0/0
5
58/1.13
4/0.15
>5.9 million
>126 thousand
55.8k/3
10/0
6
70/1.35
6/0.18
>7.1 million
>152 thousand
61.1k/3
22/0
7
82/1.56
7/0.21
>8.3 million
>176 thousand
70.0k/3
23/0
8
90/2.55
7/0.23
>9.5 million
>201 thousand
121.3k/42
28/0
9
105/2.21
8/0.27
>10.7 million
>226 thousand
111.6k/13
25/0
10
115/2.49
8/0.28
>11.9 million
>252 thousand
123.1k/17
41/0
*Note: Latch wait count is in thousands for No Binds, but not for Binds.
The interesting observation is that 10 users using bind variables (and very few latch requests as a result) use the
same amount of hardware resources (CPU) as 1 user that does not use bind variables (i.e., that overuse a latch or
process more than they need to). When you examine the results for 10 users, you see that nonuse of bind variables
results in the use of over 14 times the CPU and takes almost 9 times the execution time when compared to the bind
variable solution. The more users are added over time, the longer each user spends waiting for these latches. We went
from an average of 0.6 seconds/session (3 seconds of wait/5 sessions) of wait time for latches with 5 users to an
average of 1.7 seconds/session of wait time with 10 users. However, the implementation that avoided overuse of the
latch suffered no ill effects as it scaled up.
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 was introduced in Oracle 10
g
Release 1 and 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.
Search WWH ::
Custom Search