Hash semi join with IN subquery
- From: Stalin <stalinsk@xxxxxxxxx>
- To: Oracle-L@xxxxxxxxxxxxx
- Date: Wed, 18 May 2005 12:31:34 -0700
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 =3D 'T'
WHERE session_key IN
(5710676,5710677,5710678,5710679,5710680,5710681,5710682,5710683,5710684,57=
10685)
call count cpu elapsed disk query current =
rows
------- ------ -------- ---------- ---------- ---------- ---------- -----=
-----
Parse 1 0.01 0.00 0 0 0 =
0
Execute 1 0.00 0.00 4 31 16 =
10
Fetch 0 0.00 0.00 0 0 0 =
0
------- ------ -------- ---------- ---------- ---------- ---------- -----=
-----
total 2 0.01 0.00 4 31 16 =
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 while)
UPDATE sessions
SET expired =3D 'T'
WHERE session_key IN
(SELECT session_id FROM temp_session_ids)
call count cpu elapsed disk query current =
rows
------- ------ -------- ---------- ---------- ---------- ---------- -----=
-----
Parse 1 0.00 0.00 0 1 0 =
0
Execute 1 3.54 10.30 4895 4899 0 =
0
Fetch 0 0.00 0.00 0 0 0 =
0
------- ------ -------- ---------- ---------- ---------- ---------- -----=
-----
total 2 3.54 10.30 4895 4900 0 =
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=3Dfalse, 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
Other related posts:
