Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
OBIA 7.9.6.4 Informatica Task hanging
Hi All,
Informatica mapping is running more than 7hr, no records are insert into target table.yesterday it was completed in 10min.
here is the session log
2017-02-28 15:05:10 : INFO : (21954570 | DIRECTOR) : (IS | BIA_IS_PRD) : node01_Prodserver : TM_6228 : Writing session output to log file [/Informatica/9.5.1/server/infa_shared/SessLogs/.SDEINC_FindAux_S_ORG_EXT_1.SEBL_811.log].
2017-02-28 15:05:10 : INFO : (21954570 | DIRECTOR) : (IS | BIA_IS_PRD) : node01_Prodserver : VAR_27028 : Use override value [0] for user-defined workflow/worklet variable:[$$passInStatus].
2017-02-28 15:05:10 : INFO : (21954570 | DIRECTOR) : (IS | BIA_IS_PRD) : node01_Prodserver : VAR_27028 : Use override value [SEBL_811] for session parameter:[$DBConnection_OLTP].
2017-02-28 15:05:10 : INFO : (21954570 | DIRECTOR) : (IS | BIA_IS_PRD) : node01_Prodserver : VAR_27028 : Use override value [.SDEINC_FindAux_S_ORG_EXT_1.SEBL_811.log] for session parameter:[$PMSessionLogFile].
2017-02-28 15:05:10 : INFO : (21954570 | DIRECTOR) : (IS | BIA_IS_PRD) : node01_Prodserver : VAR_27028 : Use override value [] for mapping parameter:[$$Hint1].
2017-02-28 15:05:10 : INFO : (21954570 | DIRECTOR) : (IS | BIA_IS_PRD) : node01_Prodserver : VAR_27028 : Use override value [] for mapping parameter:[$$Hint2].
2017-02-28 15:05:10 : INFO : (21954570 | DIRECTOR) : (IS | BIA_IS_PRD) : node01_Prodserver : TM_6014 : Initializing session [SDEINC_FindAux_S_ORG_EXT_1] at [Tue Feb 28 11:35:10 2017].
2017-02-28 15:05:10 : INFO : (21954570 | DIRECTOR) : (IS | BIA_IS_PRD) : node01_Prodserver : TM_6683 : Repository Name: [BIA_RS_PRD]
2017-02-28 15:05:10 : INFO : (21954570 | DIRECTOR) : (IS | BIA_IS_PRD) : node01_Prodserver : TM_6684 : Server Name: [BIA_IS_PRD]
2017-02-28 15:05:10 : INFO : (21954570 | DIRECTOR) : (IS | BIA_IS_PRD) : node01_Prodserver : TM_6686 : Folder: [SDE_SBL_811_Adaptor]
2017-02-28 15:05:10 : INFO : (21954570 | DIRECTOR) : (IS | BIA_IS_PRD) : node01_Prodserver : TM_6685 : Workflow: [SDEINC_FindAux_S_ORG_EXT] Run Instance Name: [] Run Id: [821124]
2017-02-28 15:05:10 : INFO : (21954570 | DIRECTOR) : (IS | BIA_IS_PRD) : node01_Prodserver : TM_6101 : Mapping name: SDEINC_FindAux_S_ORG_EXT_1 [version 1].
2017-02-28 15:05:10 : INFO : (21954570 | DIRECTOR) : (IS | BIA_IS_PRD) : node01_Prodserver : TM_6963 : Pre 85 Timestamp Compatibility is Enabled
2017-02-28 15:05:10 : INFO : (21954570 | DIRECTOR) : (IS | BIA_IS_PRD) : node01_Prodserver : TM_6964 : Date format for the Session is [MM/DD/YYYY HH24:MI:SS]
2017-02-28 15:05:10 : INFO : (21954570 | DIRECTOR) : (IS | BIA_IS_PRD) : node01_Prodserver : TM_6827 : [/Informatica/9.5.1/server/infa_shared/Storage] will be used as storage directory for session [SDEINC_FindAux_S_ORG_EXT_1].
2017-02-28 15:05:10 : INFO : (21954570 | DIRECTOR) : (IS | BIA_IS_PRD) : node01_Prodserver : CMN_1805 : Recovery cache will be deleted when running in normal mode.
2017-02-28 15:05:10 : INFO : (21954570 | DIRECTOR) : (IS | BIA_IS_PRD) : node01_Prodserver : CMN_1802 : Session recovery cache initialization is complete.
2017-02-28 15:05:10 : INFO : (21954570 | DIRECTOR) : (IS | BIA_IS_PRD) : node01_Prodserver : TM_6708 : Using configuration property [overrideMpltVarWithMapVar,Yes]
2017-02-28 15:05:10 : INFO : (21954570 | DIRECTOR) : (IS | BIA_IS_PRD) : node01_Prodserver : TM_6708 : Using configuration property [AggSupprtWithNoPartLic,Yes]
2017-02-28 15:05:10 : INFO : (21954570 | DIRECTOR) : (IS | BIA_IS_PRD) : node01_Prodserver : TM_6703 : Session [SDEINC_FindAux_S_ORG_EXT_1] is run by 64-bit Integration Service [node01_Prodserver], version [9.5.1 HotFix2], build [0621].
2017-02-28 15:05:10 : INFO : (21954570 | MANAGER) : (IS | BIA_IS_PRD) : node01_Prodserver : PETL_24058 : Running Partition Group [1].
2017-02-28 15:05:10 : INFO : (21954570 | MANAGER) : (IS | BIA_IS_PRD) : node01_Prodserver : PETL_24000 : Parallel Pipeline Engine initializing.
2017-02-28 15:05:10 : INFO : (21954570 | MANAGER) : (IS | BIA_IS_PRD) : node01_Prodserver : PETL_24001 : Parallel Pipeline Engine running.
2017-02-28 15:05:10 : INFO : (21954570 | MANAGER) : (IS | BIA_IS_PRD) : node01_Prodserver : PETL_24003 : Initializing session run.
2017-02-28 15:05:10 : INFO : (21954570 | MAPPING) : (IS | BIA_IS_PRD) : node01_Prodserver : CMN_1569 : Server Mode: [UNICODE]
2017-02-28 15:05:10 : INFO : (21954570 | MAPPING) : (IS | BIA_IS_PRD) : node01_Prodserver : CMN_1570 : Server Code page: [ISO 8859-1 Western European]
2017-02-28 15:05:10 : INFO : (21954570 | MAPPING) : (IS | BIA_IS_PRD) : node01_Prodserver : TM_6151 : The session sort order is [Binary].
2017-02-28 15:05:10 : INFO : (21954570 | MAPPING) : (IS | BIA_IS_PRD) : node01_Prodserver : TM_6156 : Using low precision processing.
2017-02-28 15:05:10 : INFO : (21954570 | MAPPING) : (IS | BIA_IS_PRD) : node01_Prodserver : TM_6180 : Deadlock retry logic will not be implemented.
2017-02-28 15:05:10 : INFO : (21954570 | MAPPING) : (IS | BIA_IS_PRD) : node01_Prodserver : TM_6187 : Session target-based commit interval is [9000].
2017-02-28 15:05:10 : INFO : (21954570 | MAPPING) : (IS | BIA_IS_PRD) : node01_Prodserver : TM_6307 : DTM error log disabled.
2017-02-28 15:05:10 : INFO : (21954570 | MAPPING) : (IS | BIA_IS_PRD) : node01_Prodserver : TE_7022 : TShmWriter: Initialized
2017-02-28 15:05:10 : INFO : (21954570 | MAPPING) : (IS | BIA_IS_PRD) : node01_Prodserver : DBG_21694 : AGGTRANS Partition [0]: Index cache size = [1048576], Data cache size = [2097152]
2017-02-28 15:05:10 : INFO : (21954570 | MAPPING) : (IS | BIA_IS_PRD) : node01_Prodserver : TE_7212 : Increasing [Index Cache] size for transformation [AGGTRANS] from [1048576] to [1051904].
2017-02-28 15:05:10 : INFO : (21954570 | MAPPING) : (IS | BIA_IS_PRD) : node01_Prodserver : TE_7212 : Increasing [Data Cache] size for transformation [AGGTRANS] from [2097152] to [2097216].
2017-02-28 15:05:10 : INFO : (21954570 | MAPPING) : (IS | BIA_IS_PRD) : node01_Prodserver : TE_7029 : Aggregate Information: Creating New Index and Data Files
2017-02-28 15:05:10 : INFO : (21954570 | MAPPING) : (IS | BIA_IS_PRD) : node01_Prodserver : TE_7034 : Aggregate Information: Index file is [/Informatica/9.5.1/server/infa_shared/Cache/PMAGG5004_3_0_821124.idx]
2017-02-28 15:05:10 : INFO : (21954570 | MAPPING) : (IS | BIA_IS_PRD) : node01_Prodserver : TE_7035 : Aggregate Information: Data file is [/Informatica/9.5.1/server/infa_shared/Cache/PMAGG5004_3_0_821124.dat]
2017-02-28 15:05:10 : INFO : (21954570 | MAPPING) : (IS | BIA_IS_PRD) : node01_Prodserver : TM_6007 : DTM initialized successfully for session [SDEINC_FindAux_S_ORG_EXT_1]
2017-02-28 15:05:10 : INFO : (21954570 | DIRECTOR) : (IS | BIA_IS_PRD) : node01_Prodserver : PETL_24033 : All DTM Connection Info: [<NONE>].
2017-02-28 15:05:10 : INFO : (21954570 | MANAGER) : (IS | BIA_IS_PRD) : node01_Prodserver : PETL_24004 : PETL_24004 Starting pre-session tasks. : (Tue Feb 28 11:35:10 2017)
2017-02-28 15:05:10 : INFO : (21954570 | MANAGER) : (IS | BIA_IS_PRD) : node01_Prodserver : PETL_24027 : PETL_24027 Pre-session task completed successfully. : (Tue Feb 28 11:35:10 2017)
2017-02-28 15:05:10 : INFO : (21954570 | DIRECTOR) : (IS | BIA_IS_PRD) : node01_Prodserver : PETL_24006 : Starting data movement.
2017-02-28 15:05:10 : INFO : (21954570 | MAPPING) : (IS | BIA_IS_PRD) : node01_Prodserver : TM_6660 : Total Buffer Pool size is 36000000 bytes and Block size is 128000 bytes.
2017-02-28 15:05:10 : INFO : (21954570 | READER_1_1_1) : (IS | BIA_IS_PRD) : node01_Prodserver : DBG_21438 : Reader: Source is [PCRM], user [SU_IS_OBIEE]
2017-02-28 15:05:10 : INFO : (21954570 | READER_1_1_1) : (IS | BIA_IS_PRD) : node01_Prodserver : BLKR_16051 : Source database connection [SEBL_811] code page: [MS Windows Latin 1 (ANSI), superset of Latin1]
2017-02-28 15:05:10 : INFO : (21954570 | READER_1_1_1) : (IS | BIA_IS_PRD) : node01_Prodserver : BLKR_16003 : Initialization completed successfully.
2017-02-28 15:05:10 : INFO : (21954570 | WRITER_1_*_1) : (IS | BIA_IS_PRD) : node01_Prodserver : WRT_8147 : Writer: Target is database [PCRM], user [SU_IS_OBIEE], bulk mode [OFF]
2017-02-28 15:05:10 : INFO : (21954570 | WRITER_1_*_1) : (IS | BIA_IS_PRD) : node01_Prodserver : WRT_8221 : Target database connection [SEBL_811] code page: [MS Windows Latin 1 (ANSI), superset of Latin1]
2017-02-28 15:05:10 : INFO : (21954570 | WRITER_1_*_1) : (IS | BIA_IS_PRD) : node01_Prodserver : WRT_8124 : Target Table S_ETL_I_IMG_26 :SQL INSERT statement:
INSERT INTO S_ETL_I_IMG_26(ROW_ID,MODIFICATION_NUM,OPERATION,LAST_UPD) VALUES ( ?, ?, ?, ?)
2017-02-28 15:05:10 : INFO : (21954570 | WRITER_1_*_1) : (IS | BIA_IS_PRD) : node01_Prodserver : WRT_8124 : Target Table S_ETL_I_IMG_26 :SQL UPDATE statement:
UPDATE S_ETL_I_IMG_26 SET MODIFICATION_NUM = ?, OPERATION = ?, LAST_UPD = ? WHERE ROW_ID = ?
2017-02-28 15:05:10 : INFO : (21954570 | WRITER_1_*_1) : (IS | BIA_IS_PRD) : node01_Prodserver : WRT_8124 : Target Table S_ETL_I_IMG_26 :SQL DELETE statement:
DELETE FROM S_ETL_I_IMG_26 WHERE ROW_ID = ?
2017-02-28 15:05:10 : INFO : (21954570 | WRITER_1_*_1) : (IS | BIA_IS_PRD) : node01_Prodserver : WRT_8270 : Target connection group #1 consists of target(s) [S_ETL_I_IMG_26]
2017-02-28 15:05:10 : INFO : (21954570 | WRITER_1_*_1) : (IS | BIA_IS_PRD) : node01_Prodserver : WRT_8003 : Writer initialization complete.
2017-02-28 15:05:10 : INFO : (21954570 | WRITER_1_*_1) : (IS | BIA_IS_PRD) : node01_Prodserver : WRT_8005 : Writer run started.
2017-02-28 15:05:10 : INFO : (21954570 | WRITER_1_*_1) : (IS | BIA_IS_PRD) : node01_Prodserver : WRT_8158 :
*****START LOAD SESSION*****
Load Start Time: Tue Feb 28 11:35:10 2017
Target tables:
S_ETL_I_IMG_26
2017-02-28 15:05:10 : INFO : (21954570 | READER_1_1_1) : (IS | BIA_IS_PRD) : node01_Prodserver : BLKR_16007 : Reader run started.
2017-02-28 15:05:10 : INFO : (21954570 | READER_1_1_1) : (IS | BIA_IS_PRD) : node01_Prodserver : RR_4029 : SQ Instance [SQ_ALL_S_TABLES] User specified SQL Query [SELECT
S_ORG_EXT.ROW_ID,
S_ORG_EXT.MODIFICATION_NUM,
S_ORG_EXT.LAST_UPD
FROM
S_ORG_EXT,
S_ORG_EXT_X,
S_ETL_I_IMG_27 IMG
WHERE
(
IMG.ROW_ID = S_ORG_EXT_X.ROW_ID
AND
S_ORG_EXT_X.PAR_ROW_ID = S_ORG_EXT.ROW_ID
)
AND NOT EXISTS
( SELECT 'X'
FROM
S_ETL_I_IMG_26 IMG1
WHERE
IMG1.ROW_ID = S_ORG_EXT.ROW_ID
)
UNION ALL
SELECT
S_ORG_EXT.ROW_ID,
S_ORG_EXT.MODIFICATION_NUM,
S_ORG_EXT.LAST_UPD
FROM
S_ORG_EXT,
S_ETL_I_IMG_46 IMG
WHERE
(
IMG.ROW_ID = S_ORG_EXT.ROW_ID
)
AND NOT EXISTS
( SELECT 'X'
FROM
S_ETL_I_IMG_26 IMG1
WHERE
IMG1.ROW_ID = S_ORG_EXT.ROW_ID
)
UNION ALL
SELECT
S_ORG_EXT.ROW_ID,
S_ORG_EXT.MODIFICATION_NUM,
S_ORG_EXT.LAST_UPD
FROM
S_ORG_EXT,
S_ETL_I_IMG_1 IMG
WHERE
(
IMG.ROW_ID = S_ORG_EXT.PR_ADDR_ID
)
AND NOT EXISTS
( SELECT 'X'
FROM
S_ETL_I_IMG_26 IMG1
WHERE
IMG1.ROW_ID = S_ORG_EXT.ROW_ID
)
UNION ALL
SELECT
S_ORG_EXT.ROW_ID,
S_ORG_EXT.MODIFICATION_NUM,
S_ORG_EXT.LAST_UPD
FROM
S_ORG_EXT,
S_ORG_EXT_T,
S_ETL_I_IMG_81 IMG
WHERE
(
IMG.ROW_ID = S_ORG_EXT_T.ROW_ID
AND
S_ORG_EXT_T.PAR_ROW_ID = S_ORG_EXT.ROW_ID
)
AND NOT EXISTS
( SELECT 'X'
FROM
S_ETL_I_IMG_26 IMG1
WHERE
IMG1.ROW_ID = S_ORG_EXT.ROW_ID
)
UNION ALL
SELECT
S_ORG_EXT.ROW_ID,
S_ORG_EXT.MODIFICATION_NUM,
S_ORG_EXT.LAST_UPD
FROM
S_ORG_EXT,
S_ETL_I_IMG_21 IMG
WHERE
(
IMG.ROW_ID = S_ORG_EXT.PR_INDUST_ID
)
AND NOT EXISTS
( SELECT 'X'
FROM
S_ETL_I_IMG_26 IMG1
WHERE
IMG1.ROW_ID = S_ORG_EXT.ROW_ID
)
UNION ALL
SELECT
S_ORG_EXT.ROW_ID,
S_ORG_EXT.MODIFICATION_NUM,
S_ORG_EXT.LAST_UPD
FROM
S_ORG_EXT,
S_ORG_PRTNR,
S_ETL_I_IMG_82 IMG
WHERE
(
IMG.ROW_ID = S_ORG_PRTNR.ROW_ID
AND
S_ORG_PRTNR.PAR_ROW_ID = S_ORG_EXT.ROW_ID
)
AND NOT EXISTS
( SELECT 'X'
FROM
S_ETL_I_IMG_26 IMG1
WHERE
IMG1.ROW_ID = S_ORG_EXT.ROW_ID
)
UNION ALL
SELECT
S_ORG_EXT.ROW_ID,
S_ORG_EXT.MODIFICATION_NUM,
S_ORG_EXT.LAST_UPD
FROM
S_ORG_EXT,
S_ETL_I_IMG_83 IMG
WHERE
(
IMG.ROW_ID = S_ORG_EXT.PR_PTSHP_MKTSEG_ID
)
AND NOT EXISTS
( SELECT 'X'
FROM
S_ETL_I_IMG_26 IMG1
WHERE
IMG1.ROW_ID = S_ORG_EXT.ROW_ID
)]
2017-02-28 15:05:10 : INFO : (21954570 | READER_1_1_1) : (IS | BIA_IS_PRD) : node01_Prodserver : RR_4049 : RR_4049 SQL Query issued to database : (Tue Feb 28 11:35:10 2017)
Answers
-
is the Source Qualifier query fetched any records? if not consult DBA for the issue on the SQL Execution in DB.
0 -
Did you monitor the session on the source database and verify it was actually running? You should be able to capture the SQL from v$sql and hence the plan. Then you can start looking at where the time is being taken through things like longops, active session history and so on.
0