I agree with Christian. We had the same memory leak
problem here before. It turned out there are some
heavily used sql statement without the bind variables.
- Christian Trassens <ctrassens_at_yahoo.com> wrote:
> Answering your first message about the shrink of
> free
> space in shared pool. Check the parsing ratio:
>
> The hard parse with something like this
>
> SELECT substr(sql_text,1,40) "SQL",
> count(*) ,
> sum(executions) "TotExecs"
> FROM v$sqlarea
> WHERE executions < 5
> GROUP BY substr(sql_text,1,40)
> HAVING count(*) > 30
> ORDER BY 2
> ;
>
> The soft parsing something like this:
>
>
> select count(*) from v$sqlarea
> where version_count>5;
>
> And about parsing look for a note in metalink about
> parameter _sqlexec_progression_cost.
>
> Try with cursor_sharing. Also refer to possibles
> bugs
> related in metalink.
>
> And about parsing you can also look in the v$sysstat
> for statistics related.
>
> Check the shared pool latch rate too.
>
> Regards.
>
>
>
>
>
>
> --- Kimberly Smith <ksmith2_at_myfirstlink.net> wrote:
> > Only thing I have to add here is that I was not
> > using MTS so which ever bug I was hitting (doco is
>
> > at the office) it was not related to that.
> >
> > -----Original Message-----
> > Sent: Sunday, December 09, 2001 9:50 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > WinterSun,
> >
> > Hmm, this doesn't sound like bug 1397603. That
> bug
> > manifests itself as a memory leak in the 'State
> > objects' area of the shared pool, not the
> > 'Miscellaneous' area. Bug 1397603 is also fixed
> in
> > the 8.1.7.2 patchset, so if you already have that
> > applied (hint, hint!) you're not encountering this
> > bug
> > and there's no need to set _db_handles_cached = 0.
> >
> > I believe the bug Kimberly is referring to is bug
> > 1240484, which is a process memory leak (i.e.
> > ORA-4030, not ORA-4031) with MTS shared server
> > sessions when there are frequent
> > connect/disconnects.
> > That bug is fixed in 8.1.7.1.
> >
> > If IOT's are involved, you could be hitting bug
> > 1642964 if the IOT is the inner table in a nested
> > loop
> > join. Fixed in 9.0.1. Workaround: set
> > optimizer_index_caching = 1
> >
> > I'm more inclined to believe you're hitting bug
> > 1921561 or bug 1970290, both of which have been
> > awaiting more info from the customer for whom the
> > bug
> > was filed. The workaround for both of them was to
> > set
> > STAR_TRANSFORMATION_ENABLED = FALSE or
> > _db_file_noncontig_mblock_read_count = 1. If you
> > use
> > bitmap indexes, disabling star transformation is
> > likely to cause a noticeable performance hit so
> you
> > might want to try the other workaround.
> >
> > Otherwise, if you can reproduce this in a test
> > environment, it would be helpful to file a tar
> with
> > support so a bug can be filed.
> >
> > HTH,
> >
> > -- Anita
> >
> > --- WinterSun Zhao <wintersun_zhao_at_21cn.com>
> wrote:
> > > Hi, Kimberly:
> > > Thank you for your guide.
> > > I checked Metalink and find that is a bug.
> Bug
> > > No. 1397603.
> > > I think I will add the parameter
> > > _db_handles_cached to 1 later. I will also patch
> > it
> > > to 8.1.7.2 too.
> > > Thank all of you.
> > > B.R.
> > >
> > > > This sound pretty much like the same problem I
> > had
> > > with 8.1.7 on HP.
> > > > They have a memory leak when you
> > > connect/disconnect and it you have
> > > > a lot of those you start to see it in your
> SGA.
> > I
> > > had to patch to
> > > > 8.1.7.1 plus an additional bug fix. Check on
> > > Metalink to see if there
> > > > is the same problem with Windows. It also
> could
> > > be that you really
> > > > do need more then 50M. Pin in the bigger
> > packages
> > > you use (including
> > > > Oracle's) right after startup and see where
> you
> > > are memory wise.
> > > >
> > > > -----Original Message-----
> > > > Zhao
> > > > Sent: Sunday, December 09, 2001 5:30 PM
> > > > To: Multiple recipients of list ORACLE-L
> > > >
> > > >
> > > > Hi, DBAs:
> > > > I find that one of our database's Shared
> > > Pool's memory decreased every
> > > > day.
> > > > It is Oracle 8.1.7 on Windows 2K, with
> 512M
> > > Physical memory.
> > > > When I check v$sgastat, I find the
> > > "miscellaneous" part of "shared pool"
> > > > increased every day, it begans with 500K,
> then,
> > > after two days, it increased
> > > > to 5586228 bytes, after about 10 days, it
> > > increased to 40M, and because I
> > > > had allocated 50M to the Shared Pool, So the
> > > memory available became less
> > > > and less. And I had to shutdown and restart
> the
> > > database when the available
> > > > memory of shared pool is below 5M.
> > > > I want to know why the memory occupied by
> > > "miscellaneous" part is
> > > > increased? The other database on solaris did
> not
> > > increased. How can I find
> > > > out what is it? How to prevent it or resolve
> it
> > > without shutdown and restart
> > > > the database? ( alter system flush shared pool
> > > only flush the sql and
> > > > library cache, and it didn't decrease the
> > > miscellaneous part's memory
> > > > usage. )
> > > > Thank you very much!
> > > >
> > > > WinterSun
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Send your FREE holiday greetings online!
> > http://greetings.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: A. Bardeen
> > INET: abardeen1_at_yahoo.com
> >
> > Fat City Network Services -- (858) 538-5051
> FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
> >
>
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
>
=== message truncated ===
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at
http://shopping.yahoo.com
or bid at
http://auctions.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: CC Harvest
INET: ccharvest_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Tue Dec 11 2001 - 11:51:35 CST
