Friday Dec 11, 2015

Demantra Demand Management DM Permance and Setup Analyzer

Hello!  We are actively changing the Demantra Analyzer until it is stable.   Please check MOS note 1618885.1 for any updates or download the latest on a weekly basis.  Thank You!  Jeffv

Friday Dec 04, 2015

Advanced Demantra Demand Management Training: Best Practices, Diagnostics, APIs, Series Locking, Launch Management, VCP Deployment, User Access Control + More!

Hello!   There is a new 10 module course that is available via MOS!  This course is the Advanced Demantra Demand Management Training and covers: Best Practices, Diagnostics, APIs, Series Locking, Launch Management, VCP Deployment, User Access Control + More!  There are even some quizzes!

Start here:

Advanced Demantra Demand Management Training: Best Practices, Diagnostics, APIs, Series Locking, Launch Management, VCP Deployment, User Access Control + More! This is the Index to a 10 Part Course. (Doc ID 2085264.1)

Let me know what you think.   Take Care!  Jeff

Thursday Dec 03, 2015

New! Demantra Demand Management Performance and Setup Analyzer

Hello!   There is a new version of the Demantra Demand Management Performance and Setup Analyzer.

See MyOracleSupport note

Demantra Performance and Setup Analyzer Script and Monitoring Tool (7.3.1 and Above) (Doc ID 1618885.1)

Let us know if you have any comments.  Comment here, in this BLOG, or follow the link to the Demantra Community where there is an existing thread ready for your comments/suggestions.   Regards!   Jeff

Wednesday Nov 18, 2015

Demantra Demant Planning Analyzer v200.2 is NOW Available!

Hello!  There is a new version of the Demantra Analyzer.  

See the latest in this MOS note: 1618885.1

See the overview doc here: 2079537.1

There are additional data points, a new format that is easier to review.  Additional comprehensive verification!  Give it a try!

Regards!   Jeff

Monday Aug 24, 2015

How to avoid ORA-06512 and ORA-20000 when Concurrent Statistics Gathering is enabled. New in 12.1 Database, Concurrent Statistics Gathering, Simultaneous for Multiple Tables or Partitions

Oracle Database 12.1 introduces a new feature, Concurrent Statistics Gathering. 

Concurrent statistics collection is simply the ability to gather statistics on multiple tables, or table partitions, at the same time.  When CONCURRENT statistics gathering is enabled, you can execute each statistics gathering job in parallel.  This combination is useful when you need to analyze large tables, partitions, or subpartitions.  This is accomplished  using a combination of the job scheduler, advanced queuing and resource manager.  Concurrent statistics collection can reduce the time it takes to gather statistics, provided the system can accommodate the extra workload.

Functionality wise, it is a way to gather stats on multiple tables, table partitions or subpartitions at the same time.

From a user perspective, the concurrent statistics collection functionality is very simple.  You set the CONCURRENT global preference to the required value using the DBMS_STATS package.  The RDBMS determines if concurrency is appropriate and if so, the level of concurrency to use. 
See MOS Note: 2034376.1, How to avoid ORA-06512 and ORA-20000 when Concurrent Statistics Gathering is enabled. New in 12.1 Database, Concurrent Statistics Gathering, Simultaneous for Multiple Tables or Partitions

Friday May 22, 2015

Demantra Worksheet Performance - A summary guide at Customer Request

Worksheet performance.  There are dozens of notes.  It can be challenging to find the best approach. 

  • If you are on or greater, see the following three notes.  Upgrade to the latest version of TABLE_REORG.  Run TABLE_REORG with the 'T' option and review the suggestions in the LOG_TABLE_REORG table.
  • Demantra TABLE_REORG procedure. Did you know that TABLE_REORG has replace rebuild_schema mad rebuild_tables?(Doc ID 2005086.1)
    - Demantra TABLE_REORG Tool New Release with Multiple Updates! Partitions, DROP_TEMPS and More! to 12.2.x.(Doc ID 1980408.1)
  • If you have an error: Demantra table_reorg Procedure Failed ORA- on sales_data mdp_matrix promotion_data How do I Restart? rupd$_ mlog$_ I have Table cannot be redefinitioned in the LOG_TABLE_REORG table(Doc ID 2006779.1)

I would consider these notes to be the best regarding worksheet performance:

  • Oracle Demantra Worksheet Performance - A White Paper (Doc ID 470852.1)
  • Oracle Demantra Worksheet Performance FAQ/TIPS 7.3+! (Doc ID 1110517.1)
  • Demantra 12.2.4 Worksheet Performance Enhancements Parameter dynamic_hint_enabled, Enable Dynamic Degree of Parallelism Hint for Worksheets. 
  • Development Recommended Proper Setup and Use (Doc ID 1923933.1)
  • Demantra Development Suggested Performance Advice Plus Reference Docs (Doc ID 1157173.1)
  • Oracle Demantra Worksheets Caching, Details how the Caching Functionality can be Leveraged to Potentially Increase Performance (Doc ID 1627652.1)
  • The Column Prediction_Status, MDP_Matrix and Engine. How are they Related? Understand Prediction_status Values (Doc ID 1509754.1)

