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 returns requests.

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

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.