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) -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l