Friday Dec 14, 2012

The Column Prediction_Status, MDP_Matrix and Engine. How are they Related? Understand Prediction_status Values

Do you know what these values are telling you?

19854 99 1 1 1 1 3
1077 99 0 1 1 1 0
262691 99 1 1 -1
56 99 0 1 1 1 2
1 98 1 1 1 1
1 99 0 1 1 1
748796 1 1 1 4
351633 1 1 1 1 1 2
1877829 97 1 1 4
840 99 1 1 1 1
27 99 0 1 1 1 3
1 97 1 1 -1
66712 99 1 1 1 1 2
53213 1 1 1 1 1 3
2560 98 1 1 4


Check out The Column Prediction_Status, MDP_Matrix and Engine. How are they Related? Understand Prediction_status Values (Doc ID 1509754.1)

This customer is adding an additional processing burden, adding no value.  The incoming data should be scrubbed to eliminate the overhead. 

Thursday Oct 11, 2012

System Wide Performance Sanity Check Procedures

Do you need to boost your overall implementation performance?

Do you need a direction to pinpoint possible performance opportunities?

Are you looking for a general performance guide?

Try MOS note 69565.1

This paper describes a holistic methodology that defines a systematic approach to resolve complex Application performance problems.  It has been successfully used on many critical accounts.  The 'end-to-end' tuning approach encompasses the client, network and database and has proven far more effective than isolated tuning exercises.  It has been used to define and measure targets to ensure success. 

Even though it was created in 2001 and checked for relevance on 13-Oct-2008, the procedures are still very valuable.



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

Thursday Sep 06, 2012

Using DB_PARAMS to Tune the EP_LOAD_SALES Performance

The DB_PARAMS table can be used to tune the EP_LOAD_SALES performance.  The
AWR report supplied shows 16 CPUs so I imaging that you can run with 8 or more
parallel threads.  This can be done by setting the following DB_PARAMS
parameters.  Note that most of parameter changes are just changing a 2 or 4 into an 8:

DBHintEp_Load_SalesUseParallel = TRUE
DBHintEp_Load_SalesUseParallelDML = TRUE
DBHintEp_Load_SalesInsertErr = + parallel(@T_SRC_SALES@ 8) full(@T_SRC_SALES@)
DBHintEp_Load_SalesInsertLd  = + parallel(@T_SRC_SALES@ 8)
DBHintEp_Load_SalesMergeSALES_DATA = + parallel(@T_SRC_SALES_LD@ 8) full(@T_SRC_SALES_LD@)
DBHintMdp_AddUpdateIs_Fictive0SD = + parallel(s 8 )
DBHintMdp_AddUpdateIs_Fictive2SD = + parallel(s 8 )

AWR Performance Report and Read by Other Session Waits

For the questions regarding "read by other session" and its relation to "db file sequential/scattered read",
the logic is like this:

When a "db file sequential/scattered read" is done, the blocks are either already in the cache or on the disk.  Since any operation on blocks is done in the cache and since and the issue is "read by other session" I will relate to the case the blocks are on the disk.

Process A is reading the needed block from the disk to the cache.  During that time, if process B (and C and others) need the same block, it will wait on "read by other session".  A and B can be threads of the same process running in parallel or unrelated processes.  For example two processes doing full table scan on mdp_matrix etc.

Solutions for that can be lowering the number of processes competing on the same blocks, increasing PCTFREE.  If it is a full table scan, maybe an index is missing that can result in less blocks being read from the cache and so on.

Thursday Aug 02, 2012

Are you Implementing Partitions for Demantra? Consider These Points.

1) Partition columns must be a subset of the primary key columns

2) Each partition should have its own tablespace.  All partitions of a partitioned object must reside in tablespaces of a single block size.

3) Each of the large tables should have their own tablespace.

4) Set the following parameters so that the Analytical Engine can find the partition on which any combination resides:
    Parameter                Purpose
    PartitionColumnItem Specifies the name of the column that partitions the data by item.
    PartitionColumnLoc  Specifies the name of the column that partitions the data by location.

    Note: When the SALES_DATA table is not partitioned by a level column, you need to set:
    update init_params_0 set value_string = column name
    where pname in ('PartitionColumnItem', 'PartitionColumnLoc');

5) Compute the optimal PCTFREE, PCTUSED and INITRANS values for the tables.

6) Ensure that the Schema statistics are up to date.

7) When creating partitions, consider your main worksheet levels.  Does your primary key follow the worksheet levels?  Partitions should also follow your worksheet levels and primary key.  If you have several worksheets that have different levels, way your options according to use.

Also, please review the following MyOracleSupport Documents:

Oracle Demantra Implementing Partitions for Performance (Doc ID 1227173.1)
Demantra Performance Overview and Recommendations High Impact Discussion Points (Doc ID 1162795.1)
Partitioned Sales_data Table But Engine Run Is Slower (Doc ID 1331482.1)

Tuesday Jul 10, 2012

Data Loading Issues? Try the new Demantra Data Load Guided Resolution

Hello!   Do you have data loading issues?  Perhaps you are trying the new partial schema export tool.  

