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

Hey all,

I'm trying to help our folks get an 8.1.7.4.0 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

...ad 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>

TIA,
Rich

Rich Jesse                        System/Database Administrator
rich.jesse@xxxxxxxxxxxxxxxxx      QuadTech, Sussex, WI USA
----------------------------------------------------------------
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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: