Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL INSERT INTO SELECT FROM fast then slow then fast again

Re: SQL INSERT INTO SELECT FROM fast then slow then fast again

From: Sybrand Bakker <gooiditweg_at_sybrandb.verwijderdit.demon.nl>
Date: Fri, 21 May 2004 14:41:58 +0200
Message-ID: <f6ura0hfcb78fg5dp0lg313q14pur6bb8m@4ax.com>


On 21 May 2004 04:50:29 -0700, info_at_thunkbox.com (John Ashton) wrote:

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

Get the explain plans and show them here. Other than that: CBO runs by default in ALL_ROWS mode in stored procedure.
You might run in FIRST_ROWS mode in plain vanilla SQL

--
Sybrand Bakker, Senior Oracle DBA
Received on Fri May 21 2004 - 07:41:58 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US