Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> INSERT...SELECT pegs CPU, but is waiting on scattered read?

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

From: Jesse, Rich <>
Date: Thu, 29 Apr 2004 11:22:45 -0500
Message-ID: <>

Hey all,

I'm trying to help our folks get an procedure down from 18 hours without a complete redesign (which it desperately needs), so I run a 10046 trace on it from the test system. It merrily hops along the trail of INSERT...SELECTs (five of them, looped five times to create five tables), until the last INSERT...SELECT on the first loop. The trace shows:

WAIT #26: nam='db file scattered read' ela= 0 p1=7 p2=57145 p3=8 nauseum, but each of these lines in the trace takes at least a couple of seconds to show while viewing the trace using "tail -f". Previous data in the trace showed up with a typical "tail -f" batching of several lines or pages per second. While in this "state", HP-UX's GlancePlus ("gpm") shows the server process pegging the single CPU on the test system, while doing little or no I/O.

Sometimes, the process breaks loose and continues on it's I/O trashing, then back to the CPU-hogging super slow mode. My knee-jerk is that the data's buffered during the CPU-hogging, but I'm at a loss as to what steps to take next to determine what's really going on.

As this process is creating a mini-DM, there's going to be significant I/O. I plan on reducing the 25 FTSs (five loops of five INSERT...SELECTs) to 1 to 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.

I know, I know. "Finish with Cary's book." My six-month old isn't enjoying the "Method R" bedtime stories anymore, so the book's been relegated to my "library". <sigh>


Rich Jesse                        System/Database Administrator      QuadTech, Sussex, WI USA

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.

Archives are at FAQ is at
Received on Thu Apr 29 2004 - 11:23:53 CDT

Original text of this message