Attached is output from a utility called snapper, being run against the
Session that is basically at a complete standstill. Any ideas from this
report where the problem lies?
SQL> @snapper.sql out 1 30 202
-- Session Snapper v1.03 by Tanel Poder ( http://www.tanelpoder.com )
--------------------------------------------------------------------------------------------------------------------------------------------
-- SID, SNAPSHOT START , SECONDS , TYPE,
STATISTIC , DELTA, D/SEC,
HDELTA, HD/SEC
--------------------------------------------------------------------------------------------------------------------------------------------
DATA, 202, 20070915 05:42:23, 1, WAIT, virtual circuit
status , 3000000, 3000000, 3s 3s
-- End of snap 1
DATA, 202, 20070915 05:42:26, 1, WAIT, virtual circuit
status , 3000000, 3000000, 3s 3s
-- End of snap 4
DATA, 202, 20070915 05:42:29, 1, WAIT, virtual circuit
status , 3000000, 3000000, 3s 3s
-- End of snap 7
DATA, 202, 20070915 05:42:32, 1, WAIT, virtual circuit
status , 3000000, 3000000, 3s 3s
-- End of snap 10
DATA, 202, 20070915 05:42:35, 1, WAIT, virtual circuit
status , 3000000, 3000000, 3s 3s
-- End of snap 13
DATA, 202, 20070915 05:42:36, 1, STAT, opened cursors
cumulative , 6, 6, 6 6
DATA, 202, 20070915 05:42:36, 1, STAT, user
calls , 18, 18,
18 18
DATA, 202, 20070915 05:42:36, 1, STAT, session logical
reads , 164, 164, 164 164
DATA, 202, 20070915 05:42:36, 1, STAT, enqueue
requests , 1, 1,
1 1
DATA, 202, 20070915 05:42:36, 1, STAT, enqueue
releases , 1, 1,
1 1
DATA, 202, 20070915 05:42:36, 1, STAT, db block
gets , 153, 153,
153 153
DATA, 202, 20070915 05:42:36, 1, STAT, db block gets from
cache , 153, 153, 153 153
DATA, 202, 20070915 05:42:36, 1, STAT, consistent
gets , 11, 11,
11 11
DATA, 202, 20070915 05:42:36, 1, STAT, consistent gets from
cache , 11, 11, 11 11
DATA, 202, 20070915 05:42:36, 1, STAT, consistent gets -
examination , 4, 4, 4 4
DATA, 202, 20070915 05:42:36, 1, STAT, db block
changes , 119, 119,
119 119
DATA, 202, 20070915 05:42:36, 1, STAT, free buffer
requested , 1, 1,
1 1
DATA, 202, 20070915 05:42:36, 1, STAT, commit
cleanouts , 3, 3,
3 3
DATA, 202, 20070915 05:42:36, 1, STAT, commit cleanouts
successfully completed , 3, 3, 3 3
DATA, 202, 20070915 05:42:36, 1, STAT, calls to
kcmgcs , 2, 2,
2 2
DATA, 202, 20070915 05:42:36, 1, STAT, calls to
kcmgas , 2, 2,
2 2
DATA, 202, 20070915 05:42:36, 1, STAT, calls to get snapshot
scn: kcmgss , 8, 8, 8 8
DATA, 202, 20070915 05:42:36, 1, STAT, redo
entries , 61, 61,
61 61
DATA, 202, 20070915 05:42:36, 1, STAT, redo
size , 36012, 36012,
36.01k 36.01k
DATA, 202, 20070915 05:42:36, 1, STAT, redo ordering
marks , 1, 1, 1 1
DATA, 202, 20070915 05:42:36, 1, STAT, undo change vector
size , 13720, 13720, 13.72k 13.72k
DATA, 202, 20070915 05:42:36, 1, STAT, immediate (CURRENT)
block cleanout appli, 3, 3, 3 3
DATA, 202, 20070915 05:42:36, 1, STAT, deferred (CURRENT)
block cleanout applic, 6, 6, 6 6
DATA, 202, 20070915 05:42:36, 1, STAT, commit txn count
during cleanout , 2, 2, 2 2
DATA, 202, 20070915 05:42:36, 1, STAT, active txn count
during cleanout , 2, 2, 2 2
DATA, 202, 20070915 05:42:36, 1, STAT, cleanout - number of
ktugct calls , 3, 3, 3 3
DATA, 202, 20070915 05:42:36, 1, STAT, leaf node
splits , 1, 1,
1 1
DATA, 202, 20070915 05:42:36, 1, STAT, heap block
compress , 1, 1,
1 1
DATA, 202, 20070915 05:42:36, 1, STAT, session cursor cache
hits , 6, 6, 6 6
DATA, 202, 20070915 05:42:36, 1, STAT, parse count
(total) , 6, 6,
6 6
DATA, 202, 20070915 05:42:36, 1, STAT, execute
count , 12, 12,
12 12
DATA, 202, 20070915 05:42:36, 1, STAT, bytes sent via
SQL*Net to client , 10438540, 10438540, 10.44M 10.44M
DATA, 202, 20070915 05:42:36, 1, STAT, bytes received via
SQL*Net from client , 20823, 20823, 20.82k 20.82k
DATA, 202, 20070915 05:42:36, 1, STAT, SQL*Net roundtrips
to/from client , 26, 26, 26 26
DATA, 202, 20070915 05:42:36, 1, TIME, repeated bind elapsed
time , 30, 30, 30us 30us
DATA, 202, 20070915 05:42:36, 1, TIME, parse time
elapsed , 87, 87,
87us 87us
DATA, 202, 20070915 05:42:36, 1, TIME, DB
CPU , 2086, 2086,
2.09ms 2.09ms
DATA, 202, 20070915 05:42:36, 1, TIME, sql execute elapsed
time , 1794, 1794, 1.79ms 1.79ms
DATA, 202, 20070915 05:42:36, 1, TIME, DB
time , 2086, 2086,
2.09ms 2.09ms
DATA, 202, 20070915 05:42:36, 1, WAIT, virtual circuit
status , -707825, -707825, -707.83ms -707.83ms
DATA, 202, 20070915 05:42:36, 1, WAIT, SQL*Net message to
client , 26, 26, 26us 26us
DATA, 202, 20070915 05:42:36, 1, WAIT, SQL*Net message from
client , 16, 16, 16us 16us
DATA, 202, 20070915 05:42:36, 1, WAIT, SQL*Net more data
from client , 29292216, 29292216, 29.29s 29.29s
-- End of snap 14
DATA, 202, 20070915 05:42:38, 1, WAIT, virtual circuit
status , 3000000, 3000000, 3s 3s
-- End of snap 16
DATA, 202, 20070915 05:42:41, 1, WAIT, virtual circuit
status , 3000000, 3000000, 3s 3s
-- End of snap 19
DATA, 202, 20070915 05:42:44, 1, WAIT, virtual circuit
status , 3000000, 3000000, 3s 3s
-- End of snap 22
DATA, 202, 20070915 05:42:47, 1, WAIT, virtual circuit
status , 3000000, 3000000, 3s 3s
-- End of snap 25
DATA, 202, 20070915 05:42:50, 1, WAIT, virtual circuit
status , 3000000, 3000000, 3s 3s
-- End of snap 28
PL/SQL procedure successfully completed.
