In the oracle database, the connection test statements in the application are correctly used.

In the oracle database, the connection test statements in the application are correctly used.
Correct use of connection detection statements in applications in oracle databases

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

In many programs, connection detection (or connection detection) Statements use select sysdate from dual;
This is a typical "as long as the function is implemented, no matter what the performance is. In fact, the connection test statement should use select 1 from dual;
Some people may say that programmers are very hard to implement functions. However, what I want to say is: in the form of select 1 from dual;, there is no need to change exist to multi-table join, so it has nothing to do with the business logic. This is an easy task.

The following describes the harm of select sysdate from dual; as a connection test statement:

1. Increase the database's parse burden:

This can be seen from "SQL ordered by Parse CILS" reported by awr. (The following is based on 10.2.0.5)

Parse Calls          Executions   % Total Parses     SQL Id        SQL Module         SQL Text 3000                 3000              15%          aqswdefrgthy  JDBC Thin Client   select sysdate from dual;

However, in the mos article: troubleshooting: Shared Pool optimization and Library Cache Latch conflict optimization (Doc ID 1523934.1), the following descriptions are provided:

Example 2: The following statement does not need to bind variables but is not considered literal SQL, because it can be shared by multiple executions. SELECT sysdate FROM dual;

My question is: Since the execution can be shared, why is the number of Parse cballs as high as that of Executions? Does the select sysdate from dual improve on the 11g?
Looking back, you can find a 11G environment for testing.

2. Increase the processing burden on 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 "gettimeofday" call).

From: How To Add A Day, Hour, Minute And/Or Second To A Date Value? (Docid 1419615.1)


Oracle database, using the select where Statement of SQL

What is the READ command used? Send it to the code.

What should I do if I connect to the ORACLE database remotely?

Install the oracle client on your computer, search for the tnsnames. ora file in the directory of the oracle client that you installed, and add:
YOUR_ORACLE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.200) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = TEST)
)
)
If your database SID is TEST, change (SERVICE_NAME = TEST) to (SID = TEST)
If you use SQLPLUS for connection, write it as sqlplus tzm/777 @ your_oracle
If you use TOAD or another tool to connect to the database: