Re: Shared sql area in 10g versus 9i

From: Zoran Martic <zoran_martic_at_yahoo.com>
Date: Wed, 24 Aug 2005 17:45:56 -0700 (PDT)
Message-ID: <20050825004556.28419.qmail@web52604.mail.yahoo.com>

Thanks 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.

It looks that the problem is when kept_versions in v$sql starts then the sql area for the cursor starts growing up because of child cursors.

It looks that something is keeping the child cursors kept in memory now in 10g.

Anybody, why 10g is doing much more kept operations related to cursors?

Regards,
Zoran

Terry Sutton <terrysutton_at_usa.net> wrote:

> 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
>
>
> > Does anybody know what is the difference between
> the
> > space needed for the same cursor (SQL) in 9i and
> 10g?
> >
> > I have heard about some 2x difference between 8i
> and
> > 10g.
> > This will lead to "dramatic" shared_pool_size
> increase
> > in 10g for havy loaded databases.
> >
> > Anybody has some mathematics available.
> >
> > I am very curious, because it looks that 10g is
> much
> > more hungry for the cursor space in SQL Area piece
> of
> > shared pool.
> >
> > All parameters are the same (except maybe
> > undocumented) in 9i and 10g and still it looks
> that
> > the cursors in 10g eating much more space.
> >
> > Regards,
> > Zoran
> >
> >
> >
> >
> ____________________________________________________
> > Start your day with Yahoo! - make it your home
> page
> > http://www.yahoo.com/r/hs
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

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

Received on Wed Aug 24 2005 - 19:48:19 CDT