Also, see:
Demantra Gathering Statistics on Partitioned Objects Oracle RDBMS 11gR2 (Doc ID 1601596.1)
- Demantra 11g Statistics new Features and Best Practices Gather Schema Stats (Doc ID 1458911.1)

I would review all parameters mentioned in the docs above and:

1. Monitor the workstation memory consumption and CPU utilization as the worksheet is being loaded.
   * You may have to adjust the memory ceiling for Java
2. Manage MDP_MATRIX.  Are there dead/unused combinations?  When running the engine, you can manage the footprint of the input.  If MDP_MATRIX
   is carrying sizeable dead combinations and/or entries without a matching entry in SALES_DATA, you are increasing processing load.  Check out
   note 1509754.1.  The attachment explains the principle.
3. Using the notes above, can you cache?  Can you use filters?  Can you use open with? 
   A series can be cached, aggregated by item and cached in the branch_data_items table.  This improves performance of worksheets that are aggregated
   across locations and that do not have any location or matrix filtering.
4. Run the index advisor.  Does it suggest additional indexes? 
5. If you do not have the index advisor, produce an AWR.  The AWR should be taken when the user opens the worksheet.  For example, start the AWR process. 
   Wait 10-15 minutes.  Tell the user to open the worksheet.  After the open succeeds, wait 10 minutes.  Stop the AWR process.  What are the top SQLS? 
   What are the contentions?
6. Do you have your large tables on their own tablespace?  This means each large table has a tablespace to its self.  Each large index has a
   tablespace to its self.
7. The worksheet is retrieving rows to display.  Is there row chaining causing multiple block reads?  That should be revealed in the AWR or run the
   appropriate SQL.
8. Worksheet design is important.  The worksheet designers setup what they need.  However, that does not mean that the worksheet design blends well
   with available processing capabilities.  Know the forecast branch health.  I think this is discussed in 1509754.1.  The following SQL reveals the

   select level_id,count(*) from mdp_matrix
   where prediction_status = 1
   group by level_id
   order by level_id

   If you have a branch that is 100000 and remaining branches at 5000 and 10000 that is a problem.  That would point to a setup/design issue.
   Meaning that if you have branch as a level and it just so happens that 1 branch indeed has 100,000 and the other 2 branches account for smaller
   volumne, 5000 and 10000, the chosen levels of the worksheet need to be revisted.  Perhaps a level lower than branch is better suited to
   processing the data.  While this and #2 above are probably out of your control, it will help explain the worksheet loading and engine processing
9. Reduce the amount of memory that your worksheet selects:
   - Remove series if possible
   - Reduce the span of time
   - Apply filters
10. Review all server and client expressions.  Are they affecting performance?

Tuesday May 05, 2015


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:


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:

   uname       => '&demantra_schema_name',
   orig_table  => '&original_table_name',
   int_table   => '&interim_table_name');

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. 

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

Wednesday Apr 01, 2015

New SQL to Determine Out of Order Ratio and Cluster Factor

Hello!  Please run the following for both mdp_matrix and sales_data.     Questions?  Email at

1. The first SQL will need to be adjusted to accommodate your PK for sales_data and your PK for mdp_matrix.  Adjust the select and from clause to match your PK.   In the case of this sample, the PK was:


-- ShowOOR.sql

     IS_T_EP_SPF ,
     DATA_ROW ,
       IS_T_EP_SPF ,
       ROW_NUMBER ,
         IS_T_EP_SPF ,
       ) C
     ) B
   ) A
 )                               /
 )),3)*100) AS "Out Of Order Ratio %"

-- ShowCF.sql
undefine table_name
SELECT ui.index_name, us.blocks as "Table Blocks", ui.clustering_factor as "Index clustering Factor", ut.num_rows as "Table Rows"
FROM   user_indexes     ui,
       user_tables      ut,
       user_constraints uc,
       user_segments us
WHERE  ui.table_name = ut.table_name
AND    ut.table_name = uc.table_name
AND    ui.index_name = uc.index_name
AND    ut.table_name = us.segment_name
AND    us.segment_type = 'TABLE'
AND    uc.constraint_type = 'P'
AND    ut.table_name      = '&table_name';

Thursday Feb 26, 2015

