Re: Disappearing Sessions - Can Code Alone Cause It?

On Feb 27, 5:29 pm, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> "Mark D Powell" <Mark.Pow..._at_eds.com> wrote in messagenews:934bfb09-31fe-4248-9a17-768efca843c6_at_k2g2000hse.googlegroups.com...
> On Feb 27, 5:00 am, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
>
> > Oracle 10.2.0.3.0
> > Windows 2003 Server
> > 16GB Memory (93% SGA( 93% Buffer Cache,<3% Shared Pool, <3% Large Pool),
> > 7%
> > PGA)
>
> > Our off-site DBA is saying our code is causing the disappearing sessions
> > because, to paraphrase him: that's the database it has to run in. That's
> > why hes telling us to do things like incremental commits.
>
> > My question is: can code alone cause sessions to sometimes run to
> > completion but other times just disappear without a trace?
>
> > I have a feeling he's trying to make us do work-arounds rather than trying
> > to get to the actual root cause of this problem.
>
> I am not sure I understand the memory percentages you posted but if
> the PGA is 7% of the 16G and the SGA is 93% then the PGA is probably
> way too small.  Plus some memory should be left for the OS and the
> posted numbers do not show it.
>
> What kind of database is this (OLTP, Warehouse, DSS, OLAP)?
>
> Are there error messages in the alert log related to the "disappeared"
> sessions?  Are there trace files in the udump destination?
>
> There should be clues.  Is connection pooling in use?
>
> HTH -- Mark D Powell --
>
> === Here is the memory breakdown
>
> Total 16GB of memory on server
>
> Total allocated to Oracle 12,800GB allocated as follows:
>
> SGA  - 93.75% (12000MB)
> o        shared pool     - 3.5% (416MB)
> o        buffer cache    - 94.3% (11312MB)
> o        large pool        - 0.4% (48MB)
> o        java pool         - 1.3% (160MB)
> o        other               - 0.5% (64MB)
>
> PGA - 6.25% (800MB)
Is automatic memory managment in use for the SGA. If not you might want to ask the DBA why it is not in use.

Are the disappearing sessions dedicated sessions or shared server sessions?

I agree with Joel in that if you open an SR with Oracle support you want to provide them as much information as possible on the initial post. Trace files, alert log messages would definitely be a benefit even if you just ask "are there any know bugs with version X.x.x. where sessions just vanish?".

There is no telling how much experience the analyst you get is going to have but even an experienced analysist needs something to search on such as an Oracle error message, a session strack trace, etc.... otherwise it will be pure luck if the analyst knows of such a problem.

If you have none of this then at least provide the RDA report.

• Mark D Powell --