Thanks for the question, Karl.

Asked: September 16, 2015 - 10:55 am UTC

Last updated: September 22, 2015 - 11:59 pm UTC

Version: 11.2.3.0

Viewed 10K+ times! This question is

You Asked

I'am currently struggling with a very strange thing. What i'am trying to do is an simple insert as select with only one row. The select (without insert) runs under 1minute; but with the insert statement in front it takes ten times as long. (See Runtimes for testdata at the end of this post)

Here is an example of what i am trying to do:

insert into temp
select anz1+anz2+anz3
from 
(
  select 
  (
  select count(*)
  from tempdata p 
  ) anz1,
  (
  select count(*)
 from tempdata p
  ) anz2
  ,
  (
  select count(*)
  from tempdata p
  ) anz3
  from dual
);

Table "temp" is a small plain table with a few rows,one column - nothing special; Table "tempdate" is bigger, partioned and has one index. It has about 80mio rows evenly distributed on 16 hash-partitions.

The original statement had groupings, wheres; the subselects were in with clauses. I tried several things - put away all groupings, tryied to rewrite it with subqueries - but it remains the same effect. I tried different measuring things like autotrace, but i can't distinguish between cause and effect.

Please find attached the scripts. I can reproduce the problem with those.

CREATE TABLE Tempdata 
(
  DateFrom DATE NOT NULL 
, DateTo DATE NOT NULL 
, BID NUMBER NOT NULL 
,  COL1 varchar2(50)
,  COL2 varchar2(50)
,  COL3 varchar2(50)
,  COL4 varchar2(50)
,  COL5 varchar2(50)
,  COL6 varchar2(50)
,  COL7 varchar2(50)
,  COL8 varchar2(50)
, CONSTRAINT Tempdata_PK PRIMARY KEY 
  (
    BID 
  , DateFrom 
  )
  ENABLE 
) 
PCTFREE 0 
INITRANS 1 
PARALLEL 32 
PARTITION BY HASH (BID) partitions 16;

-- Fill Table with random data
declare
var_i number := 0;
begin

-- this loop will take a while
for var_i in 0..8
loop
  insert into tempdata(datefrom, dateto, bid)
  select sysdate, sysdate +100, level from dual
  connect by level < 10000000;
  commit;
end loop;
end;

create table temp(asdf number);

-- Runtime with testdata:
-- Runtime only with select     ~ 10 seconds
-- Runtime with insert as select ~ 40 seconds

and Connor said...

Thanks for the test script. Always makes life easier for us.

One question - is the behaviour consistent with multiple exectuions, ie, if you do

insert (40 seconds)
select (10 seconds)

and then do them *again*, is it still 40 and 10 ?

(I'm asking with a mind toward block cleanout)

I altered your test script slightly:

a) two dual connect by's, to avoid my machine complaining about PGA
b) using APPEND hint

and when I run it on either 12c or 11.2.0.4 (sorry, I dont have an 11.2.0.3 handy) I'm getting comparable run times, and similarly, execution plans are aligned. See below

12c

SQL> CREATE TABLE Tempdata
  2  (
  3    DateFrom DATE NOT NULL
  4  , DateTo DATE NOT NULL
  5  , BID NUMBER NOT NULL
  6  ,  COL1 varchar2(50)
  7  ,  COL2 varchar2(50)
  8  ,  COL3 varchar2(50)
  9  ,  COL4 varchar2(50)
 10  ,  COL5 varchar2(50)
 11  ,  COL6 varchar2(50)
 12  ,  COL7 varchar2(50)
 13  ,  COL8 varchar2(50)
 14  )
 15  PCTFREE 0
 16  INITRANS 1
 17  PARALLEL 32
 18  PARTITION BY HASH (BID) partitions 16;

Table created.

SQL>
SQL> -- Fill Table with random data
SQL> declare
  2  var_i number := 0;
  3  begin
  4
  5  -- this loop will take a while
  6  for var_i in 0..8
  7  loop
  8    insert /*+ APPEND */ into tempdata(datefrom, dateto, bid)
  9    select sysdate+var_i, sysdate +100,rownum
 10    from
 11      ( select 1 from dual connect by level <= 10000) ,
 12      ( select 1 from dual connect by level <= 1000);
 13
 14    commit;
 15  end loop;
 16  end;
 17  /

PL/SQL procedure successfully completed.

SQL>
SQL> alter table Tempdata add
  2   CONSTRAINT Tempdata_PK PRIMARY KEY
  3    (
  4      BID
  5    , DateFrom
  6    )
  7  /

Table altered.

SQL>
SQL> drop table temp purge;

Table dropped.

SQL> create table temp(asdf number);

Table created.

SQL>
SQL> set timing on
SQL> select anz1+anz2+anz3
  2  from
  3  (
  4    select
  5    (
  6    select count(*)
  7    from tempdata p
  8    ) anz1,
  9    (
 10    select count(*)
 11   from tempdata p
 12    ) anz2
 13    ,
 14    (
 15    select count(*)
 16    from tempdata p
 17    ) anz3
 18    from dual
 19  );

