Soft Parses

I’ve done a little research to figure out what a “soft parse” is because I’m working on a system that is experiencing performance issues doing soft parses.  My own translation is that a “soft parse” occurs whenever the Oracle optimizer has to examine the text of a SQL statement.  A “hard parse” is just a soft parse plus the creation of a new execution plan for the parsed SQL text.

The concepts manual describes a soft parse as the conversion of the SQL text into an internal data structure – presumably something tree like.  But what wasn’t clear to me was that even if a SQL statement has been run before and has its plan cached it still has to be parsed and the resulting data structure is used prior to looking up the pre-existing plan in the shared pool.  So, since we are having performance issues with soft parses how can we avoid them?  It seems like every time we run a SQL query you will have a parse of some type.  It turns out that in some cases the text of the query can be parsed once producing a cursor and then that cursor can be executed multiple times bypassing parsing altogether.

I built a test case to demonstrate soft and hard parses and cursors.

I used the following query to show the number of parses and executions of the test query.

select sn.name,ms.value
from V$MYSTAT ms,V$STATNAME sn
where
ms.STATISTIC#=sn.STATISTIC# and
(name like  '%parse%' or name = 'execute count');

First I run the query against dual 1000 times and it produces 1000 soft parses and executes:

SQL> CREATE OR REPLACE PROCEDURE testparse2(num_executions number)
       is
  2       dummy_var        VARCHAR2(1);
  3    BEGIN
  4  
  5       FOR i IN 1..num_executions LOOP
  6  
  7    -- run query normally
  8         SELECT dummy into dummy_var from dual;
  9  
 10       END LOOP;
 11  
 12    END;
 13  /

NAME                    VALUE
-----------------------------
parse count (total)        19
parse count (hard)          2
execute count              17

SQL> execute testparse2(1000);

NAME                    VALUE
-----------------------------
parse count (total)      1022
parse count (hard)          2
execute count            1020

Next I used DBMS_SQL to parse the SQL query once and execute it 1000 times producing 1000 executions.

SQL> CREATE OR REPLACE PROCEDURE testparse(num_executions number)
       is
  2       dummy_var        VARCHAR2(1);
  3       test_cursor      INTEGER;
  4       rows_fetched     NUMBER;
  5       ignore           INTEGER;
  6    BEGIN
  7  
  8    -- Prepare a cursor to select from the source table:
  9       test_cursor := dbms_sql.open_cursor;
 10       DBMS_SQL.PARSE(test_cursor,
 11            'SELECT dummy from dual',
 12            DBMS_SQL.NATIVE);
 13       DBMS_SQL.DEFINE_COLUMN(test_cursor, 1, dummy_var,1);
 14  
 15       FOR i IN 1..num_executions LOOP
 16  
 17    -- execute
 18         ignore := 
              DBMS_SQL.EXECUTE(test_cursor);
 19         rows_fetched := 
              DBMS_SQL.FETCH_ROWS(test_cursor);
 20         DBMS_SQL.COLUMN_VALUE(test_cursor, 1,
              dummy_var);
 21  
 22       END LOOP;
 23  
 24  
 25    -- close cursor
 26  
 27       DBMS_SQL.CLOSE_CURSOR(test_cursor);
 28    END;
 29  /

NAME                  VALUE
---------------------------
parse count (total)      19
parse count (hard)        2
execute count            17

SQL> execute testparse(1000);

NAME                   VALUE
----------------------------
parse count (total)       23
parse count (hard)         2
execute count           1020

FYI – I’ve edited the output a bit to make this more readable.  See the testcase for the exact output.  To make these results come out so cleanly I had to turn off session cursor caching with this command:

alter session set session_cached_cursors=0;

I turned this back on setting  session_cached_cursors=50 and the original loop worked almost as well as the one with DBMS_SQL:

NAME                           VALUE
------------------------------------
parse count (total)               19
parse count (hard)                 2
execute count                     17

SQL> execute testparse2(1000);

NAME                           VALUE
------------------------------------
parse count (total)               22
parse count (hard)                 2
execute count                   1020

So, the session cursor caching must work like my DBMS_SQL example in that it saves the cursor from the first parse of a SQL statement and executes it multiple times.  Lastly, just to show what I already know, that hard parses come from new SQL text that isn’t cached in any way I ran the following test with the cursor caching on:

SQL> CREATE OR REPLACE PROCEDURE testparse3(num_executions number)
       is
  2       dummy_var        VARCHAR2(80);
  3       query            varchar2(80);
  4    BEGIN
  5  
  6       FOR i IN 1..num_executions LOOP
  7        query := 'SELECT dummy||''a'||to_char(i)||''' from dual';
  8    -- add a unique table alias to force hard parse with each
  9    -- loop
 10        execute immediate query into dummy_var;
 11  
 12       END LOOP;
 13  
 14    END;
 15  /

NAME                            VALUE
-------------------------------------
parse count (total)                66
parse count (hard)                  2
execute count                      88

SQL> execute testparse3(1000);

NAME                            VALUE
-------------------------------------
parse count (total)              1069
parse count (hard)               1003
execute count                    1091

There are 1000 hard parses.  But, I can’t run this test script twice because the next time the hard parses disappear;

NAME                   VALUE
----------------------------
parse time cpu             1
parse time elapsed         5
parse count (total)     1022
parse count (hard)         2
parse count (failures)     0
parse count (describe)     0
execute count           1020

So, what’s the point of all this?  In my case we know a production system is spending a lot of time in soft parses waiting on library cache locks.  So, the problematic queries have been run before so they aren’t hard parses, but they aren’t cached in the session cursor cache.  It remains to be seen if this can help us track down the source of the problem but at least it helps explain where the time is spent.

– Bobby

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.