Re: Hash semi join with IN subquery

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)
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
>=20
>=20
> --
> Christo Kutrovsky
> Database/System Administrator
> The Pythian Group
>
--
http://www.freelists.org/webpage/oracle-l

Other related posts: