RE: catsearch and latch: cache buffers chains waits

From: Bobak, Mark <Mark.Bobak_at_proquest.com>
Date: Fri, 26 Feb 2010 10:48:15 -0500
Message-ID: <6AFC12B9BFCDEA45B7274C534738067F3DCE10FD_at_AAPQMAILBX02V.proque.st>

Well, I'm far from an expert in context indexes, or the catsearch functionality, so, I'm not going to comment much on that.

However, cache buffers chains latch contention has nothing to do with parsing. Likely latch waits due to excessive parsing are library cache and shared pool.

cache buffers chains are all about buffer cache access.

Rather than go on and on, I'll just point you to: http://sites.google.com/site/embtdbo/wait-event-documentation/oracle-latch-cache-buffers-chains

which is an excellent write up by Kyle Hailey.

Hope that helps,

-Mark

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Dba DBA Sent: Friday, February 26, 2010 9:24 AM
To: ORACLE-L
Subject: catsearch and latch: cache buffers chains waits

I am trying to tune a query that uses the catsearch function and this appears to be the problem. I first looked at the event in v$session and noticed latch: cache buffers chains. This typically indicates excess parsing, but this is just 1 query?

So I am running a 10046 trace and I am seeing something interesting. It appears that catsearch execute a query over and over again. Similiar to having a function in your query.

I am also noticing that it executes for each row returned for each keyword. This is causing excess parsing.

has anyone used catsearch before? This does not seem to be very efficient. It is using my ctxcat index. Here is a sample of one of the parses in the 10046 trace. This happens over and over again in my trace file.

PARSING IN CURSOR #8 len=247 dep=1 uid=13686 oct=3 lid=13686 tim=25146063685495 hv=2206871408 ad='c5437b70' select /*+ INDEX_ASC(i "DR$MYPOLICY$X") */ dr$rowid from "DR$MYPOLICY$I" i where dr$token = :token and dr$token_type = :ttype and DR$ROWID >= :r01 order by dr$token ASC, dr$token_type ASC, dr$rowid ASC END OF STMT
PARSE #8:c=0,e=73,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=25146063685490 BINDS #8:
 bind 0: dty=1 mxl=128(64) mal=00 scl=00 pre=00 oacflg=00 oacfl2=0000 size=184 offset=0    bfp=ffffffff7b991790 bln=128 avl=10 flg=05    value="TOWNAME"
 bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=00 oacfl2=0000 size=0 offset=128    bfp=ffffffff7b991810 bln=22 avl=01 flg=01    value=0
 bind 2: dty=1 mxl=32(18) mal=00 scl=00 pre=00 oacflg=00 oacfl2=0000 size=0 offset=152 ^C bfp=ffffffff7b991828 bln=32 avl=18 flg=01

   value="AAa8Q5ADYAAAAUQAAn"

EXEC #8:c=0,e=156349,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=25146063956032
FETCH #8:c=0,e=250,p=0,cr=3,cu=0,mis=0,r=215,dep=1,og=1,tim=25146064003460
FETCH #8:c=0,e=241,p=0,cr=1,cu=0,mis=0,r=215,dep=1,og=1,tim=25146064022705

STAT #8 id=1 cnt=0 pid=0 pos=1 obj=7062703 op='INDEX RANGE SCAN DR$MYPOLICY$X (cr=0 pr=0 pw=0 time=0 us)'

--

http://www.freelists.org/webpage/oracle-l Received on Fri Feb 26 2010 - 09:48:15 CST