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

From: Ron Rogers <RROGERS_at_galottery.org>
Date: Fri, 17 Sep 2004 15:41:46 -0400
Message-Id: <s14b05d0.068@gwia.galottery.org>

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_at_tusc.com> 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.

-----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:40:35 CDT