Totally bizarre........
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 17 Sep 2004 14:33:56 -0400
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(+) =3D vsq.hash_value
13 and vs.sql_address(+) =3D 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 =3D :B1 AN 79007 237208
D ROWNUM =3D 1
SQL> /
SID SERIAL# USERNAME MACHINE
LOGON_TIM
---------- ---------- ------------------------------
----------------------------------------------------------------
---------
OSUSER SQL_TEXT
EXECUTIONS BUFFER_GETS
------------------------------
------------------------------------------------------------ ----------
-----------
SELECT DOC_ID FROM DOCUMENT_RETRIEVALS
WHERE DOC_ID =3D :B1 AN 79016 237235
D ROWNUM =3D 1
SQL> /
SID SERIAL# USERNAME MACHINE
LOGON_TIM
---------- ---------- ------------------------------
----------------------------------------------------------------
---------
OSUSER SQL_TEXT
EXECUTIONS BUFFER_GETS
------------------------------
------------------------------------------------------------ ----------
-----------
SELECT DOC_ID FROM DOCUMENT_RETRIEVALS
WHERE DOC_ID =3D :B1 AN 79023 237256
D ROWNUM =3D 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
Other related posts:
