| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: latch free - library cache
Dilip,
I'm not sure if this will help but,
I've faced a similar problem some time ago with the
library cache, I set CURSOR_SHARING = FORCE in a 8.1.7
database and my "library cache pin wait" went crazy.
After some tracing (by selecting from x$kglpn and v$session and v$sqlarea), I realized that the problem was that every time the SQL was executed a new SQL version was being generated. A bug of course.
So when sqlarea.version_count was really high we noticed a serious performance problem.
So try checking v$sqlarea...
and take Michael advice and trace the session with
10046 event to get further information.
HTH
Greetings
Diego Cutrone
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
Fat City Network Services -- 858-538-5051
http://www.fatcity.com
San Diego, California -- Mailing list and web hosting
services
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
Fat City Network Services -- 858-538-5051
http://www.fatcity.com
San Diego, California -- Mailing list and web
hosting services
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 - 12:21:44 CDT
 |
 |