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?

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 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!

Wednesday May 21, 2014

Topical Procedural Demantra Debugging Using Log Files

Demantra Debugging Using Log Files

Default log location is the Demantra_root/Collaborator/virtual_directory/portal/logs directory.
Review the comments in the Demantra_root/Collaborator/virtual_directory/portal/conf/logconf.lcf  file to specify this log file information:
- Name and location
- Maximum size of the log file
- Number to keep
- Log categories and log level

You can also set log levels at runtime with the following URL:
http://Demantra_root/Collaborator/virtual_directory /admin/loggerManager.jsp


Application server memory configuration (-Xmx)

Web Application troubleshooting:
- Demantra Web application includes 2 components:
  Application Server
  – Demantra Web application deployment inside a Web server.

  – User side browser and resources (CPU, Memory) running SUN Java Plug-in.

  They are connected by a Network (LAN, WAN...).
  SQL*Net log

  For better troubleshooting we need to understand where we have a problem is it on the Server side/Client side / Both ?

If Application out of memory errors:
- Identify where the problem is by using logs:
  Collaborator log, application server side log.   Plug-in log, Client side log.

Server side issues:
- Heap dump from the webserver
- Thread dump from the Webserver, in case of application freeze or slow down
- Application server memory configuration ( -Xmx)
- Count of Level members, need level member caching?

Client side issues:
- Heap dump from the Java plug-in
- Thread dump from the Java plug-in
- Plug-in memory configuration ( -Xmx)

When having installation errors there are a few files that can be analyzed:
• Installer log
• Screenshots of the selections in the different Installer panels.
• Review db_audit_log table for object changes made by the upgrade DB_Exception_Log table

Data Model: When having data model errors there are few files that can be analyzed:
• An Export of the Data model
• Review db_audit_log table for object changes made by the upgrade.
• LOG_DATA_MODEL table (an automatic LOG_IT log).

Data Loading
– Ep_Load, Ep_load Errors:
• Enable LOG_IT process on relevant Demantra stored procedures as defined in LOG_IT_PARAMS table
• DB_Exception_Log.

Ep_Load performance:
• Count of data from the staging tables.
• DB_Params table
  – Ep_Load performance related parameters
• Execute “Call_DM_Build_Procedures” after changing parallelization parameters in DB_Params
• DB HW definitions.
• System Memory Disk IO and CPU Utilization monitoring
Either system performance monitor or Linux top events
• AWR /ADDM reports.

Data Loading, Integration:

Integration Errors:
• Collaborator & Integration log (Enable sql logging: appserver.sql, appserver.update.sql)
• Concurrent request logs (collaborator.login.user)
• apscheck.sql (Note:246150.1)

Integration Performance:
• Count of data from the staging tables.
• APS_Params
  – Integration Interface related parameters
• DB HW definitions.
• system memory disk IO and CPU utilization monitoring
  - either system performance monitor or Linux top events.
• AWR/ADDM reports.

DB deadlock/ internal database error:
• Collaborator Log
• Database \ udump logs (USER_DUMP_DEST specifies the pathname for a directory where the DB server will write debugging trace files on behalf of a user process.)
• Enable LOG_IT process on selected Demantra stored procedures as defined in LOG_IT_Params table.

Web Application troubleshooting:
• Demantra Web application includes 2 components:
• Application Server

Demantra Web application deployment inside a Web server.
• Client
  – User side browser and resources (CPU, Memory) running SUN Java Plug-in.
• They are connected by a Network (LAN, WAN...).
• For better troubleshooting we need to understand where we have a problem is it on the Server side/Client side / Both ?

Application out of memory errors:
• Identify where the problem is by using logs:
• Collaborator log
  – server side log.
• Plug-in log
  – Client side log.
• Server side issues:
• Heap dump from the webserver
• Thread dump from the Webserver (in case of application freeze or slow down)
• Application server memory configuration (-Xmx)
• Count of Level members (need level member caching?)
• Client side issues:
• Heap dump from the Java plug-in.
• Thread dump from the Java plug-in
• Plug-in memory configuration (-Xmx)

WS performance and errors:
• Identify where the problem is:
• Server side
  – AWR /ADDM reports, DB side CPU and Memory.  Application server CPU and configuration, APS_PARAMS relevant parameters.
• Client side
  - CPU utilization (and for how long).
• Server side issues:
• Demantra Index Advisor report.
• Thread dump (in case of application freeze or slow down).
• System Memory Disk IO and CPU Utilization monitoring
  - either system performance monitor or Linux top events.
• DB HW definitions.
• Number of concurrent users at the time (concurrent user log)
- Also see Level Member Caching Purpose Performance Guidance (Doc ID 1627689.1)
- Also see Oracle Demantra Worksheets Caching, Details how the Caching Functionality can be Leveraged to Potentially Reduce Performance (Doc ID 1627652.1)


