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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
