Re: Disappearing Sessions - Part 2

On Feb 8, 2:07 pm, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> Oracle 10.2.0.3.0
> Windows Server 2003
> 16 GB of memory
>
> Our off-site DBA had this to say about the disappearing sessions and
> ORA-07445 errors:
>
>  "I think the problem with disappearing sessions is the dispatcher process.
>
>  when we increased the session limit, I did not take into account that there
> are web connections coming in  also.
>
>  ORA-07445 is defenitely points back to Hardware/OS. Since the memory
> upgrade (16 which will need to go to   32), we have not seen any errors."
>
> Does this make sense when:
>
> 1. I'm running my process in Dedicated Server mode, not Shared Server.
>
> 2. If I add just one more nested table that gets loaded in my code my
> process will disappear however if
>  I take it out my process will run to completion.
>
> 3. If I force a full table scan my process will complete.  If I try using a
> FIRST_ROWS hint or change
>  my query to use an extra column join (which seems to cause a hash join) my
> process disappears.
>
> I should also state that just today, after receiving this information from
> our off-site DBA that the database went down (around the time it went down
> there looked like there was massive contention memory-wise) and we have a
> new ORA-07445 error.

Like Anurag said, you should work with Oracle on this, note there is a metalink site for checking these.

Also check the 10.2.0.4 bug list for 7445's, as well as MTS issues and memory leaks. Ths is just pure speculation of course, but I see Bug 6333663 has a workaround for shared pool contention (if that is being seen), remember, it is not necessarily your query that has the problem, but can be everyone else's dogpiling on, with your issue just one symptom out of many. Adding memory and increasing large pool may simply be putting the other issues off for a while. Your #3 sounds familiar, but I can't recall where...

It's a good sign that the DBA admitted s/he didn't account for the web connections, though.

jg

--
@home.com is bogus.
http://news.bbc.co.uk/2/hi/uk_news/7234786.stm