Friday Sep 27, 2013

New Demanta Performance and Setup Analyzer on the way!

Hello Demantra Customers!  There is a new tool being developed in Oracle Proactive Services.  The Demantra Performance and Setup Analyzer.  We are reviewing the data points now and release is scheduled for November 2013.   Check out the areas of coverage below.  Any comments?  Please post.  Regards!  Jeff

There are some parameters listed twice to deliver in context results.

========================================
========================================
VERIFICATION
========================================
========================================
Verify database name

Verify Database User

DBUser

Present the tnsnames.ora file to verify settings

Verify the ds.ini file

Check the Demantra version

Check the RIGHT PRIVILEGES are GRANTED

Echo on engine server, $ENGINE_ROOT, $ORACLE_HOME, $PATH, $LD_LIBRARY_PATH, $JAVA_HOME

Verify profiles 'MSD_DEM: Schema' and 'MSD_DEM: Host URL'

Verify profile MSD_DEM: Debug Mode

ADD SQL to reveal the setting

========================================
========================================
PROCESSING
========================================
========================================

Are you running Multiple Batch Processes in Parallel?

Checking for active combinations and combinations that could be archived in BIIO table

Number of Active combinations in MDP_MATRIX

Improving Your Batch Forecast

Tasks and Branches, TreeHierarchyTest.exe

Managing Simulations

MDP_MATRIX - ACTIVE COMBINATIONS and ENGINE PERFORMANCE

Find Missing Dates

========================================
PARAMETERS
========================================

Verify the basic setup.

Verify Sys_params - AppServerURL AppServerLocation

threadpool.update.data.manual.size

threadpool.update.table.manual.size

threadpool.update.comb.manual.size=

threadpool.update.record.manual.size=

MaxDBConnections

Debug Mode

tunnel.client.maxConnections 

threadpool.update.comb.batch.size

threadpool.update.record.batch.size

threadpool.update.record.batch.size

MaxUpdateThreads Check

Is Auditing Turned on?

MaxSqlInExpressionTokens

StatsLowRowLimit

Import Block Size

enginestarter port

Are any engine libraries missing?

Are the permissions set correctly for Engine.exe

========================================
========================================
Database Objects
========================================
========================================

Check for corruption
- ANALYZE TABLE SALES_DATA VALIDATE STRUCTURE CASCADE FAST;
- ANALYZE TABLE MDP_MATRIX VALIDATE STRUCTURE CASCADE FAST;
- ANALYZE TABLE PROMOTION_DATA VALIDATE STRUCTURE CASCADE FAST;

Implementing Parallization

Chained Row count
 
Should you rebuild tables by Primary Key (PK). 

Partitioning

INITRANS

PCTFREE, PCTUSED

Excess Rows

Not Null Columns

Indexes, Unused

========================================
========================================
Database Parameters
========================================
========================================

DB_BLOCK_SIZE

OPTIMIZER_INDEX

Optimizer_Mode

Cursor_Sharing

db_file_multiblock_read_count (MBRC)

SQL Plan Baselines

parallel_dml

SQLNET.EXPIRE_TIME

========================================
========================================
Statistics
========================================
========================================

Verify the health of large tables

System Statistics

Gathering Statistics

========================================
========================================
Java Application Server, Client Cache
========================================
========================================

Which is the java version on application server and client?

Do your workstations have the Java runtime parameters setup to support the memory of their system per Note <>?

Clearing Cache could help performance
- we discuss all tiers

========================================
========================================
Logs, Display bottom 75 lines
========================================
========================================

DB_EXCEPTION_LOG

Do you have an engine2k.log file?

EngineManager.log

collaborator.log

EngineManagerPreRunLog.txt

DB_TIMING_LOG

DB_SECTION_LOG

========================================
========================================
Middle Tier
========================================
========================================

Cache

Clearing Server Caches

Limit Number of retained files

Verify the Connection Pool

connectionTimeout

========================================
========================================
Engine
========================================
========================================

Number of Engines, ComputerNames, localhost

Is logging turned on for the engine?

Engine Profiles

Verify date formats

Clearing Forecast Values

Verify that the six system parameters specifying engine used tablespaces are set correctly

Verify engineplatform

Verify enginebaseURL

====================================
====================================
$ENGINE_ROOT/bin/Settings.xml file
====================================
====================================

EngineUnixPortConfig

EngUnixDebugMode

ComputerNames, localhost

Is the Engine Running?  Where?

Verify SQLLDR

OCCITest

Present permissions for DS_CONFIG.sh

Present permissions for $ENGINE_ROOT/lib and $ENGINE_ROOT/bin

Do you have at least 1 combination that meets the rule for Insert_Units to work on and insert rows into the future?

