Re: INSERT...SELECT pegs CPU, but is waiting on scattered read?

From: Mladen Gogala <mladen@xxxxxxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Date: Thu, 29 Apr 2004 12:44:24 -0400

Rich, 
On 04/29/2004 12:22:45 PM, "Jesse, Rich" wrote:
> Hey all,

 Yeah? Whaddya want now? May watch NASCAR in peace and quiet? Ya know,
the Non-Athletic Sport Created Around Redn... OK, it's not important.

> eliminate most of the I/O, but I'm curious as to the troubleshooting of this
> from a systems perspective -- as though I didn't have access to the SQL.

Rich, the "top" utility should show you majority of the CPU time spent in 
"system"
mode, because I/O's are, essentially, interrupts.  CPU executing I/O request 
elevates
the IPL (Interrupt Priority Level) and announces that to the bus, so that no 
other CPU
can do the same, copies user buffer to system buffer and schedules a soft 
interrupt
for further processing. After that, it lowers the IPL back to the normal level. 
soft interrupt
waits for any higher priority interrupts (clock, network card), then elevates 
the IPL to
the level which doesn't block others and sends the data to the disk device. If 
you have
many scattered reads (a.k.a "readv"), you should see a whole lot of system mode 
because
CPU's will schedule a lot of interrupts and will be waiting for the other 
processors to
lower down their IPL. If your files are on the raw devices, then sar -b will 
show many more
pread's (partition read="raw read" ) then usual. Those two things come off the 
top my head.
I don't know iostat all that well (I'm a honest SYSV guy, not one of those 
BSD-hippies),
but there must be a way to detect significantly increased I/O activity using 
that, as well.

-- 
Mladen Gogala
Oracle DBA

Note:
This message is for the named person's use only.  It may contain confidential, 
proprietary or legally privileged information.  No confidentiality or privilege 
is waived or lost by any mistransmission.  If you receive this message in 
error, please immediately delete it and all copies of it from your system, 
destroy any hard copies of it and notify the sender.  You must not, directly or 
indirectly, use, disclose, distribute, print, or copy any part of this message 
if you are not the intended recipient. Wang Trading LLC and any of its 
subsidiaries each reserve the right to monitor all e-mail communications 
through its networks.
Any views expressed in this message are those of the individual sender, except 
where the message states otherwise and the sender is authorized to state them 
to be the views of any such entity.

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

References:
- INSERT...SELECT pegs CPU, but is waiting on scattered read?
  - From: Jesse, Rich

Re: INSERT...SELECT pegs CPU, but is waiting on scattered read?

Other related posts: