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
