RE: Totally bizarre........
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: "Dan Tow" <dantow@xxxxxxxxxxxxxx>
- Date: Fri, 17 Sep 2004 15:44:07 -0400
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@xxxxxxxxxxxxxx]
Sent: Friday, September 17, 2004 3:16 PM
To: Bobak, Mark
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxx>:
> 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)
>
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
