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

So it appears that it is being repeatedly executed, but that the session(s)
executing it are gone each time you run the query. Since it is a preparsed 1
row query, it sure could execute a number of times very quickly and your
chances of a specific sid being tied to it are slim. Set time on and get an
idea of the number of increments per unit time. It seems like this might be
a very tiny bit of a multi-query functional bit of an application.

Possibly run strings on the binaries or search for a reasonable bit in
source$.

The only good news here is that it doesn't seem to take up much resource per
execution. Looks like it's very quick and does 3 buffer gets per execution.

mwf

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Freeman Robert - IL
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(+) =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


--
http://www.freelists.org/webpage/oracle-l

Other related posts: