What Is a Parse Call?

There are two key concepts that need to be kept separate when we speak of parsing—one is the action of parsing, the other is a program subroutine called a parse call. It may come as a surprise, but

• a parse call may not happen in your code when you think it ought to
• a parse call does not necessarily result in any optimization taking place
• a parse call does not necessarily result in any parsing taking place
• parsing and optimization can occur on an execute call, not just on a parse call

If we go back to our simple loop program and check the session statistics for the two variants of the SQL statement we have constructed (see core_dc_activitiy_03.sql in the code library), we will see the following results about parse calls:

Parse count (total) is a count of the number of parse calls made to the Oracle kernel; and the second set of results shows that your code doesn’t always make a parse call when you might expect it to—the source code is only slight different from the literal string test, but the behavior is totally different. This is the effect of the PL/SQL cursor cache coming into play—the PL/SQL interpreter recognized that we were really re-executing a single statement inside a loop and used an internal mechanism to create a local cursor variable for our statement and held that cursor open—which means it didn’t issue a parse call every time round the loop, it simply issued an execute call after the first pass through the loop. This was an optimization that didn’t apply to the execute immediate mechanism until 10g, and if we run the test on Oracle 9i we can see the difference it has made to the statistics for the bind variable case, where we see a parse call on every cycle of the loop, as follows:

Parse count (hard) is a count of the number of times Oracle optimized the statement, and in the first example—where every statement was different—Oracle had to optimize every single one of the 1,000 statements we sent it because it had never seen them before. As we saw earlier on in the chapter a “hard” parse will also show up as a Miss in library cache during parse in the tkprof output. (The difference of 3 between total and hard is a little bit of noise introduced by the test mechanism itself.)

But in the third example Oracle doesn’t actually optimize the statement every time it arrives—it’s possible to issue a parse call (parse count(total)) that Oracle can handle without optimizing (parse count(hard)) the statement—and that’s where the library cache comes into the picture. The first time around the loop Oracle parses and optimizes the statement and loads it into the library cache; the second time around the loop Oracle searches the library cache for a match before starting the full work of parsing and optimizing the statement. If it finds the statement then it reuses it and your session doesn’t have to pay the cost of optimizing it again—so doesn’t need to record a hard parse.

Cursor Caching

We haven’t quite finished with the variation in the consequences of parse calls, though. To demonstrate this, the following is another version of the code loop (see core_dc_activity_04.sql in the code library) that uses explicit parse and execute calls from the dbms_sql package to make a point:

        for i in 1..1000 loop

                m_cursor := dbms_sql.open_cursor;
                dbms_sql.parse(
                        m_cursor,
                        'select n1 from t1 where id = :n',
                        dbms_sql.native
                );
                dbms_sql.define_column(m_cursor,1,m_n);

                dbms_sql.bind_variable(m_cursor, ':n', i);
                m_rows_processed := dbms_sql.execute(m_cursor);

                if dbms_sql.fetch_rows(m_cursor) > 0 then
                        dbms_sql.column_value(m_cursor, 1, m_n);
                end if;

                dbms_sql.close_cursor(m_cursor);

        end loop;

In this script I’ve captured the session statistics and the latch activity from v$latch, and I’ve run the above loop twice, making a critical change to the session environment between runs. On the second run I’ve reduced the parameter session_cache_cursors from its default value to zero with the command: alter session set session_cache_cursors=0; (the defaults are 20 in 10g and 50 in 11g). Tables 7-1 (session stats) and 7-2 (latch gets) show us the key consequences of this change.

At first sight, the session statistics seem to tell us that the work we’ve done on parse calls is the same in both cases, and we note particularly that even though we’ve included 1,000 explicit parse calls (dbms_sql.parse) in our code the number of hard parses is tiny—which means we’ve only optimized a handful of different statements, so we must be re-using the statement that appears in our loop. However, there is a difference in the way we are accessing the cursor for that statement, and this changes the amount of work involved and the scope for contention.

By default we have the session cursor cache enabled, which means that if we call a statement often enough Oracle (10g) will attach a KGL lock to that statement’s cursor to hold it open, and create a state object in our session memory that links to the cursor so that we have a short cut to the cursor and don’t need to search the library cache for it every time we use it. You will often see comments that cursor caching occurs on the third call to the statement—this isn’t quite correct; technically it’s on the call after cursor authentication.

