Parse and Parse again.
By amacias-Oracle on Apr 29, 2013
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.
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.