Wednesday Feb 13, 2013

Demantra MDP_Matrix 7.3.1.3 and Above using Table_Reorg to Rebuild by Primary Key.


Demantra MDP_Matrix table reorg by Primary Key.  This is a non partitioned table.

Sample Action Plan to create partitions on Demantra Sales_data table.  This has been used recently and was successful.

USE at your own RISK after careful study and adjustment to meet your needs.  This was produced to use in an 11gr2 RDBMS environment.   

Step 1:- Create tablespace for MDP_MATRIX
=================================================================
Create tablespace MDP_MATRIX_DATA
datafile '/my_instance/oradata/data01/MDP_MATRIX_DATA_01.dbf' size 5000m
LOGGING ONLINE PERMANENT BLOCKSIZE 16384
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

Alter tablespace MDP_MATRIX_DATA add datafile '/my_instance/oradata/data01/MDP_MATRIX_DATA_02.dbf' size 5000m;
Alter tablespace MDP_MATRIX_DATA add datafile '/my_instance/oradata/data01/MDP_MATRIX_DATA_03.dbf' size 5000m;

Step 2:- Create tablespace for MDP_MATRIX INDEX's
=================================================================
Create tablespace MDP_MATRIX_IDX
datafile '/my_instance/oradata/data01/MDP_MATRIX_IDX_01.dbf' size 5000m
LOGGING ONLINE PERMANENT BLOCKSIZE 16384
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

Alter tablespace MDP_MATRIX_DATA add datafile '/my_instance/oradata/data01/MDP_MATRIX_IDX_02.dbf' size 5000m;
Alter tablespace MDP_MATRIX_DATA add datafile '/my_instance/oradata/data01/MDP_MATRIX_IDX_03.dbf' size 5000m;

Step 3:- Move the table MDP_MATRIX to MDP_MATRIX_DATA
=================================================================
conn demantra/<Passwd>

alter session force parallel dml;

alter table MDP_MATRIX move MDP_MATRIX_DATA parallel 7;

alter table MDP_MATRIX parallel 3;


Step 4:- Do the Row order reorg for MDP_MATRIX Table as per the Primary Key
===========================================================================================

NOTE:- Monitor the Tablespaces Closely MDP_MATRIX_DATA/ And MDP_MATRIX_IDX for this below activity.


SELECT /*+ PARALLEL(SALES_DATA,4) */(ROUND(((SELECT /*+ PARALLEL(SALES_DATA,4) */ COUNT(*) AS CNT     FROM (SELECT /*+ PARALLEL(SALES_DATA,4) */ SALES_DATE , ITEM_ID
          , LOCATION_ID,RELATIVE_FNO,BLOCK_NUMBER ,ROW_NUMBER,DATA_ROW
     ,(LAG(DATA_ROW) OVER(PARTITION BY RELATIVE_FNO, BLOCK_NUMBER ORDER BY
              ROW_NUMBER)) AS PREV_DATA_ROW FROM (SELECT /*+ PARALLEL(SALES_DATA,4) */ SALES_DATE, ITEM_ID, LOCATION_ID
          ,RELATIVE_FNO ,BLOCK_NUMBER
          ,ROW_NUMBER
     ,(DENSE_RANK() OVER(PARTITION BY RELATIVE_FNO, BLOCK_NUMBER ORDER
              BY SALES_DATE, ITEM_ID, LOCATION_ID)) AS DATA_ROW
          FROM (SELECT  /*+ PARALLEL(SALES_DATA,8) */ SALES_DATE, ITEM_ID, LOCATION_ID
          ,DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) RELATIVE_FNO
          ,DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS BLOCK_NUMBER
          ,DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) AS ROW_NUMBER
          FROM SALES_DATA
          ) C
          ) B
          ) A
          WHERE DATA_ROW != PREV_DATA_ROW
          AND DATA_ROW != PREV_DATA_ROW + 1)/(SELECT /*+ PARALLEL(SALES_DATA,4) */ COUNT(*) FROM SALES_DATA)),3)*100) AS "Out Of Order Ratio %"


Step 5:- Reorder the MDP_MATRIX Table
===========================================================================================
Reorder the MDP_MATRIX Table as per Primary Key

Script to grant_table_reorg (locate the sql and adjust your path)
SQL> @your_instance_name/oracle/sales_data/grant_table_reorg.sql

