23 января 2012

Оптимизация БД 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;

Комментариев нет:

Отправить комментарий