Friday Feb 13, 2015

Demantra Table Reorganization, Fragmentation, Null Columns, Primary Key, Editioning, Cluster Factor, PCT Fee, Freelist, Initrans, Automatic Segment Management (ASM), Blocksize....

Hello All!   Why does Demantra require table reordering and null column movement on a, what seems to be, frequent basis?  Your other apps do not require this type of action.  Can this be explained?   I can give you the following.  Let me know if you have questions.  Thank You!   Jeff


Addressing the reason why Demantra requires frequent reordering/reorganization and null column placement, I can explain the reason as follows.  As data is inserted into the Demantra table it will not be in any particular order.  The null columns will be scattered throughout the row.  The main quest is to increase throughput and reduce contention.  Since Demantra is both OLTP and data warehouse the apps DBA must tune accordingly.  Of course EBS and VCP applications have elements of both OLTP and data warehouse but are largely transaction processing at their core.  Applications such as EBS and VCP can typically provide satisfactory response time using typical DBA tuning methods.  Specifically, indexing and proper SQL tuning is plenty to improve and maintain acceptable performance.  However, large tables in data warehouse implementations require unique DBA tuning techniques to reduce IO when accessing the big database tables.  Reducing wait times by eliminating contention is the main goal of table reorganization and the movement of null columns to the end of the row.  The data warehouse requirements of Demantra are no different than other data warehouse applications.

By themselves these are not unique and are implemented in data warehouse applications every day.  For each and every read operation we want to fetch as many relevant rows as possible.  To that end we reorder the large tables according to the PK of each table.  The PK is the main access method to the data according the business requirement.  In the case of Demantra, the PK chosen covers approximately 80% of the data access needed for the main forecasting worksheets.  The rest are addressed using typical DBA tuning methods.  There are new booked, shipped orders inserted into the Demantra world daily, weekly, etc.  When this occurs, the rows naturally loose table/index locality and thus the selectiveness is reduced.  This increases the cluster factor because the number of read operations increases to fetch the desired rows.  The CBO recognizes this and calculates the access method accordingly. 

We want to assist the CBO decision making process as much as possible.  If data was static this effort would be greatly reduced.  Because the Demantra data is dynamic, we need to push the CBO our way to deliver the access plan we desire.  If the table is in Primary Key (PK) order, making your Out of Order Ratio (OOR) low, rows are brought into the SGA in sequential order.  Reducing muli-block reads to process data.  The Cluster Factor (CF) is closely related to OOR.

To review the cluster factor concept, see Demantra Large Database Object Diagnosis and Maintenance Best Practices for Best Performance (Doc ID 1629078.1)

The selectivity of a column value, the db_block_size, the avg_row_len and the cardinality all work together in helping the optimizer to decide whether to use an index versus a full table scan.  If a data column has high selectivity and a low CF, then an index scan is usually the fastest execution method.  Once your large tables are in PK order with a low CF, performance can increase dramatically.  The main goal of table reorganization is to reduce IO when accessing the big database tables.  What it does is two main things:

1. Reorders the table data according to the primary key index.
2. When using the “C” which stands for column reordering, it also pushes columns that have no data, nulls, to the end of the table row. This is good thing for tables having a high number of rows as Oracle works in a 254 columns chunks.

Some of the larger Demantra tables have upwards of 200+ columns.  Since every customer uses the Demantra tool in a different manner a portion of these columns will naturally be left as null.  Pushing the null columns to the end of the row decreases data access waits or contention by streamlining the fetch action.  The end result is a column ordered, less fragmented table that will reduce the IO operations for range operations, for example scan data between dates.  The cluster factor will be lower and thus the CBO will look at our desired access method favorably.

Demantra is sensitive to database scans therefore reviewing and implementing sound database performance techniques is essential.  While the EBS and Value Chain Planning (VCP) RDBMS can provide adequate response with proper statistics, indexes, tuning and patching, Demantra brings processing methods to the table that require tighter controls.  I will highlight some of these areas that require tighter control.

1) Tables that have 300+ columns many of the columns are null.  The columns are in the table to represent the family of Demantra solutions.  For
   example Demand Planning, Advanced Demand Planning, Promotion Planning, S&OP, etc.  When the null columns are pushed to the end of the row, the
   read operation becomes streamlined thus increasing performance.

2) Demantra moves significant amounts of data to and from the disk.  It behooves the implementer/Applications DBA to implement a strategy that reduces wait constraints.  Here is a sample of techniques that require strategic implementation attention:

   - Block size 16k or larger
   - PCTFREE set at 30% to 40%
   - FREELIST increased according to your particular implementation
   - INITRANS increased according to your particular implementation
   - Statistics maintained at a 30% sample or larger
   - Parallel Processing at every possible contention point
   - Redo size appropriate to keep switches at 3-4 per hour
   - No Automatic Segment Management (ASM)
   - No Editioning
   - Partitions implemented
   - Large SGA to accommodate multi-block reads
   - Synchronous IO implemented
   - Careful setting of Demantra CBO parameters

