Re: Hash semi join with IN subquery
- From: Stalin <stalinsk@xxxxxxxxx>
- To: Christo Kutrovsky <kutrovsky.oracle@xxxxxxxxx>
- Date: Wed, 18 May 2005 16:45:25 -0700
Thanks to all replied.
dynamic_sampling hint did the trick.
As far as why batch process recreates GTT everytime it's run, is the
question I posted to developers long back but apprantely no plausible
replies to it.
On 5/18/05, Christo Kutrovsky <kutrovsky.oracle@xxxxxxxxx> wrote:
> Why do you drop/recreate the GTT every time ? You dont need to do so.
>=20
> The problem is that the optimizer thinks the GTT has many rows. You
> have several options in order of recommendation:
>=20
> - use dynamic sampling !
> UPDATE
> SET expired =3D 'T'
> WHERE session_key sk IN
> (SELECT /*+ DYNAMIC_SAMPLING(3) session_id FROM temp_session_ids)
>=20
> - put some "sample" data in the GTT and analyze it !
>=20
> - Hint the index:
> UPDATE /*+ index(sk) */
> SET expired =3D 'T'
> WHERE session_key sk IN
> (SELECT session_id FROM temp_session_ids)
>=20
>=20
> On 5/18/05, Stalin <stalinsk@xxxxxxxxx> wrote:
> > One of the batch process started to run really slow and after looking
> > at 10046 trace of the batch process, i figured the culprit sql that
> > uses IN subquery on GTT.
> >
> > The query runs less than a second if i don't use GTT on the IN subquery=
.
> >
> > here goes...
> >
> > 10046 trace for IN subquery without GTT
> >
> > UPDATE sessions
> > SET expired =3D3D 'T'
> > WHERE session_key IN
> > (5710676,5710677,5710678,5710679,5710680,5710681,5710682,5710683,571068=
4,57=3D
> > 10685)
> >
> > call count cpu elapsed disk query current =
=3D
> > rows
> > ------- ------ -------- ---------- ---------- ---------- ---------- -=
----=3D
> > -----
> > Parse 1 0.01 0.00 0 0 0 =
=3D
> > 0
> > Execute 1 0.00 0.00 4 31 16 =
=3D
> > 10
> > Fetch 0 0.00 0.00 0 0 0 =
=3D
> > 0
> > ------- ------ -------- ---------- ---------- ---------- ---------- -=
----=3D
> > -----
> > total 2 0.01 0.00 4 31 16 =
=3D
> > 10
> >
> > Rows Row Source Operation
> > ------- ---------------------------------------------------
> > 0 UPDATE
> > 10 INLIST ITERATOR
> > 10 INDEX RANGE SCAN SESSIONS_WH_N1 (object id 25441)
> >
> > 10046 trace for IN subquery with GTT (had to kill the query after a whi=
le)
> >
> > UPDATE sessions
> > SET expired =3D3D 'T'
> > WHERE session_key IN
> > (SELECT session_id FROM temp_session_ids)
> >
> > call count cpu elapsed disk query current =
=3D
> > rows
> > ------- ------ -------- ---------- ---------- ---------- ---------- -=
----=3D
> > -----
> > Parse 1 0.00 0.00 0 1 0 =
=3D
> > 0
> > Execute 1 3.54 10.30 4895 4899 0 =
=3D
> > 0
> > Fetch 0 0.00 0.00 0 0 0 =
=3D
> > 0
> > ------- ------ -------- ---------- ---------- ---------- ---------- -=
----=3D
> > -----
> > total 2 3.54 10.30 4895 4900 0 =
=3D
> > 0
> >
> > Rows Row Source Operation
> > ------- ---------------------------------------------------
> > 0 UPDATE
> > 0 HASH JOIN SEMI
> > 722219 TABLE ACCESS FULL SESSIONS
> > 0 TABLE ACCESS FULL TEMP_SESSION_IDS
> >
> > Sessions table stats are up-to-date and temp_session_ids (GTT) gets
> > dropped and recreated everytime the batch runs.
> >
> > I tried _always_semi_join=3D3Dfalse, even that didn't do any help. Any
> > pointers to what's going on with this sql.
> >
> > Thanks,
> > Stalin
> >
> > Sol 2.8, 9.2.0.5 (32bit)
> > --
> > //www.freelists.org/webpage/oracle-l
> >
>=20
>=20
> --
> Christo Kutrovsky
> Database/System Administrator
> The Pythian Group
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
