Event 10842

This event traces the functions invoked by the application and can be set on both the client and the server. On client, it can be set in the environment variable EVENT_10842 or sqlnet.ora by specifying EVENT_10842=level. Possible values are 0-16. The levels and their meanings have not changed in 11G. The trace facility is now thread-safe and works with diagnosability enabled and disabled. With diagnosability enabled, a separate trace file is created for each thread. Without diagnosability, two trace files are used and trace records are written to one file and then to the other when the file size limit is reached cycling between the two. With release 11G and beyond, “diagnosability” enabled is the preferred way to enable tracing. When diagnosability is turned off, one can specify a directory to write the traces to using the following environment variable:

ORA_CLIENTTRACE_DIR controls where the trace output is written. This environment variable is necessary when diagnosability is disabled which is not preferred in 11G. Output is written to the file $ORA_CLIENTTRACE_DIR/ora_skgu_pid.trc.

EVENT_10842 Settings on client

The environment variable EVENT_10842 controls what to trace. The value is specified in the format: "server=<>;user=<>;stmt=<>;level=<>;interval=<>" where:

server	Comma separated list of servers	"all" for all servers
user	Comma separated list of users	"all" for all users
stmt	Comma separated list of statement types	"all" for all statement types

level = 1 through 15 thus:

1	Trace all server attach and server detach calls for servers listed in "server" attribute of the environment variable
2	Trace all session begin, logon, session end, logoff calls for the users listed in "user" attribute of the environment variable
3	Trace all prepare, execute, fetch calls for the specified statement types listed in "stmt" attribute of environment variable
4	Trace all Bind, Define, Describe calls
5	Trace all OCI LOB calls
7	Get statistical info on all connection pooling /connection related calls
8	Get statistical info on all session info
9	Get statistical info on all handle info
10	Get statistical info on time taken in execute and fetch calls
11	Get statistical info on transaction related calls
15	Trace all calls with statistical info

interval = interval in seconds. Can be omitted.
filesize = length of the trace files in bytes. Can be omitted.

Examples

To trace all OCI relational functions :

			
				
				                        setenv EVENT_10842 "server=all;user=all;stmt=all;level=15"

To trace all OCI connection(server) calls for servers inst1 and inst2.

				
			     
			
			
			setenv EVENT_10842 "server=inst1,inst2;user=all;stmt=all;level=1"

In this case, the values set for user and stmt attributes of environment variable are ignored.

To trace all OCI users (sessions) calls for users scott and miller.

				
	    		
			
			
			  setenv EVENT_10842 "server=all;user=scott,miller;stmt=all;level=2" 
				
			In this case, the values set for server and stmt attributes of environment variable are ignored.

To trace all OCI statement execution related calls for statement type SELECT.
```
				
			
			
			
			    setenv EVENT_10842 "server=all;user=all;stmt=select;level=3"
			
			
			
					
		
		
		
		
```
In this case, the values set for server and user attributes of environment variable are ignored.

To Trace all OCI bind, define related calls, the values of server, user and stmt does not matter.

				
		            
			
			
			setenv EVENT_10842 "server=all;user=all;stmt=all;level=4"

To trace all OCI LOB related calls, other attribute values are ignored, as only level number needs to be set to 5.

				
			
			
			
			setenv EVENT_10842 "server=all;user=all;stmt=all;level=5"

Event 10842 Settings on server

On server one can set the event 10842 to control what to trace. Only the level is necessary, specifying other arguments mentioned above will result in data base startup failure as the server does not know how to parse them. Here is an example of setting the trace event to level 15 in the init.ora:

	
	
	
	
	event="10842 trace name context forever, level 15"

A Example of a Client OCI trace


OCIServerAttach(srvhp = 0xde4974,
errhp = 0xe071a8,
dblink = DB10204,
dblink_len = 15,
mode = OCI_DEFAULT);
			
			
			
Entry - OCIErrorGet(
hndlp = 0xe071a8,
recordno = 1
sqlstate = NULL,
errcodep = 12f610 = 0,
bufsiz = 2048,
type = OCI_HTYPE_ERROR);
			
			
			
Exit - OCIErrorGet(
hndlp = 0xe071a8,
recordno = 1
sqlstate = NULL,
errcodep = 12f610 = 12154,
bufsiz = 2048,
type = OCI_HTYPE_ERROR);
			
			
			
Entry - OCIAttrGet(
trgthndlp = 0xde4974,
trghndltyp = 8,
attributep = 0xd669e0,
sizep = 0x0,
143,
errhp = 0xe071a8);
			
			
			
OCIHandleFree(
hndlp = 0x8066248,
type = Session handle);