New to Demantra, the Data Load Guided Resolution, document 1461899.1.  This interactive guide will help you locate known solutions to previously discovered issues quickly.  From performance, ORA and ODPM errors to collections related issues that have no known hard number error.   This guide includes the diagnosis of data being imported into Demantra and data being exported from Demantra. 

Contact me with any questions or suggestions.   Thank You!

Friday Jul 06, 2012

Demantra Controlling MDP_MATRIX Combinations Assigned to Forecasting Tasks Using TargetTaskSize

New parameter: TargetTaskSize

Old parameter: BranchID  Multiple, deprecated onwards

Parameter Location: Parameters > System Parameters > Engine > Proport
Default: 0
Engine Mode: Both
Details: Specifies how many MDP_MATRIX combinations the analytical engine attempts to assign to each forecasting task.  Allocation will be affected by forecsat tree branch size.  TaskTargetSize is automcatically calculated.  It holds the perferred branch size, in number of combinations in the lowest level.
This parameter is adjusted to a lower value for smaller schemas, depending on the number of available engines.

  - As the forecast is generated the engine goes up the tree using max_fore_level and not top_level -1.  Max_fore_level has
    to be less than or equal to top_level -1.  Due to this requirement, combinations falling under the same top level -1
    member must be in the same task.  A member of the top level -1 of the forecast tree is known as a branch.  An engine
    task is therefore comprised of one or more branches.

    - Reveal current task size
      go to Engine Administrator --> View --> Branch Information and run the application on your Demantra schema.  This will be deprecated in since there is no longer a means of adjusting the brach size directly.  The focus is now on proper hierarchy / forecast design.

    - Control of tasks
      The number of tasks created is the lowest of number of branches, as defined by top level -1 members in forecast
      tree, and engine sessions and the value of TargetTaskSize.  You are used to using the branch multiplier in this
      calculation.  As of, the branch ID multiple is deprecated.

    - Discovery of current branch size
      To resolve this you must review the 2nd highest level in the forecast tree (below highest/highest) as this is the
      level which determines the size of the branches.  If a few resulting tasks are too large it is recommended that
      the forecast tree level driving branches be revised or at times completely removed from the forecast tree.

    - Control of foreacast tree branch size
        - Run the following sql to determine how even the branches are being split by the engine:
          select count(*),branch_id from mdp_matrix where prediction_status = 1 and do_fore = 1 group by branch_id;
          This will give you an understanding if some of the individual branches have an unusually large number of
          rows and thus might indicate that the engine is not efficiently dividing up the parallel tasks.

        - Based on the results of this sql, we may want to adjust the branch id multiplier and/or the number of engines
          (both of these settings are found in the Engine Administrator)

          select count(*), level_id from mdp_matrix where prediction_status = 1 and do_fore = 1 group by level_id;

          This will give us an understanding at which level of the Forecast tree where the forecast is being generated. 
          Having a majority of combinations higher on the forecast tree might indicate either a poorly designed forecast
          tree and/or engine parameters that are too strict

          Based on the results of this we would adjust the Forecast Tree to see if choosing a different hierarchy might
          produce a forecast, with more combinations, at a lower level.

          For example:

            - Review the 2nd highest level in the forecast tree, below highest/highest, as this is the level which
              determines the size of the branches.

            - If a few resulting tasks are too large it is recommended that the forecast tree level driving branches
              be revised or at times completely removed from the forecast tree.
            - For example, if the highest level of the forecast tree is set to Brand/All Locations.

            - You have 10 brands but 2 of the brands account for 67% and 29% of all combinations.

            - There is a distinct possibility that the tasks resulting from these 2 branches will be too large for
              a single engine to process.  Some possible solutions could be to remove the Brand level and instead
              use a different product grouping which has a more even distribution, possibly Product Group.
            - It is also possible to add a location dimension to this forecast tree level, for example Customer. 
              This will also reduce forecast tree branch size and will deliver a balanced task allocation.

            - A correctly configured Forecast Tree is something that is done by the Implementation team and is
              not the responsibility of Oracle Support. 

Allocation will be affected by forecast tree branch size.  When TargetTaskSize is set to 0, the default value, the system
automatically calculates a value for 'TargetTaskSize' depending on the number of engines.

  - QUESTION:  Does this mean that if TargetTaskSize is 1, we use tree branch size to allocate branches to tasks instead
               of automatically calculating the size?
    ANSWER: DEV Strongly recommends that the setting of TargetTaskSize remain at the DEFAULT of ZERO (0).

  - How to control the number of engines?
    Determine how many CPUs are on the machine(s) that is (are) running the engine.  As mentioned earlier, the general
    rule is that you should designate 2 engines per each CPU that is available.  So for example, if you are running the
    engine on a machine that has 4 CPU then you can have up to 8 engines designated in the Engine Administrator.  In this
    type of architecture then instead of having one 'localhost' in your Engine Settings Screen, you would have 'localhost'
    repeated eight times in this field.

    Where do I set the number of engines?
        To add multiples computers where engine will run, please do a back-up of Settings.xml file under
        Analytical Engines\bin\ folder, then edit it and add there the selected machines.
        Example, this will allow 3 engines to start:

        - <Entry>
          <Key argument="ComputerNames" />
          <Value type="string" argument="localhost,localhost,localhost" />

