In some large environments, you may observe that the Strategy Enterprise Manager (EM) dataload takes a long time to load, due to the SP_IS_SESSION sometimes taking up to 36 hours to complete.
If you check the EM dataload log, you can see a log like the following:
<log4j:event logger="MAEntMgr" Date="2020-01-07 17:06:28" level="INFO" thread="pool-1-thread-1"> <log4j:message type ="DEFAULT"><![CDATA[Executing SP_IS_SESSION]]></log4j:message></log4j:event> <log4j:event logger="MAEntMgr" Date="2020-01-08 00:42:13" level="INFO" thread="pool-1-thread-1"> <log4j:message type ="DEFAULT"><![CDATA[Executing SP_EM_SESSION]]></log4j:message></log4j:event>
After reviewing the Enterprise Manager Warehouse, there are more than 130 million data rows in the table "is_session_stats." This is a very large amount and causes the procedure "SP_IS_SESSION" to take a long time each time it is ran. Next, check the DB server status to see which query is taking a long time. On the server status, there is one statement, "INSERT INTO IS_SESSION_TMP1...," which was executed more than 18,290 seconds, and is still running. This should be the root causing that results in the procedure taking a long time.
Upon reviewing the procedure, it was found that "is_session_stats"is used to join against other tables. This will use a lot of resources, leading to slowed performance.
FROM IS_SESSION_ST_VW ST
LEFT JOIN EM_MD MD ON MD.REP_GUID = ST.REPOSITORYID
LEFT OUTER JOIN EM_USER U ON ST.USERID = U.EM_USER_GUID AND MD.REP_ID = U.IS_REPOSITORY_ID
LEFT OUTER JOIN IS_SERVER S ON ST.SERVERID = S.IS_SERVER_GUID
CROSS JOIN EM_IS_LAST_UPD_1 LU
WHERE ((ST.CONNECTTIME >=LU.IS_WIN_BEGIN AND ST.CONNECTTIME < LU.IS_WIN_END)
OR (ST.RECORDTIME >= LU.IS_WIN_BEGIN AND ST.RECORDTIME < LU.IS_WIN_END))
AND ST.CONNECTTIME IS NOT NULL
Most data in "is_session_stats" are historical data and irrelevant during the data load, so you should ignore them and not include them in the JOIN clause. You are suggested to use the time window to first filter the data to reduce the amount of data. Then, you should perform the join. This will improve performance.
You should only use the table "is_session_stats" and "em_is_last_upd_1" to create a view:
CREATE algorithm=undefined
SQL security definer view 'is_session_st_upd_vw'
AS SELECT
............
FROM ('is_sessoin_stats 'st'
JOIN 'em_is_last_upd' 'lu'
ON (( 1 = 1 ))
WHERE ((( 'st'.'connecttime' >= 'lu'.'is_win_begin')
AND ( 'st'.'connecttime' < 'lu'.'is_win_end'))
OR ( 'st'.'recordtime' >= 'lu'.'is_win_begin')
AND ( 'st'.'recordtime' >= 'lu'.'is_win_end')))
GROUP BY 'st'.'sessionid';
Use this view as the FROM clause of the insert statement:
INSERT INTO
..........
FROM IS_SESSION_ST_UPD_VW ST
LEFT JOIN EM_MD MD
ON MD.REP_GUID = ST.REPOSITORYID
LEFT OUTER JOIN EM_USER U
ON ST.USERID = U.EM_USER_GUID
AND MD.REP_ID = U.IS_REPOSITORY_ID
LEFT OUTER JOIN IS_SERVER S
ON ST.SERVERID = S.IS_SERVER_GUID
Then, apply this procedure on the Enterprise Manager Warehouse. The data load will finish in about one hour and the procedure "SP_IS_SESSION" will take less than ten minutes.
<log4j:event logger="MAEntMgr" Date="2020-01-16 05:09:16" level="INFO" thread="pool-1-thread-2"> <log4j:message type ="DEFAULT"><![CDATA[Executing SP_IS_SESSION]]></log4j:message></log4j:event> <log4j:event logger="MAEntMgr" Date="2020-01-16 05:13:53" level="INFO" thread="pool-1-thread-2"> <log4j:message type ="DEFAULT"><![CDATA[Executing SP_EM_SESSION]]></log4j:message></log4j:event>
