RE: Totally bizarre........
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: "Freeman Robert - IL" <FREEMANR@xxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 17 Sep 2004 15:34:27 -0400
Nope, that's one of the first things I checked for:
select spid from v$process where addr not in(select paddr from =
v$session);
yields no rows.
-----Original Message-----
From: Freeman Robert - IL [mailto:FREEMANR@xxxxxxxx]
Sent: Friday, September 17, 2004 3:18 PM
To: Bobak, Mark; 'oracle-l@xxxxxxxxxxxxx '
Subject: RE: Totally bizarre........
Hmmmmmmm.... interesting (I missed the increasing execution numbers in =
the
output, my bad).... Are there any orphaned processes that don't have
sessions?
RF
-----Original Message-----
From: Bobak, Mark
To: Freeman Robert - IL; oracle-l@xxxxxxxxxxxxx
Sent: 9/17/2004 2:09 PM
Subject: RE: Totally bizarre........
No.....repeated executions just moments apart shows that
executions CONTINUES to increase, as does buffer_gets....
It's increasing right now, in front of my face, even though
I can't associate it w/ any session......
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Freeman Robert - IL
Sent: Friday, September 17, 2004 3:04 PM
To: 'oracle-l@xxxxxxxxxxxxx '
Subject: RE: Totally bizarre........
This query simply implies that it was not being executed by a session
when
the query was being run. There is a great deal of history in the SQL
Area...
this query might have run an hour ago or five hours ago.
RF
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
To: oracle-l@xxxxxxxxxxxxx
Sent: 9/17/2004 1:33 PM
Subject: Totally bizarre........
Ok, database is 9.2.0.5 on Solaris8.
So, I wrote this query:
1 select sid,
2 serial#,
3 username,
4 machine,
5 logon_time,
6 osuser,
7 sql_text,
8 executions,
9 buffer_gets
10 from v$session vs,
11 v$sql vsq
12 where vs.sql_hash_value(+) =3D3D vsq.hash_value
13 and vs.sql_address(+) =3D3D vsq.address
14 and vsq.executions>70000
15* and vsq.sql_text like '%RETRIEVAL%'
And repeated executions show output like this:
SID SERIAL# USERNAME MACHINE
LOGON_TIM
---------- ---------- ------------------------------
----------------------------------------------------------------
---------
OSUSER SQL_TEXT
EXECUTIONS BUFFER_GETS
------------------------------
------------------------------------------------------------ ----------
-----------
SELECT DOC_ID FROM DOCUMENT_RETRIEVALS
WHERE DOC_ID =3D3D :B1 AN 79007 237208
D ROWNUM =3D3D 1
SQL> /
SID SERIAL# USERNAME MACHINE
LOGON_TIM
---------- ---------- ------------------------------
----------------------------------------------------------------
---------
OSUSER SQL_TEXT
EXECUTIONS BUFFER_GETS
------------------------------
------------------------------------------------------------ ----------
-----------
SELECT DOC_ID FROM DOCUMENT_RETRIEVALS
WHERE DOC_ID =3D3D :B1 AN 79016 237235
D ROWNUM =3D3D 1
SQL> /
SID SERIAL# USERNAME MACHINE
LOGON_TIM
---------- ---------- ------------------------------
----------------------------------------------------------------
---------
OSUSER SQL_TEXT
EXECUTIONS BUFFER_GETS
------------------------------
------------------------------------------------------------ ----------
-----------
SELECT DOC_ID FROM DOCUMENT_RETRIEVALS
WHERE DOC_ID =3D3D :B1 AN 79023 237256
D ROWNUM =3D3D 1
So, what I'm seeing here is a SQL that's continuously getting executed
and consuming buffer gets.....but from where? Join to V$SESSION
fails.....
If no sessions are executing it, where's it executing from?
The ghost in the machine??
Any ideas are appreciated.
Thanks,
-Mark
--
Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"On two occasions, I have been asked [by members of Parliament], "Pray,
Mr. Babbage, if you put into the machine wrong figures, will the right
answers come out?' I am not able to rightly apprehend the kind of
confusion of ideas that could provoke such a question."
-- Charles Babbage (1791-1871)
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
