Re: catsearch and latch: cache buffers chains waits
Date: Tue, 2 Mar 2010 08:41:12 -0500
Message-ID: <f30139791003020541n362d2f05i588a3e94edf24f5f_at_mail.gmail.com>
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_at_poderc.com> 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_at_poderc.com> 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).
>>
>>
>>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 02 2010 - 07:41:12 CST