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
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 for Me (Login required).Search for additional results
Visit SAP Support Portal's SAP Notes and KBA Search.