Performance Tuning of Scenarios

5.1 Performance Tuning of Scenarios

This section describes the Performance Tuning of Scenarios.

Customers can enable Performance Tuning of Scenarios using custom hints in the Customer's environment depending on Customer data volume. Currently this feature is offered for the following seven scenarios:

114000122-(ML/AC) Terrorist Financing
114000123-(ML/EN) Terrorist Financing
115200003-(ML/AC) Large Depreciation of Account Value
115400007-(ML/EN) Large Reportable Transactions
114000078-(ML/CU) Patterns of Funds Transfers Between Customers and External Entities
114000082-(ML/EN) Patterns of Recurring Originators/Beneficiaries in FundsTransfers
116000079-(ML/CU) Rapid Movement of Funds - All Activity
118860005 - (ML/CU) Hub and Spoke
118860014 - (ML/EN) Hub and Spoke
To configure this feature:
1. Run the scenario job with TRACE ON.
2. Read the log of job and identify SQL that took more time and needs to be tuned. It could be Primary Rule's SQL or SQL from any Sub-Rule.
3. This SQL will be having QB_NAME keywords with names in used subqueries.
  For example: Take scenario ML-RapidMvmtFundsAllActivity.116000079.xml for analyzing. Assume this Primary Rule's dataset takes time to execute.
```
<COLUMN NAME="DATASET_ID" TYPE="NUMBER" PK="YES" NULL="NO">114012498</COLUMN>
<COLUMN NAME="DATASET_NM" TYPE="CHAR" PK="NO"NULL="NO">ML_RapidMvmtFundsAll_CU_Pri_Remm1</COLUMN>
```
  And Sub Rules dataset takes time to execute:
```
<COLUMN NAME="DATASET_ID" TYPE="NUMBER" PK="YES" NULL="NO">114012544</ COLUMN>
<COLUMN NAME="DATASET_NM" TYPE="CHAR" PK="NO" NULL="NO">ML_RapidMvmtFundsAll_CU_Wi1m1</COLUMN>
```
  In SQL, the following subqueries are named by QB_NAME.
  
  For example:
  1. Primary Rule SQL blocks are as the following:
    wires as
```
(
select /*+ QB_NAME(WIRE) */ w.FO_TRXN_SEQ_ID, w.BENEF_ACCT_ID, w.SCND_BENEF_ACCT_ID, w.ORIG_ACCT_ID ...
from @MINER@.WIRE_TRXN w
...
---
```
    mitrxn as
```
(
select /*+ QB_NAME(MI) */ m.FO_TRXN_SEQ_ID, m.BENEF_ACCT_ID, m.SCND_BENEF_ACCT_ID, m.REM_ACCT_ID ...
from @MINER@.MI_TRXN m
...
---
```
    Cust_Accounts as
```
(SELECT /*+ QB_NAME(ACCT) */ c.CUST_SEQ_ID,
ac.PRMRY_CUST_INTRL_ID as CUST_INTRL_ID, ac.ACCT_INTRL_ID,
c.CUST_EFCTV_RISK_NB, ac.ACCT_OPEN_DT FROM
@MINER@.ACCT ac, @MINER@.CUST c
...
---
```
  2. Sub Rule SQL blocks are as the following:
```
SELECT /*+ QB_NAME(WIRE_ORG) */ 
c.CUST_INTRL_ID as CUST_INTRL_ID,
w.FO_TRXN_SEQ_ID
FROM
@MINER@.WIRE_TRXN w,
@MINER@.ACCT ac, 
@MINER@.CUST c 
... 
--- 
SELECT /*+ QB_NAME(WIRE_BEN) */
c.CUST_INTRL_ID as CUST_INTRL_ID,
w.FO_TRXN_SEQ_ID
FROM
@MINER@.WIRE_TRXN w, 
@MINER@.ACCT ac, 
@MINER@.CUST_ACCT ca, 
@MINER@.CUST c, 
@MINER@.CUST_ACCT_ROLE car
...
--- 
```
    These names allow us to set the hints for used tables by parameter in KDD_INSTALL_PARAM. For example:
    - Based on analysis of Explain plan of the tuned SQL, some hints can significantly improve the performance.
    - Say it was concluded after due analysis that hint PARALLEL for ACCT table and FULL hint for WIRE_TRXN should be used in both Primary and Sub-Rule SQL.
    - Insert parameter which will add needed hints to code of the dataset.
    - The names of subqueries where hints need to be added are /*+ QB_NAME(ACCT) */ and/ *+ QB_NAME(WIRE) */ for primary and /*+ QB_NAME(WIRE_ORG) */ and /*+ QB_NAME(WIRE_BEN) */ for Sub-Rule.
    Add following parameter to KDD_INSTALL_PARAM.
    For example: Sample insert query to insert values in KDD_INSTALL_PARAM.
```
Insert into KDD_INSTALL_PARAM (PARAM_ID, PARAM_NM,ATTR_1_CD, ATTR_1_VALUE_TX,ATTR_2_CD, ATTR_2_VALUE_TX)values (449, 'DLY', 'rule_matcher.primaryrule.hint.116000079', '/*+ PARALLEL(@ACCT, ac) FULL(@WIRE, w) */ ','rule_matcher.subrule.hint.116000079','/*+ PARALLEL(@WIRE_ORG, ac) FULL(@WIRE_BEN, w) */); 
commit;
```
    Note:
    The above used hints are for illustrative purpose only.
4. When these jobs are run again, these hints will be added to scenario SQL. This can be checked in scenario log.
  
  Note:
  Continue this tuning process till the desired result is achieved.