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
> '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).
ENG. Christian Trassens
Senior DBA
ctrassens_at_yahoo.com
christian_trassens_at_yahoo.es
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: ctrassens_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 Mon Dec 10 2001 - 10:18:44 CST
