Inserting million of time series data (rows) per second inside oracle database part2

Posted on Updated on

In the previous post we have demonstrated how index columns order (matching or not, the data arrival pattern ) can impact the performance of the insertion.

Going a little bit further we will try to increase again the insertion performance rate.

One of the available options is to use direct path insert, since 11.2 release the APPEND_VALUES hint is available for standard insert statement.

Again using the two different column order, we will check the impact using direct path insert.

Then let’s create again the kpi table :

SQL> create table kpi (timeid number,insid number,indid number,value number);

And first create the index  like the first example in the previous blog post (not sorted on our data arrival pattern):

SQL> create unique index ix_kpi on kpi (insid,indid,timeid);

Using the same dataset let’s insert again 7 millions rows in our table via direct path insert method :

declare
 TYPE kpi_array is TABLE OF kpi%ROWTYPE;
 l_kpi_array kpi_array:=kpi_array ();
 start_time number;
 elapsed number;
 begin
 for time in 1 .. 100
 loop
 for indid in 1 .. 7
 loop
 for insid in 1 .. 10000
 loop
 l_kpi_array.extend();
 l_kpi_array(l_kpi_array.last).timeid:=time;
 l_kpi_array(l_kpi_array.last).indid:=indid;
 l_kpi_array(l_kpi_array.last).insid:=insid;
 l_kpi_array(l_kpi_array.last).value:=dbms_random.value(1,1000);
 end loop;
 end loop;
 end loop;
 start_time:=DBMS_UTILITY.GET_TIME ;
 forall p in l_kpi_array.first .. l_kpi_array.last
 insert /*+ APPEND_VALUES */ into kpi
 values l_kpi_array(p);
 elapsed := DBMS_UTILITY.GET_TIME - start_time;
 dbms_output.put_line('elapsed time (seconds)=' || elapsed/100 );end;
 /

elapsed time (seconds)=31,41 seconds

After executing this script, we can see that it takes 31 seconds to insert 7 millions rows, for comparison purpose, the exact same test without direct path insert used to complete within 56 seconds.Let’s have a look at the session statistics :

select
       name, value
from    v$mystat,v$statname
where
       v$mystat.statistic#=v$statname.statistic#
and name in ('leaf node splits','leaf node 90-10 splits','redo size','redo entries','session logical reads','physical reads',
'db block changes','db block gets','physical reads direct temporary tablespace','physical writes direct temporary tablespace')
;

NAME       VALUE
---------------------------------------------------------------- ----------
session logical reads     506672
db block gets     470582
physical reads       87041
db block changes     466778
physical reads direct temporary tablespace       87038
physical writes direct temporary tablespace       87038
redo entries     235452
redo size   550084680
leaf node splits       17526
leaf node 90-10 splits       17526

You can notice now than the index leaf node split are now 90-10 splits as opposed to the same example without direct path insert when we had mainly 50-50 node splits :

leaf node splits      17050
leaf node 90-10 splits 32

For comparison purpose here are the statistics for the former test (without direct path insert) :

NAME VALUE
---------------------------------------------------------------- ----------
session logical reads 15684333
db block gets         15544039
physical reads        413
db block changes      10610256
redo entries          5363399
redo size             1922236380
leaf node splits      17050
leaf node 90-10 splits 32

Why this difference between direct and non direct path insert?

Because when direct path insert is used the index maintenance is deferred and merged at the end of the process.

That means that oracle has to sort the entire array before updating the index and now can also take advantage of array processing to maintain the index structure.

This explains why the redo entries and the session logical reads have decreased so much compared to the former test (without direct path insert).

Another difference from the former test, is that we now have some physical reads/writes direct temporary tablespace.

The work area is too much small to process the entire sorts in memory and oracle has to spilled sort operations to disk.

This can be verified by checking the insert statement statistics values in v$sql_workarea :

select sql_id,child_number,operation_type,policy,last_execution,optimal_executions,multipasses_executions from v$sql_workarea where sql_id='10qx37pz299jw' and child_number=0;

SQL_ID CHILD_NUMBER OPERATION_TYPE     POLICY     LAST_EXECUTION     OPTIMAL_EXECUTIONS MULTIPASSES_EXECUTIONS
------------- ------------ --------------- ---------- ------------------------------ ------------------ ---------------------
10qx37pz299jw 0 IDX MAINTENANCE(SORT)  AUTO       13 PASSES             0       1

10qx37pz299jw 0 LOAD WRITE BUFFERS     AUTO       OPTIMAL               1       0

The sort was done using  multipasses executions (13 passes)

