Hi All,
I have this really strange problem. Oracle v11.2 on Linux.
We have a really busy database, where massive updates/inserts are going on (at the moment).
In a SQL*Plus session, I started running one SQL (a SELECT command, with GROUP BY on join of 2 big tables). I was not expecting the SQL to run within few seconds, but rather few minutes.
From another session, I was monitoring this SQL (its plan etc etc).
Few minutes later, SQL_ID disappeared from the v$session view (for the session where the SQL was running), as if the SQL execution is complete.
I am pasting some stats I can see in various v$ views. I have run the monitoring SQLs after few minutes intervals and I can see the resource consumption is going on, wait events are increasing, SQL still running in one session, but SQL_ID disappeared from v$session.
This is one of those SQLs which has written lot of temp segments.
Any ideas??
Thanks in advance
SQL> select sql_id from v$session where sid=903 ;
SQL_ID
-------------
1 row selected.
SQL> column value format 999,999,999,999,999
SQL> select name , value from v$sesstat a , v$statname b
2 where a.STATISTIC# = b.STATISTIC# and sid = &value_of_sid
3 and ........ ;
Enter value for value_of_sid: 903
old 2: where a.STATISTIC# = b.STATISTIC# and sid = &value_of_sid
new 2: where a.STATISTIC# = b.STATISTIC# and sid = 903
NAME VALUE
---------------------------------------------------------------- --------------------
session logical reads 22,303,490
user I/O wait time 383,505
session uga memory 34,506,920
session pga memory 35,996,216
physical read total IO requests 1,512,406
physical read total bytes 13,681,795,072
physical write total bytes 514,244,608
consistent gets 22,303,896
consistent gets from cache 22,303,896
consistent gets from cache (fastpath) 5,422,895
consistent gets - examination 15,154,120
physical reads 1,670,278
physical reads cache 1,643,735
physical reads direct 26,543
physical read IO requests 1,512,464
physical read bytes 13,682,974,720
consistent changes 201,317
physical writes 62,774
physical writes direct 62,774
physical reads direct temporary tablespace 26,543
physical writes direct temporary tablespace 62,774
physical write bytes 514,244,608
physical writes non checkpoint 62,774
free buffer requested 1,650,632
redo size 31,976
file io wait time 2,721,739,444
temp space allocated (bytes) 514,850,816
transaction tables consistent reads - undo records applied 22,467
data blocks consistent reads - undo records applied 178,640
index scans kdiixs1 31,411,934
sorts (rows) 42,541,268
-- ************* after few minutes *********************
SQL> /
Enter value for value_of_sid: 903
old 2: where a.STATISTIC# = b.STATISTIC# and sid = &value_of_sid
new 2: where a.STATISTIC# = b.STATISTIC# and sid = 903
NAME VALUE
---------------------------------------------------------------- --------------------
session logical reads 23,072,821
user I/O wait time 395,267
session uga memory 34,506,920
session pga memory 35,996,216
physical read total IO requests 1,582,272
physical read total bytes 14,263,779,328
physical write total bytes 514,244,608
consistent gets 23,073,562
consistent gets from cache 23,073,562
consistent gets from cache (fastpath) 5,580,921
consistent gets - examination 15,700,946
physical reads 1,741,249
physical reads cache 1,713,593
physical reads direct 27,658
physical read IO requests 1,582,323
physical read bytes 14,264,344,576
consistent changes 201,345
physical writes 62,774
physical writes direct 62,774
physical reads direct temporary tablespace 27,658
physical writes direct temporary tablespace 62,774
physical write bytes 514,244,608
physical writes non checkpoint 62,774
free buffer requested 1,720,423
redo size 32,844
file io wait time 2,811,484,399
temp space allocated (bytes) 514,850,816
transaction tables consistent reads - undo records applied 22,467
data blocks consistent reads - undo records applied 178,668
index scans kdiixs1 32,821,724
sorts (rows) 42,541,268
-- ************* after few more minutes *********************
SQL> /
Enter value for value_of_sid: 903
old 2: where a.STATISTIC# = b.STATISTIC# and sid = &value_of_sid
new 2: where a.STATISTIC# = b.STATISTIC# and sid = 903
NAME VALUE
---------------------------------------------------------------- --------------------
session logical reads 32,830,244
user I/O wait time 589,591
session uga memory 34,506,920
session pga memory 35,996,216
physical read total IO requests 2,507,726
physical read total bytes 21,962,170,368
physical write total bytes 514,244,608
consistent gets 32,831,184
consistent gets from cache 32,831,184
consistent gets from cache (fastpath) 7,520,624
consistent gets - examination 22,687,929
physical reads 2,680,962
physical reads cache 2,638,858
physical reads direct 42,105
physical read IO requests 2,507,771
physical read bytes 21,962,547,200
consistent changes 203,897
physical writes 62,774
physical writes direct 62,774
physical reads direct temporary tablespace 42,105
physical writes direct temporary tablespace 62,774
physical write bytes 514,244,608
physical writes non checkpoint 62,774
free buffer requested 2,645,702
redo size 52,232
file io wait time 4,288,061,921
temp space allocated (bytes) 514,850,816
transaction tables consistent reads - undo records applied 24,839
data blocks consistent reads - undo records applied 178,848
index scans kdiixs1 50,436,973
sorts (rows) 42,541,268
-- ********** now the wait events ********************
SQL> select event, total_waits, time_waited from v$session_event where sid=903 ;
EVENT TOTAL_WAITS TIME_WAITED
---------------------------------------------------------------- ----------- -----------
Disk file operations I/O 13 10
latch: cache buffers chains 1 0
db file sequential read 1959316 475845
db file scattered read 24391 7008
db file parallel read 391451 176339
direct path read temp 316 870
direct path write temp 1066 800
SQL*Net message to client 22 0
SQL*Net message from client 21 24280
events in waitclass Other 5 2
10 rows selected.
SQL> /
EVENT TOTAL_WAITS TIME_WAITED
---------------------------------------------------------------- ----------- -----------
Disk file operations I/O 13 10
latch: cache buffers chains 1 0
db file sequential read 1970091 477666
db file scattered read 24401 7008
db file parallel read 393529 176790
direct path read temp 316 870
direct path write temp 1066 800
SQL*Net message to client 22 0
SQL*Net message from client 21 24280
events in waitclass Other 5 2
10 rows selected.
SQL> /
EVENT TOTAL_WAITS TIME_WAITED
---------------------------------------------------------------- ----------- -----------
Disk file operations I/O 13 10
latch: cache buffers chains 1 0
db file sequential read 1976809 479313
db file scattered read 24403 7008
db file parallel read 394854 177319
direct path read temp 316 870
direct path write temp 1066 800
SQL*Net message to client 22 0
SQL*Net message from client 21 24280
events in waitclass Other 5 2
10 rows selected.