Some things you could look at: a) implement a very agressive keep-ing policy using dbms_shared_pool to limit fragmentation b) 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" <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> > 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: [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).
