Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: latch free - library cache

RE: latch free - library cache

From: Arun Chakrapanirao <ArunC_at_1800FLOWERS.com>
Date: Thu, 15 May 2003 06:31:51 -0800
Message-ID: <F001.0059989B.20030515063151@fatcity.com>


There is a x$table called X$kgllk in this table you can see who is running that particular sql link this along with the v$session to get the logon time then u can catch the guy who is running that sql

select a.sid,to_char(a.logon_time,'dd-mm-yyyy hh24:mi:ss'), b.KGLNAOBJ from v$session a,x$kgllk b
where b.kgllksnm=a.sid
and b.KGLNAOBJ like '%ATS_SYS_PARAMETERS_at_IOTTOSM%' order by a.logon_time

-----Original Message-----
Sent: Thursday, May 15, 2003 7:22 AM
To: Multiple recipients of list ORACLE-L

CPU utilization was 100 % on the server. In fact we were looking at the top command. From that came to know about one very long running oracle process. Then traced that process to this perticular problem.

~Dilip       

ORACLE-L_at_fatcity.com wrote:

What is the cpu utilization of the server?

Waleed

-----Original Message-----
Sent: Wednesday, May 14, 2003 6:37 AM
To: Multiple recipients of list ORACLE-L

Hi List,

DB 8.1.7.4.0 on Sun sparc solaris 2.6
This is oracle applications 11.5.4

One of the program was running for very long time. So checked out v$session_wait for that perticular session. It was waiting on 'latch free' and from P2 column and v$latch, found out that it is waiting for library cache latch.
Now docs says this is related to shared pool fragmentation. So checked v$sgastat where it showed 38M space free in the shared pool (Total shared pool is 800 MB). But it didnt throw ORA-4031 and the process was running for last 17 hours. I looked at v$librarycache and v$latchholder but didn't get any clue.
At last, I flushed the shared pool but still that session was waiting on same wait event. Finally I had to kill the session. I am facing this situation once or twice in production and everytime I can't kill the session.

Can somebody tell me how should I debug furthur and go to the root cause of the problem. Does this mean I need to increse the shared pool ? CURSOR_SHARING is kept to FORCE.

Thanks,
~Dilip

Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com

Buy The Best In BOOKS at http://www.bestsellers.indiatimes.com

Bid for for Air Tickets @ Re.1 on Air Sahara Flights. Just log on to http://airsahara.indiatimes.com and Bid Now !

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Dilip
INET: dilip7772002_at_indiatimes.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services



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.net
--

Author: Khedr, Waleed
INET: Waleed.Khedr_at_FMR.COM

Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services



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).

Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com

 Buy The Best In BOOKS at http://www.bestsellers.indiatimes.com

Bid for for Air Tickets @ Re.1 on Air Sahara Flights. Just log on to http://airsahara.indiatimes.com and Bid Now !

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Dilip
  INET: dilip7772002_at_indiatimes.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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.net
--

Author: Arun Chakrapanirao
  INET: ArunC_at_1800FLOWERS.com
Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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 Thu May 15 2003 - 09:31:51 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US