Re: Strange session blocking issue
- From: Tanel Poder <tanel@xxxxxxxxxx>
- To: martin.a.berger@xxxxxxxxx
- Date: Sat, 22 Jan 2011 17:30:05 +0200
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@xxxxxxxxx>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
>
>
>
>
Other related posts:
