SAP Knowledge Base Article - Preview

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

Symptom

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.


Read more...

Environment

SAP IQ 16.0 SP11 PL11

Product

SAP IQ 16.0

Keywords

 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

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP ONE Support launchpad (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.