Figure 7-12. Shown is an example of severe library cache contention when mutexes are enabled. The same exact load and reporting interval was used as in Figure 7-11.

As was shown in Figure 7-9, Oracle is very particular about what it considers a similar SQL statement. Every statement must be parsed, and if the cursor is not found in the library cache, the cursor must be completely built (a hard parse). Hard parsing requires library cache-related latches and locks, so if hard parsing becomes so intense the related wait events are driven to the top of our reports, we will look for ways to create similar SQL statements. Oracle provides two powerful methods to do this.

The first method is to simply use bind variables instead of literals. For example, the statement select * from employee where emp_no=100 uses a literal. If the statement select * from employee where emp_no=200 was then issued, because of Oracle's hashing algorithm, the two statements would have different hash values, reside in different buckets (probably), and have different handles. As you can imagine, during intensive online transaction activity, this will result in a tremendous amount of hard parsing. If the application developers can submit the SQL statement as select * from employee where emp_no=:b1 along with employee number, the cursor will not contain the employee number, and the cursor is highly likely to be reused (since regardless of the employee number, the same cursor will be reused). This dramatically reduces hard parsing.