If you run a statement that has not previously been run, and then repeat the call a few times, you will see the statistic cursor authentications increment on the second call, and the statistics session cursor cache hits and session cursor cache count increment on the fourth call—the sequence of events is as follows:

1. Optimize on first call
2. Authenticate on second call
3. Cache after third call completes
4. Use cache thereafter

This sequence of events is consistent with the commonly held view that you have to run a statement three times to cache it. However, if someone else has already run the statement, your first call to run the statement will be an authentication against an existing cursor, so the statement will go into your session cursor cache after the second call. It is the existence of the session cursor cache that means a parse call may result in no parsing at all.

One of the surprising things about the session cursor cache is the default size—it’s really rather small. There are probably a number of sites that could benefit from increasing the session_cache_cursor parameter, and it’s probably appropriate to do so if they keep seeing the statistic session cursor cache hits much smaller than the difference between parse count (total) and parse count (hard).

There is a great deal of overlap between the session cursor cache and the PL/SQL cache—in fact, since 9.2.0.5 the sizes of the two caches have both been set by the parameter session_cached_cursors (prior to that, the size of the PL/SQL cursor cache was set to match the value of parameter open_cursors). You might even ask, if you go back to the results of my second test loop (core_dc_activity_02.sql) how could I tell whether the SQL statement was being held in the PL/SQL cursor cache or the session cursor cache—the answer is that the number of session cursor cache hits for that test was zero, so the cursor must have been held in the PL/SQL cursor cache.

Holding Cursors

There is one more demonstration I can squeeze out of my test script, and that’s the effect of holding cursors. The Oracle pre-compilers allow you to generate code that holds cursors without having to do any special coding, simply by using a precompiler directive; but sometimes you have to code explicitly for held cursors. If you look at the code in the loop for core_dc_activity_04.sql you’ll notice that it has calls to open and close cursors inside the loop. This really isn’t necessary. If I know that I’m going to be reusing a statement fairly frequently I can declare a cursor variable with a much wider scope, and keep the cursor open for as long as I like; then all I have to do is re-execute the cursor whenever I want. The following is the modified code (see core_dc_activity_05.sql in the code library) to demonstrate this:

Running this code, the number of latch gets on the various library cache latches (in 10g) is in the hundreds, rather than the thousands we saw in the test where the open and close were inside the loop. As far as efficiency is concerned for high-frequency, commonly used, lightweight SQL statements, this strategy is the most cost-effective, and causes least risk of contention.

You do have to be a bit careful with this code strategy, though. I have come across two key errors where people have used it incorrectly—both, as it happens, only in Java environments. The first error appears when people do in Java the equivalent of my PL/SQL code with the open and close inside the loop—they adopt the strategy, but they have a class that opens and closes the cursor, but only executes the statement once; this is a large overhead with no offsetting benefits if you don’t execute the statement frequently before closing the cursor. (I believe the latest JDBC drivers can circumvent this issue by keeping the cursor open in a library code layer below the program code—in much the same way that the Oracle session uses the session cursor cache.)

The second error is to forget to close the cursor—possibly in an exception handler rather than in the program body— and then discard the class. As far as the Java program (or programmer) is concerned the cursor must be closed; as far as Oracle is concerned the session still has the cursor open, and the library cache cursor won’t close until the session ends. When this happens the program usually ends up crashing with Oracle error ORA-01000: maximum open cursors exceeded.

A few pages back I pointed out in a list of “surprises” that parsing and optimization can occur on an execute call, this is a side effect of holding cursors whether through explicit coding or because of the hidden PL/SQL optimizations. When you hold a cursor the user code is only handling a simple numeric variable—but internally this identifies various structures that, eventually, lead to a child cursor and its parent cursor in the library cache. If there is a demand from other sessions for free memory Oracle is allowed to clear almost everything about that child cursor from memory—even when it’s a held cursor—leaving only enough information for your session to be able to recreate the execution plan. When this happens, the next time you execute the statement you will record a Miss in library cache during execute and increment the parse count (hard) value even though you won’t increment the parse count (total) value. Counter-intuitively, it is possible to get see a value of parse count (hard) that is higher than the parse count (total).

To get a better idea of why, and how, cursor content can be flushed out of the library cache (even when held), it’s time to switch our attention to the library cache and the shared pool that it belongs to.