SCD performance can be improved by providing hints and session alter statements. This requires the presence of the following four columns in SYS_TBL_MASTER:
· merge_hint
· select_hint
· session_enable_statement
· session_disable_statement
These columns are present in the OFSAAI versions 7.3.2.4.0 and above. If these have to be used in OFSAAI versions 7.3.2.2.0 or 7.3.2.3.0, execute the following SQL queries:
ALTER TABLE SYS_TBL_MASTER ADD MERGE_HINT VARCHAR2(255)
/
ALTER TABLE SYS_TBL_MASTER ADD SELECT_HINT VARCHAR2(255)
/
ALTER TABLE SYS_TBL_MASTER ADD SESSION_ENABLE_STATEMENT VARCHAR2(255)
/
ALTER TABLE SYS_TBL_MASTER ADD SESSION_DISABLE_STATEMENT VARCHAR2(255)
/
During upgrade to OFSAAI 7.3.2.4.0, ensure to backup SYS_TBL_MASTER table and to drop the preceding four columns, if these scripts are executed in any of the OFSAAI versions prior to 7.3.2.4.0. Otherwise, an upgrade to OFSAAI 7.3.2.4.0 may throw an error, since the columns exist.
For improving performance, hints for the MERGE query, which is generated internally by the SCD, can be provided under MERGE_HINT. The following session alters can be mentioned in the SESSION_ENABLE_STATEMENT and SESSION_DISABLE_STATEMENT columns.
1. SESSION_ENABLE_STATEMENTs are executed before the MERGE in the SCD and SESSION_DISABLE_STATEMENTs are executed after the SCD MERGE.
2. Since all the tasks under the SCD batch for DIM_ACCOUNT works on the same target, the SESSION_DISABLE_STATEMENTs in SYS_TBL_MASTER cannot be provided when tasks are executed. In this case, there can be a separate SQL file to contain all the SESSION_DISABLE_STATEMENTs to be executed once after all the tasks in the SCD are done. The SESSION_DISABLE_STATEMENT will hold a null in SYS_TBL_MASTER table.
3. SESSION_ENABLE_STATEMENTs are required to be mentioned only for the first task in the batch. Here the target is the same for all the tasks under a batch. In case any of the tasks are to be executed separately, then the SESSION_ENABLE_STATEMENTs should be mentioned for any one of the tasks which is included in the batch for the execution.
4. MERGE_HINT and SESSION_ENABLE_STATEMENT are in SYS_TBL_MASTER.
|
NOTE |
For illustration, Account Dimension is considered. |
|
Table Name |
Stage Table Name |
Merge Hint |
Session Enable Statement |
|
DIM_ACCOUNT |
STG_LOAN_CONTRACTS_V |
/*+ parallel(DIM_ACCOUNT,1 0) */ |
"alter session enable parallel dml query", "alter table DIM_ACCOUNT nologging parallel 10" |
5. Execute all the tasks in parallel. This may cause N_RCV_LEG_ACCT_SKEY to have an incremental value as compared to N_ACCT_SKEY.
6. Execute the SQL file with all the SESSION_DISABLE_STATEMENTs, after the successful completion of the SCD batch.
7. After the DIM_ACCOUNT table is populated using this approach, you cannot use the initial approach (FN_POPDIMACCOUNT) as this will lead to Skey conflict.
8. Ensure that you have set the value of the sequence SEQ_DIM_ACCOUNT_SCD as max (value of SKey in DIM_ACCOUNT) +1, before moving from old to new approach.
9. The F_LATEST_RECORD_INDICATOR for an existing DIM_ACCOUNT data already loaded by the function must be updated to 'Y' before running the SCD, failing which a new SKey may get generated for the same account number.
10. SCD execution occurs based on the GAAP code, which is configured in SETUP_MASTER table. These are introduced to tackle the scenario of multiple GAAP codes. Whether or not there exist multiple GAAP codes, SETUP_MASTER must be manually configured as follows:
|
V_COMPONENT_CODE |
V_COMPONENT_DESC |
V_COMPONENT_VALUE |
|
DEFAULT_GAAP |
DEFAULT_GAAP |
USGAAP |
Where V_COMPONENT_VALUE must be manually populated with the required GAAP code. For all other GAAP codes, ensure to update SETUP_MASTER manually before running DIM_ACCOUNT SCD.