RE: Totally bizarre........

From: Freeman Robert - IL <FREEMANR_at_tusc.com>
Date: Fri, 17 Sep 2004 14:17:33 -0500
Message-ID: <F5E885BEF9540D47A7BDC03CF16880870A7D50B3@tuscil_ex1>

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_at_freelists.org 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Freeman Robert - IL Sent: Friday, September 17, 2004 3:04 PM To: 'oracle-l_at_freelists.org '
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_at_freelists.org
To: oracle-l_at_freelists.org
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(+) =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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Fri Sep 17 2004 - 14:13:13 CDT