RE: RE: Where is the memory gone?

From: Kimberly Smith <ksmith2_at_myfirstlink.net>
Date: Mon, 10 Dec 2001 07:28:17 -0800
Message-ID: <F001.003D8D9D.20011210065020@fatcity.com>

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 '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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kimberly Smith
  INET: ksmith2_at_myfirstlink.net

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 Mon Dec 10 2001 - 09:28:17 CST