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

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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------