Databases Reference
In-Depth Information
It was also worth noting that 71 updates of the table
SYS.SEQ$
, the data dictionary base
table which holds sequences, had occurred:
Hash Value: 2635489469 - Total Elapsed Time (excluding think time): 0.082s
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,
highwater=:8,audit$ =:9,flags=:10 where obj#=:1
DB Call Count Elapsed CPU Disk Query Current Rows
------- -------- ---------- ---------- -------- -------- -------- --------
PARSE 71 0.0026s 0.0000s 0 0 0 0
EXEC
71
0.0790s 0.0781s 0 71 142 71
FETCH 0 0.0000s 0.0000s 0 0 0 0
------- -------- ---------- ---------- -------- -------- -------- --------
Total 142 0.0815s 0.0781s 0 71 142 71
This probably meant that a sequence, which was not cached, was incremented 71 times.
To verify this assumption, I searched the trace file for the hash value 2635489469 in the ESQL-
TRCPROF report
3
and retrieved the bind variable value for column
obj#
. Since the script
awr_
capture.sql
enables SQL trace at level 12, the trace file does contain bind variables. Counting
from left to right, the tenth bind variable was applicable to
obj#
. Since bind variable values are
numbered from 0, I needed to look for
Bind#9
.
Bind#9
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=0915bf8c bln=22 avl=04 flg=05
value=53740
This yielded the object identifier of the sequence, which I then used to retrieve informa-
tion on the object from
DBA_OBJECTS
. It turned out that object 53740 was indeed a sequence and
that it was not cached.
SQL> SELECT object_name, object_type FROM dba_objects WHERE object_id=53740;
OBJECT_NAME OBJECT_TYPE
------------- -------------------
IMAGE_ID_SEQ SEQUENCE
SQL> SELECT cache_size
FROM dba_objects o, dba_sequences s
WHERE o.object_id=53740
AND o.owner=s.sequence_owner
AND o.object_name=s.sequence_name;
CACHE_SIZE
----------
0
3.
TKPROF omits the hash values for SQL statement texts.