Are you viewing the correct series?

Are you using the correct profile?

The Parameter MaxEngMemory is introduced from 7.3.0.1 onwards

Are you using promotions?

Are you setup for a distributed engine?

Verify settings.xml EngineUnixPortConfig

Verify the Logging Level of the Run Time Environment

Determining if the Engine Failed

Verify Null Pn Rows/Column verification

========================================
========================================
Forecast Specific
========================================
========================================

TargetTaskSize

BranchIDMultiple

========================================
========================================
Rolling Updates
========================================
========================================

RollingUpdatesUseJobQueues

RollingUpdatesMaxQueues

RollingUpdatesTimeout

RollingUpdatesRenameColumns

RunInsertUnits

========================================
========================================
Worksheet Specific
========================================
========================================

MaxAvailableFilterMembers

AppServerURL

MaxSqlInExpressionTokens

worksheet.full.load

client.worksheet.calcSummaryExpressions

Excess Rows

threadpool.query_run.per_user

threadpool.query_run.size

worksheet.data.comb.block_size

ApprovalProcessScope

Review your worksheet hints

Configurable Combinations in the Worksheet
- Client.uilimitations.maxcombs.ws
- Client.uilimitations.maxcells.ws
- Client.uilimitations.maxcells
- Client.uilimitations.maxdiskspace
- Client.uilimitations.warning

========================================
========================================
Data Loading
========================================
========================================

Verify any data loading errors
- select * from BIIO_SUPPLY_PLANS_err;
- select * from BIIO_SUPPLY_PLANS_POP_err;
- select * from BIIO_OTHER_PLAN_DATA_ERR;
- select * from t_src_item_tmpl_err;
- select * from t_src_loc_tmpl_err;
- select * from t_src_sales_tmpl_err;

Monitor Data Load Process

Ep_Load_Sales_LoadNullActualQty

Ep_Load_Sales_LoadFromStagingTableDirectly

Ep_Load_Sales_DisableEnableTriggers

Ep_Load_Sales_SALES_DATA_Merge_LoopControl

ep_load_do_commits

========================================
========================================
Workflow
========================================
========================================

Workflow Status

Verify your schema

========================================
========================================
insert_units
========================================
========================================

Verify Combinations for insert_units

========================================
========================================
Proport in Parallel
========================================
========================================

ProportTableLabel

ProportRunsInCycle

ProportParallelJobs

========================================
========================================
appserver properties.bat
========================================
========================================

Display the appserver properties.bat
- Displays the file and delivers detailed explanation of settings

Implementing Large-Scale Demantra Table Rebuilds To Improve Performance with Zero Downtime Using DBMS_REDEFINITION

Greetings Demantra Users! There is an excellent note detailing Oracle's implementation of Demantra in a big data environment. This was Oracle's largest ever Internal value chain planning database table. Read about how we did this, implementing RDBMS partitions. See Implementing Large-Scale Demantra Table Rebuilds To Improve Performance with Zero Downtime (Doc ID 1587179.1)

Wednesday Sep 04, 2013

Are you ready for a Demantra Worksheet Webcast?

The Key to Optimizing Demantra Worksheet Performance will be presented September 11, 2013 at 10am Central.
Please see Demantra Solutions Advisor Webcast Calendar And Archive (Doc ID 800030.1) in My Oracle Support.

Worksheet Performance Problems? Try this action plan

The index advisor can be ran for a user, all users, a worksheet, all worksheets or the entire schema. I am suggesting we run it for the two slowest worksheets and implment the results.
1) Are the workstations configured to use the maximum amount of Java memory?
2) Are you using client expressions?  
3) Are you using server expressions?  Can any of the data be adjusted and saved prior to worksheet open?
4) Have you iosolated the SQL that drives the slowest responding worksheets for tuning/index improvements?
5) Have you submitted the longest running SQL to Support for suggestions?
6) Has that longest running SQL been executed outside of the worksheet display process to determine if the expected results are realistic considering available hardware and matematical physical limits of hardware?
7) Have you analyzed your approach to retrieving/adjusting forecast data? The past approach may not be the best. For example, aggregation levels, forecast horizon, the amount of families or business lines retrieved at once for any particular worksheet.
8) Is your instance undersized before adding any new sales_data data. A complete review of
production and supporting instances, such as test, could be conducted.  Use note 1301231.1.
9) What is your forecast horizon?  Is this required for every worksheet even during the planners day-to-day tasks of forecast adjustment? Can you use a 'master plan' worksheet approach.
10) A complete review of all Demantra param and environment variable needs to be conducted. These should match the capabilites of the RDBMS and the application server.
11) A complete review of the application server settings needs to be conducted to allow for maximum performance.

