Skip to Content
avatar image
Former Member

Extra-long wait in merge phase

When running INSERT INTO <table> <select statement> in 10 min intervals to aggregate data from temporary tables and insert it to main datastore. All goes perfectly, but after 4-5 hours we observe that insert runs 10-100x times slower. In iqmsg logs we see following:

for 'long' insert:

I. 05/20 07:50:05. 0000021624 Load phase completed

I. 05/20 07:50:05. 0000021624 Merge in progress: 45%

I. 05/20 07:50:05. 0000021624 Merge in progress: 67%

I. 05/20 07:50:05. 0000021624 Merge in progress: 90%

I. 05/20 08:46:11. 0000021624 Merge in progress: 100%

I. 05/20 08:46:11. 0000021624 Merge phase completed

I. 05/20 08:46:11. 0000021624 [20896]: Insert for 'natflow_20_05_2016' completed in 3422 seconds. 9024346 rows inserted.

for 'normal' insert:

I. 05/20 09:10:16. 0000000133 Load phase completed

I. 05/20 09:10:16. 0000000133 Merge in progress: 45%

I. 05/20 09:10:16. 0000000133 Merge in progress: 67%

I. 05/20 09:10:16. 0000000133 Merge in progress: 90%

I. 05/20 09:10:25. 0000000133 Merge in progress: 100%

I. 05/20 09:10:25. 0000000133 Merge phase completed

I. 05/20 09:10:25. 0000000133 [20896]: Insert for 'natflow_20_05_2016' completed in 23 seconds.  18966510 rows inserted.

no other activity in database, only inserts. After restarting SAP IQ all goes to normal until next 5 hours.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    May 20, 2016 at 04:00 PM

    Questions:

    What are your cache configurations (-iqlm, -iqtc, -iqmc)?

    How many main and temp devices do you have?  RLV space?  Are they all on the same disk?
    Are you using RLV and is it enabled for this table?  If you are using RLV, what is the -iqrlvmem cache size?  Do you really need RLV (i.e. is there some insert concurrency on this table that needs RLV?

    In a 'normal' process, does the table start empty?  Does it have a primary key?

    We need more details

    Chris

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 20, 2016 at 03:57 PM

    Hi Oleg,

       Can you please check the iostat/vmstat during NORMAL and this SLOW period? Many times in my life as support architect, I have seen that this points to disks getting flooded with I/O requests and then they cannot handle those in timely manner. That is first area to check, we also need html plans form IQ during such insert into Queries to look at , set following options

    set temporary option query_plan = 'ON';

    set temporary option query_detail = 'ON';

    set temporary option query_plan_as_html ='ON' ;

    set temporary option query_plan_after_run='on';

    set temporary option query_timing='on';

    set temporary option DML_OPTIONS10='on';

    set temporary option FORCE_NO_SCROLL_CURSORS ='on' ;

    set temporary option index_advisor='on' ;

    Add comment
    10|10000 characters needed characters exceeded

  • May 20, 2016 at 04:21 PM

    Hi,

    What is the exact IQ version (select @@version)?

    In next insert test, I would suggest try option below before executing the insert and see if it helps:

    -- check current setting using sp_iqcheckoptions ;

    -- Change option in current SQL user session

    set TEMPORARY OPTION  CORE_Options71 = 0  ;

    -- check new setting using sp_iqcheckoptions ;

    -- execute the insert

    Insert into ... ;



    Regards,

    Tayeb


    Add comment
    10|10000 characters needed characters exceeded