Friday, April 17, 2015

Optimize Domain and Repository Size and Performance



Informatica Admin team will receive concerns from developers saying that development repository performance is too bad and have issues to work on their regular tasks on daily wise, so as administrator its admin responsibly to review issues and fix, below are some of my findings where this could help in improving of performance.

Also please note below steps will clean up all history or logs etls and you cannot fetch history runs of your jobs, and note if the maintenance of the repository is not performed regularly, command line (pmrep truncatelog) might not work or takes long time to truncate the tables.
  
NOTE: Make sure to discuss internally with your team /client before implementing these steps, also you understand logic behind these steps.

1.1           Cleanup Unused ETL Objects:

·        Clean up unwanted / unused folders in repository
(select * from opb_subject)
·        Clean up all Backup Folders
·        Identify unwanted/unused workflows in the repository (Check below query)

select * from(
select subj_name, workflow_name,server_name, trunc(start_time) as start_time,
rank() over (partition by subject_id, workflow_name order by start_time desc ) rnk
from  OPB_WFLOW_RUN,opb_subject
where OPB_WFLOW_RUN.subject_id=opb_subject.subj_id
) where rnk=1


select server_name,count(1) from(
select subject_id, workflow_name,server_name, trunc(start_time) as start_time,
rank() over (partition by subject_id, workflow_name order by start_time desc ) rnk
from  OPB_WFLOW_RUN
) where rnk=1
group by server_name
--order by 1,2,3

·        Identify in any un used connections can be cleaned up
Select * from opb_cnx

1.2           Update Stats of Repository Schema

·        After cleanup of unwanted Folders or ETL’s make sure execute stats job on Repository database.

 1.3           Steps to complete before running the Cleanup queries

1.      Stop the repository service
2.      Take the backup of repository (skip workflow and session log, skip deployment group history, skip MX data) – this is an optional step.
Ideally – the below operations will not cause any impact on the repository service, but if required you can take the backup.

Note: Please do not make this as a script. Perform manually.

1.4           Steps to cleanup - Queries

1.       Take Backup of below OPB tables.
2.       To truncate all data from the OPB tables which stores the run history information

TRUNCATE TABLE OPB_SESS_TASK_LOG;
TRUNCATE TABLE OPB_SWIDGINST_LOG;
TRUNCATE TABLE OPB_DTL_SWIDG_LOG;
TRUNCATE TABLE OPB_TASK_INST_RUN;
TRUNCATE TABLE OPB_WFLOW_VAR_RUN;
TRUNCATE TABLE OPB_WFLOW_DEP_RUN;
TRUNCATE TABLE OPB_WFLOW_CACHE;
TRUNCATE TABLE OPB_WFLOW_RUN;
TRUNCATE TABLE OPB_PERF_COUNT;

3.       To backup and retain last  15 days run history (if you have any)

DROP TABLE OPB_SESS_TASK_LOG_BACKUP;
DROP TABLE OPB_SWIDGINST_LOG_BACKUP;
DROP TABLE OPB_DTL_SWIDG_LOG_BACKUP;
DROP TABLE OPB_TASK_INST_RUN_BACKUP;
DROP TABLE OPB_WFLOW_VAR_RUN_BACKUP;
DROP TABLE OPB_WFLOW_DEP_RUN_BACKUP;
DROP TABLE OPB_WFLOW_CACHE_BACKUP;
DROP TABLE OPB_WFLOW_RUN_BACKUP;
DROP TABLE OPB_PERF_COUNT_BACKUP;


CREATE TABLE OPB_SESS_TASK_LOG_BACKUP AS SELECT A.* FROM OPB_SESS_TASK_LOG A,OPB_WFLOW_RUN B WHERE A.WORKFLOW_RUN_ID=B.WORKFLOW_RUN_ID AND
B.END_TIME >= SYSDATE-15 AND B.END_TIME IS NOT NULL;

CREATE TABLE OPB_SWIDGINST_LOG_BACKUP AS SELECT A.* FROM OPB_SWIDGINST_LOG A,OPB_WFLOW_RUN B WHERE A.WORKFLOW_RUN_ID=B.WORKFLOW_RUN_ID AND
B.END_TIME >= SYSDATE-15 AND B.END_TIME IS NOT NULL;

CREATE TABLE OPB_DTL_SWIDG_LOG_BACKUP AS
SELECT A.* FROM OPB_DTL_SWIDG_LOG A,OPB_WFLOW_RUN B
WHERE A.WORKFLOW_RUN_ID=B.WORKFLOW_RUN_ID AND
B.END_TIME >= SYSDATE-15 AND B.END_TIME IS NOT NULL;

