Some things you could look at:
- implement a very agressive keep-ing policy using
dbms_shared_pool to limit fragmentation
- once (a) is done, you can do a shared pool flush
without having much impact.
Are you parsing a lot? If you are, you may get some
joy with the cursor_sharing parameter - but this is
known to have some bugs here and there in the earlier
8i versions.
hth
connor
- "Gupta, Brijesh" <Brijesh.Gupta_at_Airliquide.com>
wrote: > Thanks. Yes its shared pool and Library
cache.
> Shared pool was 4 GB initially I reduced it to 1.5
> GB
> then we had problem and we increased it to 1.9 GB.
>
> We can't go to 8.1.6 new as it requires lot of
> testing and users are not
> ready for that.
>
> I guess I will have to live with that till they
> decide to go to 8.1.6.
>
> Thanks
>
> -----Original Message-----
> Sent: Monday, October 01, 2001 5:41 PM
> To: Multiple recipients of list ORACLE-L
>
>
> What is in V$LATCH. If its shared pool and library
> cache contention I'd be tempted to:
>
> a) upgrade to 8.1.6+ where the algorithm is better
> b) reduce the size of your shared pool
>
> hth
> connor
>
> --- "Gupta, Brijesh" <Brijesh.Gupta_at_Airliquide.com>
> wrote: > Hi all
> > My database almost hangs and when I query the
> > v$session_wait I found
> > latch contention.
> > we are running on Oracle 8.0.5 (64bit) Oracle
> > application 10.7 SC.
> > Shared pool is 1.9GB and SGA 9GB.
> > I have to flush the shared_pool to get rid of the
> > problem.
> >
> > Can any one help me in finding the permanent
> > solution of this problem.
> >
> > Brijesh
> >
> > Wait Sec
> in
> > EVENT time SID
> Wait
> > STATE
> > BLOCK
> > ------------------------------ ------ -------
> > ------- -------------------
> > --------------------------
> > latch free 0 40
>
> > 1 WAITING
> > address=pp92556056 number=60 tries=25
> > latch free 0 43
>
> > 0 WAITING
> > address=9465536000 number=53 tries=1
> > latch free -1 89
>
> > 0 WAITED SHORT TIME
> > address=pp92559080 number=60 tries=0
> > latch free 0 47
>
> > 0 WAITING
> > address=pp92556056 number=60 tries=1
> > latch free -1 190
>
> > 0 WAITED SHORT TIME
> > address=9465548816 number=59 tries=0
> > latch free 0 281
>
> > 1 WAITING
> > address=pp92556488 number=60 tries=0
> > latch free 1 317
>
> > 2 WAITED KNOWN TIME
> > address=9465548816 number=59 tries=0
> > latch free -1 343
>
> > 1 WAITED SHORT TIME
> > address=9465548816 number=59 tries=0
> > latch free 0 391
>
> > 2 WAITING
> > address=pp92556200 number=60 tries=25
> > latch free 0 369
>
> > 2 WAITING
> > address=pp92555768 number=60 tries=26
> > latch free 0 631
>
> > 1 WAITING
> > address=pp92556344 number=60 tries=0
> > latch free -1 627
>
> > 0 WAITED SHORT TIME
> > address=pp92559080 number=60 tries=0
> > latch free 0 600
>
> > 0 WAITING
> > address=pp92556344 number=60 tries=0
> > latch free 1 577
>
> > 1 WAITED KNOWN TIME
> > address=pp92559224 number=60 tries=0
> > latch free -1 465
>
> > 1 WAITED SHORT TIME
> > address=9465548816 number=59 tries=1
> > latch free 0 448
>
> > 0 WAITING
> > address=pp92557208 number=60 tries=0
> > latch free 0 414
>
> > 0 WAITING
> > address=pp92556920 number=60 tries=0
> > latch free 0 409
>
> > 2 WAITING
> > address=pp92555912 number=60 tries=26
> > latch free 0 950
>
> > 0 WAITING
> > address=pp92556632 number=60 tries=2
> > latch free 0 949
>
> > 0 WAITING
> > address=pp92555768 number=60 tries=1
> > latch free 0 930
>
> > 0 WAITING
> > address=pp92556776 number=60 tries=1
> > latch free 0 915
>
> > 0 WAITING
> > address=pp92556920 number=60 tries=0
> > latch free 0 901
>
> > 0 WAITING
> > address=pp92557064 number=60 tries=0
> > latch free -1 889
>
> > 0 WAITED SHORT TIME
> > address=9465536000 number=53 tries=0
> > latch free 0 858
>
> > 0 WAITING
> > address=pp92556344 number=60 tries=1
> > latch free 1 832
>
> > 1 WAITED KNOWN TIME
> > address=pp92559224 number=60 tries=0
> > latch free 0 827
>
> > 0 WAITING
> > address=pp92556200 number=60 tries=1
> > latch free 0 826
>
> > 0 WAITING
> > address=pp92556344 number=60 tries=0
> > latch free 0 820
>
> > 0 WAITING
> > address=pp92556776 number=60 tries=0
> > latch free 0 805
>
> > 0 WAITING
> > address=pp92556344 number=60 tries=1
> > latch free 0 757
>
> > 0 WAITING
> > address=pp92555768 number=60 tries=1
> > latch free 0 718
>
> > 1 WAITING
> > address=pp92556632 number=60 tries=0
> > latch free 0 697
>
> > 0 WAITING
> > address=pp92556056 number=60 tries=1
> > latch free 0 666
>
> > 0 WAITING
> > address=pp92556200 number=60 tries=0
> > latch free 0 1154
>
> > 0 WAITING
> > address=pp92555912 number=60 tries=1
> > latch free 0 1138
>
> > 0 WAITING
> > address=pp92556200 number=60 tries=0
> > latch free 0 1132
>
> > 0 WAITING
> > address=9465536000 number=53 tries=1
>
=== message truncated ===
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)
"Some days you're the pigeon, some days you're the statue"
Do You Yahoo!?
Get your free @yahoo.co.uk address at
http://mail.yahoo.co.uk
or your free @yahoo.ie address at
http://mail.yahoo.ie
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
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 Oct 02 2001 - 15:47:09 CDT
