2568019 - An initial loading performance after upgrading to IQ 16.0 from IQ 15.4 was so bad.

1. The performance problem happened when loading data into some huge tables(with hg indexes) for the first time after upgrading IQ16 SP11 PL11 from IQ15.4.
The load performance was also so poor even when loading an empty sam file.
Once the initial loading completed, the subsequent load jobs against those tables worked well.

2. The query plan of the load table command was generated when loading an empty file.
Even though there was no rows to load from the sam file, it took more than 11 hours.
The row count of the target table is 1,468,903,165.
According to the query plan, the root node shows that there were big time gap between cursor creation time and end of DFE generation

=============================================
Time of Cursor Creation 2017-11-20 16:11:19.192404
End of DFE Generation 2017-11-21 03:17:34.901687
...
Time of Complete Return 2017-11-21 03:17:35.851734
Elapsed Condition time 0:00:00.000000
Working time 11:06:16.613421
Elapsed time 11:06:16.659330
=============================================

3. The thread stack which was responsible for the loading data is as below.

----------------- lwp# 133 / thread# 133 --------------------
_pread (....................
__1cMhos_pi_pread6FnGhos_fd_pvIl_i_ (....................
__1cJhos_svbioFRead26MXpviri2pXpnSs_bufman_errorInfo__i_ (....................
__1cJhos_mvbioFRead26MXpviri2pXpnSs_bufman_errorInfo__i_ (....................
__1cFs_bufQReadBufAndVerify6MpnSs_bufman_errorInfo_pnRs_diskblockheader_XIIihXIipnTs_bufmanCallerStats__v_ (....................
__1cFs_bufERead6MpnSs_bufman_errorInfo_XIihXIIpknKs_blockmap_pnTs_bufmanCallerStats__v_ (....................
__1cOs_bufpartitionEFind6MpnSs_bufman_errorInfo_pnWs_connectionCacheStats_pnHhos_bio_XXXIIiIIhpnKs_blockmap_nIs_bufmanIs_paging_n0FLs_dirtyMode_IpnTs_bufmanCallerStats__pnFs_buf__ (....................
__1cKs_blockmapEFind6MXhipnTs_bufmanCallerStats__pnFs_buf__ (....................
__1cQs_pageListCursorMPositionPage6MX_pv_ (....................
__1cUs_garrayReadCursorV2PResettoNextPage6M_i_ (....................
__1cOhs_ohcidentitySTryUpgradeIdentity6MpnRhs_ohcIdentity_15_pnGhs_ohc_pnKhs_context_nKs_openmode__i_ (....................
__1cGhs_ohc2t5B6MpnLdb_IndexDef_nKs_openmode_pknFhs_dp__v_ (....................
__1cLdb_IndexDefSOpenSIndexWithLock6MI_pnHs_index__ (....................
__1cLdb_IndexDefKOpenSIndex6MI_pnHs_index__ (....................
__1cLdb_IndexDefOOpenSIdxInsert6MI_pnNs_indexInsert__ (....................
__1cIdf_TableLAddIndexDef6MpnLdb_IndexDef_i_v_ (....................
__1cNopt_LoadTableMGenerateDFEs6MpnHdf_Heap_pnMopt_QdDueryExp__v_ (....................
__1cKopt_DriverMGenerateTree6FpnJQdDueryNode_pnJst_cursor_pknLa_statement_pnHUITable_pnHUIIndex_pnEs_bm_pIppnTdb_RebuildIndexInfo_ipnHa_table_pnShos_vector_noerror_pnIhs_store_ipnWdb_IQdDParallelIndexInfo_pnQrv_MergeCmdState__pnIdfo_Root (....................
__1cMdb_LoadTableHExecute6M_v_ (....................

4. In case of removing the hg indexes before running the initial data loading, it worked well even at the initial data loading or insertion.

SAP IQ 16.0 SP11 PL11

 SAP IQ, IQ, load, load table, initial loading, first data loading, migration, IQ 16.0, performance, __1cMhos_pi_pread6FnGhos_fd_pvIl_i_, __1cJhos_svbioFRead26MXpviri2pXpnSs_bufman_errorInfo__i_, __1cJhos_mvbioFRead26MXpviri2pXpnSs_bufman_errorInfo__i_, CR 811985, CR#811985 , KBA , BC-SYB-IQ , Sybase IQ , Problem