| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Totally bizarre........
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_at_il.proquest.com>
Sent by: oracle-l-bounce_at_freelists.org
18/09/2004 04:33 AM
Please respond to
Mark.Bobak_at_il.proquest.com
To
<oracle-l_at_freelists.org>
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
And repeated executions show output like this:
SID SERIAL# USERNAME MACHINE
LOGON_TIM
OSUSER SQL_TEXT
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
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
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 -- 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. -- http://www.freelists.org/webpage/oracle-lReceived on Fri Sep 17 2004 - 18:44:10 CDT
 |
 |