SQL INSERT INTO SELECT FROM fast then slow then fast again

From: John Ashton <info_at_thunkbox.com>
Date: 21 May 2004 04:50:29 -0700
Message-ID: <236d8572.0405210350.11ed9f7b@posting.google.com>

I have some sql running on the following: Oracle9i Enterprise Edition Release 9.2.0.2.0 - 64bit Production Solaris 9

It is basically of the form:
INSERT into temp_table (c1, c2)

      SELECT t1.c1, t2.c2
        FROM tabel1 t1, table2 t2 ...

I don't think there is a problem with the query per se as it takes about 2 seconds most of the time which is fine given the amount of data I have. The only interesting things about the SQL are: 1. insert is into a global temporary table 2. select looks at a date-partitioned-by-month table (with around 1.5 million rows per month) for record for a single date (about 55,000 rows)

The problem is that this query, when run as a procedure, will sometimes take about 20 minutes. While it is running, if I take the same query and run it as sql, it will complete in about 2 secs! A some point later (which appears to be random, normally hours later) the procedure will again take 2 secs.

Here is an extract of the TKProf when its fast:

call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      0.00       0.00          0          0          0  
        0
Execute      1      0.00       3.32        400     178362         89  
       24
Fetch        0      0.00       0.00          0          0          0  
        0

------- ------ -------- ---------- ---------- ---------- ----------

---

total        2      0.00       3.32        400     178362         89  
       24

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 33 (recursive depth: 1)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total

Waited

• Waited ----------

  ---

  db file sequential read 400 0.01 0.50

And when it's slow:

call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      0.00       0.00          0          0          0  
        0
Execute      1      0.00    1360.16        631   69335893         92  
       24
Fetch        0      0.00       0.00          0          0          0  
        0

------- ------ -------- ---------- ---------- ---------- ----------

---

total        2      0.00    1360.16        631   69335893         92  
       24

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 33 (recursive depth: 1)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total

Waited

• Waited ----------

  ---

  db file sequential read 631 0.03 5.07 latch free 5 0.00 0.00

The 'db file sequential read' events occur on the index and data files for the month partition I'm selecting.

I am guessing there is some IO problem, maybe dues to something else running on the system, but I'm not sure why the SQL query that is run outside the procedure at the same time is fast. I'm also unsure of where to look next for the cause of this behaviour.

Any help would be appreciated. Received on Fri May 21 2004 - 06:50:29 CDT