Totally bizarre........

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Fri, 17 Sep 2004 14:33:56 -0400
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF09EFEB69@bosmail00.bos.il.pqe>

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)

--

http://www.freelists.org/webpage/oracle-l Received on Fri Sep 17 2004 - 13:29:27 CDT