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

From: Singer, Phillip (P.W.) <psinger1_at_ford.com>
Date: Thu, 29 Apr 2004 12:48:16 -0400
Message-ID: <A45063A7D336504580F0161CEB7FEBE201AE77D7@na1fcm60.dearborn.ford.com>

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org

> Hey all,

>=20

> The last INSERT...SELECT on the first loop trace shows:
>=20
> WAIT #26: nam=3D'db file scattered read' ela=3D 0 p1=3D7 p2=3D57145 =
p3=3D8

>=20

> ...ad nauseum, but each of these lines in the trace takes at=20
> least a couple
> of seconds to show while viewing the trace using "tail -f". =20
> Previous data
> in the trace showed up with a typical "tail -f" batching of=20
> several lines or

One situation I have run into (several times, actually) which mimics = your
description (others will list the additional data which would be = helpful) is
where, due to poorly chosen init.ora parameters, the Optimizer is doing = a
hash join, which in this case is a Big Mistake. While trying to form = the
hash table in memory, and doing the hashing in memory, the cpu's burn. When it has to read the child table from disk, you get some I/O. Setting ALTER SESSION SET OPTIMIZER_INDEX_CACHING=3D100 fixed it. (leaving it at 100 slowed down other queries, where the hash join was essential).

To test you must use tkprof. We found that even when we had hints added to get an explain plan output giving a nested loops join, the hash join = was
used during actual execution.

---

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

Received on Thu Apr 29 2004 - 11:49:13 CDT