In conclusion, it is true that Demantra requires RDBMS implementation strategies that are unique but not uncommon for OLTP and Warehouse systems.
However, VCP requires a unique trick that is unique to VCP.  Creating statistics on empty MLOG$ objects then locking the statistics to the object.
It has been proven that this increases performance by reducing a bottleneck.

Reference Notes:
- Demantra Out of Order Ratio, Table_reorg Procedure, Partitions and Clustering Factor - Manage Your Demantra Database Objects (Doc ID 1548760.1)
- Demantra DB Health and Performance: Oracle Demantra Database Best Practices - A White Paper / Demantra Performance Clustering Factor Out of Order Ratio TABLE_REORG CHECK_REORG (Doc ID 1499638.1)
- Demantra Performance Clustering Factor Out of Order Ratio TABLE_REORG CHECK_REORG (Doc ID 1594372.1)
- Demantra How to Use TABLE_REORG to Reorder MDP_MATRIX in Primary Key (PK) Order Action Plan Version and Later. See 1085012.1 Prior to (Doc ID 1528966.1)
- Demantra Large Database Object Diagnosis and Maintenance Best Practices for Best Performance (Doc ID 1629078.1)
- Demantra Large Database Object Diagnosis and Maintenance Best Practices for Best Performance (Doc ID 1629078.1)

Tuesday Nov 19, 2013

Oracle Demantra Gathering Statistics on Large Partitioned Tables in 11gR2

Hello!   Have you implemented partitions on a large Demantra table?  Are you using RDBMS version 11gR2?  Then consider implementing incremental statistics.

For all 11gr2 environments with large partitioned or subpartitioned objects turn on incremental statistics using this command:

exec dbms_stats.set_table_prefs('OWNER','TABLE','INCREMENTAL','TRUE');

Once this is set for any given table we should gather statistics on that table using the normal tool (fnd_stats in ebs or dbms_stats elsewhere). This first gather after turning it on will take longer than previous analyzes. Then going forward we will see the following :

1 – the normal dbms_stats (or fnd_stats) will only gather statistics on lower level partitions if the statistics on that partition are stale. This is a significant change. That is going forward using the default options of a gather command may in fact perform no reanalyze on the actual data if the modifications to the table don’t warrant it.

2 – if a subpartition is stale the normal stats will ONLY gather statistics on that subpartition. The partition for that subpartition will be re-derived as will the global statistics – no other statistics will be gathered.

Making this change promises to reduce gather stats by hours in some cases.


Wednesday Oct 23, 2013

Demantra Performance Clustering Factor Out of Order Ratio TABLE_REORG CHECK_REORG (Doc ID 1594372.1)

Hello!   There is a new document available:

Demantra Performance Clustering Factor Out of Order Ratio TABLE_REORG CHECK_REORG (Doc ID 1594372.1)

Demantra Performance Clustering Factor Out of Order Ratio TABLE_REORG CHECK_REORG

The table reorganization can be setup to automatically run in version 

In version 12.2.2 we run the TABLE_REORG.CHECK_REORG function at every appserver restart.
If the function recommends a reorg then we strongly encourage to reorg the database object. 
This is documented in the official docs.

In versions and, the TABLE_REORG module exists and can be used.
It has two main functions that are documented in the Implementation Guide Supplement,
Release 7.3, Part No. E26760-03, chapter 4.

In short, if you are using version or higher, you can check for the need to run a reorg by doing the following 2 steps:

2. Check the table LOG_TABLE_REORG for recommendations

If you are on a version before, you will need to follow the instructions below to determine if you need to do a manual reorg.

How to determine if a table reorg is needed

1. It is strongly encouraged by DEV that You gather statistics on the required table.  The prefered percentage for the gather is 100%.

2. Run the following SQL to evaluate how table reorg might affect Primary Key (PK) based access:

SELECT ui.index_name,trunc((ut.num_rows/ui.clustering_factor)/(ut.num_rows/ut.blocks),2)
FROM user_indexes ui, user_tables ut, user_constraints uc
WHERE ui.table_name=ut.table_name
AND ut.table_name=uc.table_name
AND ui.index_name=uc.index_name
AND ut.table_name=upper('&enter_table_name');

3. Based on the result:




Updates to the Demantra Partial Schema Exporter Tool, Patch 13930627, are Available.

Hello!  Updates to the Demantra Partial Schema Exporter Tool, Patch 13930627, are Available.
This is an updated re-release of the generic Partial Schema Exporter Tool.  The generic patch is for 7.3.1.x and 12.2.x.
TABLE_REORG was introduced in 12.2.0.  Therefore for 7.3.1.x the schema must be at or above.

This is build 3 of the patch.

It contains fixes for the following bugs
  It now only uses DATA_PUMP COMPRESSION only on Enterprise Edition for 11g and and up.

  It now builds GL level filters with and without the GL id column where applicable.

These bugs are also fixed in and 12.2.3.


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


« July 2016