In order to perform the entire sorts in memory,  we can set the workarea size policy to manual and set the sort_area_size large enough.

alter session set workarea_size_policy=manual;
alter session set sort_area_size=800000000;

Let’s restart the same test with modified settings :

now 23.38 seconds to insert the 7 millions rows .

Let’s check sorting statistics again:

select sql_id,child_number,operation_type,policy,last_execution,optimal_executions,multipasses_executions from v$sql_workarea where sql_id='10qx37pz299jw' and child_number=1;

SQL_ID       CHILD_NUMBER OPERATION_TYPE  POLICY     LAST_EXECUTION     OPTIMAL_EXECUTIONS MULTIPASSES_EXECUTIONS ------------- ------------ --------------- ---------- ------------------------------ ------------------ ------- 
10qx37pz299jw 1 IDX MAINTENANCE(SORT) MANUAL     OPTIMAL       1       0 

10qx37pz299jw 1 LOAD WRITE BUFFERS    MANUAL     OPTIMAL       1       0

The execution time has reduced and all the sort operations are performed in memory.

Now let’s execute again the same test changing the index column order based on the data arrival pattern (see the previous post where without direct path insert we took advantage of array processing) and check if there is still a difference:

SQL> create unique index ix_kpi on kpi (timeid,indid,insid);
alter session set workarea_size_policy=manual;
alter session set sort_area_size=800000000;
declare
TYPE kpi_array is TABLE OF kpi%ROWTYPE;
l_kpi_array kpi_array:=kpi_array ();
start_time number;
elapsed number;
begin
for time in 1 .. 100
loop
for indid in 1 .. 7
loop
for insid in 1 .. 10000
loop
l_kpi_array.extend();
l_kpi_array(l_kpi_array.last).timeid:=time;
l_kpi_array(l_kpi_array.last).indid:=indid;
l_kpi_array(l_kpi_array.last).insid:=insid;
l_kpi_array(l_kpi_array.last).value:=dbms_random.value(1,1000);
end loop;
end loop;
end loop;
start_time:=DBMS_UTILITY.GET_TIME ;
forall p in l_kpi_array.first .. l_kpi_array.last
insert /*+ APPEND_VALUES */ into kpi
values l_kpi_array(p);
elapsed := DBMS_UTILITY.GET_TIME - start_time;
dbms_output.put_line('elapsed time (seconds)=' || elapsed/100 );
commit;
end;
elapsed time (seconds)=13.9 seconds
As you can see the index column order still has a impact on the insertion performance.

Let’s compare the event 10032 (trace sort statistics) with the two different columns order:

first index (insid,indid,timeid) 

—- Sort Parameters ——————————
sort_area_size                                800000000
sort_area_retained_size               800000000
sort_multiblock_read_count       2
max intermediate merge width  21982
 
*** 2015-07-07 14:53:01.341
—- Sort Statistics ——————————
Input records                             7000000
Output records                            7000000
Total number of comparisons performed     150334451
  Comparisons performed by in-memory sort 150334451
Total amount of memory used               524142592
Uses version 1 sort
—- End of Sort Statistics ———————–
 

second index (timeid,indid,insid)

—- Sort Parameters ——————————
sort_area_size                                  800000000
sort_area_retained_size                800000000
sort_multiblock_read_count        2
max intermediate merge width    21982
 
*** 2015-07-07 15:05:19.599
—- Sort Statistics ——————————
Input records                             7000000
Output records                            7000000
Total number of comparisons performed     6999999
  Comparisons performed by in-memory sort 6999999
Total amount of memory used               459501568
Uses version 1 sort
—- End of Sort Statistics ———————–
 

You can see that the algorithm used for sorting is “version 1 sort”, as explained in this jonathan lewis blog this algorithm is a “binary insertion tree” so oracle builds a binary tree as reading data from memory.

Oracle has to compare each row to the current binary tree structure and as in the second example the data are already sorted in the same order than the index only 6999999 comparaisons (7 Millions – 1) were done compared to the previous example where it has performed 150334451 comparaisons.

This sorting cost could  also be verified by checking ‘CPU used by this session’ session statistic, please take in account that this statistic includes the creation of the nested table and the insert statement (all other statistics were almost the same )

first index column order :       5432 cs
second index column order :      1891 cs

Conclusion :

When using direct path insert the index maintenance is deferred  so oracle is able to process index operation by array even if the data pattern is not sorted in the same way that our index column order.

We have also demonstrated that the sorting algorithm keeps taking advantage of sorted data pattern and good index order even in direct path insert mode.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s