Hi

This is a snip from a tkprof.

SQL ID: 4wgb411d8s7ss Plan Hash: 0

INSERT INTO QP_NPREQ_LINE_ATTRS_TMP (LINE_INDEX, ATTRIBUTE_LEVEL, 
  ATTRIBUTE_TYPE, CONTEXT, ATTRIBUTE, VALUE_FROM, VALIDATED_FLAG, 
  APPLIED_FLAG, PRICING_STATUS_CODE, PRICING_ATTR_FLAG ) 
VALUES
 (:B1 , :B10 , :B2 , :B3 , :B4 , :B5 , :B6 , :B9 , :B8 , :B7 )


call     count       cpu    elapsed       rows
------- ------  -------- ---------- ----------
Parse        6      0.00       0.00          0
Execute   5556     16.99      25.67     234460
Fetch        0      0.00       0.00          0
------- ------  -------- ---------- ----------
total     5562     16.99      25.67     234460

(I carefully removed the disk, query and current columns to help clarify).

So 5,556 executions - 234,460 rows. How is that possible? AWR shows the same situation - 42 rows per single row insert.

I should mention that QP_NPREQ_LINE_ATTRS_TMP is a simple view that links to QP_PREQ_LINE_ATTRS_TMP_T2 - which is a GTT.

If you do the inserts in SQL*Plus, or an anonymous PL/SQL block - you get one row per insert.

Any ideas?

The unmodified snip is below. It wraps.

Thanks and Regards

Ian

SQL ID: 4wgb411d8s7ss Plan Hash: 0

INSERT INTO QP_NPREQ_LINE_ATTRS_TMP (LINE_INDEX, ATTRIBUTE_LEVEL, 
  ATTRIBUTE_TYPE, CONTEXT, ATTRIBUTE, VALUE_FROM, VALIDATED_FLAG, 
  APPLIED_FLAG, PRICING_STATUS_CODE, PRICING_ATTR_FLAG ) 
VALUES
 (:B1 , :B10 , :B2 , :B3 , :B4 , :B5 , :B6 , :B9 , :B8 , :B7 )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        6      0.00       0.00          0          0          0           0
Execute   5556     16.99      25.67       5002      92404    2589462      234460
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     5562     16.99      25.67       5002      92404    2589462      234460

SQL> create table t ( x int );

Table created.

SQL>
SQL> alter session set sql_trace = true;

Session altered.

SQL> declare
  2    type numlist is table of int
  3      index by pls_integer;
  4    r numlist;
  5  begin
  6    for i in 1 .. 100 loop
  7       r(i) := i;
  8    end loop;
  9
 10    forall i in 1 .. 100
 11      insert into t values (r(i));
 12  end;
 13  /

PL/SQL procedure successfully completed.

INSERT INTO T
VALUES
 (:B1 )


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          1          8         100
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          1          8         100