Thursday Jul 24, 2014

Demantra 12.2.x Focusing on 12.2.3 Install, Upgrade, Operation Bug Fixes and Best Practices

Hello Demantra Customers! 

There is a new r12, focusing on r12.2.3, MOS note that summarizes all known issues as of 24-Jul-2014.  Quickly review the
problems and solutions to help you save time!

See Demantra 12.2.x Focusing on 12.2.3 Install, Upgrade, Operation Bug Fixes and Best Practices.  Known Issue Summary (Doc ID 1910650.1).

Friday Jul 11, 2014

Demantra Index Cluster Factor Major Update Performance Impact

Hello Everyone!   Please check out the following important information regarding the cluster factor and the impact to the CBO.   Please comment if you like.

Regards!   Jeff

 The Cluster Factor (CF) of an index a very important statistic used by the Cost Based Optimizer (CBO) to determin
the most efficient execution plan.  However, there is a major flaw in the current CF calculation.  Simply put, the CF
is calculated by performing a Full Index Scan, looking at the rowid of each index entry.  If the table block being referenced
is different from the previous index entry, the CF is incremented.  However if the table block being referenced is the same
as the previous index entry, the CF is not incremented.  So the CF gives an indication of how well ordered the data in the table is
in relation to the chosen index entries.  

The lower the CF, closer to the number of blocks in the table vs the number of rows, the more efficient it is to use the index as
less table blocks would need to be accessed to retrieve the necessary data via the chosen index.

So far so good.  However, what if part of the incoming row is stored in block one and the remainder in block 2?  When the row is
required, block 1 and block 2 will be read into cache resulting in near zero waiting for the entire row.  OK, fast forward, what if
the table has 100m rows and imagine that this situation occurs 30% of the time.  Your current CF method is most likely skewed
and incorrect.  

Now for the good news, bug 13262857 - INDEX CLUSTERING FACTOR COMPUTATION IS PESSIMISTIC.  The computation of the index clustering
factor in dbms_stats package is pessimistic about the caching ratio of the table blocks.  It assumes that at most one block from the
table is cached.

This is an enhancement to allow a user to specify the number of blocks that dbms_stats package will consider when gathering the index clustering
factor statistics. Prior to this enhancement dbms_stats assumed 1 and it still does after the enhancement.  This enhancement allows the user to
specify a value between 1 and 255.  There is also an AUTO option which if specified then dbms_stats will use 1% of the table blocks up to
0.1% of the buffer cache size, in blocks.  

The new CF approach is currently available with patches that can be applied on both Exadata databases and Oracle versions, and
The Patch ID is 15830250.

Once applied, there is a new statistics collection preference that can be defined, called TABLE_CACHED_BLOCKS.  This basically sets the number of
table blocks we can assume would already be cached when performing an index scan and can be ignored when incrementing the CF during statistics gathering.
The default is 1, as stated, the same as today.  But can be set up to be a value between 1 and 255, meaning that during the collection of index statistics
the process will not increment the CF if the table block being referenced by the current index entry has already been referenced by any of the prior 255 index
entries, if set to 255.  It basically sets the appropriate parameter in the sys_op_countchg function used to calculate the CF value during statistic gathering
to not increment the CF if the current table block has already been accessed “x” index entries previously.

Apply the patch, change the TABLE_CACHED_BLOCKS preference for this table and re-calculate the index statistics:

SQL> exec dbms_stats.set_table_prefs(ownname=>dm, tabname=>'SALES_DATA',

pname=>'TABLE_CACHED_BLOCKS', pvalue=>50);

PL/SQL procedure successfully completed.

SQL> EXEC dbms_stats.gather_index_stats(ownname=>dm, indname=>'SALE_DATA', estimate_percent=> null);

PL/SQL procedure successfully completed.

SQL> SELECT t.table_name, i.index_name, t.blocks, t.num_rows, i.clustering_factor
2  FROM user_tables t, user_indexes i
3  WHERE t.table_name = i.table_name AND i.index_name='SALES_DATA';

Although I found this bug internally, many thanks to several on the WWW for their comments.

Tuesday Jul 08, 2014

Linux Engine Crashes When Receiving Simulation Request After Configuring Calendar Month

Linux Engine Crashes When Receiving Simulation Request After configuring Calendar Month, See MOS Note 1684019.11684019.1

Customer mentions that the engine was running fine until they implemented a change since the last successful engine run: they set up the month calendar.

Now the engine crashes (the Engine.exe process ends and then restarts) when receiving a simulation request.
Nothing is reported as error on the simulation worksheet.  The process status is "started".  A second simulation request will crash the newly started process.

This happens on both modes(batch and simulation run) which cause the Engine process to crash

Checked the Engine Manager web application is started
Checked EngineStarter is running
Started the simulation engine:which Returns -1
Checked Engine process is running: ps -e | grep Engine
Shows EngineStarter and Engine running
Open a worksheet and started simulation for the selected combination
Process monitor shows status "running"
The Engine process crashes, and ps command returns only EngineStarter
Engine restarts on its own (Engine process is seen again when checking with ps)
Engine logs show the engine is frozen (no new logged info once initialized)

When customer changed EnginePlatform to 0 and executed the batch and simulation engine on Windows, the simulation run didn't crash the engine, but no forecast was generated due to errors:
ERROR Failed to execute : ProcFillArrays. ExecuteHook threw Matlab exception: All matrices on a row in the bracketed expression must have the same number of rows.
ERROR Failed to execute : ProcForecast9. ExecuteHook threw Matlab exception: Matrix dimensions must agree.

Demantra Customers - Welcome to Finding E-Business Suite Patches & E-Business Suite Technology Stack Patches

Hello Demantra Customers!   Do you need additional help certifying your tech stack?   Sometimes, I do.  When I do, I start at MOS note

How to Find E-Business Suite & E-Business Suite Technology Stack Patches (Doc ID 1633974.2)

This document is meant to assist an E-Business Suite customer in finding all Patches necessary to maintain a healthy system.  This includes Recommended patches, Technology Stack patches, Performance patches, etc...

Latest for 12.2.3: New DocumentDemantra 12.2.3 - Create Simular Product - Error ORA-30683 ODPM-70420, ORA-06512: in "SYS.DBMS_DEBUG_JDWP" (Doc ID 1905097.1)

Hello!  Here is the latest for Demantra 12.2.3

Demantra 12.2.3 - Create Simular Product - Error ORA-30683 ODPM-70420, ORA-06512: in "SYS.DBMS_DEBUG_JDWP" (Doc ID 1905097.1)

On :  12.2.3 version, When using the Create Similar Product Option of Demantra, it fails and produces the errors

ODPM-70420: Create Similar Item process failed.
This error is on all items loaded in the system and reproducible by following the standard way of working with this functionality.
-- Collaboration.log files shows ORA-30683.
ORA-12541: TNS: no listener.
ORA-06512: in "SYS.DBMS_DEBUG_JDWP", regel 68
ORA-06512: in regel 1
[Additional info: null]
java.sql.SQLException: O

Users cannot use NPI process

See MOS Note 1905097.1 for additional details.   Regards!   Jeff


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


« July 2014 »