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: Mladen Gogala <mgogala_at_adelphia.net>
Date: Sun, 18 May 2003 22:31:37 -0800
Message-ID: <F001.0059C362.20030518223137@fatcity.com>


So, what is spending the CPU? Why is a CPU running pedal to the medal a problem? I thought that CPU hardware was bought in order to run and not to sit idle? The same goes for the memory. There are some people, mostly known as sys admins that will try telling you that "we need to keep CPU / memory N% free". Well, hardware is bought to be utilised. Nobody will stop a CPU for speeding (unlike myself and my passion for speeds above 80mph). CPU running at 100% is not a problem. An application not performing adequately is a problem.

On 2003.05.19 01:36 Dilip wrote:
> I have 6 CPUs in the server and have 2 different ora Apps instances running. Sometimes utilization goes to 100% e.g. at the month end time.
>
> I think this is not a problem unless CPU run queue is greater than the number of CPUs. In last few days I have noticed some processes which were running for more than 50 hours on the machine. These are all the processes which are stuck because of such latch free events.
>
> I will try lowering the shared pool. As per Diego's suggestion I looked at v$sqlarea and most of the sqls have version count as 1. We are having ora Apps without any custmized code so I think most of the queries have bind variables used and sql sharing does happen.
>
> Regards,
> ~Dilip
>
>
>
> ORACLE-L_at_fatcity.com wrote:
>
>
>
> 100% CPU utilization will cause any running process to be extremely slow
> finishing what it's supposed to do while holding any needed shared
> resources.
>
> High cpu utilization could be the cause if the apps need more CPU power than
> the available or could be symptom of another problem (bad sql, bug, tuning).
>
> My suggestion, if your shared pool is really 800M, lower it to 200M, and see
> how it performs.
>
> Also that process that eats the CPU time, needs to be investigated, and
> tuned.
>
> 100% cpu utilization can not be reached except having all the CPUs busy all
> the time.
>
> How many CPU in the server?
>
>
> Waleed
>
> -----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: 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).
>

-- 
Mladen Gogala
Oracle DBA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mgogala_at_adelphia.net

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 Mon May 19 2003 - 01:31:37 CDT

Original text of this message

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