Re: Shared sql area in 10g versus 9i

From: Zoran Martic <zoran_martic_at_yahoo.com>
Date: Fri, 26 Aug 2005 13:58:12 -0700 (PDT)
Message-ID: <20050826205812.73572.qmail@web52610.mail.yahoo.com>

The paper is great.
I need to agree too.

The problem with needing more shared pool for sql area cache is because of Oracle PL/SQL caching behaviour change while having session_cached_cursors=0 (per Tom Kyte it then uses twice the default that is 50 = 100).

Very ugly thing when having remote PL/SQL calls over the links causing the same SQL to have so many versions in sql area and they are all kept in shared pool. Crazy behaviour.

Watch your shared pool in 10g :)
It can blow.

Zoran

• Terry Sutton <terrysutton_at_usa.net> wrote:

> Plus, in the case of the paper, the upgrade was from
> 32-bit to 64-bit.
>
> --Terry
>
> > I have already seen that and read it.
> >
> > Basically this is because of the introduction of
> > cached execution plans in 9i.
> > 10g does not occupy too much more space then 9i. I
> > checked that.
>
> > > From "An Oracle 10g Upgrade Case Study: Looking
> at
> > > System Performance Before
> > > and After the Upgrade," by Roger Schrag
> (February
> > > 2005)
> > >
> >

>
(http://www.dbspecialists.com/presentations/case_study_10g.html):
> > >
> > > "In addition to the shared pool having less
> usable
> > > space in Oracle 10g for
> > > the same shared_pool_size setting, it also
> appears
> > > that individual SQL
> > > statements occupy more space in Oracle 10g's
> shared
> > > SQL area than Oracle 8i's-in
> > > our case almost twice as much."
> > >
> > > Detailed stats are in the paper.
> > >
> > > --Terry
>
> --
> http://www.freelists.org/webpage/oracle-l
>
                

---

Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs  

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Aug 26 2005 - 16:00:20 CDT