Setting Worksheet Related Parameters and Hardware Requirement Example

 Hello!   This is an example when determining how to set critical aps_params for worksheet performance:

If you expect to have 150 users with concurrency rate of 30% your setting and hardware should be:

   50 users * 30% = 45 users

   threadpool.query_run.per_user = 8

   threadpool.query_run.size = Number of concurrent users * threadpool.query_run.per_user (8) = 360

   MaxDBConnections = threadpool.query_run.size + 10 = 370

   To run a production environment with those setting you should have:

   Database: DB machine with 60 CPU's (= MaxDBConnections / 6 = 60)

   Application Server: AP with 27 CPU's (= threadpool.query_run.size/16) + 4), 4GB of memory

Only with this hardware you can run Demantra with 150 users with concurrency rate of 30%, is your hardware powerful enough for your environment?

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   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 Dec 10, 2014

Hello Demantra Customers! Are you at version A new cumulative patch will be released soon.

Hello Demantra Customers!  Are you at version  A new cumulative patch will be released soon.  This cumulative patch is different to the 12.2.x patch strategies in the respect
that it will only contain fixes that are new to  This cumulative patch is scheduled to be released later this month, December 2014.  To monitor, see this note, 1952805.1 or visit the Oracle Demantra BLOG at

Wednesday Oct 01, 2014

Demantra Performance and Setup Analyzer v2.00 is FINALLY Available!

Hello Demantra Customer!  There is a new release of the Demantra Performance and Setup Analyzer!  Version 2.00 has been released!
You can pick and choose any SQL that you prefer to see given any type of task at hand.   See MyOracleSupport note
Demantra Analyzer script for Setup and Performance Monitoring (11.5.X and R12), 1618885.1

I am considering adding more topical analysis and deeper digging into additional details for the next release. 

Any questions / comments, please email

Thank You!

Demantra 12.2.4 Performance Enhancement Data Points that you need to know!

Demantra 12.2.4 Customers, There is a new MyOracleSupport note highly recommended by Demantra Development.  There are new performance enhancements available!  However, there is additional information in this note that is critical to implement some of these new enhancements.

Please see, Demantra 12.2.4 Worksheet Performance Enhancements Parameter dynamic_hint_enabled. Development Recommended Proper Setup and Use (Doc ID 1923933.1)

Thursday Sep 25, 2014

Demantra Integration Webcast 24-Sep-2014

Hello!   Were you able to attend the the Demantra webcast 24-Sep-2014?   The topic was integration.  You can watch a replay found at:
Demantra Solutions Advisor Webcast Calendar And Archive (Doc ID 800030.1) and scroll to Integrating Demantra With Oracle Applications.


Coming up:

1) 8th OCT, V2 Analyzer review and Data Mining Live Demo

2) I am thinking about breaking the 24-Sep-2014 into two presentations.  This would allow us to dig deeper into debugging the integration and workflow.  These would concentrate on data once it arrives into the denorm table AND data mapping. 

Has anyone used the e_plan_tree database object?

SQL> desc msdem.e_plan_tree
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 E_PLAN_NODE_ID                            NOT NULL NUMBER(10)
 E_PLAN_CHILD_NODE                                  NUMBER(10)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 FIELD_NAME                                NOT NULL VARCHAR2(30)
 E_PLAN_NAME                                        VARCHAR2(30)
 E_PLAN_TITLE                                       VARCHAR2(100 CHAR)
 E_PLAN_BASE_DIM                                    VARCHAR2(30)
 E_PLAN_TYPE                                        VARCHAR2(10)
 E_PLAN_DATA_TYPE                                   VARCHAR2(10)
 E_PLAN_RELATION                                    NUMBER(10)
 SOURCE_NAME                                        VARCHAR2(30)
 E_PLAN_AGGRI                                       VARCHAR2(400)
 MODEL_VERSION                             NOT NULL NUMBER(5)
 VERSION_NAME                                       VARCHAR2(30)
 FICTIVE_COLUMN_NAME                                VARCHAR2(30)
 E_PLAN_TABLE_NAME                                  VARCHAR2(30)
 HYRARCHY_NO                                        NUMBER(5)
 ENABLE_LEVEL                                       VARCHAR2(20)
 USE_EP_GEN_ID_FOR_FILTER                           NUMBER(1)
 USE_ITEM_LOC_ID                                    VARCHAR2(20)
 AGGRI_FUNC                                         VARCHAR2(400)
 WAVG_DEPENDANT                                     VARCHAR2(30)
 PARTS_VERSION                             NOT NULL NUMBER(5)
 IS_HIERARCHY_LEVEL                                 NUMBER(1)
 PARENT_FIELD_NAME                                  VARCHAR2(30)
 APPLICATION_ID                                     VARCHAR2(63)



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


« July 2016