Re: Low CPU time, no Wait time but high elapsed time

at least the OP is not about "waits" that actually burn CPU :-)




________________________________
From: Tanel Poder <tanel_at_tanelpoder.com>
To: exriscer_at_gmail.com
Cc: rajendra.pande_at_ubs.com; toon.koppelaars@rulegen.com; oracle-l@freelists.org
Sent: Monday, August 8, 2011 4:23 PM
Subject: Re: Low CPU time, no Wait time but high elapsed time


uninstrumented wait events can be the other reason .... pstack and truss would help in such cases ... 

There's a bug in up to Oracle 10.2.0.4 and 11.1.0.6 for example where the waits for external table IO are not instrumented and Oracle (plus all Oracle tools, like ASH) will think the process is on CPU. I wrote about it here:

http://blog.tanelpoder.com/2007/06/18/advanced-oracle-troubleshooting-guide-when-the-wait-interface-is-not-enough-part-1/

There are more bugs/uninstrumented waits in Oracle... old ones get fixed, new ones get introduced.

--
Tanel Poder
Free hacking session tomorrow :) -> http://blog.tanelpoder.com/seminar/secret/




On Mon, Aug 8, 2011 at 10:54 PM, LS Cheng <exriscer_at_gmail.com> wrote:

I am not sure how LPAR works but 1 second per DML and no I/O waits is really strange, I cannot find any reasoning except what Toon stated
>
>Thanks
>
>
>On Fri, Aug 5, 2011 at 1:39 PM, <rajendra.pande_at_ubs.com> wrote:
>
>  
>>I did not
think LPAR was managed in a similar manner like a VM. 
>>My
understanding was that in a LPAR the resources are hard bound when the LPAR
gets created 
>>You can
move them around later but still this is not the same as a hypervisor.
>>Once moved
the resources stay with the new LPAR – but I am not sure. Maybe the newer
LPARs are hypervised.
>> 
>>
>>________________________________
>> 
>>From:oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of LS Cheng
>>Sent: Friday, August 05, 2011 3:17
AM
>>To: Toon Koppelaars
>>Cc: Oracle Mailinglist
>>Subject: Re: Low CPU time, no Wait
time but high elapsed time
>> 
>>aha so this might mean
the box was saturated and the LPAR couldnt get CPU fast enough?
>>
>>Thanks
>>
>>
>>On Fri, Aug 5, 2011 at 8:07 AM, Toon Koppelaars <toon.koppelaars_at_rulegen.com>
wrote:
>>That could explain it. I have had experiences where a trace-file of a
slow session did not record any waittime with similar observation like yours.
>>This was due to the fact that the 'slow session' was running inside a VM that
did not get a lot of cpu-cycles allocated from the VM manager.
>> 
>>On Fri, Aug 5, 2011 at 7:45 AM, LS Cheng <exriscer_at_gmail.com>
wrote:
>>The catalog database runs in side an IBM P780 Logical Partition (LPAR)
>>
>>
>>Thanks
>>
>>
>>
>>On Fri, Aug 5, 2011 at 7:42 AM, Toon Koppelaars <toon.koppelaars_at_rulegen.com>
wrote:
>>Is this a virtualized environment?
>>
>>
>>
>>On Fri, Aug 5, 2011 at 7:38 AM, LS Cheng <exriscer_at_gmail.com>
wrote:
>>Hi
>>
>>The other day while I was troubleshooting with 10046 some RMAN resync issues
with the catalog (was taking very long time) I noticed that insert and update
statements were taking extremely long time, one second per execution more or
less. The 10046 trace for the RMAN catalog database session showed cpu time
10000 microseconds, no waits and elapsed around 1200000 microseconds. This is
10.2.0.5 running on AIX.
>>
>>I did some research and couldnt find any good reason.
>>
>>Anyone come across with this sort of issue?
>>
>>
>>Thanks!
>>
>>--
>>LSC
>>
>>
>>
>>-- 
>>Toon Koppelaars
>>RuleGen BV
>>Toon.Koppelaars_at_RuleGen.com
>>www.RuleGen.com
>>TheHelsinkiDeclaration.blogspot.com
>>
>>(co)Author: "Applied Mathematics for Database Professionals"
>>www.rulegen.com/am4dp-backcover-text
>> 
>>
>>
>>
>>-- 
>>Toon Koppelaars
>>RuleGen BV
>>Toon.Koppelaars_at_RuleGen.com
>>www.RuleGen.com
>>TheHelsinkiDeclaration.blogspot.com
>>
>>(co)Author: "Applied Mathematics for Database Professionals"
>>www.rulegen.com/am4dp-backcover-text
>> 
>>Please visit our website at
>>http://financialservicesinc.ubs.com/wealth/E-maildisclaimer.html
>>for important disclosures and information about our e-mail
>>policies. For your protection, please do not transmit orders
>>or instructions by e-mail or include account numbers, Social
>>Security numbers, credit card numbers, passwords, or other
>>personal information.
>>
>

--
http://www.freelists.org/webpage/oracle-l