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

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Fri, 17 Sep 2004 15:44:07 -0400
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF09660E7D@bosmail00.bos.il.pqe>

That's a good point, Dan. It's very possible that's what's happening.

I was hoping that w/ enough repeated executions of my query, I could = catch
it in action.

-Mark

-----Original Message-----
From: Dan Tow [mailto:dantow_at_singingsql.com] Sent: Friday, September 17, 2004 3:16 PM To: Bobak, Mark
Cc: oracle-l_at_freelists.org
Subject: Re: Totally bizarre........

Not bizarre at all, I think - the query is very efficient, with 3 = logical I/Os
per execution. Since it is not executing *super* frequently, though, at = any
given *moment*, you are very likely to find yourself *between* (*very* = brief)
executions of this query, so it's sql_address need not be found in any v$session entry for most snapshots. You are only guaranteed (to the = extent v$
queries can be trusted) to see a sql_address in v$session for the = duration of
the query execution, which is extraordinarily low, in this case.

Thanks,

Dan Tow
650-858-1557
www.singingsql.com

Quoting "Bobak, Mark" <Mark.Bobak_at_il.proquest.com>:

> 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(+) =3D3D vsq.hash_value
> 13 and vs.sql_address(+) =3D3D 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 =3D3D :B1 AN 79007 237208
> D ROWNUM =3D3D 1
>
>
> SQL> /
>
> SID SERIAL# USERNAME MACHINE
> LOGON_TIM
> ---------- ---------- ------------------------------
> ----------------------------------------------------------------
> ---------
> OSUSER SQL_TEXT
> EXECUTIONS BUFFER_GETS
> ------------------------------
> ------------------------------------------------------------ =

---

> -----------
>
> SELECT DOC_ID FROM DOCUMENT_RETRIEVALS
> WHERE DOC_ID =3D3D :B1 AN 79016 237235
> D ROWNUM =3D3D 1
>
>
> SQL> /
>
> SID SERIAL# USERNAME MACHINE
> LOGON_TIM
> ---------- ---------- ------------------------------
> ----------------------------------------------------------------
> ---------
> OSUSER SQL_TEXT
> EXECUTIONS BUFFER_GETS
> ------------------------------
> ------------------------------------------------------------ =

---

> -----------
>
> SELECT DOC_ID FROM DOCUMENT_RETRIEVALS
> WHERE DOC_ID =3D3D :B1 AN 79023 237256
> D ROWNUM =3D3D 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

Received on Fri Sep 17 2004 - 14:41:56 CDT