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

  • From: "Singer, Phillip (P.W.)" <psinger1@xxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 29 Apr 2004 12:48:16 -0400


> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx

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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 04-2004