Tuesday Aug 13, 2013

Do Not Run Multiple Branch Processes in Parallel

Query to show log:
------------------

select (select schema_name
        from  demantra.wf_schemas
        where schema_id = a.schema_id),
        to_char(a.record_created, 'DD-MON-YYYY HH24:MI:SS'),
        to_char(a.record_updated, 'DD-MON-YYYY HH24:MI:SS'),
        a.*
from    demantra.wf_process_log a
where process_id >= 922599
order by a.record_created desc


Does the above output indicate that you are running multiple batch processes in parallel?

Please review your environment and make sure you run processes in the correct order and
that the processes are not competing for hardware resources.

If you are trying to run processes that compete for same data in same tables you can try extending
your undo space and settings to be much larger, assuming your hardware can support this path.

However, running overlapping engines is not recommended.

* Attain the process ID from the OS for the most recently ran batch process 

Thursday Aug 08, 2013

7.3.0.1 or 7.3.1.4 Demantra with EBS 11i, ASCP VCP 11i?

Many customers are unwilling to upgrade their EBS due to customizations, users and overall effort required, but upgrading their VCP apps is easier and is supported.  You can leave your 11i system as is and install a separate VCP 12.1 instance which collects data from 11i10.

This is a fully supported configuration and allows you to connect Demantra 7.3.1.4 to EBS 11i10.

You do not even need to use any VCP apps other than Demantra.  The data flows through the 12.1 VCP instance into Demantra 7.3.1.4. 
This is the certified configuration for 11i and Demantra 7.3.1.4, so we advise considering this mix of tools.

The additional benefit you achieve is the ability to bring your VCP applications forward without impacting your EBS applications.

For 7.3.1.4 you would work with the following configuration.
    EBS 11.5.10  (11i)
    VCP 12.1.3.8
    Demantra 7.3.1.4

Worksheet Issues During or After Upgrade?

 Proactive action During/After an Upgrade may help.  Please review the following:

  1. Clean the Java cache by deleting the Java cache windows folder 
  2. Set the Java console debug mode to '5'
  3. Reproduced the issue
  4. Review the Client Java console
  5. Review the collaborator.log
  6. Produce an action plan

Wednesday Jul 17, 2013

Demantra Using Oracle database Real Application Clusters (RAC)

Demantra version:7.3.1.2

Database version:11.2.0.3

Due to the large amount of data within our system we are plannig to implement RAC to improve performance.  We have already recommended other performance guidelines from Oracle like reordering columns, partitioning sales_data table etc.  For further performance improvement since we expect more stores and items being introduced which would increase data we are planning to go for RAC as it is mentioned in the Demantra Installation Guide that RAC is supported in Demanra.

We would like the below information before proceeding with this major step:-

1.) For Distributed Engine Scaling - The Oracle Doc ID 468688.1 states that "The number of engines/machines defined in the Engine Administrator should not exceed 3 times the number of database CPUs".  So we want to know in case of RAC will the above calculation be based on cumulative CPU nodes of all database RAC nodes? I.e when using RAC can we set the total number of Engines upto 3 times the cumulative CPU nodes of all database RAC nodes.

Answer 1: For Distributed Engine Scaling -- using the cumulative CPU nodes is not quite right for RAC, since there will be extra interconnect overhead in a RAC system.  So you can try using the "3 times CPU's" but you might have to scale it back based on empirical performance.

2.) Using a Distributed Engine with one master and multiple slave machines. Is it possible to point different slave engines to different RAC nodes?

Answer 2: Is it possible to point different slave engines to different RAC nodes?  No.  The RAC will automatically load balance when you connect using the SCAN addresses.

Oracle Demantra 12.2.1 Available

We are excited to announce Oracle Demantra 12.2.1 is now available for new and existing customers. All customers who are not incorporating Demantra with other VCP products are welcome to upgrade without any restrictions. Customers who are using Demantra in conjunction with VCP products will need to upgrade VCP to 12.2.1 which requires application and participation in Oracle E-Business Suite early adopter program.
Demantra 12.2.1 includes a wide array of new features driven by customer requirements and needs. Key features include:
• Streamlined import and export from Microsoft Excel
• Support Gregorian Month data aggregation in weekly system
• Multilanguage support for eleven languages
• Promotion Calendar Optimization
• Enhanced integration with Advanced Planning Command Center (VCP 12.2.1 Required)

Demantra 12.2.1 will work with JD Edwards EnterpriseOne 9.1 using the AIA 11.4 for the Value Chain Planning Base Integration Pack.
Demantra 12.2.1 will only work with VCP 12.2.1.
Demantra 12.2.1 and VCP 12.2.1 will work with EBS 12.1.3 or EBS 12.2.1.