Client side issues:
• Plug-in –Xmx setting
• WS wizard panels screen shots.
• Client side log
  – If requested (Number of combinations in the WS).

Engine errors:
Always provide a full Engine log (manager and engine) with relevant logging option.
• DB errors:
• Engine log with Sql logging
  – depends on where the DB error is Engine manager or engine2k.

Processor error:
• Engine log with DebugPro logging’ groups 100/110
• Engine Out of memory errors:
• No of Tasks
• Count of Active Combinations (Select Count(1) from MDP_Matrix where prediction_status = 1)
• Engine not running:
• On Linux
  – Application server log
• Engine performance:
• AWR report
• Check whether CPU Hyper threading enabled on Engine servers
  – Disable if its enabled
• Engine Blades/Server + DB HW definitions.
• DB/Engine Blades Memory Disk IO and CPU Utilization Monitoring
  - Either system performance monitor or Linux top events.
• For more information check Oracle Support note 800030.1 "Demantra Engine Troubleshooting” Presentation (April 6th, 2011)

Configure auditing capabilities for BM configuration changes:
• This is a customization enhancement introduced by DEV application team in a white paper (“Implementing Demantra Audit log”)
  - Configuring an Audit Log in Demantra Using Database Triggers (Doc ID 1629974.1)
    * Currently captures Inserts/ Updates on Sessions/Computed_fields tables by a trigger based process
• The mechanism and idea can easily be enhanced for further Metadata audit opportunities
• Difference from BAL diff
  – This mechanism captures all the changes, not just how the latest change differs between schemas
• The logs are kept in a separate table (AUDIT_LOG ), so no impact on the out of the box schema.
• AUDIT_LOG table includes the following information (columns):
  - log_date
  - session_id
  - os_user
  - host_name
  - module
  - user_id
  - object_type
  - object_name
  - old_value
  - new_value
  - Message

Friday Nov 15, 2013

Feb 12, 2014 Upcoming Webcast: Demantra Logs by Procedure. Where are They, When are They Updated

Hello!   Feb 12, 2014, We will present a webcast that details the Demantra log file matrix.   Here are the essentials:

Title: Demantra Logs by Procedure.  Where are They, When are They Updated

- Abstract: When you are installing, cloning, updating the worksheet, etc, which logs are updated and where are they?

- Target Audience: Technical Users, Functional Users, System Administrators, DBAs

- This one-hour session is recommended for technical and functional users who utilize Demantra processes

- Benefits/Agenda List:

  - Describe the log files by function or procedure
  - Locating the log files
  - Controlling the log files
  - Producing deeper verbage in the log file
  - Review and explain examples

To register in January 2014, follow

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.

Verify database name

Verify Database User


Present the tnsnames.ora file to verify settings

Verify the ds.ini file

Check the Demantra version



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

Verify profile MSD_DEM: Debug Mode

ADD SQL to reveal the setting


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


Find Missing Dates


Verify the basic setup.

Verify Sys_params - AppServerURL AppServerLocation





Debug Mode





MaxUpdateThreads Check

Is Auditing Turned on?



Import Block Size

enginestarter port

Are any engine libraries missing?

Are the permissions set correctly for Engine.exe

Database Objects

Check for corruption

Implementing Parallization

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




Excess Rows

Not Null Columns

Indexes, Unused

Database Parameters





db_file_multiblock_read_count (MBRC)

SQL Plan Baselines




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


Do you have an engine2k.log file?






Middle Tier


Clearing Server Caches

Limit Number of retained files

Verify the Connection Pool



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



ComputerNames, localhost

Is the Engine Running?  Where?



Present permissions for

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 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



Rolling Updates






Worksheet Specific






Excess Rows




Review your worksheet hints

Configurable Combinations in the Worksheet
- 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







Workflow Status

Verify your schema


Verify Combinations for insert_units

Proport in Parallel




appserver properties.bat

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

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.

Thursday Aug 08, 2013

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 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

Friday Sep 14, 2012

Introducing the new Demantra Guided Resolutions!

There is a new method to find your solution called guided resolution or search helper.

Currently, we cover 5 specific topical areas:

Oracle Demantra 7.x and Above Data Loading Guided Resolution Note 1461899.1

Oracle Demantra Load Data Issues Not Necessarily Error Code Related Guided Resolution Note 1468884.1

Oracle Demantra 7.x and Above Workflow Issues Guided Resolution Note 1353217.1

Oracle Demantra 7.x and Above Worksheet Related Guided Resolution Note 1486639.1

Oracle Demantra 7.x and Above Engine Related Guided Resolution Note 1486634.1

The guides will be updated with the latest proven solutions on a regular basis, keeping the contect relevant.

What is a guided resolution?   What is the best practice using a guided resolution?

How to Use the Demantra Guided Resolution, a Proactive Services Tutorial.  Note 1473992.1

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


« May 2015