Re: catsearch and latch: cache buffers chains waits
- From: Dba DBA <oracledbaquestions@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 2 Mar 2010 08:41:12 -0500
thanks for the responses. So for the delayed responses... been out of the
office.
The problem isn't the latch waits. The problem is that the latch waits are
caused by the CATSEARCH query being executed for each row in a table. I can
see this by running a 10046 trace, doing a tail -f on the 10046 trace and
seeing that the catsearch trace runs over and over again.
I kill the query part way through it and then I do a grep and wc -l to count
the number of executions of the CATSEARCH query and it was into the
millions.
These re-execution of the CATSEARCH for what appears to be EVERY row in one
of my tables appears to be what is causing the latching. Since I get the
latch wait while the constant fetching is occurring. This appears to be
extremely inefficient. It appears to work like a typical user created
function in where clause. So it executes for each row.
For the guy who talked about the settings of the domain index. I don't know
alot about domain indexes. What are you referring to?
On Fri, Feb 26, 2010 at 11:10 AM, Tanel Poder <tanel@xxxxxxxxxx> wrote:
> Also, when you looked into V$SESSION, did you also check what the STATE
> column was or just the EVENT?
>
> State column is what you should check first, look into the event ONLY when
> STATE=WAITING. If it's anything else than WAITING then the event is
> completely irrelevant as the session isn't waiting for anything but is on
> CPU
>
> --
> Tanel Poder
> http://tech.e2sn.com
> http://blog.tanelpoder.com
>
>
>
> On Sat, Feb 27, 2010 at 12:08 AM, Tanel Poder <tanel@xxxxxxxxxx> wrote:
>
>> First I'd ask that how big % of your session's response time was wasted
>> waiting for latches? Sometimes people troubleshoot the wrong symptom (like
>> cases where 99% of response time was spent on CPU and 1% waiting for
>> latches).
>>
>>
>>
Other related posts:
