Re: Totally bizarre........
- From: Graeme.Farmer@xxxxxxxxxx
- To: Mark.Bobak@xxxxxxxxxxxxxxx
- Date: Sat, 18 Sep 2004 09:48:28 +1000
Mark,
You could get the parsing_user_id from v$sql then see what sessions are
logged on for this user. You may then be able to trace the session further
if you need more of an insight into what it is doing. Otherwise, if you
don't see sessions logged on, it's possible that new connections are being
made (very) often to execute this query in which case a logon trigger may
be your best friend.
Good luck.
Graeme Farmer
"Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
18/09/2004 04:33 AM
Please respond to
Mark.Bobak@xxxxxxxxxxxxxxx
To
<oracle-l@xxxxxxxxxxxxx>
cc
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
--
This transmission is for the intended addressee only and is confidential
information. If you have received this transmission in error, please notify the
sender and delete the transmission. The contents of this e-mail are the opinion
of the writer only and are not endorsed by the Mincom Group of companies unless
expressly stated otherwise.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
