Оптимизация БД EPMA для EPM 11.1.x

Со временем сильно увеличивается БД EPM Architect, что негативно сказывается на скорости работы этого компонента. А в некоторых случаях, веб-сервер не успевает получить ответ в установленный таймаут, и возникает проблема, когда приложение (наример, Planning) может остаться в подвешенном состоянии.

Ниже представлено решение этой проблемы для EPM 11.1.3+

1. Остановить службу EPMA Process Manager
2. Сделать полный бэкап БД EPM Architect
3. Запустить службу EPMA Process Manager
4. Запустить утилиту database_cleanup_utility.exe, которая находится
   либо здесь для 11.1.1.x: \Hyperion\products\Foundation\BPMA\AppServer\DimensionServer\ServerEngine\bin\
   либо здесь для 11.1.2.x: \Oracle\Middleware\EPMSystem11R1\products\Foundation\BPMA\AppServer\DimensionServer\ServerEngine\bin
5. Использовать администраторский логин/пароль EPM
6. Запустить очистку для записей старше 45-60 дней
7. Либо почистить БД EPM Architect скриптом (пример для Oracle DB 10g+):
   

   SELECT TO_CHAR(SYSDATE,'Dy DD-Mon-YYYY HH24:MI:SS') AS "Current Time 1" FROM DUAL;
   
   CREATE TABLE tmp_DS_MaxTransactions NOLOGGING AS
   SELECT a.i_application_id, MAX(i_transaction_id) AS i_transaction_id
     FROM DS_Transaction_History a
     JOIN DS_Application b
     ON a.i_library_id = b.i_library_id
     AND a.i_application_id = b.i_application_id
     JOIN DS_Library c
     ON a.i_library_id = c.i_library_id
     WHERE c.b_master = 1
     AND a.i_application_id IS NOT NULL
     AND (b.i_first_deploy_transaction_id IS NOT NULL OR b.b_master = 1)
     AND i_transaction_id >= COALESCE(b.i_last_deployed_transaction_id, b.i_first_deploy_transaction_id, 0)
     AND e_transaction_action = 'OrderChildren'
     GROUP BY a.i_application_id;
   COMMIT;
   
   SELECT TO_CHAR(SYSDATE,'Dy DD-Mon-YYYY HH24:MI:SS') AS "Current Time 2" FROM DUAL;
   
   -- Keep newest OrderChildren row for each application.  Logging resumes for this table
   -- after it is created and populated by this statement.
   CREATE TABLE tmp_DS_Transaction_History NOLOGGING AS
     SELECT a.* FROM DS_Transaction_History a
       JOIN tmp_DS_MaxTransactions b
       ON a.i_transaction_id = b.i_transaction_id;
   COMMIT;
   
   SELECT TO_CHAR(SYSDATE,'Dy DD-Mon-YYYY HH24:MI:SS') AS "Current Time 3" FROM DUAL;
   
   DROP TABLE tmp_DS_MaxTransactions;
   
   SELECT TO_CHAR(SYSDATE,'Dy DD-Mon-YYYY HH24:MI:SS') AS "Current Time 4" FROM DUAL;
   
   ALTER TABLE tmp_DS_Transaction_History NOLOGGING;
   
   SELECT TO_CHAR(SYSDATE,'Dy DD-Mon-YYYY HH24:MI:SS') AS "Current Time 5" FROM DUAL;
   
   -- Add back all non-OrderChildren rows.
   INSERT INTO tmp_DS_Transaction_History
     SELECT * FROM DS_Transaction_History
     WHERE e_transaction_action != 'OrderChildren';
   COMMIT;
   
   SELECT TO_CHAR(SYSDATE,'Dy DD-Mon-YYYY HH24:MI:SS') AS "Current Time 6" FROM DUAL;
   
   DROP TABLE DS_Transaction_History;
   
   SELECT TO_CHAR(SYSDATE,'Dy DD-Mon-YYYY HH24:MI:SS') AS "Current Time 7" FROM DUAL;
   
   RENAME tmp_DS_Transaction_History TO DS_Transaction_History;
   
   SELECT TO_CHAR(SYSDATE,'Dy DD-Mon-YYYY HH24:MI:SS') AS "Current Time 8" FROM DUAL;
   
   ALTER TABLE DS_Transaction_History
     ADD CONSTRAINT PK_TranHist PRIMARY KEY (i_transaction_id, i_detail_id);
   
   SELECT TO_CHAR(SYSDATE,'Dy DD-Mon-YYYY HH24:MI:SS') AS "Current Time 9" FROM DUAL;
   
   CREATE INDEX IdxTranHist01 ON DS_Transaction_History(i_library_id, i_dimension_id, i_member_id);
   
   SELECT TO_CHAR(SYSDATE,'Dy DD-Mon-YYYY HH24:MI:SS') AS "Current Time 10" FROM DUAL;
   
   CREATE INDEX IdxTranHist02 ON DS_Transaction_History(i_library_id, d_created);
   
   SELECT TO_CHAR(SYSDATE,'Dy DD-Mon-YYYY HH24:MI:SS') AS "Current Time 11" FROM DUAL;
   
   ALTER TABLE DS_Transaction_History LOGGING;
   
   SELECT TO_CHAR(SYSDATE,'Dy DD-Mon-YYYY HH24:MI:SS') AS "Current Time 12" FROM DUAL;