Please review the release readme and all 12.2.1 documentation carefully to determine content details for this patch. You can also find documentation in the Demantra Documentation Library on My Oracle Support (note 443969.1).

The release is currently available on My Oracle Support (formerly known as Metalink) as Patch 16409031.

Thursday Jun 27, 2013

Demantra Implementation Tip Windows and Unix or Linux

Hello!  Are you implementing using a third party or consulting resources?  

Recently we have seen some cases where customers no longer have a windows installation.  After the initial install and configuration, once the instance has gone live, the windows install is either deleted or most likely no longer with the customer as the same was installed on the implementers' laptop to start with. As a result when support comes back requesting the customer to apply a patch and/or upgrade they do not have a windows installation.  This has started happening after Oracle Demantra gave them the option to configure the engine on Unix. 

Workaround:

It is advisable that the customer keep their Windows installation intact for further patching and/or upgrade.  It is aslo possible that the implementer had installed Demantra on his Windows box and you do not have access to it any more.  It is possible that with the web and engine on Unix, and the silent installer having downloaded all the executable for Business Modeler, to work on the User's client machine, you may no longer need the windows install.

I have not tested the above 

Friday Jun 21, 2013

Announcing Oracle-Demantra 12.2.1 Release

We are excited to announce Oracle Demantra 12.2.1 is now available for new and existing customers. All customers who are not incorporating Demantra with other VCP products are welcome to upgrade without any restrictions. Customers who are using Demantra in conjunction with VCP products will need to upgrade VCP to 12.2.1 which requires application and participation in Oracle E-Business Suite early adopter program.

Demantra 12.2.1 includes a wide array of new features driven by customer requirements and needs. Key features include:

·       Streamlined import and export from Microsoft Excel

·       Support Gregorian Month data aggregation in weekly system

·       Multilanguage support for eleven languages

·       Promotion Calendar Optimization

·       Enhanced integration with Advanced Planning Command Center (VCP 12.2.1 Required)

Demantra 12.2.1 will work with JD Edwards EnterpriseOne 9.1 using the AIA 11.4 for the Value Chain Planning Base Integration Pack.

Demantra 12.2.1 will only work with VCP 12.2.1.

Demantra 12.2.1 and VCP 12.2.1 will work with EBS 12.1.3 or EBS 12.2.1.

Thursday May 23, 2013

Have you heard about the Partial Extrac Tool? Do you use the Partial Extract Tool?

Hello Demantra Users!  There has been a complete rewrite of the Demantra Data Extraction Tool guide.  Check out Demantra Non Invasive Partial Schema Export Utility MOS Note 1448266.1. 

Monday Apr 29, 2013

EBS Weblogic Demantra Logging Into Application Servlet Mode

Hello!  There is a new configuration when attempting to log into Demantra via EBS using forms servlet.  Are you experiencing the following?   Then see MOS note 1547287.1.

Rlease 12.1.3 EBS Advance Planning environment and configured Demantra to work with it.
EBS and Demantra have separate AppServers.

EBS has OAS and Demantra has Weblogic.
Demantra 7.3.1.3 w/ Weblogic 10.3.5
VCP 12.1.3.7 w/OAS

The weblogic.xml shows the following for the http setup:
false

The EBS and Demantra work fine by themselves.

When logged into EBS and then select an activity that prompts for the Demantra login, then the EBS form session gets disconnected when running in Forms Servlet Mode.
The Demantra application does not have any issues.
The problem does not occur if they run Forms in Socket mode.

When trying to connect to Demantra from EBS, it is redirecting to Demantra page instead of opening a new page and so we are losing the EBS page.

In the Java Console we can see 5 attempts to reconnect are logged and then the session fails with a FRM-92102.
Bumped up frmNetworkRetries = 30 and the problem still occurs.

The Demantra session continues to work fine, however the user is forced to shutdown the browser and log back into the EBS environment and restart forms each and every time.

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 Feb 06, 2013

Need Additional Details? Check out these latest MOS notes!

Demantra DATABASE PARAMETERS General, Organized Checklist. A Place to Begin for Maintenance and Proper Diagnostics, (Doc ID 1519789.1)

Demantra Index Advisor 7.3.0 and Upwards User Guide Addendum, Comment and Output for Performance (Doc ID 1520222.1)

Demantra DB Configuration and Application Maintenance Best Practices (Doc ID 1458751.1)

Demantra Process Management and Performance Summary Guide Parameters Logs Triggers Indexes Index and More! (Doc ID 1520581.1)

Demantra WORKSHEET PARAMETERS General, Organized Checklist. Begin for Maintenance and Proper Diagnostics, (Doc ID 1519805.1)

links not included for security

About

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

Search

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