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

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)

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

Other related posts: