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 > '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: [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). ===== ENG. Christian Trassens Senior DBA [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +34-699240979 +34-649824704 __________________________________________________ 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: Christian Trassens 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).