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

Source: Internet
Author: User

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

This article is an original article. Reprint Please specify source:

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 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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.