Oracle SQL*Net Parse Execute
Oracle Tips by Burleson Consulting
The Data Warehouse Development Life Cycle
The Internals Of Oracle's SQL*Net
On the client side, the User Programmatic Interface (UPI)
converts SQL to associated PARSE, EXECUTE, and FETCH statements. The
UPI parses the SQL, opens the SQL cursor, binds the client
application, describes the contents of returned data fields,
executes the SQL, fetches the rows, and closes the cursor. Oracle
attempts to minimize messages to the server by combining UPI calls
whenever possible. On the server side, the Oracle Programmatic
Interface (OPI) responds to all possible messages from the UPI and
No UPI exists for server-to-server communication. Instead, a Network
Programmatic Interface (NPI) resides at the initiating server, and
the responding server uses its OPI.
SQL*Net supports network transparency such that the network
structure may be changed without affecting the SQL*Net application.
Location transparency is achieved with database links and synonyms.
Let's trace a sample data request through SQL*Net. Essentially,
SQL*Net will look for the link name in the database link table (Remote DBA_DB_LINKS)
and extract the service name. The service name is then located in
the tnsnames.ora file, and the host name is extracted. Once again,
we have a two-stage process beginning with the link name referencing
the service name, then the service name referencing the host name.
In Unix environments, the host name is found in a host file
(etc/hosts), and the Internet Protocol (IP) address is gathered. In
the following example, london_unix might translate into an IP
address of 184.108.40.206. The following four steps illustrate how
SQL*Net takes a remote request and translates it into the IP address
of a destination database.
This is an excerpt from "High Performance
Data Warehousing", copyright 1997.
like Oracle tuning, you may enjoy the book
Oracle Tuning: The Definitive
Reference , with
over 900 pages of BC's favorite tuning tips &
You can buy it
directly from the publisher and save 30%, and get
instant access to the code depot of Oracle tuning