ANZ1+ANZ2+ANZ3
--------------
     270000000

Elapsed: 00:00:15.91
SQL>
SQL>
SQL> insert into temp
  2  select anz1+anz2+anz3
  3  from
  4  (
  5    select
  6    (
  7    select count(*)
  8    from tempdata p
  9    ) anz1,
 10    (
 11    select count(*)
 12   from tempdata p
 13    ) anz2
 14    ,
 15    (
 16    select count(*)
 17    from tempdata p
 18    ) anz3
 19    from dual
 20  );

1 row created.

Elapsed: 00:00:14.90
SQL>
SQL> select anz1+anz2+anz3
  2  from
  3  (
  4    select
  5    (
  6    select count(*)
  7    from tempdata p
  8    ) anz1,
  9    (
 10    select count(*)
 11   from tempdata p
 12    ) anz2
 13    ,
 14    (
 15    select count(*)
 16    from tempdata p
 17    ) anz3
 18    from dual
 19  );

ANZ1+ANZ2+ANZ3
--------------
     270000000

Elapsed: 00:00:14.65
SQL>
SQL>
SQL> insert into temp
  2  select anz1+anz2+anz3
  3  from
  4  (
  5    select
  6    (
  7    select count(*)
  8    from tempdata p
  9    ) anz1,
 10    (
 11    select count(*)
 12   from tempdata p
 13    ) anz2
 14    ,
 15    (
 16    select count(*)
 17    from tempdata p
 18    ) anz3
 19    from dual
 20  );

1 row created.

Elapsed: 00:00:14.92
SQL>
SQL>
SQL>
SQL>

11.2.0.4



SQL> set timing off
SQL> drop table tempdata purge;
drop table tempdata purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> CREATE TABLE Tempdata
  2  (
  3    DateFrom DATE NOT NULL
  4  , DateTo DATE NOT NULL
  5  , BID NUMBER NOT NULL
  6  ,  COL1 varchar2(50)
  7  ,  COL2 varchar2(50)
  8  ,  COL3 varchar2(50)
  9  ,  COL4 varchar2(50)
 10  ,  COL5 varchar2(50)
 11  ,  COL6 varchar2(50)
 12  ,  COL7 varchar2(50)
 13  ,  COL8 varchar2(50)
 14  )
 15  PCTFREE 0
 16  INITRANS 1
 17  PARALLEL 32
 18  PARTITION BY HASH (BID) partitions 16;

Table created.

SQL>
SQL> -- Fill Table with random data
SQL> declare
  2  var_i number := 0;
  3  begin
  4
  5  -- this loop will take a while
  6  for var_i in 0..8
  7  loop
  8    insert /*+ APPEND */ into tempdata(datefrom, dateto, bid)
  9    select sysdate+var_i, sysdate +100,rownum
 10    from
 11      ( select 1 from dual connect by level <= 10000) ,
 12      ( select 1 from dual connect by level <= 1000);
 13
 14    commit;
 15  end loop;
 16  end;
 17  /

PL/SQL procedure successfully completed.

SQL>
SQL> alter table Tempdata add
  2   CONSTRAINT Tempdata_PK PRIMARY KEY
  3    (
  4      BID
  5    , DateFrom
  6    )
  7  /

Table altered.

SQL>
SQL> drop table temp purge;
drop table temp purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table temp(asdf number);

Table created.

SQL> set timing on
SQL> select anz1+anz2+anz3
  2  from
  3  (
  4    select
  5    (
  6    select count(*)
  7    from tempdata p
  8    ) anz1,
  9    (
 10    select count(*)
 11   from tempdata p
 12    ) anz2
 13    ,
 14    (
 15    select count(*)
 16    from tempdata p
 17    ) anz3
 18    from dual
 19  );

ANZ1+ANZ2+ANZ3
--------------
     270000000

Elapsed: 00:00:20.84
SQL>
SQL>
SQL> insert into temp
  2  select anz1+anz2+anz3
  3  from
  4  (
  5    select
  6    (
  7    select count(*)
  8    from tempdata p
  9    ) anz1,
 10    (
 11    select count(*)
 12   from tempdata p
 13    ) anz2
 14    ,
 15    (
 16    select count(*)
 17    from tempdata p
 18    ) anz3
 19    from dual
 20  );

1 row created.

Elapsed: 00:00:15.38
SQL>
SQL> select anz1+anz2+anz3
  2  from
  3  (
  4    select
  5    (
  6    select count(*)
  7    from tempdata p
  8    ) anz1,
  9    (
 10    select count(*)
 11   from tempdata p
 12    ) anz2
 13    ,
 14    (
 15    select count(*)
 16    from tempdata p
 17    ) anz3
 18    from dual
 19  );

