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 > latch free 0 1068 > 0 WAITING > address=pp92556776 number=60 tries=2 > latch free 0 1058 > 0 WAITING > address=9465536000 number=53 tries=1 > latch free 1 1037 > 0 WAITED KNOWN TIME > address=pp92557064 number=60 tries=0 > latch free -1 963 > 0 WAITED SHORT TIME > address=pp92556200 number=60 tries=1 > latch free 0 952 > 0 WAITING > address=pp92556776 number=60 tries=1 > latch free 1 1425 > 0 WAITED KNOWN TIME > address=pp92556200 number=60 tries=0 > latch free 0 1421 > 2 WAITING > address=pp92555768 number=60 tries=29 > latch free 0 1412 > 0 WAITING > address=pp92556344 number=60 tries=0 > latch free 0 1398 > 0 WAITING > address=9465536000 number=53 tries=1 > latch free -1 1385 > 1 WAITED SHORT TIME > address=9465548816 number=59 tries=0 > latch free 1 1357 > 0 WAITED KNOWN TIME > address=9465536000 number=53 tries=1 > latch free 0 1338 > 2 WAITING > address=pp92555912 number=60 tries=25 > latch free 0 1330 > 2 WAITING > address=pp92556200 number=60 tries=19 > latch free 0 1301 > 0 WAITING > address=pp92556200 number=60 tries=0 > latch free -1 1239 > 0 WAITED SHORT TIME > address=9465548816 number=59 tries=1 > latch free 0 1188 > 2 WAITING > address=pp92556200 number=60 tries=18 > latch free -1 1186 > 0 WAITED SHORT TIME > address=9465548816 number=59 tries=0 > latch free -1 1498 > 1 WAITED SHORT TIME > address=pp92557640 number=60 tries=0 > latch free 0 395 > 0 WAITING > address=pp92556488 number=60 tries=0 > latch free 0 333 > 1 WAITING > address=pp92556488 number=60 tries=0 > latch free -1 289 > 1 WAITED SHORT TIME > address=9465548816 number=59 tries=0 > latch free 0 227 > 0 WAITING > address=pp92556056 number=60 tries=0 > latch free 0 129 > 0 WAITING > === 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).
