The correct use of connection probe statements in an application in an Oracle database

The correct use of connection probe statements in an application in an Oracle database

This article is an original article. Reprint Please specify source: http://blog.csdn.net/msdnchina/article/details/38513765

In very many programs inside. The connection probe (or connection detection) statement, are used by the select sysdate from dual;
This is the typical "just to function, no matter how the performance" of the wording. In fact, the connection of the detector statement. Should use select 1 from dual;
Perhaps some people say that the program apes are very hard, can achieve the function is good. But. What I want to say is: Swap with select 1 from dual; There is no need to turn exist into a multi-table connection. So it has nothing to do with business logic.

It's a matter of no effort.

The following is a look at select sysdate from dual; As a measure of the harm of a connected probe:

1. The parse burden of adding a database:

This can be seen from the AWR report "SQL ordered by Parse Calls". (based on 10.2.0.5 below)

Parse Calls          Executions   % total parses     SQL Id        sql Module         SQL Text +              15%          Aqswdefrgthy  JDBC Thin Client   select sysdate from dual;

But in MOS article: Troubleshooting: Shared pool optimization and library Cache latch conflict optimization (DOC ID 1523934.1) have descriptions such as the following:

Example 2: The following statement does not use a bound variable but is not considered to be literal SQL. Because this statement can be run multiple times for sharing.  SELECT sysdate from dual;

My question is: Since said can be shared run, why the Parse calls the number of times as much as executions? Has the select Sysdate from dual been improved on the 11g?
Looking back can find a 11g environment to test.

2. Add the processing burden of the database server (i.e.: OS) for the following reasons:

The Sysdate and Systimestamp function simply performs a system-call to the Operating system to get the time (a "gettimeofd Ay "call).

Excerpt from: How to Add A Day, Hour, Minute and/or Second to A Date Value? (Doc ID 1419615.1)

The correct use of connection probe statements in an application in an Oracle database