16.3 Connection Profiles

---

A profile is a definitional configuration placed onto a client machine or a server where parameters within the profile definition determine how a connection to that server machine will behave. For instance, on a Unix machine, each Unix user has a profile. Essentially this profile declares variables global to the user (local variables) and any default behavior required. Unix user profiles would contain variables such as path names. Following is a stripped-down example Unix profile for a Unix Oracle database administrator user on an Oracle8i Release 2 (8.1.7) database server.

#This .cshrc file is executed in tcsh (/usr/local/bin/tcsh)    on Solaris setenv ORACLE_BASE /<mp1>/oracle setenv ORACLE_HOME /<mp1>/oracle/product/8.1.7 setenv ORACLE_DOC /<mp1>/oracle/doc setenv LD_LIBRARY_PATH /<mp1>/oracle/product/8.1.7/lib:    /usr/openwin/lib setenv JAVA_HOME /<mp1>/oracle/product/jre/1.1.8 setenv TNS_ADMIN /<mp1>/oracle/product/8.1.7/network/admin setenv ORACLE_SID <SID> setenv ORACLE_DBF1 /<mp1>/oracle/oradata/$ORACLE_SID setenv ORACLE_DBF2 /<mp2>/oracle/oradata/$ORACLE_SID setenv ORACLE_BACKUPS /<mp2>/backups setenv ORACLE_SBIN /<mp1>/oracle/product/8.1.7/sbin setenv ORACLE_ALERT /<mp1>/oracle/admin/$ORACLE_SID/bdump setenv EDITOR vi     setenv PATH /bin:/usr/bin:/etc:/usr/ccs/bin:/usr/openwin/    bin:/usr/ucb setenv PATH ${PATH}:/usr/local/bin:/usr/sbin:/usr/X/bin:    $JAVA_HOME setenv PATH ${PATH}:/<mp1>/oracle/product/8.1.7/bin setenv PATH ${PATH}:/<mp1>/oracle/product/8.1.7/sbin     set host='hostname' alias sp 'set prompt="$user@$host:r":"$cwd>" alias cd 'cd \!*;sp' alias rm 'rm -i' alias ll 'ls -la' cd     alias dbs 'cd $ORACLE_HOME/dbs' alias bin 'cd $ORACLE_HOME' alias net8 'cd $TNS_ADMIN' alias pfile 'cd $ORACLE_BASE/admin/$ORACLE_SID/pfile' alias alert 'cd $ORACLE_ALERT' alias sbin 'cd $ORACLE_HOME/sbin' alias dbf1 'cd $ORACLE_BASE/oradata/$ORACLE_SID' alias dbf2 'cd /<mp2>/oracle/oradata/$ORACLE_SID' set filec set history=100 umask 077

The $ORACLE_HOME/network/admin/sqlnet.ora contains profile information for connections to an Oracle database server. In the same way that a Unix profile is instantiated when a Unix user logs into Unix, the sqlnet.ora file instantiates a profile into a session through SQL*Net. SQL*Net profile parameters are numerous and generally cover client logging, Oracle Names configuration, SQL*Net security authentication, SQL*Net tracing (switch all this off), and even one or two Connection Manager oriented values.

It appears that more recent versions of Oracle Database are using SQL*Net profile parameters on client machines more and more. Expect more usage of SQL*Net profile parameters in the future.

As far as tuning SQL*Net profile parameters is concerned there are three parameters of immediate interest to tuning.

• TCP.NODELAY=YES.   Large amounts of data passed through SQL*Net can have the buffer flushed to the network immediately. Otherwise, there may be a delay before information is passed over the network.

• USE_DEDICATED_SERVER=YES.   Force all connections to the database server from the current client machine to connect to dedicated server connections, adding (SERVER = DEDICATED) to name connection descriptions.

• SQLNET.EXPIRE_TIME=5.   Timeout on client SQL*Net connections, allows automatic termination of idle SQL*Net connections to the database server in order to help free up needed resources.

•  Oracle Database 10 Grid   Network Outage Detection.   Various new profile parameters can be configured to limit network time used for any particular operation.

Using these profile parameters will not really make much difference to overall performance unless directed at resolving a specific performance problem. What happens when ad hoc SQL code users go to lunch? Connections not executing COMMIT or ROLLBACK commands, if they are required, could possibly use timeout capable connections.

---