| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| 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?
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_at_quadtechworld.com QuadTech, Sussex, WI USAPlease see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
 |
 |