Monday Apr 29, 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.

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

About

I am Abel Macias email, an Oracle support engineer that specialized in Performance that belongs to Exadata Support.
Disclaimer This blog looks to broadcast my experiences while diagnosing performance problems on Oracle's RDBMS, mostly on Exadata.
The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today