SQL> table_reorg.reorg ('DEMANTRA,'MDP_MATRIX','C');

Script to revoke_table_reorg  (locate the sql and adjust your path)
SQL> @your_instance_name/oracle/sales_data/revoke_table_reorg.sql

Step 6:- Rebuild the index MDP_MATRIX matrix and move them to MDP_MATRIX_IDX Tablespace
===========================================================================================
spool INDEX_REBUILD.lst
Alter Index WS_MDPMATRIX_1_IDX rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index WS_MDPMATRIX_2_IDX rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index WS_MDPMATRIX_3_IDX rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index DIVIDER_I_X_1 rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index WS_MDPMATRIX_4_IDX rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index MATRIX_IND rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_ITEM_CAT_1_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_ITEM_CAT_2_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_ITEM_CAT_3_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_ITEM_CAT_4_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_ITEM_CAT_5_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_ITEM_CAT_6_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_IT_BR_CAT_1_EP_ID_IDX rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_IT_BR_CAT_2_EP_ID_IDX rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_IT_BR_CAT_3_EP_ID_IDX rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_IT_BR_CAT_4_EP_ID_IDX rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_IT_BR_CAT_5_EP_ID_IDX rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_PARENT_AD_NUM_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_EBS_ACCOUNT_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_EBS_BUS_GROUP_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_EBS_CUSTOMER_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_EBS_CUST_CLASS_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_EBS_DEMAND_CLASS_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_EBS_LEGAL_ENTITY_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_EBS_OPER_UNIT_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_EBS_PROD_CAT_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_EBS_PROD_FAMILY_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_EBS_SALES_CH_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_EBS_SUPPLIER_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_EBS_TP_ZONE_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_EBS_ZONE_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_ITEM_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_I_ATT_10_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_I_ATT_1_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_I_ATT_2_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_I_ATT_3_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_I_ATT_4_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_I_ATT_5_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_I_ATT_6_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_I_ATT_7_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_I_ATT_8_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_I_ATT_9_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_LR1_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_LR2A_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_LR2_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_LS1_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_LS2_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_LS3_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_LS4_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_LS5_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_LS6_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_L_ATT_10_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_L_ATT_1_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index MDP_PK rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_L_ATT_6_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_L_ATT_7_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_L_ATT_8_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_L_ATT_9_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_ORGANIZATION_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_ORG_TYPE_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_P1_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_P2A1_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_P2A2_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_P2A_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_P2B_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_P3_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_P4_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_SITE_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index MDP_MATRIX_1465_IDX rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_ITEM_CAT_7_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_L_ATT_2_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_L_ATT_3_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_L_ATT_4_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_L_ATT_5_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index DO_FORE_IND rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index LOC_NO rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index MDP_BRANCH_ID_IDX rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index MDP_IN rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index MDP_LOC rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index MDP_MATRIX_481_IDX rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index MDP_MATRIX_537_IDX rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index MDP_MATRIX_706_IDX rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index MDP_MATRIX_ITEM_ID_IDX rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index MM_COMP_LEAD_DATE_IDX rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index PREDICTION_STATUS_IND rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index PROP_CHANGES_IND rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_CORP_CODE_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_BR_CAT_1_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_BR_CAT_2_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_BR_CAT_3_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_BR_CAT_4_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_BR_CAT_5_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_BR_CITY_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_BR_COUNTRY_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_BR_STATE_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_CUST_CAT_1_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_CUST_CAT_2_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_CUST_CAT_3_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_CUST_CAT_4_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_CUST_CAT_5_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_CUST_CAT_6_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_CUST_CAT_7_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_CUST_CITY_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_CUST_CTRY_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_E1_CUST_STATE_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index T_EP_P2_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
Alter Index DIVIDER_I_X rebuild Online parallel (degree 7)  tablespace MDP_MATRIX_IDX;
spool off;

spool Change_parallel.lst
Alter Index WS_MDPMATRIX_1_IDX parallel 1;
Alter Index WS_MDPMATRIX_2_IDX parallel 1;
Alter Index WS_MDPMATRIX_3_IDX parallel 1;
Alter Index DIVIDER_I_X_1 parallel 1;
Alter Index WS_MDPMATRIX_4_IDX parallel 1;
Alter Index MATRIX_IND parallel 1;
Alter Index T_EP_E1_ITEM_CAT_1_I_X parallel 1;
Alter Index T_EP_E1_ITEM_CAT_2_I_X parallel 1;
Alter Index T_EP_E1_ITEM_CAT_3_I_X parallel 1;
Alter Index T_EP_E1_ITEM_CAT_4_I_X parallel 1;
Alter Index T_EP_E1_ITEM_CAT_5_I_X parallel 1;
Alter Index T_EP_E1_ITEM_CAT_6_I_X parallel 1;
Alter Index T_EP_E1_IT_BR_CAT_1_EP_ID_IDX parallel 1;
Alter Index T_EP_E1_IT_BR_CAT_2_EP_ID_IDX parallel 1;
Alter Index T_EP_E1_IT_BR_CAT_3_EP_ID_IDX parallel 1;
Alter Index T_EP_E1_IT_BR_CAT_4_EP_ID_IDX parallel 1;
Alter Index T_EP_E1_IT_BR_CAT_5_EP_ID_IDX parallel 1;
Alter Index T_EP_E1_PARENT_AD_NUM_I_X parallel 1;
Alter Index T_EP_EBS_ACCOUNT_I_X parallel 1;
Alter Index T_EP_EBS_BUS_GROUP_I_X parallel 1;
Alter Index T_EP_EBS_CUSTOMER_I_X parallel 1;
Alter Index T_EP_EBS_CUST_CLASS_I_X parallel 1;
Alter Index T_EP_EBS_DEMAND_CLASS_I_X parallel 1;
Alter Index T_EP_EBS_LEGAL_ENTITY_I_X parallel 1;
Alter Index T_EP_EBS_OPER_UNIT_I_X parallel 1;
Alter Index T_EP_EBS_PROD_CAT_I_X parallel 1;
Alter Index T_EP_EBS_PROD_FAMILY_I_X parallel 1;
Alter Index T_EP_EBS_SALES_CH_I_X parallel 1;
Alter Index T_EP_EBS_SUPPLIER_I_X parallel 1;
Alter Index T_EP_EBS_TP_ZONE_I_X parallel 1;
Alter Index T_EP_EBS_ZONE_I_X parallel 1;
Alter Index T_EP_ITEM_I_X parallel 1;
Alter Index T_EP_I_ATT_10_I_X parallel 1;
Alter Index T_EP_I_ATT_1_I_X parallel 1;
Alter Index T_EP_I_ATT_2_I_X parallel 1;
Alter Index T_EP_I_ATT_3_I_X parallel 1;
Alter Index T_EP_I_ATT_4_I_X parallel 1;
Alter Index T_EP_I_ATT_5_I_X parallel 1;
Alter Index T_EP_I_ATT_6_I_X parallel 1;
Alter Index T_EP_I_ATT_7_I_X parallel 1;
Alter Index T_EP_I_ATT_8_I_X parallel 1;
Alter Index T_EP_I_ATT_9_I_X parallel 1;
Alter Index T_EP_LR1_I_X parallel 1;
Alter Index T_EP_LR2A_I_X parallel 1;
Alter Index T_EP_LR2_I_X parallel 1;
Alter Index T_EP_LS1_I_X parallel 1;
Alter Index T_EP_LS2_I_X parallel 1;
Alter Index T_EP_LS3_I_X parallel 1;
Alter Index T_EP_LS4_I_X parallel 1;
Alter Index T_EP_LS5_I_X parallel 1;
Alter Index T_EP_LS6_I_X parallel 1;
Alter Index T_EP_L_ATT_10_I_X parallel 1;
Alter Index T_EP_L_ATT_1_I_X parallel 1;
Alter Index MDP_PK parallel 1;
Alter Index T_EP_L_ATT_6_I_X parallel 1;
Alter Index T_EP_L_ATT_7_I_X parallel 1;
Alter Index T_EP_L_ATT_8_I_X parallel 1;
Alter Index T_EP_L_ATT_9_I_X parallel 1;
Alter Index T_EP_ORGANIZATION_I_X parallel 1;
Alter Index T_EP_ORG_TYPE_I_X parallel 1;
Alter Index T_EP_P1_I_X parallel 1;
Alter Index T_EP_P2A1_I_X parallel 1;
Alter Index T_EP_P2A2_I_X parallel 1;
Alter Index T_EP_P2A_I_X parallel 1;
Alter Index T_EP_P2B_I_X parallel 1;
Alter Index T_EP_P3_I_X parallel 1;
Alter Index T_EP_P4_I_X parallel 1;
Alter Index T_EP_SITE_I_X parallel 1;
Alter Index MDP_MATRIX_1465_IDX parallel 1;
Alter Index T_EP_E1_ITEM_CAT_7_I_X parallel 1;
Alter Index T_EP_L_ATT_2_I_X parallel 1;
Alter Index T_EP_L_ATT_3_I_X parallel 1;
Alter Index T_EP_L_ATT_4_I_X parallel 1;
Alter Index T_EP_L_ATT_5_I_X parallel 1;
Alter Index DO_FORE_IND parallel 1;
Alter Index LOC_NO parallel 1;
Alter Index MDP_BRANCH_ID_IDX parallel 1;
Alter Index MDP_IN parallel 1;
Alter Index MDP_LOC parallel 1;
Alter Index MDP_MATRIX_481_IDX parallel 1;
Alter Index MDP_MATRIX_537_IDX parallel 1;
Alter Index MDP_MATRIX_706_IDX parallel 1;
Alter Index MDP_MATRIX_ITEM_ID_IDX parallel 1;
Alter Index MM_COMP_LEAD_DATE_IDX parallel 1;
Alter Index PREDICTION_STATUS_IND parallel 1;
Alter Index PROP_CHANGES_IND parallel 1;
Alter Index T_EP_CORP_CODE_I_X parallel 1;
Alter Index T_EP_E1_BR_CAT_1_I_X parallel 1;
Alter Index T_EP_E1_BR_CAT_2_I_X parallel 1;
Alter Index T_EP_E1_BR_CAT_3_I_X parallel 1;
Alter Index T_EP_E1_BR_CAT_4_I_X parallel 1;
Alter Index T_EP_E1_BR_CAT_5_I_X parallel 1;
Alter Index T_EP_E1_BR_CITY_I_X parallel 1;
Alter Index T_EP_E1_BR_COUNTRY_I_X parallel 1;
Alter Index T_EP_E1_BR_STATE_I_X parallel 1;
Alter Index T_EP_E1_CUST_CAT_1_I_X parallel 1;
Alter Index T_EP_E1_CUST_CAT_2_I_X parallel 1;
Alter Index T_EP_E1_CUST_CAT_3_I_X parallel 1;
Alter Index T_EP_E1_CUST_CAT_4_I_X parallel 1;
Alter Index T_EP_E1_CUST_CAT_5_I_X parallel 1;
Alter Index T_EP_E1_CUST_CAT_6_I_X parallel 1;
Alter Index T_EP_E1_CUST_CAT_7_I_X parallel 1;
Alter Index T_EP_E1_CUST_CITY_I_X parallel 1;
Alter Index T_EP_E1_CUST_CTRY_I_X parallel 1;
Alter Index T_EP_E1_CUST_STATE_I_X parallel 1;
Alter Index T_EP_P2_I_X parallel 1;
Alter Index DIVIDER_I_X parallel 1;
spool off;

Step 7:- Recompile the Invalid Objects
===========================================================================================

Run utlrp.sql / dbms_recomp  Recompile all the objects.

Spool Data_collection_after_MDP_Tablereorg.lst
select object_type,count(1) from dba_objects where owner='DEMANTRA' group by object_type order by  2;
select object_name,object_type,owner,status from dba_objects where status='INVALID';
select index_name,table_name,status from dba_indexes where table_name='MDP_MATRIX';
select constraint_name,table_name,status from dba_constraints where table_name='MDP_MATRIX';
Spool Off


Step 8:- Gather the Schema Stats Again
===========================================================================================
SQL> DROP TEMPS
SQL> execute DBMS_STATS.DELETE_TABLE_STATS(ownname => 'DEMANTRA', tabname => 'SALES_DATA');
SQL> execute DBMS_STATS.DELETE_TABLE_STATS(ownname => 'DEMANTRA', tabname => 'MDP_MATRIX');

exec dbms_stats.GATHER_SCHEMA_STATS(OWNNAME=>'DEMANTRA', estimate_percent=>30 ,DEGREE=> 10);


Step 9:- Check the Reorder out of ratio
===========================================================================================

SELECT /*+ PARALLEL(SALES_DATA,4) */(ROUND(((SELECT /*+ PARALLEL(SALES_DATA,4) */ COUNT(*) AS CNT     FROM (SELECT /*+ PARALLEL(SALES_DATA,4) */ SALES_DATE , ITEM_ID
          , LOCATION_ID,RELATIVE_FNO,BLOCK_NUMBER ,ROW_NUMBER,DATA_ROW
     ,(LAG(DATA_ROW) OVER(PARTITION BY RELATIVE_FNO, BLOCK_NUMBER ORDER BY
              ROW_NUMBER)) AS PREV_DATA_ROW FROM (SELECT /*+ PARALLEL(SALES_DATA,4) */ SALES_DATE, ITEM_ID, LOCATION_ID
          ,RELATIVE_FNO ,BLOCK_NUMBER
          ,ROW_NUMBER
     ,(DENSE_RANK() OVER(PARTITION BY RELATIVE_FNO, BLOCK_NUMBER ORDER
              BY SALES_DATE, ITEM_ID, LOCATION_ID)) AS DATA_ROW
          FROM (SELECT  /*+ PARALLEL(SALES_DATA,8) */ SALES_DATE, ITEM_ID, LOCATION_ID
          ,DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) RELATIVE_FNO
          ,DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS BLOCK_NUMBER
          ,DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) AS ROW_NUMBER
          FROM SALES_DATA
          ) C
          ) B
          ) A
          WHERE DATA_ROW != PREV_DATA_ROW
          AND DATA_ROW != PREV_DATA_ROW + 1)/(SELECT /*+ PARALLEL(SALES_DATA,4) */ COUNT(*) FROM SALES_DATA)),3)*100) AS "Out Of Order Ratio %"

