An Oracle blog about Oracle Cloud
-
Database Performance
April 30, 2013
Parse and Parse again.
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.
swing out sister - windmills of your mind
Hello,
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:
Replace:
<statement>
<share-prepared-statements>false</share-prepared-statements>
</statement>
by
<statement>
<share-prepared-statements>true</share-prepared-statements>
<prepared-statement-cache-size>100</prepared-statement-cache-size>
</statement>
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.