Robert, I agree with what you say about the SQL area but the query is currently running. The executations and buffer gets are increasing with each query. Could it be that the query was a "gather info from hell and report to me" type of query and the user session died, was killed, etc. Would not the query continue to function internally until it had to get info or pass info and at that time it would realize the connection died and be cleaned up bu SMON? Ron >>> Freeman Robert - IL <FREEMANR@xxxxxxxx> 09/17/2004 3:03:37 PM >>> 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