RE: Totally bizarre........
- From: "Ron Rogers" <RROGERS@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>, <FREEMANR@xxxxxxxx>
- Date: Fri, 17 Sep 2004 15:41:46 -0400
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
Other related posts:
