Hash semi join with IN subquery

From: Stalin <stalinsk_at_gmail.com>
Date: Wed, 18 May 2005 12:31:34 -0700
Message-ID: <c5363d3a0505181231f6fbb9@mail.gmail.com>

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)

--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 18 2005 - 15:36:18 CDT