When an application issues a statement, Oracle first sees whether a parsed version of the
statement already exists. If it does, the result is a so-called soft parse and is considered a library cache hit.
If, during a parse phase or the execution phase, Oracle isn’t able to find the parsed version or the executable version of the code in the shared pool, it will perform a hard parse, which means that the SQL statement has to be reloaded into the shared pool and parsed completely.
During a hard parse, Oracle performs syntactic and semantic checking, checks the object and system privileges, builds the optimal execution plan, and finally loads it into the library cache. A hard parse involves a lot more CPU usage and is inefficient compared to a soft parse, which depends on reusing previously parsed statements. Hard parsing involves building all parse information from scratch, and therefore it’s more resource intensive. Besides involving a higher CPU usage, hard parsing involves a large number of latch gets, which may increase the response time of the query. The ideal situation is where you parse once and execute many times. Otherwise, Oracle has to perform a hard parse.
A soft parse simply involves checking the library cache for an identical statement and reusing it.
The major step of optimizing the SQL statement is completely omitted during a soft parse. There’s no parsing (as done during a hard parse) during a soft parse, because the new statement is hashed and its hash value is compared with the hash values of similar statements in the library cache. During a soft parse, Oracle only checks for the necessary privileges. For example, even if there’s an identical statement in the library cache, your statement may not be executed if Oracle determines during the (soft) parsing stage that you don’t have the necessary privileges. Oracle recommends that you treat a hard parse rate of more than 100 per second as excessive.
Measuring Library Cache Efficiency
You can use simple ratios to see if your library cache is sized correctly. The V$LIBRARYCACHE data dictionary view provides you with all the information you need to see whether the library cache is efficiently sized. Listing 20-1 shows the structure of the V$LIBRARYCACHE view.
Comments