catsearch and latch: cache buffers chains waits
- From: Dba DBA <oracledbaquestions@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 26 Feb 2010 09:23:57 -0500
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)'
Other related posts:
