Correct use of connection detection statements in an application in an Oracle database

Correct use of connection detection statements in an application in an Oracle database

This article is original article, reprint please indicate source: http://blog.csdn.net/msdnchina/article/details/38513765

In many programs, connection detection (or connection detection) statements are used by select Sysdate from dual;
This is typical of "as long as the functionality is implemented, regardless of performance". In fact, the connection detection statement, should use the Select 1 from dual;
Some people may say that programmers are very hard, and it is good to realize the function. However, I would like to say: Swap with select 1 from dual, do not need to change the exist into a multi-table connection, so it is not related to business logic. It's a matter of no effort.

Let's talk about select Sysdate from dual; As a connection detection statement, the harm:

1. Add the parse burden to the database:

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

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

But in MOS article: Troubleshooting: Shared pool optimization and library Cache latch conflict optimization (DOC ID 1523934.1) has the following description:

Example 2: The following statement is not bound to a variable but is not considered literal SQL because the statement can be shared multiple times.  SELECT sysdate from dual;

My question is: Since said can be shared execution, 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 be a 11g environment test.

2. Increase the processing burden on the database server (ie: 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)