Wednesday May 13, 2015

TABLE_REORG Causing ORA-42012: error occurred while completing the redefinition and ORA-00600

Hello!  The latest information for you.

Submitting the TABLE_REORG procedure for a table that has a foreign key constraint that refers to the same table, leaves the constraint in a disabled state.  However, Foreign key constraints that refer to other tables, behave as expected. i.e., the constraint is re-enabled at the end of the redefinition.

When this condition exists, TABLE_REORG will fail with the following:

ORA-42012: error occurred while completing the redefinition
ORA-00600: internal error code, arguments: [17183], [0x3FFF81BF400], [], [], [], [], [], [], [], [], [], []

It does generate a trace file and dump file.

ERROR at line 1:
ORA-42012: error occurred while completing the redefinition
ORA-00600: internal error code, arguments: [17183], [0x3FFF81BF400], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_REDEFINITION", line 82
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1524
ORA-06512: at line 1
ORA-06512: at "DEMANTRA.TABLE_REORG", line 1193

To resolve this issue, please apply Patch 13867469 if available for your database version and platform.  The fix for Bug 13867469 should be included in 12.2 future release.

- See MOS Note <Note 13572659.8> for additional details.  Bug 13572659 - DBMS_REDEFINITION disables Foreign Keys used for REFERENCE partitioning

- Also available, patch 20954948 : MERGE REQUEST ON TOP OF 11.2.0.3.0 FOR BUGS 13040943 13572659 13642044

As a workaround, if you did not receive an ORA-00600, re-enable the constraint after the redefinition.

Note: The above workaround is not applicable for a nested table built on a partitioned parent table. Reference: <Note 1929007.1>.
Foreign Constraint On Nested Table is Created With Status Disabled.

Tuesday May 05, 2015

Demant TABLE_REORG Procedure Failed ORA- on SALES_DATA MDP_MATRIX PROMOTION_DATA How do I Restart? RUPD$_ MLOG$_

When you submit TABLE_REORG, DBMS_REDEFINITION is used.  This will create an MLOG$_ database object.  The table you supplied for the TABLE_REORG procedure has a Primary Key (PK).  Since there is a PK on the table, when DBMS_DEFINITION creates the MLOG$_ object, DBMS_REDEFINITION automatically creates a RUPD$_ object.  These objects are meant to be used for Java RepAPI.  If you execute a 'drop snapshot log on tablename' the snapshot log as well as the temporary snapshot log are dropped.  However, dropping the MLOG$_ object is not best practice. 

If these objects exist prior to executing TABLE_REORG, you will see the following: ‘Table cannot be redefinitioned.' in log_table_reorg table.  This means that one or both objects/segments exist.  For example, if I had a table_reorg for mdp_matrix fail, the following segments would most likely be left behind:

RUPD$_MDP_MATRIX
MLOG$_MDP_MATRIX

Use the following SQL to verify (10g and above):

select substr(object_name,1,30)
from dba_objects
where regexp_like( object_name, 'MLOG$|RUPD$')
and owner = '&Schema_owner'

While you can drop these temporary RDBMS segments, it is best practice to use the following:

BEGIN
 DBMS_REDEFINITION.ABORT_REDEF_TABLE (
   uname       => '&demantra_schema_name',
   orig_table  => '&original_table_name',
   int_table   => '&interim_table_name');
END;

In the above, supply the arguments:

- DM or your schema name
- MDP_MATRIX is the original_table_name
- MLOG$_MDP_MATRIX is the interim_table_name

Verify that the objects are dropped.  Submit the TABLE_REORG again AFTER repairing the cause of the last failure.

Wednesday Apr 29, 2015

Did you know that TABLE_REORG has replace REBUILD_SCHEMA mad REBUILD_TABLES?

Demantra TABLE_REORG procedure. 

Did you know that TABLE_REORG has replace REBUILD_SCHEMA mad REBUILD_TABLES?  TABLE_REORG
can addressed partitioned tables and is more efficient!  Get the latest release at My Oracle Support using bug 17640575.

TABLE_REORG has really replaced and improved the functionality of REBUILD_SCHEMA and REBUILD_TABLES.
It rebuilds the table which is done in primary key order and it moves empty columns to the end of the row.

REBUILD_SCHEMA uses ALTER TABLE MOVE tablespace to reduce chained rows of all tables in the schema.
However, it does not support partitioned tables.

REBUILD_TABLES  is the similar.  It was originally designed for MDP_MATRIX / SALES_DATA, but it can run for all tables and
also for a specific table.  From 2010 it does support partitioned tables.

The procedure MOVE_TABLE was fixed to handle partitioned tables.  It is also out of date, I see ANALYZE TABLE has used parts of the
code (eg for SALES_DATA and MDP_MATRIX).  For an "all tables run" is uses ANALYZE_SCHEMA that does use dbms_stats.GATHER_TABLE_STATS

All tables  - Where the stats value chain_cnt > 0, it does not automatically include SALES_DATA unless 'sys_params','Rebuild_Sales_Table' = 1.

REBUILD_TABLES ( table namel,  stats check,  sales,  all tables flag)

exec REBUILD_TABLES ( null, 1, null, 1) ;    -- With ANALYZE_SCHEMA(100000)  = for none or really old stats
exec REBUILD_TABLES ( null, 0, null, 1) ;

