Demantra 12.2.5.1 is available Now ...
Hello! Demantra does not have version 12.2.5 available. Demantra provides 12.2.5.1 with a mandatory patch 21959406.
Please see MOS notes 2068456.1 and 2068458.1.
It is best to keep ASCP and Demantra at the same level but not 100% required unless otherwise advised. Regards! Srini
Please see MOS notes 2068456.1 and 2068458.1.
It is best to keep ASCP and Demantra at the same level but not 100% required unless otherwise advised. Regards! Srini
Wednesday Oct 07, 2015
Are you on 12.2.4.1? There is a mandatory patch that requires application.
Hello! If you have upgraded to version 12.2.4.1 there is a mandatory patch:
Oracle Demantra Post Release 12.2.4.1 Mandatory Patch Application - Patch 19945449, patch 1960180.1.
Demantra 12.2.4.1, new required patch 19945449, ARU# 18420277, Demantra patch 12240066 - is now available.
It was discovered that the engine throws a segmentation fault while running CDP consumption profile.
Please see the additional information in the readme Notes:
Please refer to Oracle Support note 453127.1 if more then 1 instance of Demantra is installed on the same machine.
If you are using a WAR file to deploy the application to the web server, recreate and redeploy the WAR file after applying the patch on the centralized machine, in order to ensure the changes made by the patch are propagated to the web server.
It is necessary to clear the Java Plug-in cache on the client machine in order to ensure the changes made by this patch are loaded by the browser.
To clear the Java Plug-in cache click Start -> Control Panel -> Double-click the Java icon in the control panel.
Then Click Settings under Temporary Internet Files section -> Delete Files.
Oracle Demantra Post Release 12.2.4.1 Mandatory Patch Application - Patch 19945449, patch 1960180.1.
Demantra 12.2.4.1, new required patch 19945449, ARU# 18420277, Demantra patch 12240066 - is now available.
It was discovered that the engine throws a segmentation fault while running CDP consumption profile.
Please see the additional information in the readme Notes:
Please refer to Oracle Support note 453127.1 if more then 1 instance of Demantra is installed on the same machine.
If you are using a WAR file to deploy the application to the web server, recreate and redeploy the WAR file after applying the patch on the centralized machine, in order to ensure the changes made by the patch are propagated to the web server.
It is necessary to clear the Java Plug-in cache on the client machine in order to ensure the changes made by this patch are loaded by the browser.
To clear the Java Plug-in cache click Start -> Control Panel -> Double-click the Java icon in the control panel.
Then Click Settings under Temporary Internet Files section -> Delete Files.
Patch 21089579 Avoiding Duplicate Rows During Import Generic Patch for 7.3.1.x and 12.2.x
Hello! A customer brought patch 21089579 to my attention. Please consider this a mandatory patch if you are importing data. This will help you avoid duplicate rows during the import process. If you have this issue after importing data please note that as these are only descriptive values I would expect the data load process to synchronize them on the next data load. The level table descriptions should be updated to match with the staging table after the EP_LOAD_ITEMS and EP_LOAD_LOCATIONS procedures complete.
Please apply this patch if you are on Demantra Version: 7.3.1.x to 12.2.x
Bug 20455336 - AFTER APP UPGRADE SITE_TYPE_CODE AND ACCT_TYPE_CODE COLUMNS HAVE WRONG DEFAULTS
Fixed in 12.2.5.1
* Please note that this patch does not remove any existing "duplicate" rows in ITEMS, LOCATION, MDP_MATRIX or SALES_DATA.v
Please apply this patch if you are on Demantra Version: 7.3.1.x to 12.2.x
Bug 20455336 - AFTER APP UPGRADE SITE_TYPE_CODE AND ACCT_TYPE_CODE COLUMNS HAVE WRONG DEFAULTS
Fixed in 12.2.5.1
* Please note that this patch does not remove any existing "duplicate" rows in ITEMS, LOCATION, MDP_MATRIX or SALES_DATA.v
New Demantra Log File Parser in PERL
Hello! There is a new Demantra Log File Parser available. This is written in PERL. Take a look. As always, comments are welcome.
MOS Note 2053982.1: Demantra Demand Planning Log File Parser
MOS Note 2053982.1: Demantra Demand Planning Log File Parser
How to avoid ORA-06512 and ORA-20000 when Concurrent Statistics Gathering is enabled. New in 12.1 Database, Concurrent Statistics Gathering, Simultaneous for Multiple Tables or Partitions
Oracle Database 12.1 introduces a new feature, Concurrent Statistics Gathering.
Concurrent statistics collection is simply the ability to gather statistics on multiple tables, or table partitions, at the same time. When CONCURRENT statistics gathering is enabled, you can execute each statistics gathering job in parallel. This combination is useful when you need to analyze large tables, partitions, or subpartitions. This is accomplished using a combination of the job scheduler, advanced queuing and resource manager. Concurrent statistics collection can reduce the time it takes to gather statistics, provided the system can accommodate the extra workload.
Functionality wise, it is a way to gather stats on multiple tables, table partitions or subpartitions at the same time.
From a user perspective, the concurrent statistics collection functionality is very simple. You set the CONCURRENT global preference to the required value using the DBMS_STATS package. The RDBMS determines if concurrency is appropriate and if so, the level of concurrency to use.
See MOS Note: 2034376.1, How to avoid ORA-06512 and ORA-20000 when Concurrent Statistics Gathering is enabled. New in 12.1 Database, Concurrent Statistics Gathering, Simultaneous for Multiple Tables or Partitions
Concurrent statistics collection is simply the ability to gather statistics on multiple tables, or table partitions, at the same time. When CONCURRENT statistics gathering is enabled, you can execute each statistics gathering job in parallel. This combination is useful when you need to analyze large tables, partitions, or subpartitions. This is accomplished using a combination of the job scheduler, advanced queuing and resource manager. Concurrent statistics collection can reduce the time it takes to gather statistics, provided the system can accommodate the extra workload.
Functionality wise, it is a way to gather stats on multiple tables, table partitions or subpartitions at the same time.
From a user perspective, the concurrent statistics collection functionality is very simple. You set the CONCURRENT global preference to the required value using the DBMS_STATS package. The RDBMS determines if concurrency is appropriate and if so, the level of concurrency to use.
See MOS Note: 2034376.1, How to avoid ORA-06512 and ORA-20000 when Concurrent Statistics Gathering is enabled. New in 12.1 Database, Concurrent Statistics Gathering, Simultaneous for Multiple Tables or Partitions
Upgrading Demantra 7.3.1.x to 12.2.x can produce duplicate rows. There are two ways to solve this issue.
Upgrading Demantra 7.3.1.x to 12.2.x can produce duplicate rows. There are two ways to solve; apply 21089579 before your upgrade to 12.2.x or wait for 12.2.5.1. The platform upgrade resolves this issue in 12.2.5.1. Apply this patch before your upgrade from 7.3.1.x to 12.2.x. After an application upgrade SITE_TYPE_CODE, SITE_TYPE_DESC, ACCT_TYPE_CODE, ACCT_TYPE_DESC columns do not have a default '0'. Please note that this patch does not remove any existing "duplicate" rows in ITEMS, LOCATION, MDP_MATRIX or SALES_DATA.
Upgrading from Demantra 7.3.1.x to 12.2.x? This can produce duplicate rows. There are two ways to solve. See patch 21089579 and the details in this note. (Doc ID 2025954.1)
Upgrading from Demantra 7.3.1.x to 12.2.x? This can produce duplicate rows. There are two ways to solve. See patch 21089579 and the details in this note. (Doc ID 2025954.1)
Demantra Certification Study Guides, Exam Preparation
Hello All! We have the certification documents available! They are located at
https://community.oracle.com/docs/DOC-916146
There you will find:
There is one more site that will help you drill into specific topics. This site has whitepapers from Demantra DEV and Support Proactive Services that will provide detailed analysis and direction to Demantra functional and technical topics. The MOS note is:
Development and Proactive Services Document Library. CRITICAL Updates, Comprehensive, Impactful White Papers, Guides, Notes +! Oracle Value Chain Planning Suite ASCP GOP Demantra RP APCC SNO IO DSR PS SPP, Note 1669052.1
Additionally, the Demantra Information Center is being updated and integrated into the NEW Value Chain Planning (VCP) Information Center! I will provide details as soon as they are available.
As always, feel free to contact me. Best to you in your efforts! srini
https://community.oracle.com/docs/DOC-916146
There you will find:
- The PDF from May 2015 webcast, Demantra Certification. Are you attempting to get certified? Let's walk through the process!
- Mfg_DEM_Advisor_Webcast_2015_0610.pdf
- Mfg_DEM_Advisor_Webcast_Certification_2015_0610-Part-2.pdf
- Demantra-Certification-Technical.pdf
- This is still in draft however, plenty of technical points that will add to your understanding of Demantra troubleshooting and management
- Demantra-Certification-Topics.pdf
There is one more site that will help you drill into specific topics. This site has whitepapers from Demantra DEV and Support Proactive Services that will provide detailed analysis and direction to Demantra functional and technical topics. The MOS note is:
Development and Proactive Services Document Library. CRITICAL Updates, Comprehensive, Impactful White Papers, Guides, Notes +! Oracle Value Chain Planning Suite ASCP GOP Demantra RP APCC SNO IO DSR PS SPP, Note 1669052.1
Additionally, the Demantra Information Center is being updated and integrated into the NEW Value Chain Planning (VCP) Information Center! I will provide details as soon as they are available.
As always, feel free to contact me. Best to you in your efforts! srini
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.
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
- 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)
- 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)
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
TABLE_REORG Causing ORA-42012: error occurred while completing the redefinition and ORA-00600
Hello! The latest information for you.
Submitting the TABLE_REORG procedure for a table that has a foreign key constraint that refers to the same table, leaves the constraint in a disabled state. However, Foreign key constraints that refer to other tables, behave as expected. i.e., the constraint is re-enabled at the end of the redefinition.
When this condition exists, TABLE_REORG will fail with the following:
ORA-42012: error occurred while completing the redefinition
ORA-00600: internal error code, arguments: [17183], [0x3FFF81BF400], [], [], [], [], [], [], [], [], [], []
It does generate a trace file and dump file.
ERROR at line 1:
ORA-42012: error occurred while completing the redefinition
ORA-00600: internal error code, arguments: [17183], [0x3FFF81BF400], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_REDEFINITION", line 82
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1524
ORA-06512: at line 1
ORA-06512: at "DEMANTRA.TABLE_REORG", line 1193
To resolve this issue, please apply Patch 13867469 if available for your database version and platform. The fix for Bug 13867469 should be included in 12.2 future release.
- See MOS Note <Note 13572659.8> for additional details. Bug 13572659 - DBMS_REDEFINITION disables Foreign Keys used for REFERENCE partitioning
- Also available, patch 20954948 : MERGE REQUEST ON TOP OF 11.2.0.3.0 FOR BUGS 13040943 13572659 13642044
As a workaround, if you did not receive an ORA-00600, re-enable the constraint after the redefinition.
Note: The above workaround is not applicable for a nested table built on a partitioned parent table. Reference: <Note 1929007.1>.
Foreign Constraint On Nested Table is Created With Status Disabled.
Submitting the TABLE_REORG procedure for a table that has a foreign key constraint that refers to the same table, leaves the constraint in a disabled state. However, Foreign key constraints that refer to other tables, behave as expected. i.e., the constraint is re-enabled at the end of the redefinition.
When this condition exists, TABLE_REORG will fail with the following:
ORA-42012: error occurred while completing the redefinition
ORA-00600: internal error code, arguments: [17183], [0x3FFF81BF400], [], [], [], [], [], [], [], [], [], []
It does generate a trace file and dump file.
ERROR at line 1:
ORA-42012: error occurred while completing the redefinition
ORA-00600: internal error code, arguments: [17183], [0x3FFF81BF400], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_REDEFINITION", line 82
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1524
ORA-06512: at line 1
ORA-06512: at "DEMANTRA.TABLE_REORG", line 1193
To resolve this issue, please apply Patch 13867469 if available for your database version and platform. The fix for Bug 13867469 should be included in 12.2 future release.
- See MOS Note <Note 13572659.8> for additional details. Bug 13572659 - DBMS_REDEFINITION disables Foreign Keys used for REFERENCE partitioning
- Also available, patch 20954948 : MERGE REQUEST ON TOP OF 11.2.0.3.0 FOR BUGS 13040943 13572659 13642044
As a workaround, if you did not receive an ORA-00600, re-enable the constraint after the redefinition.
Note: The above workaround is not applicable for a nested table built on a partitioned parent table. Reference: <Note 1929007.1>.
Foreign Constraint On Nested Table is Created With Status Disabled.
Demant TABLE_REORG Procedure Failed ORA- on SALES_DATA MDP_MATRIX PROMOTION_DATA How do I Restart? RUPD$_ MLOG$_
When you submit TABLE_REORG, DBMS_REDEFINITION is used. This will create an MLOG$_ database object. The table you supplied for the TABLE_REORG procedure has a Primary Key (PK). Since there is a PK on the table, when DBMS_DEFINITION creates the MLOG$_ object, DBMS_REDEFINITION automatically creates a RUPD$_ object. These objects are meant to be used for Java RepAPI. If you execute a 'drop snapshot log on tablename' the snapshot log as well as the temporary snapshot log are dropped. However, dropping the MLOG$_ object is not best practice.
If these objects exist prior to executing TABLE_REORG, you will see the following: ‘Table cannot be redefinitioned.' in log_table_reorg table. This means that one or both objects/segments exist. For example, if I had a table_reorg for mdp_matrix fail, the following segments would most likely be left behind:
RUPD$_MDP_MATRIX
MLOG$_MDP_MATRIX
Use the following SQL to verify (10g and above):
select substr(object_name,1,30)
from dba_objects
where regexp_like( object_name, 'MLOG$|RUPD$')
and owner = '&Schema_owner'
While you can drop these temporary RDBMS segments, it is best practice to use the following:
BEGIN
DBMS_REDEFINITION.ABORT_REDEF_TABLE (
uname => '&demantra_schema_name',
orig_table => '&original_table_name',
int_table => '&interim_table_name');
END;
In the above, supply the arguments:
- DM or your schema name
- MDP_MATRIX is the original_table_name
- MLOG$_MDP_MATRIX is the interim_table_name
Verify that the objects are dropped. Submit the TABLE_REORG again AFTER repairing the cause of the last failure.
If these objects exist prior to executing TABLE_REORG, you will see the following: ‘Table cannot be redefinitioned.' in log_table_reorg table. This means that one or both objects/segments exist. For example, if I had a table_reorg for mdp_matrix fail, the following segments would most likely be left behind:
RUPD$_MDP_MATRIX
MLOG$_MDP_MATRIX
Use the following SQL to verify (10g and above):
select substr(object_name,1,30)
from dba_objects
where regexp_like( object_name, 'MLOG$|RUPD$')
and owner = '&Schema_owner'
While you can drop these temporary RDBMS segments, it is best practice to use the following:
BEGIN
DBMS_REDEFINITION.ABORT_REDEF_TABLE (
uname => '&demantra_schema_name',
orig_table => '&original_table_name',
int_table => '&interim_table_name');
END;
In the above, supply the arguments:
- DM or your schema name
- MDP_MATRIX is the original_table_name
- MLOG$_MDP_MATRIX is the interim_table_name
Verify that the objects are dropped. Submit the TABLE_REORG again AFTER repairing the cause of the last failure.
Did you know that TABLE_REORG has replace REBUILD_SCHEMA mad REBUILD_TABLES?
Demantra TABLE_REORG procedure.
Did you know that TABLE_REORG has replace REBUILD_SCHEMA mad REBUILD_TABLES? TABLE_REORG
can addressed partitioned tables and is more efficient! Get the latest release at My Oracle Support using bug 17640575.
TABLE_REORG has really replaced and improved the functionality of REBUILD_SCHEMA and REBUILD_TABLES.
It rebuilds the table which is done in primary key order and it moves empty columns to the end of the row.
REBUILD_SCHEMA uses ALTER TABLE MOVE tablespace to reduce chained rows of all tables in the schema.
However, it does not support partitioned tables.
REBUILD_TABLES is the similar. It was originally designed for MDP_MATRIX / SALES_DATA, but it can run for all tables and
also for a specific table. From 2010 it does support partitioned tables.
The procedure MOVE_TABLE was fixed to handle partitioned tables. It is also out of date, I see ANALYZE TABLE has used parts of the
code (eg for SALES_DATA and MDP_MATRIX). For an "all tables run" is uses ANALYZE_SCHEMA that does use dbms_stats.GATHER_TABLE_STATS
All tables - Where the stats value chain_cnt > 0, it does not automatically include SALES_DATA unless 'sys_params','Rebuild_Sales_Table' = 1.
REBUILD_TABLES ( table namel, stats check, sales, all tables flag)
exec REBUILD_TABLES ( null, 1, null, 1) ; -- With ANALYZE_SCHEMA(100000) = for none or really old stats
exec REBUILD_TABLES ( null, 0, null, 1) ;
exec REBUILD_TABLES ( null, 0, 1, 1) ; -- Will include SALES_DATA
For more information see: Troubleshooting TABLE_REORG Package issues - RDF Snapshot drop when process fails + TABLE_REORG Guide MOS Note 1964291.1
Gathering statistics on partitioned tables. Best practice:
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, 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 re-analyze on the actual data if the modifications to the table do not 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.
For more information: Demantra Gathering Statistics on Partitioned Objects Oracle RDBMS 11gR2, MOS Note 1601596.1
Did you know that TABLE_REORG has replace REBUILD_SCHEMA mad REBUILD_TABLES? TABLE_REORG
can addressed partitioned tables and is more efficient! Get the latest release at My Oracle Support using bug 17640575.
TABLE_REORG has really replaced and improved the functionality of REBUILD_SCHEMA and REBUILD_TABLES.
It rebuilds the table which is done in primary key order and it moves empty columns to the end of the row.
REBUILD_SCHEMA uses ALTER TABLE MOVE tablespace to reduce chained rows of all tables in the schema.
However, it does not support partitioned tables.
REBUILD_TABLES is the similar. It was originally designed for MDP_MATRIX / SALES_DATA, but it can run for all tables and
also for a specific table. From 2010 it does support partitioned tables.
The procedure MOVE_TABLE was fixed to handle partitioned tables. It is also out of date, I see ANALYZE TABLE has used parts of the
code (eg for SALES_DATA and MDP_MATRIX). For an "all tables run" is uses ANALYZE_SCHEMA that does use dbms_stats.GATHER_TABLE_STATS
All tables - Where the stats value chain_cnt > 0, it does not automatically include SALES_DATA unless 'sys_params','Rebuild_Sales_Table' = 1.
REBUILD_TABLES ( table namel, stats check, sales, all tables flag)
exec REBUILD_TABLES ( null, 1, null, 1) ; -- With ANALYZE_SCHEMA(100000) = for none or really old stats
exec REBUILD_TABLES ( null, 0, null, 1) ;
exec REBUILD_TABLES ( null, 0, 1, 1) ; -- Will include SALES_DATA
For more information see: Troubleshooting TABLE_REORG Package issues - RDF Snapshot drop when process fails + TABLE_REORG Guide MOS Note 1964291.1
Gathering statistics on partitioned tables. Best practice:
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, 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 re-analyze on the actual data if the modifications to the table do not 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.
For more information: Demantra Gathering Statistics on Partitioned Objects Oracle RDBMS 11gR2, MOS Note 1601596.1
The table_reorg procedure ran for SALES_DATA errored due to lack of tablespace. Can I delete the RDF segments?
Yes, you may delete these temporary rdbms_redefinition tables. They are snapshots of the table to be reorganized. The redefinition requires creating a
snapshot. While you can use SQL to release the RDF segments, it is advised that you call the dbms_redefinition.abort_redef_table to release the snapshot. Then re-start the procedure. The dbms_redefinition.abort_redef_table procedure requires three parameters: schema, original table name, interim table name.
Use the following or SQL
BEGIN
DBMS_REDEFINITION.ABORT_REDEF_TABLE (
uname => '&demantra_schema_name',
orig_table => '&original_table_name',
int_table => '&interim_table_name');
END;
/
snapshot. While you can use SQL to release the RDF segments, it is advised that you call the dbms_redefinition.abort_redef_table to release the snapshot. Then re-start the procedure. The dbms_redefinition.abort_redef_table procedure requires three parameters: schema, original table name, interim table name.
Use the following or SQL
BEGIN
DBMS_REDEFINITION.ABORT_REDEF_TABLE (
uname => '&demantra_schema_name',
orig_table => '&original_table_name',
int_table => '&interim_table_name');
END;
/
Demantra Installing 12.2.4.1 as SYS or SYSTEM Installer Fails or is Failing
We are aware of a bug that might raise an error during the installation of the 12.2.4.1 Generic Patch recently released.
In some cases, when using SYS as the DBA user and “Enable Automatic Table Maintenance” checkbox has been checked, sys_grants.sql script which is being
run as part of the installation may fail.
sys_grants.sql is using an internal function in with a specific combination of hardware/platform may generate invalid value.
Run the installer using SYSTEM and not SYS, then, after the installation completes, replace sys_grants.sql with the attached file and run it as sys.
Make sure to add “TRUE” as the 4th parameter.
For example:
@sys_grants.sql DEMANTRA ACL_DEFAULT ACL_DEFAULT TRUE
This has been addressed in release 12.2.5.1.
Also:
Uptake of VCP 12.2.4.1 is mandatory for all VCP and Demantra 12.2.4 installations.
Are you already at 12.2.4.1?
Demantra 12.2.4.1 has a new required patch 19945449, ARU# 18420277, Demantra patch 12240066 - is now available. It was discovered that the engine throws a segmentation fault while running CDP consumption profile. Please see the additional information in the readme Notes.
Reference Notes:
Oracle Demantra Post Release 12.2.4.1 Mandatory Patch Application - Patch 19945449 (Doc ID 1960180.1)
Demantra 12.2.4.1 Cumulative Patch. This CU is Specifically for r12.2.4.1. There are 13 Issues Patched and or Improved. (Doc ID 1952805.1)
Oracle Demantra Release Notes for Release 12.2.4.1 (Doc ID 1947062.1)
Demantra Release 12.2.4.1 Known Upgrade and Known Product Issues with Workarounds (Doc ID 1948769.1)
Oracle Demantra Demand Planning Announcing New Release 12.2.4.1 is Now Available (Doc ID 1948684.1)
Troubleshooting Demantra 12.2.4.1 Installation (Doc ID 1986634.1)
In some cases, when using SYS as the DBA user and “Enable Automatic Table Maintenance” checkbox has been checked, sys_grants.sql script which is being
run as part of the installation may fail.
sys_grants.sql is using an internal function in with a specific combination of hardware/platform may generate invalid value.
Run the installer using SYSTEM and not SYS, then, after the installation completes, replace sys_grants.sql with the attached file and run it as sys.
Make sure to add “TRUE” as the 4th parameter.
For example:
@sys_grants.sql DEMANTRA ACL_DEFAULT ACL_DEFAULT TRUE
This has been addressed in release 12.2.5.1.
Also:
Uptake of VCP 12.2.4.1 is mandatory for all VCP and Demantra 12.2.4 installations.
Are you already at 12.2.4.1?
Demantra 12.2.4.1 has a new required patch 19945449, ARU# 18420277, Demantra patch 12240066 - is now available. It was discovered that the engine throws a segmentation fault while running CDP consumption profile. Please see the additional information in the readme Notes.
Reference Notes:
Oracle Demantra Post Release 12.2.4.1 Mandatory Patch Application - Patch 19945449 (Doc ID 1960180.1)
Demantra 12.2.4.1 Cumulative Patch. This CU is Specifically for r12.2.4.1. There are 13 Issues Patched and or Improved. (Doc ID 1952805.1)
Oracle Demantra Release Notes for Release 12.2.4.1 (Doc ID 1947062.1)
Demantra Release 12.2.4.1 Known Upgrade and Known Product Issues with Workarounds (Doc ID 1948769.1)
Oracle Demantra Demand Planning Announcing New Release 12.2.4.1 is Now Available (Doc ID 1948684.1)
Troubleshooting Demantra 12.2.4.1 Installation (Doc ID 1986634.1)
Hello! Please run the following for both mdp_matrix and sales_data.
1. The first SQL will need to be adjusted to accommodate your PK for sales_data and your PK for mdp_matrix. Adjust the select and from clause to match your PK. In the case of this sample, the PK was:
ITEM_ID
LOCATION_ID
SALES_DATE
IS_T_EP_SPF
-- ShowOOR.sql
SELECT (ROUND((
(SELECT COUNT(*) AS CNT
FROM
(SELECT ITEM_ID,
LOCATION_ID,
SALES_DATE,
IS_T_EP_SPF ,
RELATIVE_FNO ,
BLOCK_NUMBER ,
ROW_NUMBER ,
DATA_ROW ,
(LAG(DATA_ROW) OVER(PARTITION BY RELATIVE_FNO, BLOCK_NUMBER ORDER BY ROW_NUMBER)) AS PREV_DATA_ROW
FROM
(SELECT ITEM_ID,
LOCATION_ID,
SALES_DATE,
IS_T_EP_SPF ,
RELATIVE_FNO ,
BLOCK_NUMBER ,
ROW_NUMBER ,
(DENSE_RANK() OVER(PARTITION BY RELATIVE_FNO, BLOCK_NUMBER ORDER BY ITEM_ID,LOCATION_ID,SALES_DATE,IS_T_EP_SPF)) AS DATA_ROW
FROM
(SELECT ITEM_ID,
LOCATION_ID,
SALES_DATE,
IS_T_EP_SPF ,
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) RELATIVE_FNO ,
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS BLOCK_NUMBER ,
DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) AS ROW_NUMBER
FROM SALES_DATA
) C
) B
) A
WHERE DATA_ROW != PREV_DATA_ROW
AND DATA_ROW != PREV_DATA_ROW + 1
) /
(SELECT COUNT(*) FROM SALES_DATA
)),3)*100) AS "Out Of Order Ratio %"
FROM DUAL;
-- ShowCF.sql
undefine table_name
SELECT ui.index_name, us.blocks as "Table Blocks", ui.clustering_factor as "Index clustering Factor", ut.num_rows as "Table Rows"
FROM user_indexes ui,
user_tables ut,
user_constraints uc,
user_segments us
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 = us.segment_name
AND us.segment_type = 'TABLE'
AND uc.constraint_type = 'P'
AND ut.table_name = '&table_name';
1. The first SQL will need to be adjusted to accommodate your PK for sales_data and your PK for mdp_matrix. Adjust the select and from clause to match your PK. In the case of this sample, the PK was:
ITEM_ID
LOCATION_ID
SALES_DATE
IS_T_EP_SPF
-- ShowOOR.sql
SELECT (ROUND((
(SELECT COUNT(*) AS CNT
FROM
(SELECT ITEM_ID,
LOCATION_ID,
SALES_DATE,
IS_T_EP_SPF ,
RELATIVE_FNO ,
BLOCK_NUMBER ,
ROW_NUMBER ,
DATA_ROW ,
(LAG(DATA_ROW) OVER(PARTITION BY RELATIVE_FNO, BLOCK_NUMBER ORDER BY ROW_NUMBER)) AS PREV_DATA_ROW
FROM
(SELECT ITEM_ID,
LOCATION_ID,
SALES_DATE,
IS_T_EP_SPF ,
RELATIVE_FNO ,
BLOCK_NUMBER ,
ROW_NUMBER ,
(DENSE_RANK() OVER(PARTITION BY RELATIVE_FNO, BLOCK_NUMBER ORDER BY ITEM_ID,LOCATION_ID,SALES_DATE,IS_T_EP_SPF)) AS DATA_ROW
FROM
(SELECT ITEM_ID,
LOCATION_ID,
SALES_DATE,
IS_T_EP_SPF ,
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) RELATIVE_FNO ,
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS BLOCK_NUMBER ,
DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) AS ROW_NUMBER
FROM SALES_DATA
) C
) B
) A
WHERE DATA_ROW != PREV_DATA_ROW
AND DATA_ROW != PREV_DATA_ROW + 1
) /
(SELECT COUNT(*) FROM SALES_DATA
)),3)*100) AS "Out Of Order Ratio %"
FROM DUAL;
-- ShowCF.sql
undefine table_name
SELECT ui.index_name, us.blocks as "Table Blocks", ui.clustering_factor as "Index clustering Factor", ut.num_rows as "Table Rows"
FROM user_indexes ui,
user_tables ut,
user_constraints uc,
user_segments us
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 = us.segment_name
AND us.segment_type = 'TABLE'
AND uc.constraint_type = 'P'
AND ut.table_name = '&table_name';
No comments:
Post a Comment
No one has ever become poor by giving