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

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 

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?

COUNT(*) PREDICTION_STATUS DO_FORE DO_AGGRI AGGRI_98 AGGRI_99 LEVEL_ID
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 Sep 06, 2012

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.

Tuesday Jun 26, 2012

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 May 02, 2012

Demantra Engine Common Run Time Errors

Hello!  These errors are common.  Please take a minute to review.  Regards!

* DP shapes are defined but seem to have no effect

1. Verify that the DP shape causals were defined on the relevant BATCH (or parent BATCH profile in case of simulation) and not only on profile 0. Profile 0 is only used as template when creating new profiles.
This is common for customers that upgraded from versions that did not support profile IDs.


2. Check if the "CONSTANT" causal is missing in the CAUSAL_FACTORS table. Run the next query (modify the profile id value if needed):
select * from causal_factors where engine_profiles_id=1
If no row has 'CONSTANT' value in the factor_name column, it should be added.

* Engine run crash after first branch have finished when using many engines in parralel

When not all engines were started yet, due to the 10 seconds delay between each and first branch already finished.  Engine Manager will crash and stop the run.  Workaround: decrease number of branch multiplier so branches will be larger or decrease number of engines in parralel if multiplier is already 1.

* ERROR alter table PDE_6 add constraint PDE_6_PK primary key (item_id,location_id,sales_date,promo_id) ERROR - Error Description?: ORA-02437: cannot validate (SARALEE_APR11.PDE_6_PK) - primary key violated


1. During the work on the ptp tuning project, I've come up against following error:


creating Promotions Data Engine? table
ERROR


ERROR DB_Connection::Execute threw an exception:
ERROR alter table PDE_6 add constraint PDE_6_PK primary key (item_id,location_id,sales_date,promo_id)
ERROR - Error Description?: ORA-02437: cannot validate (YOURCOMPANY_APR11.PDE_6_PK) - primary key violated


ERROR - Error Number?: 0x80004005 - Unspecified error


ERROR - Native Error Number?: 2437
ERROR


ERROR Execute SQL? failed: alter table PDE_6 add constraint PDE_6_PK primary key (item_id,location_id,sales_date,promo_id)
WARNING Failed to create indices on PDE table, possibly due to duplicate/NULL values, please verify related expressions in M3_CAUSAL_FACTORS and 'Init Params.Shift Dyn Promo Date?'.


The source of this issue was found in group by expression of create PDE Sql. That expression included promotion_type_id. Customer schema had same promotion on same combination at same date with different promotion types. So SQL inserted two rows to PDE_X table with same combination/promotion_id/date. After that it tried to add constraint of primary key item/location/promotion_id/sales_date and failed because of duplicate rows with same primary key.


In general it is a configuration error of customer that inserted different promotion types with same promotion_id. But engine should know to handle it or to produce some meaningful error.


As a workaround in one of the bugs DEV proposed the following : Configure Analytical Level Type? parameter in Init_Params_0 table, set value_string to be IS_SELF instead of PROMOTION_TYPE_ID.


2. During the work on patch. Price causal treatment was modified. Currently engine should use its default expression in case this causal is active.
Some customers schemas have a unique expression for price causal and engine has a problem to parse it in a right way that cause to engine errors from sql.

Example:
ERROR


ERROR DB_Command::Execute threw an exception: 
ERROR SELECT sales_date datett, round(NVL 
(sum(greatest(nvl(nvl(pseudo_sale,actual_quantity),0)*(1 + 
nvl(demand_fact,0)),0)),sum(FORE_3)),6) sales, ...................... 
ERROR - Error Description?: ORA-00909: invalid number of arguments


Bug 12604957 was opened and resolved in 7.3.1.1.  In case you cannot apply the fix, use workaround and set price expression to a default one.


About

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

Search

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