RE: database server CPU is 100%

From: Koivu, Lisa <lkoivu_at_qode.com>
Date: Fri, 21 Jul 2000 15:49:15 -0400
Message-Id: <10565.112679@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01BFF34C.C03FA9C8
Content-Type: text/plain;

charset="iso-8859-1"

Mike, if that's what you suspect, wouldn't this be evident in your Library cache hit ratio? I don't think a super spendy tool is always necessary to see what's going on.

Lisa
Ft. Lauderdale, FL, USA

-----Original Message-----
From: Mike & Martha [mailto:mmrose_at_home.com] Sent: Friday, July 21, 2000 2:50 PM
To: Multiple recipients of list ORACLE-L Subject: Re: database server CPU is 100%

Hi,

I'd try using a tool like Quest Software's SQL Lab and see if you aren't having problems with many different applications and/or dynamic queries using SQL statements that are just slightly different from each other. This prevents code re-use (SQL Statements), causes the database to have to reparse the 'new' (different) SQL Statements, and most of all causes the contents of the SQL Cache to 'thrash'. There is good reason to have ALL of the 'Production Code' for each Instance analyzed/approved by a DBA.

Michael Rose

mmrose_at_home.com <mailto:mmrose_at_home.com>

Original Message ----- From: Gautam_Reddy_at_Dell.com <mailto:Gautam_Reddy_at_Dell.com> To: Multiple recipients of list ORACLE-L <mailto:ORACLE-L_at_fatcity.com> Sent: Friday, July 21, 2000 12:03 PM Subject: database server CPU is 100%

We have a v7.3.4 production OLTP database on 4 CPU NT 4.0 server. The last 2 weeks the CPU is pegged to 100% at least 80% of the time. Nothing has changed in the last two weeks. We don't have any long running jobs. We have taken system state dumps and found nothing there too(done this with the help of oracle support). We have flushed the share pool couple of times to see if it was related to parsing problems. The minute we flush the CPU usage comes down to 50-60% but goes back up to 100% within 15min. Our NT admins don't see any problems with the server.

Has anyone encountered this? Are they any specific things should I be looking for. Any pointers would be appreciated.

Thx
Gautam

------_=_NextPart_001_01BFF34C.C03FA9C8
Content-Type: text/html;

charset="iso-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">

<META content="MSHTML 5.50.4134.600" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><SPAN class=145514119-21072000><FONT face=Arial color=#0000ff size=2>Mike,
if that's what you suspect, wouldn't this be evident in your Library cache hit ratio?  I don't think a super spendy tool is always necessary to see what's going on.</FONT></SPAN></DIV>
<DIV><SPAN class=145514119-21072000><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=145514119-21072000><FONT face=Arial color=#0000ff
size=2>Lisa</FONT></SPAN></DIV>
<DIV><SPAN class=145514119-21072000><FONT face=Arial color=#0000ff size=2>Ft.
Lauderdale, FL, USA</FONT></SPAN></DIV>
<DIV><SPAN class=145514119-21072000><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=145514119-21072000></SPAN><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Mike & Martha [mailto:mmrose_at_home.com]<BR><B>Sent:</B> Friday, July 21, 2000 2:50 PM<BR><B>To:</B> Multiple recipients of list ORACLE-L<BR><B>Subject:</B> Re: database server CPU is 100%<BR><BR></DIV></FONT>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">

  <DIV><FONT face=Arial size=2>Hi,</FONT></DIV>
  <DIV>&nbsp;</DIV>
  <DIV><FONT face=Arial size=2>I'd try using a tool like Quest Software's 
  <STRONG>SQL Lab </STRONG>and see if you aren't having problems with many

different applications and/or dynamic queries using SQL statements that are just slightly different from each other. This prevents code re-use (SQL Statements), causes the database to have to reparse the 'new' (different) SQL Statements, and most of all causes the contents of the <STRONG>SQL Cache</STRONG> to 'thrash'. There is good reason to have ALL of the 'Production Code' for each <STRONG>Instance</STRONG> analyzed/approved by a DBA.</FONT></DIV>

  <DIV>&nbsp;</DIV>
  <DIV><FONT face=Arial size=2>Michael Rose</FONT></DIV>
  <DIV>&nbsp;</DIV>
  <DIV><FONT face=Arial size=2><A

  href="mailto:mmrose_at_home.com">mmrose_at_home.com</A></FONT></DIV>   <DIV> </DIV>
  <BLOCKQUOTE
  style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">     <DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>     <DIV
    style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>     <A title=Gautam_Reddy_at_Dell.com
    href="mailto:Gautam_Reddy_at_Dell.com">Gautam_Reddy_at_Dell.com</A> </DIV>     <DIV style="FONT: 10pt arial"><B>To:</B> <A title=ORACLE-L_at_fatcity.com     href="mailto:ORACLE-L_at_fatcity.com">Multiple recipients of list ORACLE-L</A>     </DIV>
    <DIV style="FONT: 10pt arial"><B>Sent:</B> Friday, July 21, 2000 12:03     PM</DIV>
    <DIV style="FONT: 10pt arial"><B>Subject:</B> database server CPU is     100%</DIV>
    <DIV><BR></DIV>
    <DIV><FONT face="Comic Sans MS" color=#800000 size=2><SPAN     class=177033714-21072000>We have a v7.3.4 production OLTP database on 4     CPU  NT 4.0 server.  The last 2 weeks the CPU is pegged to 100% at     least 80% of the time. Nothing has changed in the last two weeks. We don't     have any long running jobs. We have taken system state dumps and found     nothing there too(done this with the help of oracle support).  We have     flushed the share pool couple of times to see if it was related to parsing     problems. The minute we flush the CPU usage comes down to 50-60% but goes     back up to 100% within 15min. Our NT admins don't see any problems with the     server. </SPAN></FONT></DIV>
    <DIV><FONT face="Comic Sans MS" color=#800000 size=2><SPAN     class=177033714-21072000></SPAN></FONT> </DIV>     <DIV><FONT face="Comic Sans MS" color=#800000 size=2><SPAN     class=177033714-21072000>Has anyone encountered this? Are they     any specific things should I be looking for.  Any pointers     would be appreciated.</SPAN></FONT></DIV>

    <DIV>&nbsp;</DIV>
    <DIV><FONT face="Comic Sans MS" color=#800000 size=2>Thx</FONT></DIV>
    <DIV><FONT face="Comic Sans MS" size=2><STRONG>Gautam </STRONG></FONT></DIV>

Received on Fri Jul 21 2000 - 14:49:15 CDT