Tuesday, July 27. 2010
Demantra Configuration and Tuning ... Posted by Barb Matthews
in Demantra at
07:32Comments (0) Trackbacks (0) Demantra Configuration and Tuning Case Study by Mike Swing, TruTek
TruTek recently participated in a tuning exercise for one of our Demantra customers. This paper describes some of our lessons learned.
What is Demantra? Demantra provides demand management, sales and operations planning, and trade promotions management solutions. Demantra provides powerful analytics for more accurate demand-driven planning, forecasting and modeling. You can read more about Demantra performance at “Oracle Demantra Performance, An Oracle White Paper, September 2007”. This paper describes recommended design considerations, appserver properties, database parameters, and client server attributes for Demantra Worksheets, which are the primary user interface for the Demantra tools. The bottom line is that there are many areas where tuning can improve the overall performance of Demantra worksheets. Our efforts focused on tuning the operating system and database. The Hardware Configuration Our client recently implemented new AIX hardware with 64 threads, 128 GB RAM, 1.6 TB of SSD and 2 TB of 15K Raid 10. In general, when compared to the initial configuration on 6/21/2010, we were able to improve the performance of worksheets from about 300% to 1000% on 7/2/2010. The following table illustrates the worksheet runtimes before and after performance tuning.  The Operating System (AIX) Configuration Concurrent I/O Our testing concluded that using Concurrent I/O would improve performance. Concurrent I/O was first introduced in AIX Version 5.2. This feature invokes direct I/O, so it has all the other performance considerations associated with direct I/O. With standard direct I/O, inodes (data structures associated with a file) are locked to prevent a condition where multiple threads might try to change the consults of a file simultaneously. Concurrent I/O bypasses the inode lock, which allows multiple threads to read and write data concurrently to the same file. This is due to the way in which JFS2 is implemented with a write-exclusive inode lock, allowing multiple users to read the same file simultaneously. As you can imagine, direct I/O can cause major problems with databases that continuously read from the same file. Concurrent I/O solves this problem, which is why it is known as a feature that is used primarily for relational databases. Similar to direct I/O, you can implement this either through an open system call or by mounting the file system, as follows: # mount -o cio /u When you mount the file system with this command, all its files use concurrent I/O. Even more so than using direct I/O, concurrent I/O provides almost all the advantages of using raw logical volumes, while still keeping the ease of administration available with file systems. Note that you cannot use concurrent I/O with JFS (only JFS2). Further, applications that might benefit from having a file system read ahead or that have high buffer cache hit rates might actually see performance degradation. Performance Improvement – Oracle won’t work without this setting: AIXTHREAD_SCOPE=S AIX has a setting that allows process PIDs to change. In order for Oracle to work correctly, the PIDs need to be static. set AIXTHREAD_SCOPE=S in the environment: export AIXTHREAD_SCOPE=S Demantra I/O Requirements Demantra installations for medium to large companies will need to have I/O subsystems that can accommodate 150K - 300K I/Os per second (IOPS). Low-end solid state drives (SSD) have IOPS of about 120K. High-end SSDs have IOPS over 500K. With 15K hard disks, the IOPS is about 200. Achieving 150K IOPS with JBOD would require 750 drives. Database Configuration and Performance Tuning 1. Solid State Disks (SSD) improve I/O performance by 1000 times. For moved objects that require fast access to the SSD, two SSD mount points would be best: o SALES_DATA, MDP_MATRIX tables, partitions, local indexes and indexes o redo logs, TEMP tablespace, undo tablespace Reading and writing from SSD is about 1000 times faster than comparable operations on JBOD. In combination with 128 GB of RAM, the use of SSD has minimized the physical read wait times and the IOPS is roughly 1000 times greater than the previous system. Coupled with 64 processor threads the SSD I/O system is more than 1000 times faster than the old I/O system. 2. Running the worksheets with multiple CPUs in parallel improved performance by 8 times. We decided to evaluate the effect of varying degrees of parallelism. In our example, we have 64 processor threads available. We found that the degree of parallelism for tables could be high, but that indexes generally should have a degree of 1, to avoid issues with the CBO. However, by setting the SALES_DATA to a higher degree we were able to increase the CPU utilization of the worksheet. For example, if the SALES_DATA table is set to a parallel degree of 8, 8 processes will execute the parallel query. The following diagram shows the same Capacity Planning worksheet run twice: once for 2:00 minutes with 8 CPUs and the second run for 15:45 minutes with 1 CPU.  The CAPACITY_PLANNING worksheet uses the parallel index SALES_DATA_144_IDXN. The following example shows how increasing the parallelism of the worksheet decreases the runtime of the worksheet, almost linearly.  The associated worksheet runtimes to this graph are as follows:  Note that as the number of processors doubles, the time should be reduced by half. For example, with a parallel degree of 8, 8 CPUs are used to process the worksheet and the runtime is 1:55. By doubling the CPUs running a worksheet, we might expect the runtime to be about 1:00 minute. However, the runtime with 16 processors is 1:20, meaning this is a non-linear process with some loss due to ‘friction’ and approximately 63% efficient. 3. Partitioning improved performance by up to 8 times (degree of parallelism) for most worksheets Partitioning allows for performance improvements by reducing the physical reads by using partition pruning for SELECT statements and allows for parallel updates, when the updates fall in different partitions and parallel execution is enabled. Evaluate the effect of varying the number of partitions. Partition – different partitioning options were evaluated SALES_DATA – change partitions to be based on week. This could be a finer resolution, maybe “day”, but usually data is loaded weekly , so the effective resolution is weekly. MDP_MATRIX – change the number of partitions of locations to be 1500, about 200 locations per partition. Initial testing with 30,000 location partitions proved to be unusable. 4. Enable Parallel DML for Load Programs: alter session enable parallel_dml; For tables with partitions and loads that cross partitions with parallel dml enabled, this will cause normal update statements to work in parallel. 5. We changed multiblock_read_count from 8 to 32. This resulted in 4 times faster I/O during full object scans. Overall worksheet performance improved an average of 20% – 25%. 6. We researched the most efficient memory utilization – after much testing, 65% SGA and 35% PGA, with 128 GB of available RAM turned out to be the best. By effectively tuning the database memory usage, we were able to reduce physical I/Os by allowing the memory to cache both data blocks in the buffer cache, and code in the PGA. This allowed for a performance improvement of about 10% to 20%, depending on how this was initially set. 7. Reorganizing tables resulted in about a 5% improvement when the SALES_DATA table was 28% out of order: SALES_DATA 28% out of order Capacity worksheet 2:05 SALES_DATA 0% out of order Capacity worksheet 1:59 8. We changed the block size from 8K to 32K – reducing chaining can result in fewer physical reads and could improve performance by a factor of 2 or more if every row was chained. However, in our case, about 5% of the rows were chained in the worst table. While we have reduced future chaining, the performance improvement was small. 9. We evaluated the performance of the Default Buffer Pool, Keep Buffer Pool and Recycle Buffer Pool for use with the Solid State disks. Our recommendation – use the Default BUFFER_POOL Best Settings for Performance  Maintenance We determined the best way to rebuild the SALES_DATA table, using sales_date, item_id and location_id in the ORDER BY of the INSERT. The code was generated by TOAD and manually updated with the ORDER BY on sales_date, item_id and location_id. For help implementing this solution or other configuration or tuning tasks, please contact Sherri Dudley, sherri@trutek.com or Barb Matthews, barb@trutek.com. Tuesday, July 27. 2010eprentise Case Study: Automotive Parts Supplier Consolidates Regional Production Instances of Oracle E-Business Suite by Chris Busbee, eprentise The automotive industry aftermarket is worth more than $250 billion and is growing annually. With a booming market such as this, it is no surprise that many distributors of aftermarket automotive products have expanded their operations in order to reach additional markets and become a truly global operation. Global expansion generally requires additional investments in IT to support the add-on operations. As experienced by one aftermarket auto parts supplier, multiple enterprise resource planning (ERP) environments can be difficult to manage due to the shared aspects of the different systems as well as the disparate aspects between them. The company is headquartered in the US but has extended its network to Canada, an initiative that required additional IT support in the form of an additional instance of its Oracle E-Business Suite (EBS) ERP system, as that provided the shortest time to a go-live. Both the US and Canadian enterprise-class data centers operated a production instance, a test instance, a development instance, and a project instance, in addition to the backup and DR instances. The deliberate over-allocation of hardware resources to accommodate the sporadic peaks in CPU and RAM usage coupled with the fact that each EBS instance resided on a separate server exacerbated the cost of maintaining multiple instances. IT had waved the flag for many years on the benefits of consolidating (reduce hardware, reduce maintenance / support across all areas, eliminate duplicate effort around upgrades, enhancements, apply patches, etc.) After years of operating with two disparate instances, the company decided to find a solution to the problems of multiple systems maintenance, high infrastructure costs, tedious consolidated financial reporting, and inconsistent business processes that were hindering the company from operating as a truly global business with consistent data and a single source of truth. Using eprentise Consolidation Software for E-Business Suite, the company consolidated its US and Canadian instances into a single, global instance that is enabling them to reduce infrastructure costs, to reduce IT personnel previously required to support multiple systems, to streamline business processes and leverage suppliers, as well as to grow with agility and embrace new initiatives. The two main areas of the business involved with the planning and decision making for the consolidation project simultaneously developed their own business cases for going forward: Finance / HR: Facilitate efficient and effective decision making with timely and reliable fact based information Obtain a single version of the truth / single system of record Access real-time information at consolidated level Create Centralized Shared Services (maintenance, setup, centralized processing) IT: Reduce expenditure on hardware and IT personnel (fewer servers, fewer databases, less storage) Maintain consistent support by eliminating multiple instances Eliminate duplicate integrations and interfaces Reduce testing during patches and upgrade ________ IT wanted to consolidate because of the reduced hardware footprint, reduced maintenance, and reduced support across all areas, but it wasn’t until the stakeholders saw a direct business benefit that we finally received approval. ________ Clearly both Finance and IT saw reasons that consolidating instances would be beneficial to the business. Regional production instances were simply an obstacle to business improvements and the ability to grow. Additionally, the sales from Canadian operations accounted for only 10% of the company’s total revenue, but the level of IT support required to maintain the Canadian instance was virtually equivalent to that required to maintain the US instance. It made very little sense from a business process perspective for 10% of the revenue to be burdened by the cost structure of an entire EBS instance. The auto parts supplier considered three options for performing the instance consolidation, including keeping separate instances, before deciding to move forward with eprentise Consolidation Software. The table below indicates the pros, cons, and benefits of each.  Project Planning and Roadmap After the decision was made to use eprentise Consolidation software to get to a single, global instance, the planning for the project began with the following considerations: RICE Activities Data warehouse integration Reporting requirements OFA – Oracle Financial Analyzer impact Interfaces/Integration to and from third party systems Tax requirements New Functionality in R12 Upgrade to R12 Because the company decided on a software solution, the personnel resources required were minimal and limited to the following: Project sponsor Project management DBA Testing team Technical resources for interfaces, customizations, reports, integration of non E-Business Suite modules Standard EBS support and maintenance (clones, backups, access, security) The consolidation project was done in three stages. The first stage started involved running eprentise Metadata Analysis to discover the scope of the project and identify the rules that needed to be created by eprentise Consolidation software. The second stage of the project moved all of the data, including history, from the Canadian instance into the US instance. The third phase of the project focused on standardizing all configurations, resolving duplicates, and restructuring operating units and inventory orgs to allow the business to leverage suppliers, execute common business processes, and do business as a truly global enterprise. The graphic below represents the project roadmap and timeline, including an upgrade to R12 at a future time.  Results By using eprentise Software to consolidate two regional instances to a single, global instance, the company saved millions of dollars and months of project time compared to a similar company’s alternative approach of hiring consultants to do a manual data migration. Without using eprentise software, the other company manually migrating their instances into a single instance used ~ 250 consultants, spent approximately $12 - $15 million, and took 18 months just to migrate the data, only bringing over balances and limited history. Thursday, June 24. 2010Thursday, June 17. 2010 |