Otherwise, if there are no additional engines defined, the calculated value of 'TargetTaskSize' is used.
(Oracle does not recommend changing the default value.)

The TargetTaskSize holds the engines prefered branch size, in number of level 1 combinations.

  - Level 1 combinations, known as group size

The engine manager will use this parameter to attempt creating branches with similar size.
  * The engine manager will not create engines that do not have a branch.

The engine divider algorithm uses the value of 'TargetTaskSize' as a system-preferred branch size to create branches that
are more equal in size which improves engine performance.  The engine divider will try to add as many tasks as possible to
an existing branch, up to the limit of 'TargetTaskSize' level 1 combinations, before adding new branches.

Coming up next:
- The engine divider
- Group size
- Level 1 combinations
- Engine Parameters


Tuesday Jun 26, 2012

Latest EP_LOAD_SALES Performance Improvement for and 12.2

Hello!   Take a look at the latest EP_LOAD_SALES solution.  New for and 12.2, there are 3 additional parameters to control EP_LOAD_SALES.    Click Here

For Proactive Services: Demantra Engine2k Log File Analysis Engine Performance Optimization

Hello!  This is the latest from the Oracle Proactive Services Team.  This presentation will guide you to interpreting the enginewk log file.  Specifically, when you have a performance issue with the engine, is it the database or the engine itself.  

Look for more diagnostics soon!

Proactive Services Community Doc, Avoiding the Unexpected with Proactive Services

Wednesday Jun 06, 2012

Need a Quick Sure Method to Produce a Formatted Explain Plan? This will help!

Please use the following on the production machine to get formatted explain
plan and sql trace using the SLOW sql (e.g. 'T_COMB_LIST.COMB_ID = 216') or
any other value that takes longer:

-- Open new session is SQL*Plus */
-- Make sure you are using updated PLAN_TABLE
-- This can be done by dropping it and recreate it by running:
-- SQL> @?/rdbms/admin/utlxplan.sql)

set lines 1000
set pages 1000
spool xplan_1.txt
<<<<Replace this line with exactly the same query you used above. Force hard
parse by modifying the case of a character>>>>
spool off

--Open a second session is SQL*Plus
ALTER SESSION SET max_dump_file_size = unlimited;
ALTER SESSION SET tracefile_identifier = '10046';
ALTER SESSION SET statistics_level = ALL;
ALTER SESSION SET events '10046 trace name context forever, level 12';
<<<<Replace this line with exactly the same query you used above. Force hard
parse by modifying the case of a character>>>>
select 'verify cursor closed' from dual;
ALTER SYSTEM SET EVENTS '10046 trace name context off';

Make sure spooled file is formatted properly and that the 10046 trace has relevant explain plan in it.  Please Upload both files (10046 trace is generated in udump).

Need instructions to find udump?  

sqlplus "/ as sysdba"
show parameters dump_dest

This will show you bdump, cdump and udump locations.

Thursday May 24, 2012

Announcing: Oracle Demantra Available

We are pleased to announce the availability of Oracle Demantra

Please review the release readme and all 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 as Patch 14076370.


Wednesday May 02, 2012

Do You Want to Improve Your Data Load Performance in 7.3.1.x ?

Oracle has released 14810986 available ay My Oracle Support,

1. This a re-release. The original patch has been replaced.
2. This is not customer-specific. It is a generic EP_LOAD patch that improves performance for 7.3.1.x installations.


Tuesday Mar 06, 2012

Implementing Oracle OATM and Demantra? A Performance For You!

Recommendation for Demantra?

If you are using a striped storage device, be it striped in hardware (like RAID 1+0 or RAID 5) or striped in software over many disks, then there is less of a need to separate the DB objects into different tablespaces as OATM constructs.

When doing an index lookup the DB server reads from the index and then it reads from the data table.  If both the index and the table are on the same physical disk then the DB server will wait between the index read and the data read while the disk head moves and the disk spins around to the right spot.

But if your storage device is an array of many disks then odds are that the index block and the data table block are not on the same physical disk anyway.  The more disks in the array the lower the odds that the DB server will need to wait.

That is why the number of disks is more important than their size.  The more disks the faster the I/O.

Monday Feb 27, 2012

Demantra T_UPDATE Performance Issues? Supply an Enhanced AWR Report Using Logger

When communicating with Support or Development the performance issue you are experiencing with the T_UPDATE object, follow the instructions below to produce an AWR report with logger turned on. This may expedite your solution.

Please do the following:

1. Turn on the Logger Category 'appserver.update.sql' via server admin page.
    For example: http://<server>:8080/demantra/admin and then press the Logger Manager link.

2. Launch the AWR Report. See note 1279115.1, Producing an AWR Report for Demantra Performance Troubleshooting

3. Re-run the problematic scenario.

4. Turn off the Logger Category from step 1.

5. Supply the AWR report for the time period matching the Logger Category switch.

6. Supply the tables whose name starts with "T_UPDATE_". If they are very big then please list the T_UPDATE_  table names and their row counts.


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


« April 2014