Database Reference
In-Depth Information
Now hit return in the first window. The program enables SQL trace and inserts a single
row. After a moment the program asks for input again.
Hit return to continue
At this point, the
INSERT
statement and a subsequent
COMMIT
have been completed. Another
query on
V$SESSTAT
reveals that the figures for db block gets and consistent gets have risen to 9
and 197 respectively.
SQL> SELECT n.name, s.value
FROM v$sesstat s, v$statname n, v$session se
WHERE s.statistic#=n.statistic#
AND n.name IN ('db block gets', 'consistent gets')
AND s.sid=se.sid
AND se.program='perl.exe';
NAME VALUE
------------------- -----
db block gets 9
consistent gets 197
You may now hit return for the second time. The program disconnects and terminates.
Subtracting the initial figures from the final figures yields nine db block gets and two consistent
gets. In theory, the extended SQL trace file should contain the same figures, however small
discrepancies are in order.
The trace file that results from this test is small enough to evaluate manually. Note that the
trace file which results the first time you run the test may be larger than the file reproduced
below, since the dictionary cache and the library cached need to be loaded. Except for some
header information, the complete trace file (with line numbers added) is depicted here:
1 Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
2 With the Partitioning, Oracle Label Security, OLAP and Data Mining options
3
4 *** ACTION NAME:() 2007-11-20 15:39:38.546
5 *** MODULE NAME:(insert_customer.pl) 2007-11-20 15:39:38.546
6 *** SERVICE NAME:(TEN.oradbpro.com) 2007-11-20 15:39:38.546
7 *** SESSION ID:(44.524) 2007-11-20 15:39:38.546
8 =====================
9 PARSING IN CURSOR #2 len=68 dep=0 uid=61 oct=42 lid=61 tim=789991633616
hv=740818757 ad='6be3972c'
10 alter session set events '10046 trace name context forever, level 8'
11 END OF STMT
12 EXEC #2:c=0,e=98,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=789991633607
13 WAIT #2: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1
p3=0 obj#=-1 tim=789991638001
14 WAIT #2: nam='SQL*Net message from client' ela= 569 driver id=1413697536 #bytes=1
p3=0 obj#=-1 tim=789991638751
15 =====================
16 PARSING IN CURSOR #1 len=87 dep=0 uid=61 oct=2 lid=61 tim=789991639097
hv=2228079888 ad='6cad992c'
