Parse Calls

Here’s an extract from a report of activity in an Oracle session that I’ve just been running. Spot the anomaly:

Name                           Value
----                           -----
session cursor cache hits          3
parse count (total)                5
parse count (hard)                31
execute count                     35

There are no tricks involved with this output, though the database activity is a little unusual, and I haven’t faked any numbers. So how come I’ve got more “hard parses” than “parses” ?

I really hate the expression “hard parse”, I’m not keen on “soft parse”, and I get irritated by the frequent failure to distinguish the difference between “parse calls” and “parsing”.

The oddity of the statistics I’ve quoted can be explained very easily  – the ‘parse count (hard)’ should really be labelled ‘optimizations’, and optimisation may be required for an ‘execute call’ as well as for a ‘parse call’.

The ‘parse count (total)’ should really be labelled ‘parse calls’, and there are parse calls (the ones met through the session cursor cache) which don’t require any parsing (optimisation) to take place.

All I’ve done in my demonstration is run a small pl/sql block:

declare 
        m_n number; 
begin 
        for i in 1..30 loop 
                select  count(*) 
                into    m_n 
                from    t1; 

                dbms_lock.sleep(1); 
        end loop; 
end; 
/ 

But note the call to dbms_lock.sleep. As this block executed, I issued a truncate statement against table t1 once per second from another session.

Because I’m operating from a pl/sql block, the ‘select’ cursor is automatically held in the pl/sql cursor cache – so I don’t issue 30 ‘parse calls’ for this statement, I issue one ‘parse call’ and 30 execute calls.

But, because of the ongoing truncates from the other session, the execution plan I generated for the query on the ‘parse call’ (which parsed, optimised and held the cursor) was invalid on nearly every execution, so virtually every execute call I made had to optimise the query again. (If I showed you v$sql you would see that the child cursor had suffered about 30 invalidations and loads).

Summary:

parse count (total) – number of ‘parse calls’. If the statement has never been seen before, this will result in parsing and optimisation; if the statement has been seen before this will involve a search of the library cache (which is how you discover it’s been seen before) and may result in ‘cursor authentication’; if the statement has been seen and authenticated before and has a reference in the session cursor cache then the ‘parse call’ may do virtually nothing – because there isn’t even a need to search the library cache for the statement because it has been implicitly held.

parse count (hard) – number of optimisations that took place. Optimisation may be a consequence of a ‘parse call’ or an ‘execute call’.  Even a statement that is being held can have its plan invalidated or simply flushed from the library cache, which leads to optimisation on the next execution.

session cursor cache hits – number of times a ‘parse call’ has been virtually free because the statement has been (silently) held because the OCI layer has detected repeated use of the statement.

Addendum ( Jan 2008 )

There is another parse statistic that I haven’t mentioned. It doesn’t often figure highly in Statspack reports but, for completeness, I probably ought to mention it here as it can confuse the issue.

If you try run a query which has a parse error built into it (such as ‘select ssdate from dual’) then the optimizer will return a suitable Oracle error message (ORA-00904 in the given example).  The statistic ‘parse count (failures)’ will go up by one, as will the statistic ‘parse count (hard)‘.

Unfortunately ‘parse count (total)’ may not increase – it didn’t on my 9.2.0.8 system when I tried this test from SQL*Plus, although it did in 10.2.0.3 – so there’s another way of getting the statistics to show you more ‘hard parses’ than ‘parses’.

If you do see a lot of ‘parse count (failures)’, you will also see a corresponding number of waits for event ‘SQL*Net break/reset to client’ (or dblink, depending where the SQL is coming from).

Update Jan 2011

Randolf Geist has been looking at adaptive cursor sharing, and has noted that a parse call – including parse calls that go through the session cursor cache – seems to be the point in the code where adaptive cursor sharing can take place: in other words, it’s not an event that gets triggered or flagged by executions.