Re: Strange session blocking issue

From: Tanel Poder <tanel_at_poderc.com>
Date: Sat, 22 Jan 2011 17:30:05 +0200
Message-ID: <AANLkTikxr+dmM9hJp4piuTSyuQChrfHW3A_C3mvCaLcn_at_mail.gmail.com>



V$SQLSTATS is a separate array - not part of the library cache objects at all (unlike various other V$SQL* views which walk the library cache structure). V$SQLSTATS isn't protected by latches, but it's still protected - by kgx mutexes.

I don't know any way to list all "latchless" views, but generally, when you query a fixed length array (v$session, v$process) then your query doesn't take any latches (and sometimes can read garbage out of some in-flux slots), but when it's a complex structure like a library cache "tree" or some linked lists, you need some protection. In 11g there are no library cache latches anymore, but queries still need protection, they use mutexes.

A way to identify which query uses latches and which not, well just run the query and measure v$latch.gets or use latchprofx, that's what I do.

Regarding this problem you are having - try running the query with only one SQL_ID and with "WHERE sql_id *= " *instead of the "in" clause with multiple values. The problem here is that with IN clause with multiple values, Oracle transforms this query into a semi-join, causing to do a full scan through X$KQLFBC. When you use equality, you'll have the indexed access path into that X$. Both KQLFBC_HASH and KQLFBC_SQLID columns are "indexed" in X$KQLFBC. Another option would be to hint your query the way that Oracle wouldn't do a full scan through X$KSLFBC, but would use the good old FILTER loop instead of semi-join and would use your set of SQL_IDs to drive the nested loop - which would then do a separate indexed lookup into X$KSLFBC for each sql_id...

--
Tanel Poder
http://tech.e2sn.com
http://blog.tanelpoder.com




On Sat, Jan 22, 2011 at 7:39 AM, Martin Berger <martin.a.berger_at_gmail.com>wrote:


> Do you know how I can get all latchless views (without testing them)?
> Or is it pure try&error?
>
> Martin
>
> Am 22.01.2011 um 02:06 schrieb fairlie rego:
>
>
> Access to v$sqlstats can perhaps be given since it is a latchless view
>
>
>
>
-- http://www.freelists.org/webpage/oracle-l
Received on Sat Jan 22 2011 - 09:30:05 CST

Original text of this message