Connecting to a Database

The first step in accessing an Oracle database from your application is to establish a connection to the
database. This connection is the path that acts as the path from your client application to a shadow process
in the Oracle database that handles the SQL requests from your application.

Normally, a connection goes from a client machine over a network to a server machine. The connection is
implemented on top of a network protocol, such as TCP/IP. The connection request is actually received by
the Oracle Listener, which listens on a port for connection traffic. Once the connection is established, the
Listener passes requests to the shadow process associated with the connection.


If you are using Java as your program language, the connection will be executed with a driver, which is
software designed to handle the complexities of communication over the network.

A connection to an Oracle database is always created on behalf of a user. Consequently, establishing a
connection requires identifying and authenticating that user with a username and password.

A connection to an Oracle database is referred to as a session. The session is the overriding context for all
SQL interactions that take place. When a connection is either terminated or is lost for any other reason,
the context for that session, including any information in any uncommitted transactions within that session,
is lost.

Every session is supported by a shadow process on the Oracle server. Normally, this means that every
session has its own process. But, as you can imagine, each shadow process uses some server memory
resources, so the scalability of an individual Oracle instance might be limited by the number of sessions
it can support. To address this issue, Oracle has a feature known as shared servers, which was referred to
as multithreaded servers, or MTS, before Oracle9i.
When a connectioncomes into the Listener, it passes the request to a dispatcher. The dispatcher assigns the request to a session that can be shared. Once the request is completed, the session becomes available to service other requests.

Use of shared servers is completely transparent to your application. A single Oracle instance can use a
combination of shared servers and dedicated servers—the instance has different connection identifiers
for a dedicated session or a shared session.

When should you use a shared server? As with most issues involving performance, the answer depends
on the particulars of your particular implementation.

If you are running out of memory because of a large number of connections, shared servers can help to
address the problem by lowering the overall amount of memory required for sessions. Obviously, some
overhead is also involved with the use of a dispatcher and the functions it performs. This overhead can
be balanced against the more limited use of resources required by the shared server architecture.
Typically, a shared server can do the most good for applications that require periodic access to the database,
since the connections used by these applications will not be performing work much of the time, and as such
they are candidates to be shared with other sessions. Abrowsing application would fit this description, while
a heads-down transaction processing application would not. The good news is that you can switch between
using shared sessions and dedicated sessions by simply changing the connection parameters, so it is fairly
easy to test the effect of shared sessions on your overall application performance.
You have to establish a connection to your Oracle database instance before you can send any SQL to the
instance, but you do not necessarily have to create a connection each time you want to use one. You can
reuse connections inside of an application, or a portion of an application, or you can use connection pooling
to maintain a set of connections that a user can grab when he or she needs it. Using a connection manager
with Java