Eventually...
real: 0
SQL> select
2 name, value
3 from
4 v$sesstat vs, v$statname sn
5 where
6 vs.statistic#=sn.statistic# and
7 value is NOT NULL and
8 value<>0 and
9 sid=11;
NAME VALUE
---------------------------------------------------------------- ---------
logons cumulative 1
logons current 1
opened cursors cumulative 1072
opened cursors current 11
user commits 26
user calls 341
recursive calls 198492
recursive cpu usage 4089
session logical reads 77233609
CPU used when call started 696253
CPU used by this session 696253
session connect time 20654909
process last non-idle time 20654909
session uga memory 1347972
session uga memory max 2872124
messages sent 2154
session pga memory 6752520
session pga memory max 6752520
enqueue requests 11878
enqueue releases 11876
total file opens 13
db block gets 2642119
consistent gets 74591490
physical reads 148822
db block changes 3005410
consistent changes 141
physical writes 2728
physical writes non checkpoint 2728
change write time 29633
redo synch writes 40
redo synch time 305
free buffer requested 311344
dirty buffers inspected 63544
pinned buffers inspected 2
hot buffers moved to head of LRU 13576
free buffer inspected 63546
commit cleanout failures: block lost 860
commit cleanout failures: callback failure 12
commit cleanouts 79286
commit cleanouts successfully completed 78414
CR blocks created 141
switch current to new buffer 54870
write clones created in foreground 198
prefetched blocks 108149
physical reads direct 3594
physical writes direct 2728
calls to kcmgcs 56343
calls to kcmgas 4130
calls to get snapshot scn: kcmgss 82845
redo entries 1506007
redo size 1.184E+09
redo buffer allocation retries 886
redo log space requests 3
redo log space wait time 122
redo ordering marks 4
data blocks consistent reads - undo records applied 141
no work - consistent read gets 72058049
cleanouts only - consistent read gets 31927
rollbacks only - consistent read gets 141
immediate (CURRENT) block cleanout applications 12274
immediate (CR) block cleanout applications 31927
deferred (CURRENT) block cleanout applications 55813
table scans (short tables) 210918
table scans (long tables) 36
table scan rows gotten 798264962
table scan blocks gotten 71788386
table fetch by rowid 1074164
table fetch continued row 12
cluster key scans 743
cluster key scan block gets 3134
rows fetched via callback 660086
leaf node splits 4018
branch node splits 9
parse time cpu 143
parse time elapsed 161
parse count (total) 1186
parse count (hard) 133
execute count 74445
bytes sent via SQL*Net to client 59650
bytes received via SQL*Net from client 84233
SQL*Net roundtrips to/from client 342
sorts (memory) 279
sorts (disk) 2
sorts (rows) 66799
cursor authentications 51
buffer is pinned count 453281
buffer is not pinned count 73513922
no buffer to keep pinned count 792939
I'd appreciate any insight
Regards,
Sergey
-----Original Message-----
Sent: Tuesday, January 22, 2002 5:19 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Session_wait
Sergey, please run this and post values. Just curious.
select
name, value
from
v$sesstat vs, v$statname sn
where
vs.statistic#=sn.statistic# and
value is NOT NULL and
value<>0 and
sid=11;
hth,
- Ross
p.s. "shadow process" - your client connection's 'footprint' in the os.
if a local host connect, look for "LOCAL=YES". If not, look for
LOCAL=NO. Sort out all not in your instance name.
-----Original Message-----
Sent: Tuesday, January 22, 2002 4:57 PM
To: Multiple recipients of list ORACLE-L
Well, now it's finished, but the timing is terrible... I should've queried
v$lock. RAID had a lot of activity while that was going on. File #10 is a
"data" datafile. I am not sure what is meant by shadow process, sorry. I
guess we'll repeat this tomorrow.
Thank you, guys, I really appreciate your help.
Best regards,
Sergey
-----Original Message-----
Sent: Tuesday, January 22, 2002 4:36 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Session_wait
well, then it's an entirely different kettle of fish. Jeremiah is on track.
don't suppose you can query v$lock where sid=11 or block<>0 or lmode=6
while
this is going on, can you?
and...in the OS....what is going on w/disk? and with the shadow process?
and, lastly, what is file# 10? Probably a "data" datafile.......
-----Original Message-----
Sent: Tuesday, January 22, 2002 4:11 PM
To: Multiple recipients of list ORACLE-L
Thanks for your input, but P2 was not changing as u can c from the last
one I caught:
SID SEQ# EVENT P1TEXT P1 P1RAW
P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_TIME
SEC_WT STATE
----- --------- ------------------------- ---------- --------- --------
---------- --------- -------- ---------- --------- -------- ---------
------ -------------------
11 40019 db file sequential read file# 10 0000000A
block# 221571 00036183 blocks 1 00000001 -1
1594 WAITED SHORT TIME
After that the SQL changed....
Regards,
Sergey Babich
-----Original Message-----
Sent: Tuesday, January 22, 2002 3:32 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Session_wait
it was reading blocks into SGA buffers. No big deal. As the
wait time went up, so likely were the values of P2 changing.
A longish read by sid 11.
<shrug>
-----Original Message-----
Sent: Tuesday, January 22, 2002 3:19 PM
To: Multiple recipients of list ORACLE-L
Hi, listers,
One of the sessions seems to be a problem:
SELECT * FROM V$SESSION_WAIT WHERE SID=&NUM
/
Output:
SID SEQ# EVENT P1TEXT P1 P1RAW
P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_TIME
SEC_WT STATE
----- --------- ------------------------- ---------- --------- --------
---------- --------- -------- ---------- --------- -------- ---------
------ -------------------
11 40019 db file sequential read file# 10 0000000A
block# 221571 00036183 blocks 1 00000001 -1
335 WAITED SHORT TIME
Then wait_time was 689, then 749 and higher and higher (the rest of the
output being the same).
Any su
ggestions are appreciated.
Regards,
Sergey Babich
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Babich , Sergey
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohan, Ross
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Babich , Sergey
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohan, Ross
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Babich , Sergey
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohan, Ross
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Babich , Sergey
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
