An Oracle blog about Oracle Cloud

Parse and Parse again.

Abel Macias
Senior Principal Support Engineer

Since the first release of JDBC and event longer ago with Pro* Precompilers to this day I still see the following issue :

Please notice below in this abstract of a 30 min AWR the number of Parse Calls vs Executions, the SQL Module and in the case of the first SQL, the SQL Text too. 

Parse Calls Executions % Total Parses SQL Id SQL Module SQL Text
339,567 339,566 15.93 0mr7azgm9psws JDBC Thin Client SELECT COUNT(*) FROM DUAL
101,574 101,880 4.77 5uta56d6syjv3 JDBC Thin Client select TRIM(emp_fname || ' ' |...
46,800 46,799 2.20 6vw7kzhpb1dm3 JDBC Thin Client select "STATUS_ID" as "status...
35,509 35,509 1.67 648yjr5s1w7uc JDBC Thin Client SELECT "PROTOCOL_GROUP_ID" as...
31,292 32,540 1.47 0jvyqkxaytndr bvlab.exe select crf.CRF_LABEL from COMP...

Since the introduction of the shared pool in Oracle7 it was available the capability for clients to have a SQL Statement Cache in order to do the famous "Parse once, Execute Many" best practice. This feature is controlled on the RDBMS by the SESSION_CACHED_CURSORS parameter and this makes many people believe that the feature is managed at the RDBMS side alone but this far from the truth.

When this feature is not used then the client software parses the statement for each execution does not matter if the parameter is set in the database.
Many take this feature for granted because SQL*Plus uses it, so they assume it is true for other client software but it is a feature that has to be explicitly managed by the client software and works in conjunction with the RDBMS when the parameter is set.

In JDBC it is necessary to explicitly enable it and has an implicit setImplicitCachingEnabled and an explicit setExplicitCachingEnabled way to manage the cache

It is a little easier with the precompilers by setting are precompile time the HOLD_CURSOR and RELEASE_CURSOR parameters.

Take a moment to look at your own AWR or Statspack and verify your application is using the Client Statement Cache as intended.

Now the cherry of the sundae is the SQL "SELECT COUNT(*) FROM DUAL".

Over the years I've seen variations of queries over DUAL coming from JDBC in large quantities. I have been given the explanation from Customers and Developers that these queries are used as a heartbeat, to confirm the connection to the database is stil open.

I can understand that was necessary when jdbc and connection pooling was a young technology but isn't it anything better nowadays that will do the same thing without parsing and parsing the same useless statement without end ?

I would like to hear from people about what other techniques/features are used that can avoid the need to parse a bogus SQL to the database. 

swing out sister - windmills of your mind

Join the discussion

Comments ( 1 )
  • Sergio Wednesday, January 20, 2016


    Thank you a lot for this article, it help me a lot to decrease parse calls on DB.

    For those who use wildfly, it's very simple, just need to adapt datasources configuration in standalone.xml:










    I have checked the number of parse calls on DB before and after this change with the follow query:

    select sq.PARSING_SCHEMA_NAME, st.PARSE_CALLS, st.EXECUTIONS,round( 100*(1-( st.PARSE_CALLS / st.EXECUTIONS )),2) execute_to_parse, sq.SQL_TEXT

    from v$sqlstats st, v$sql sq

    where st.sql_id = sq.sql_id

    -- give a query id or find with sql text

    and sq.sql_text like 'select process0_.ID %where process0_.ID%'

    and st.EXECUTIONS <> 0

    order by execute_to_parse;

    Hope this can help other people with the same issue.

Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.