This was first published on https://blog.dbi-services.com/insert-into-gtt-bulk-with-appendvalues (2015-01-09)
Republishing here for new followers. The content is related to the the versions available at the publication date
This post is about the case I had where a GTT was generating too much undo. The effects were amplified by the fact that the database was in flashback logging (see Jonathan Lewis answer in my question on OTN forum about that, but that’s for the next post.
Nothing here is specific to GTT, but generating undo (and the related redo) is even more awkward on GTT. Here is the query:
INSERT INTO SCDAT.INLISTS_INT_SINGLE(INCOUNT,INNUM) VALUES (:v1 ,:v2 )
And SCDAT.INLISTS_INT_SINGLE is a GTT with only two columns and a primary key on it. Maybe they would have created it as an IOT but that’s not (yet?) possible for a GTT. Why is that table used a lot? It’s the application way to pass a list of value of variable size. Is it a good design?
But every software editor must keep code maintainable and cannot have three different SQL statements, depending on the input. Always using collections brings the risk to allocate large structures in PGA. Queries with 1000 values or variables in several IN list is even worse. And Oracle does not offer an in memory temporary table as other RDBMS do. So the design is ok but we need to optimize it.
Let’s build the test case:
SQL> connect demo/demo Connected. SQL> SQL> DROP TABLE "INLISTS_INT_SINGLE" 2 / Table dropped. SQL> CREATE GLOBAL TEMPORARY TABLE "INLISTS_INT_SINGLE" 2 ( "INCOUNT" NUMBER(10,0) CONSTRAINT "C_INLISTS_INT_SINGLE_INCOUNT" NOT NULL ENABLE, 3 "INNUM" NUMBER(14,0) DEFAULT 0 CONSTRAINT "C_INLISTS_INT_SINGLE_INNUM" NOT NULL ENABLE, 4 CONSTRAINT "P_INLISTS_INT_SINGLE" PRIMARY KEY ("INCOUNT", "INNUM") RELY ENABLE 5 ) ON COMMIT DELETE ROWS 6 / Table created.
I’ve kept the names because it may help if someone is having the same issue and is googling for it. And – once again – there is no problem about that software. It is globally using oracle in the right way.
I run 1000 single row inserts from a pl/sql loop, and I include that in another loop in order to do it 100 times and have more significant statistics.
SQL> set timing on echo on pagesize 1000 trimspool on SQL> connect demo/demo Connected. SQL> begin 2 for n in 1..1e2 loop 3 for i in 1..1e3 loop insert into INLISTS_INT_SINGLE values(mod(i,4),i); end loop; 4 commit; 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed. Elapsed: 00:00:13.55
I check my session statistics:
SQL> select name,value from v$mystat join v$statname using(statistic#) where ( name like 'redo%' or name like '%undo%' or name like '%split%' ) and value>0; NAME VALUE --------------------------- ---------- redo entries 203077 redo size 36491420 undo change vector size 20672820 leaf node splits 400 leaf node 90-10 splits 100
That’s lot of undo, which is the major part of the redo. We don’t expect that for an insert because there is no previous value to store. Except for the index.
Here is the tkprof result:
INSERT INTO INLISTS_INT_SINGLE VALUES (MOD(:B1 ,4),:B1 ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 100000 24.18 25.11 0 1918 314937 100000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 100001 24.18 25.11 0 1918 314937 100000
And the raw dump trace because I want to compare the bind variable trace with the array insert later.
PARSING IN CURSOR #140716771910880 len=55 dep=1 uid=111 oct=2 lid=111 tim=372733000350 hv=593926914 ad='b7a458a8' sqlid='8s7ru50jqd6s2' BINDS #140716771910880: Bind#0 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1206001 frm=00 csi=00 siz=48 off=0 kxsbbbfp=7ffb2d341f28 bln=22 avl=02 flg=05 value=1 Bind#1 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1206001 frm=00 csi=00 siz=0 off=24 kxsbbbfp=7ffb2d341f40 bln=22 avl=02 flg=01 value=1 BINDS #140716771910880: Bind#0 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1206001 frm=00 csi=00 siz=48 off=0 kxsbbbfp=7ffb2d341f28 bln=22 avl=02 flg=05 value=2 Bind#1 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=03 fl2=1206001 frm=00 csi=00 siz=0 off=24 kxsbbbfp=7ffb2d341f40 bln=22 avl=02 flg=01 value=2 EXEC #140716771910880:c=47000,e=87289,p=1,cr=121,cu=20,mis=1,r=1,dep=1,og=1,plh=0,tim=372733087698
One optimization is to insert in bulk:
SQL> connect demo/demo Connected. SQL> declare 2 type c_type is table of INLISTS_INT_SINGLE%rowtype; 3 c c_type; 4 begin 5 select mod(rownum,4),rownum bulk collect into c from dual connect by 1e3>level; 6 for n in 1..1e2 loop 7 forall i in 1 .. c.count insert into INLISTS_INT_SINGLE values c(i); 8 commit; 9 end loop; 10 end; 11 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.83
This is much better with 3x less undo generated:
SQL> select name,value from v$mystat join v$statname using(statistic#) where ( name like 'redo%' or name like '%undo%' or name like '%split%' ) and value>0; NAME VALUE -------------------------- ---------- redo entries 31787 redo size 9674492 undo change vector size 6892060 leaf node splits 400 leaf node 90-10 splits 100
And this is what was doing the application I way looking at. And that can be seen from tkprof where the number of rows per execution shows the average array size:
INSERT INTO INLISTS_INT_SINGLE VALUES (:B1 ,:B2 ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 100 0.78 0.78 0 2009 68277 100000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 101 0.79 0.78 0 2009 68277 100000
However, I expected to see it from the bind trace but I can see only the first value of each array:
BINDS #140157072517608: Bind#0 oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00 oacflg=13 fl2=204001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=7f78dc973530 bln=22 avl=02 flg=09 value=1 Bind#1 oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00 oacflg=13 fl2=204001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=7f78dc973548 bln=22 avl=02 flg=09 value=1 EXEC #140157072517608:c=7000,e=7144,p=0,cr=23,cu=682,mis=1,r=1000,dep=1,og=1,plh=0,tim=372772065407
The only different thing is the internal flag oacflg=13 instead of oacflg=03, but unfortunately this is not documented.
Ok, since 11g there is one way of improvement with direct-path insert. The hint APPEND_VALUES is the equivalent to the APPEND one except that it can be used with INSERT … VALUES insert instead of INSERT from SELECT. Of course it makes sense only with array insert – not when inserting only one row.
SQL> connect demo/demo Connected. SQL> declare 2 type c_type is table of INLISTS_INT_SINGLE%rowtype; 3 c c_type; 4 begin 5 select mod(rownum,4),rownum bulk collect into c from dual connect by 1e3>level; 6 for n in 1..1e2 loop 7 forall i in 1 .. c.count insert /*+ APPEND_VALUES */ into INLISTS_INT_SINGLE values c(i); 8 commit; 9 end loop; 10 end; 11 / PL/SQL procedure successfully completed. Elapsed: 00:00:04.35 SQL> select name,value from v$mystat join v$statname using(statistic#) where ( name like 'redo%' or name like '%undo%' or name like '%split%' ) and value>0; NAME VALUE ------------------------------ ---------- redo entries 6509 redo size 4113096 undo change vector size 3005700 leaf node splits 200 leaf node 90-10 splits 200
This is much better. And I don’t think we can expect any drawback when inserting into an empty GTT.
INSERT /*+ APPEND_VALUES */ INTO INLISTS_INT_SINGLE VALUES (:B1 ,:B2 ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 100 0.03 0.04 0 0 0 0 Execute 100 0.85 1.08 0 6908 7421 100000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 200 0.89 1.13 0 6908 7421 100000 BINDS #139994276856992: Bind#0 oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00 oacflg=13 fl2=204001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=7f52f52501b8 bln=22 avl=02 flg=09 value=1 Bind#1 oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00 oacflg=13 fl2=204001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=7f52f52501d0 bln=22 avl=02 flg=09 value=1 EXEC #139994276856992:c=541000,e=561632,p=29,cr=3145,cu=99,mis=1,r=1000,dep=1,og=1,plh=3581094869,tim=372773523105
The first thing to do when we insert several rows it to insert them in bulk. The second one is to used the APPEND_VALUES. There are other way to avoid passing lot of values for an IN list, and that’s for a future post.