Re: Hash semi join with IN subquery

From: Stalin <stalinsk_at_gmail.com>
Date: Wed, 18 May 2005 16:45:25 -0700
Message-ID: <c5363d3a050518164565e0a79d@mail.gmail.com>

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_at_gmail.com> 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_at_gmail.com> 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

Received on Wed May 18 2005 - 19:50:04 CDT