Spool Data_collection_after_MDP_column_reorg.lst
select object_type,count(1) from dba_objects where owner='DEMANTRA' group by object_type order by  2;
select object_name,object_type,owner,status from dba_objects where status='INVALID';
select index_name,table_name,status from dba_indexes where table_name='MDP_MATRIX';
select constraint_name,table_name,status from dba_constraints where table_name='MDP_MATRIX';
Spool Off

Wednesday Dec 19, 2012

Un-used Indexes on MDP_MATRIX Consuming Resources

Disable un-used Indexes:

As much as it is recommended to create relevant indexes, it is advised not to have too many indexes on the mdp_matrix table.  Too many indexes will cause long waits on the table as indexes needs to get updated every time the table is updated.  There are many seeded indexes on mdp_matrix, every out of the box data model level has an index on the matrix table.  If a level is unused in the specific data model of the implementation, it is advisable to disable that index.  If the customer is not sure if and how indexes are utilized, the DBA can monitor all indexes.  After a few cycles of operation, the DBA should go over that list and see which indexes have not been used.  Consider disabling them.

There are scripts on the net to monitor indexes or use the monitoring usage clause in the alter index statement.

Friday Dec 14, 2012

The Column Prediction_Status, MDP_Matrix and Engine. How are they Related? Understand Prediction_status Values

Do you know what these values are telling you?

COUNT(*) PREDICTION_STATUS DO_FORE DO_AGGRI AGGRI_98 AGGRI_99 LEVEL_ID
19854 99 1 1 1 1 3
1077 99 0 1 1 1 0
262691 99 1 1 -1
56 99 0 1 1 1 2
1 98 1 1 1 1
1 99 0 1 1 1
748796 1 1 1 4
351633 1 1 1 1 1 2
1877829 97 1 1 4
840 99 1 1 1 1
27 99 0 1 1 1 3
1 97 1 1 -1
66712 99 1 1 1 1 2
53213 1 1 1 1 1 3
2560 98 1 1 4

 

Check out The Column Prediction_Status, MDP_Matrix and Engine. How are they Related? Understand Prediction_status Values (Doc ID 1509754.1)

This customer is adding an additional processing burden, adding no value.  The incoming data should be scrubbed to eliminate the overhead. 

About

This blog delivers the latest information regarding performance and install/upgrade. Comments welcome

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today