Performance Tuning Tips For Archive

• Generate database statistics prior to running the archive DB process
• Execute ArcSetup to drop the indexes for the table that are being archived by the DB process
• After moving the data from production to archive, generate the database statistics in both Production and Archive environments
• If the Production and Archive schemas are setup in the same Oracle Instance, extra care needs to be taken for configuring the instance parameters, SGA, distributing data files across multiple disks/disk controllers. Also, archive jobs are resource intensive and need to be scheduled during off production hours
• Database activities need to be monitored for archival job #3; performance can be improved if the recursive reference indexes would be created for the duration of archiving run on production tables. Execute the following SQL in order to retrieve the list of tables/columns for the DB process. Please note that any custom indexes can only be useful for archiving jobs, they can be overheads for non-archiving jobs

SELECT DISTINCT  	DI.MAINT_OBJ_CD,
     			   	MO.TBL_NAME,
							CF.CONST_ID,
							CF.SEQ_NUM,
							CF.FLD_NAME
  FROM	CI_DB_INSTR DI,
			CI_MD_MO_TBL MO,
			CI_MD_CONST CO,
			CI_MD_CONST_FLD CF
WHERE DI.DB_PROC_CD = <input DB Process Code>
	AND MO.MAINT_OBJ_CD = DI.MAINT_OBJ_CD
 	AND CO.TBL_NAME = MO.TBL_NAME
	AND CO.REF_CONST_ID IN (SELECT C2.CONST_ID FROM CI_MD_CONST C2 WHERE C2.TBL_NAME = CO.TBL_NAME)
 	AND CO.CONST_ID = CF.CONST_ID
 	AND CO.CONST_ID NOT IN (SELECT MDT.PRNT_CONST_ID FROM CI_MD_MO_TBL MDT WHERE MDT.MAINT_OBJ_CD = MO.MAINT_OBJ_CD)
	AND CO.CONST_ID NOT IN (SELECT DPP.LNKG_CONST_ID FROM CI_DB_INSTR DPP WHERE DPP.DB_PROC_CD = DI.DB_PROC_CD)ORDER BY 1,2,3