Re: SQL*Net message to client

On Oct 30, 1:05 pm, "astalavista" <nob..._at_nowhere.com> wrote:
> Hi,
>
> the query below waits a lot of time on
> "SQL*Net message to client" (20/30 min)
> and after on "latch free "
> do you know why ?
>
> Thanks in advance
> Oracle 9.2.0.6
>

...

Without looking at this too closely, I'm guessing those full table scans coupled with the IN mean lots of scanning through the table in memory, waiting on latches as Oracle does housekeeping on the SGA. The first things I would investigate:

Are there lots more than 100 rows in that table? Should PF_THINGTYPEATTRIBUTE be the driving table? Does this happen with the queries separated out rather than a union? Is there an index on PF_THINGTYPEATTRIBUTE thingtypeid? Have you investigated EXISTS instead of IN? Is there an actual performance problem? What are the symptoms?

jg

--
@home.com is bogus.
http://en.wikipedia.org/wiki/The_War_of_the_Worlds_(radio)#Public_reaction