CREATE TABLE OPB_TASK_INST_RUN_BACKUP AS
SELECT A.* FROM OPB_TASK_INST_RUN A,OPB_WFLOW_RUN B
WHERE A.WORKFLOW_RUN_ID=B.WORKFLOW_RUN_ID AND
B.END_TIME >= SYSDATE-15 AND B.END_TIME IS NOT NULL;

CREATE TABLE OPB_WFLOW_VAR_RUN_BACKUP AS
SELECT A.* FROM OPB_WFLOW_VAR_RUN A,OPB_WFLOW_RUN B
WHERE A.WORKFLOW_RUN_ID=B.WORKFLOW_RUN_ID AND
B.END_TIME >= SYSDATE-15 AND B.END_TIME IS NOT NULL;

CREATE TABLE OPB_WFLOW_DEP_RUN_BACKUP AS
SELECT A.* FROM OPB_WFLOW_DEP_RUN A,OPB_WFLOW_RUN B
WHERE A.WORKFLOW_RUN_ID=B.WORKFLOW_RUN_ID AND
B.END_TIME >= SYSDATE-15 AND B.END_TIME IS NOT NULL;

CREATE TABLE OPB_WFLOW_CACHE_BACKUP AS
SELECT A.* FROM OPB_WFLOW_CACHE A,OPB_WFLOW_RUN B
WHERE A.WORKFLOW_RUN_ID=B.WORKFLOW_RUN_ID AND
B.END_TIME >= SYSDATE-15 AND B.END_TIME IS NOT NULL;

CREATE TABLE OPB_WFLOW_RUN_BACKUP AS SELECT * FROM OPB_WFLOW_RUN
WHERE END_TIME >= SYSDATE-15 AND END_TIME IS NOT NULL;

CREATE TABLE OPB_PERF_COUNT _BACKUP AS
SELECT A.* FROM OPB_PERF_COUNT A,OPB_WFLOW_RUN B
WHERE A.WORKFLOW_RUN_ID=B.WORKFLOW_RUN_ID AND
B.END_TIME >= SYSDATE-15 AND B.END_TIME IS NOT NULL;

TRUNCATE TABLE OPB_SESS_TASK_LOG;
TRUNCATE TABLE OPB_SWIDGINST_LOG;
TRUNCATE TABLE OPB_DTL_SWIDG_LOG;
TRUNCATE TABLE OPB_TASK_INST_RUN;
TRUNCATE TABLE OPB_WFLOW_VAR_RUN;
TRUNCATE TABLE OPB_WFLOW_DEP_RUN;
TRUNCATE TABLE OPB_WFLOW_CACHE;
TRUNCATE TABLE OPB_WFLOW_RUN;
TRUNCATE TABLE OPB_PERF_COUNT;


INSERT INTO OPB_SESS_TASK_LOG SELECT * FROM OPB_SESS_TASK_LOG_BACKUP; COMMIT;
INSERT INTO OPB_SWIDGINST_LOG  SELECT * FROM OPB_SWIDGINST_LOG_BACKUP; COMMIT;
                  INSERT INTO OPB_DTL_SWIDG_LOG SELECT * FROM OPB_DTL_SWIDG_LOG_BACKUP; COMMIT;
INSERT INTO OPB_TASK_INST_RUN SELECT * FROM OPB_TASK_INST_RUN_BACKUP; COMMIT;
                  INSERT INTO OPB_WFLOW_VAR_RUN SELECT * FROM OPB_WFLOW_VAR_RUN_BACKUP; COMMIT;
INSERT INTO OPB_WFLOW_DEP_RUN SELECT * FROM OPB_WFLOW_DEP_RUN_BACKUP; COMMIT;
INSERT INTO OPB_WFLOW_CACHE SELECT * FROM OPB_WFLOW_CACHE_BACKUP; COMMIT;
INSERT INTO OPB_WFLOW_RUN SELECT * FROM OPB_WFLOW_RUN_BACKUP; COMMIT;
                  INSERT INTO OPB_PERF_COUNT SELECT * FROM OPB_PERF_COUNT_BACKUP; COMMIT;

1.5           Steps to optimize after cleanup

After truncating the run history data from the repository tables, analyze the tables.
Run pmrep updatestatistics command.

Also, please refer to the KB article 14331 - which guides how to improve PowerCenter repository performance using the pmrep updatestatistics command

The following is the link to the KB article. Please follow the steps.

1.6           Steps to optimize Domain Database

o   Take backup of Domain
o   Take backup of TABLE ISP_RUN_LOG

o   Truncate TABLE ISP_RUN_LOG