OGL EPF : Slow Insert into FEM_INTG_DIM_HIER_GT During Hierarchy Load with Multiple Segments (Doc ID 422374.1)

Last updated on MAY 15, 2010

Applies to:

Oracle Enterprise Performance Foundation - Version: 11.5.10 to 11.5.10.2 - Release: 11.5 to 11.5
Information in this document applies to any platform.
OGL EPF Data Integration
Checked for relevance 15-May-2010.

Symptoms

In Enterprise Performance Foundation Administrator, when running a General Ledger Hierarchy Rule to transfer a hierarchy from General Ledger (GL) into Enterprise Performance Foundation (FEM), inserts into the global temporary table FEM_INTG_DIM_HIER_GT are using a lot of CPU and are very slow.  The dimension associated with the hierarchy contains a concatenation of multiple GL segments.

A tkprof'd SQL trace with the explain plan option shows that a full table scan is performed on FEM_INTG_DIM_HIER_GT:

INSERT INTO FEM_INTG_DIM_HIER_GT 
(HIERARCHY_OBJ_DEF_ID
, child_display_code
, child_id
, parent_display_code
, parent_id
, child_depth_num
, parent_depth_num
, child_leaf_flag
, single_depth_flag
, display_order_num
)
(SELECT distinct 10060
, gtm.parent_display_code
, gtm.parent_id
, gts.parent_display_code || '-' || substr(gtm.parent_display_code,
instr(gtm.parent_display_code, '-')+1)
, -1
, gtm.parent_depth_num
, gtm.parent_depth_num-1
, 'N'
, 'N'
, -1
FROM
FEM_INTG_DIM_HIER_GT gtm
, FEM_INTG_DIM_HIER_C1_GT gts
WHERE gts.child_display_code = substr(gtm.parent_display_code, 1, instr(gtm.parent_display_code,
'-')-1)
AND gts.child_display_code <> gts.parent_display_code
and not exists (select 1 from FEM_INTG_DIM_HIER_GT gte where gte.child_display_code =
gtm.parent_display_code))
call    count     cpu    elapsed     disk       query     current     rows 
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.03 0.01 0 99 0 0
Execute 4 10732.57 10567.65 4455 106713624 49 1959
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 10732.60 10567.67 4455 106713723 49 1959
Misses in library cache during parse: 1 
Optimizer goal: CHOOSE
Parsing user id: 37 (APPS) (recursive depth: 1)
Rows     Execution Plan 
------- ---------------------------------------------------
0 INSERT STATEMENT GOAL: CHOOSE
0 SORT (UNIQUE)
0 FILTER
0 MERGE JOIN
0 SORT (JOIN)
0 TABLE ACCESS (FULL) OF 'FEM_INTG_DIM_HIER_C1_GT'
0 SORT (JOIN)
0 TABLE ACCESS (FULL) OF 'FEM_INTG_DIM_HIER_GT'
0 TABLE ACCESS (FULL) OF 'FEM_INTG_DIM_HIER_GT'

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms