Re: Hash semi join with IN subquery
- From: Christo Kutrovsky <kutrovsky.oracle@xxxxxxxxx>
- To: stalinsk@xxxxxxxxx
- Date: Wed, 18 May 2005 17:50:41 -0400
Why do you drop/recreate the GTT every time ? You dont need to do so.
The problem is that the optimizer thinks the GTT has many rows. You
have several options in order of recommendation:
- use dynamic sampling !
UPDATE=20
SET expired =3D 'T'
WHERE session_key sk IN
(SELECT /*+ DYNAMIC_SAMPLING(3) session_id FROM temp_session_ids)
- put some "sample" data in the GTT and analyze it !
- Hint the index:
UPDATE /*+ index(sk) */
SET expired =3D 'T'
WHERE session_key sk IN
(SELECT session_id FROM temp_session_ids)
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.
>=20
> The query runs less than a second if i don't use GTT on the IN subquery.
>=20
> here goes...
>=20
> 10046 trace for IN subquery without GTT
>=20
> UPDATE sessions
> SET expired =3D3D 'T'
> WHERE session_key IN
> (5710676,5710677,5710678,5710679,5710680,5710681,5710682,5710683,5710684,=
57=3D
> 10685)
>=20
> 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
>=20
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 0 UPDATE
> 10 INLIST ITERATOR
> 10 INDEX RANGE SCAN SESSIONS_WH_N1 (object id 25441)
>=20
> 10046 trace for IN subquery with GTT (had to kill the query after a while=
)
>=20
> UPDATE sessions
> SET expired =3D3D 'T'
> WHERE session_key IN
> (SELECT session_id FROM temp_session_ids)
>=20
> 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
>=20
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 0 UPDATE
> 0 HASH JOIN SEMI
> 722219 TABLE ACCESS FULL SESSIONS
> 0 TABLE ACCESS FULL TEMP_SESSION_IDS
>=20
> Sessions table stats are up-to-date and temp_session_ids (GTT) gets
> dropped and recreated everytime the batch runs.
>=20
> I tried _always_semi_join=3D3Dfalse, even that didn't do any help. Any
> pointers to what's going on with this sql.
>=20
> Thanks,
> Stalin
>=20
> 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:
