Oracle SQLNet Client Tracing

Enabling sql*net client tracing can be useful for tracking down issues such as disconnects. Server-side tracing (alter session set sql_trace=true) is usually easier to interpret and includes statement execution time. If there are connection problems, then client-side tracing is appropriate.

How to implement on unix:
$ cd $ORACLE_HOME/network/admin
$ cp sqlnet.ora .sqlnet.ora.previous
$ echo "trace_level_client=16" >> sqlnet.ora
$ echo "trace_unique_client=yes >> sqlnet.ora
$ echo "trace_file_client=sqlnet.trc >> sqlnet.ora
$ echo "trace_directory_client=/u001/oracle >> sqlnet.ora

Ensure the trace file directory is world writable.
The trace files will be named sqlnet_PID.trc, where PID is the unix process ID.
Trace level 16 will create a large log file.

Oracle Processes And Sessions

The processes configuration parameter sets the upper limit on the number of processes (including background processes). Each user session can use one or more processes.

Determine the current settings with the following queries.

select name, value from v$parameter where name in ('processes', 'sessions');

NAME
----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
processes
300

sessions
600

In this query, column SESSIONS_HIGHWATER is useful for determining if the running instance is close to the limit.

SQL> select * from v$license;

SESSIONS_MAX SESSIONS_WARNING SESSIONS_CURRENT SESSIONS_HIGHWATER USERS_MAX
------------ ---------------- ---------------- ------------------ ----------
0 0 161 246 0

Oracle Utility maxmem

maxmem is a utility that ships with the unix version of the Oracle database. It is useful for showing available free memory on the unix machine.

maxmem is in $ORACLE_HOME/bin, and takes no parameters.

$ uname -a
HP-UX host21 B.11.11 U 9000/800 1288319306 unlimited-user license

$ swapinfo -t
Kb Kb Kb PCT START/ Kb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 35651584 0 35651584 0% 0 - 1 /dev/vg21_SWAP/lvol1
reserve - 20312372 -20312372
memory 27341052 3836680 23504372 14%
total 62992636 24149052 38843584 38% - 0 -

$ maxmem
Memory starts at: 9223372041149874176 (8000000100020000)
Memory ends at: 9223372047592194047 (800000027fffffff)
Memory available: 6442319871 (17ffdffff)