| 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........
So it appears that it is being repeatedly executed, but that the session(s)
executing it are gone each time you run the query. Since it is a preparsed 1
row query, it sure could execute a number of times very quickly and your
chances of a specific sid being tied to it are slim. Set time on and get an
idea of the number of increments per unit time. It seems like this might be
a very tiny bit of a multi-query functional bit of an application.
Possibly run strings on the binaries or search for a reasonable bit in source$.
The only good news here is that it doesn't seem to take up much resource per execution. Looks like it's very quick and does 3 buffer gets per execution.
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Freeman Robert - IL
Sent: Friday, September 17, 2004 3:18 PM
To: 'Bobak, Mark '; 'oracle-l_at_freelists.org '
Subject: RE: Totally bizarre........
Hmmmmmmm.... interesting (I missed the increasing execution numbers in the output, my bad).... Are there any orphaned processes that don't have sessions?
RF
-----Original Message-----
From: Bobak, Mark
To: Freeman Robert - IL; oracle-l_at_freelists.org
Sent: 9/17/2004 2:09 PM
Subject: RE: Totally bizarre........
No.....repeated executions just moments apart shows that executions CONTINUES to increase, as does buffer_gets.... It's increasing right now, in front of my face, even though I can't associate it w/ any session......
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Freeman Robert - IL
Sent: Friday, September 17, 2004 3:04 PM
To: 'oracle-l_at_freelists.org '
Subject: RE: Totally bizarre........
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_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
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 -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri Sep 17 2004 - 14:47:36 CDT
 |
 |