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 7.3.1.4 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! 7.3.1.3 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
   tree:

   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
   time.
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?
11. Run DROP_TEMPS

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

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

Search

Archives
« August 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
25
26
27
28
29
30
31
     
Today