exec REBUILD_TABLES ( null, 0, 1, 1) ;   -- Will include SALES_DATA

For more information see: Troubleshooting TABLE_REORG Package issues - RDF Snapshot drop when process fails + TABLE_REORG Guide MOS Note 1964291.1


Gathering statistics on partitioned tables.  Best practice:

For all 11gr2 environments with large partitioned or subpartitioned objects turn on incremental statistics using this command:

exec dbms_stats.set_table_prefs('OWNER','TABLE','INCREMENTAL','TRUE');

Once this is set for any given table, gather statistics on that table using the normal tool (fnd_stats in ebs or dbms_stats elsewhere).
This first gather after turning it on will take longer than previous analyzes.  Then going forward we will see the following:

1.  The normal dbms_stats or fnd_stats, will only gather statistics on lower level partitions if the statistics on that partition are stale.  This is a significant change.  That is going forward using the default options of a gather command may in fact perform no re-analyze on the actual data if the modifications to the table do not warrant it.

2.  If a subpartition is stale the normal stats will ONLY gather statistics on that subpartition.  The partition for that subpartition will be re-derived as will the global
    statistics, no other statistics will be gathered.

Making this change promises to reduce gather stats by hours in some cases.

For more information: Demantra Gathering Statistics on Partitioned Objects Oracle RDBMS 11gR2, MOS Note 1601596.1

Tuesday Feb 17, 2015

Demantra Table_Reorg. A new version has been released!

Demantra Customers, there is a new release of the table_reorg Tool.  This patch will install updates to the TABLE_REORG tool.  Demantra Version: 7.3.1.x and 12.2.x.  The minimum 7.3.1.x version is 7.3.1.3.   You can download this patch using bug 17640575 in My Oracle Support.  For additional information see MOS note:
Demantra TABLE_REORG Tool New Release with Multiple Updates! Partitions, Drop_temps and More! 7.3.1.x to 12.2.x. (Doc ID 1980408.1)

Wednesday Oct 23, 2013

Demantra Performance Clustering Factor Out of Order Ratio TABLE_REORG CHECK_REORG (Doc ID 1594372.1)

Hello!   There is a new document available:

Demantra Performance Clustering Factor Out of Order Ratio TABLE_REORG CHECK_REORG (Doc ID 1594372.1)

Demantra Performance Clustering Factor Out of Order Ratio TABLE_REORG CHECK_REORG

The table reorganization can be setup to automatically run in version 7.3.1.5. 

In version 12.2.2 we run the TABLE_REORG.CHECK_REORG function at every appserver restart.
If the function recommends a reorg then we strongly encourage to reorg the database object. 
This is documented in the official docs.

In versions 7.3.1.3 and 7.3.1.4, the TABLE_REORG module exists and can be used.
It has two main functions that are documented in the Implementation Guide Supplement,
Release 7.3, Part No. E26760-03, chapter 4.

In short, if you are using version 7.3.1.3 or higher, you can check for the need to run a reorg by doing the following 2 steps:

1. Run TABLE_REORG.CHECK_REORG('T');
2. Check the table LOG_TABLE_REORG for recommendations

If you are on a version before 7.3.1.3, you will need to follow the instructions below to determine if you need to do a manual reorg.

How to determine if a table reorg is needed

1. It is strongly encouraged by DEV that You gather statistics on the required table.  The prefered percentage for the gather is 100%.

2. Run the following SQL to evaluate how table reorg might affect Primary Key (PK) based access:

 
SELECT ui.index_name,trunc((ut.num_rows/ui.clustering_factor)/(ut.num_rows/ut.blocks),2)
FROM user_indexes ui, user_tables ut, user_constraints uc
WHERE ui.table_name=ut.table_name
AND ut.table_name=uc.table_name
AND ui.index_name=uc.index_name
AND UC.CONSTRAINT_TYPE='P'
AND ut.table_name=upper('&enter_table_name');
 

3. Based on the result:

VALUE ABOVE 0.75 - DOES NOT REQUIRE REORG

VALUE BETWEEN 0.5 AND 0.75 - REORG IS RECOMMENDED

VALUE LOWER THAN 0.5 - IT IS HIGHLY RECOMMENDED TO REORG

Updates to the Demantra Partial Schema Exporter Tool, Patch 13930627, are Available.

Hello!  Updates to the Demantra Partial Schema Exporter Tool, Patch 13930627, are Available.
This is an updated re-release of the generic Partial Schema Exporter Tool.  The generic patch is for 7.3.1.x and 12.2.x.
TABLE_REORG was introduced in 7.3.1.3 12.2.0.  Therefore for 7.3.1.x the schema must be at 7.3.1.3 or above.

This is build 3 of the patch.

It contains fixes for the following bugs
- BUG 17495971 - DEMANTRA 12.2 - CUMULATIVE HISTORY NOT CORRECT
  It now only uses DATA_PUMP COMPRESSION only on Enterprise Edition for 11g and and up.

- Bug 17452153 - 1OFF:16086475:TRYING TO FILTER DROP DOWN IN A METHOD CALL USING MORE THAN 1 ATTR
  It now builds GL level filters with and without the GL id column where applicable.

These bugs are also fixed in 7.3.1.6 and 12.2.3.

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

About

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

Search

Archives
« September 2015
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