RPAS Server: timeshift Procedure Performance Issue -- Causes Both Batch and Workbook Build Performance Degradations (Doc ID 2346991.1)

Last updated on FEBRUARY 03, 2019

Applies to:

Symptoms

timeshift has a serious design problem which is causing performance issues.   The procedure is written to essentially create a 'clone' of the source measure from the local domain in an rtk**** db in the workbook.  This appears to be done via a lag and we are cloning the whole array from the local instead of only cloning the positions that will be timeshifted, i.e. the positions in the workbook.    If we MUST clone the whole local array into a temp db (which I don't understand why we would), then we should simply do a straight 'cp' to speed up the performance.   Otherwise, please only bring relevant data into the temp array that is used for the timeshift.

Below, the workbook only ends up with 144561 cells in the destination measure.  However, we create a 310 Million popcount temporary array in the workbook which takes 94 of the 95 seconds the procedure takes to run.  

In a small 41 MB workbook, it takes ~7 minutes to build the workbook due to multiple timeshift procedures, which take 95% of the wb build time.

P 2017Dec01 11:52:21.573> TSI:4  0.000931 seconds for CalcEngine::aggForExpression

=========================================================
CalcEngine: special expression: timeshift eval()
Calculating Special Expression BPASBOPU <-timeshift(LDTYBOPU.master, AdAlWeekMapTx.master )
FIRST PASS clearing dest
Src DB:       ldom15/data/ldtybopu
Src Array:    ldtybopu%1
Src NA Value: 0
Src Popcount: 310121223
Src Logical:  15988554891

lag is scalar:
Dest DB:    ldom15/users/perftest/t00086/rtk_001380i0011
Dest Array:    tshifttmpdst
Dest NA Value:
Dest Popcount: 0
Dest Logical:  15988554891

LagMap DB:       data/adalweekmaptx
LagMap Array:    adalweekmaptx%1
LagMap NA Value:
LagMap Popcount: 261
LagMap Logical:  261

ApplyLagWithLagmap()
LAG finished: 310121223 total populated cells

=========================================================

TSI:5 94.157246 seconds for Lag::run

=========================================================
Destination Array:
  DB:       ldom15/users/perftest/t00086/t00086_1
  Array:    bpasbopu%1
  NA Value: 0
  Popcount: 0

src Array:
  DB:       ldom15/users/perftest/t00086/rtk_001380i0011
  Array:    tshifttmpdst
  NA Value: 0
  Popcount: 310121223

=========================================================

TSI:4 95.933545 seconds for SpecialExpression::eval

=========================================================
LHS Measure Info
  measure:       bpasbopu
  popcount:      144561
  nobcount:      382660
  baseint:       strestycweek
  navalue:       0
  isbase:        true

RHS Measure Info
  measure:       ldtybopu
  popcount:      310121223
  nobcount:      15988554891
  baseint:       strestycweek
  navalue:       0
  isbase:        true

RHS Measure Info
  measure:       adalweekmaptx
  popcount:      261
  nobcount:      261
  baseint:       week
  navalue:      
  isbase:        true

=========================================================

TSI:3 95.944968 seconds for CalcEngine::evalSingleExpression BPASBOPU <-timeshift(LDTYBOPU.master, AdAlWeekMapTx.master )

=========================================================

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.