ANZ1+ANZ2+ANZ3
--------------
     270000000

Elapsed: 00:00:15.14
SQL>
SQL>
SQL> insert into temp
  2  select anz1+anz2+anz3
  3  from
  4  (
  5    select
  6    (
  7    select count(*)
  8    from tempdata p
  9    ) anz1,
 10    (
 11    select count(*)
 12   from tempdata p
 13    ) anz2
 14    ,
 15    (
 16    select count(*)
 17    from tempdata p
 18    ) anz3
 19    from dual
 20  );

1 row created.

Elapsed: 00:00:14.97
SQL>
SQL>
SQL>
SQL>

and here's the execution plan (from v11)

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                    |          |     1 |     2   (0)| 00:00:01 |       |       |        |      |            |
|   1 |  LOAD TABLE CONVENTIONAL            | TEMP     |       |            |          |       |       |        |      |            |
|   2 |   SORT AGGREGATE                    |          |     1 |            |          |       |       |        |      |            |
|   3 |    PX COORDINATOR                   |          |       |            |          |       |       |        |      |            |
|   4 |     PX SEND QC (RANDOM)             | :TQ10000 |     1 |            |          |       |       |  Q1,00 | P->S | QC (RAND)  |
|   5 |      SORT AGGREGATE                 |          |     1 |            |          |       |       |  Q1,00 | PCWP |            |
|   6 |       PX BLOCK ITERATOR             |          |    83M|  2894   (2)| 00:00:35 |     1 |    16 |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL            | TEMPDATA |    83M|  2894   (2)| 00:00:35 |     1 |    16 |  Q1,00 | PCWP |            |
|   8 |         SORT AGGREGATE              |          |     1 |            |          |       |       |        |      |            |
|   9 |          PX COORDINATOR             |          |       |            |          |       |       |        |      |            |
|  10 |           PX SEND QC (RANDOM)       | :TQ20000 |     1 |            |          |       |       |  Q2,00 | P->S | QC (RAND)  |
|  11 |            SORT AGGREGATE           |          |     1 |            |          |       |       |  Q2,00 | PCWP |            |
|  12 |             PX BLOCK ITERATOR       |          |    83M|  2894   (2)| 00:00:35 |     1 |    16 |  Q2,00 | PCWC |            |
|  13 |              TABLE ACCESS FULL      | TEMPDATA |    83M|  2894   (2)| 00:00:35 |     1 |    16 |  Q2,00 | PCWP |            |
|  14 |               SORT AGGREGATE        |          |     1 |            |          |       |       |        |      |            |
|  15 |                PX COORDINATOR       |          |       |            |          |       |       |        |      |            |
|  16 |                 PX SEND QC (RANDOM) | :TQ30000 |     1 |            |          |       |       |  Q3,00 | P->S | QC (RAND)  |
|  17 |                  SORT AGGREGATE     |          |     1 |            |          |       |       |  Q3,00 | PCWP |            |
|  18 |                   PX BLOCK ITERATOR |          |    83M|  2894   (2)| 00:00:35 |     1 |    16 |  Q3,00 | PCWC |            |
|  19 |                    TABLE ACCESS FULL| TEMPDATA |    83M|  2894   (2)| 00:00:35 |     1 |    16 |  Q3,00 | PCWP |            |
|  20 |   FAST DUAL                         |          |     1 |     2   (0)| 00:00:01 |       |       |        |      |            |
-------------------------------------------------------------------------------------------------------------------------------------

Now ... thats quite possibly not *exactly* the same as your environment, because the number of slaves etc may well be different.

In terms of a plan of attack:

a) try my script (with the APPEND). If you *dont* see the difference in times, then we know its related to the way the data is being loaded.

b) Try your test case with trace enabled (dbms_monitor) and we can compare the waits.

Hope this helps.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

retested - but no real change

Karl J, September 22, 2015 - 10:44 am UTC

Hi Connor!

thank you very much for your (fast!) answer.
I'm sorry for beeing so late with my review, but i had to find time and a free db...

So, i ran the scripts you provided, but there was no change.
The times where a bit better (6seconds to 12 seconds) , but the problem with doubled times still remain.

My Execution Plans are exact the same as yours, even the costs are almost the same.

Unfortunatly i think i have no access to dbms_monitor and asking for grants will take endless...

Is there an other posibility i can provide you such stats ?
I can do an autotrace with sql developer, but i have no option to upload the files here.

Thank you very much!

September 22, 2015 - 11:59 pm UTC

Run

alter session set tracefile_identifier = SLOW_INSERT;
alter session set events = '10046 trace name context forever, level 8';

then run your query and your insert, then end/disconnect the session

Get your DBA to email the trace file (which will have "SLOW_INSERT" in the file name somewhere) to:

asktom_us@oracle.com

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

retested - but no real change

More to Explore

VLDB

VLDB

Oracle long runing insert as select - but select is fast

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

retested - but no real change

More to Explore

VLDB

VLDB