Tuesday, November 9. 2010
Oracle PO Approval Hierarchy Routing ... Posted by Barb Matthews
in EBS 11i at
09:00Comments (0) Trackbacks (0) Oracle PO Approval Hierarchy Routing Issue Resolved by Ron Morrison, UGI
Ron Morrison, from UGI, is this month’s winner of our newsletter article contest. We found Ron’s article interesting for two reasons: first, it shows his problem solving methodology that he uses when he finds an issue with Workflow. Ron worked together with Oracle Support to recommend a solution. Second, Oracle Support actually came up with a one-off bug fix for the problem, and then realized, before implementing it, that there was already a solution in place in another patch. This gives us some insight into Oracle’s problem solving methodology – they have a lot of code they’re working with, so you would expect that once in a while they might “re-fix” a problem because of issues searching for an existing fix.
Recently we encountered several PO Approvals that were routed to an incorrect routing path according to the HR hierarchy supervisor relationship after the 3rd notification approval time-out was encountered. I discovered that the hierarchy routing path was not including an edit restriction to only select supervisor relationships with an ASSIGNMENT_TYPE = ‘E’ when the PO_REQAPPROVAL_FINDAPPRV1.GetMgrHRHier was executed against the PER_ASSIGNMENTS_F table. The logic was retrieving both the active employee ASSIGNMENT_TYPE of ‘E’ and a Benefits ASSIGNMENT_TYPE of ‘B’ for the hierarchy information. The affected employee had worked with one of our subsidiaries and left the subsidiary to work at the main company office. By leaving the subsidiary, the employee was set up with a Benefits end-date, and a benefits profile was established based on his number of years with the subsidiary, and thus generated an ASSIGNMENT_TYPE ‘B’ for the Benefits information. I discovered that the ASSIGNMENT_TYPE ‘B’ information was extracted when I executed the Oracle Diagnostics application under Purchasing for the specific PO that was routed incorrectly under the section for the PER_ASSIGNMENTS_F table information that was extracted. I used Workflow builder to identify the process that was being executed, which identified the source code that was being executed. I submitted a Service Request to Oracle regarding this issue and recommended that an edit be added to the logic in the PO_REQAPPROVAL_FINDAPPRV1.GetMgrHRHier process to include a selection criteria of ASSIGNMENT_TYPE = ‘E’. Oracle agreed that this modification to the source would resolve this issue and assigned BUG 10035329. Oracle provided us with a one off Patch 6616285 to correct this issue. They said to apply the patch to $PO_TOP/patch/115/sql/ Later, Oracle realized that they had already fixed the issue, but that we were on an older version of code. The final solution was to upgrade from version 115.22.11510.2 to version 115.22.11510.3 to get this fix. Since Patch 5451684 containing version 115.22.11510.3 is not generally released, we were asked to apply Patch 7109979. The following screenshots show where and how to identify the problem source code information and the solution offered by Oracle.    When looking at the PER_ASSIGNMENT_F table you should also verify that ASSIGNMENT_TYPE = ‘E’ – not doing so allows the process to select both ASSIGNMENT_TYPE = ‘E’ or ‘B’ for benefits information. This should not be allowed in PO Approval flow. 
Friday, September 17. 2010
What You Need to Know About the July ... Posted by Barb Matthews
in EBS 11i at
05:05Comments (0) Trackbacks (0) What You Need to Know About the July CPU by Barbara Matthews Recently I listened to Integrigy's webcast about the database impact of Oracle's July 2010 CPU (Critical Patch Update). Here are just a few of the things I learned: There are CPUs and PSUs. A PSU is a CPU with extra patches, ones that Oracle thinks would be helpful to you and that are not considered high risk. If you apply a PSU, then you have to continue applying PSUs until you upgrade to a new version of your database. PSUs are well and thoroughly tested, but not necessarily by the E-Business Suite Development Group. So, you might hit a problem with a PSU in your E-Business Suite environment. But Oracle stands by those patches, so if you hit a problem, they'll help you work through it. If you are being a good defender of your database, you should be staying within P-1 as far as the CPUs or PSUs are concerned. That means that at the very least, you should have tested and then applied the April 2010 CPU or PSU to your production environment. And if you are really on the ball, you will already be testing the July 2010 CPU. Now, say you've applied that July 2010 CPU, and now you're getting ready to upgrade your database from RDBMS Version 10.2.0.4 to 11.2.0.1. Since 11gR2 was released PRIOR TO the July 2010 CPU, then when you're done upgrading, you must remember to RE-APPLY the July 2010 CPU. Change control really is a... challenge.  Now, you might wonder just how dangerous it is to be behind on your CPUs or PSUs. The Integrigy speaker was kind enough to show us how to google a snippet of code that could exploit a vulnerability, drop it into Oracle Developer, and then, well, exploit the vulnerability. My mother could take you down if she knew how to cut and paste. You can limit some of your risk from outside attacks by enabling the Managed SQL*NET Access and Server Security features. This feature will limit the people who can attack you to those whom you have given access to. You can still be hacked, so your best line of defense is to test and apply the CPUs as quickly as possible. More details about how to enable these features, and what enabling them entails, are covered in Integrigy’s April 12, 2007 Security Advisory. There are also a number of other recommendations in this document about how to reduce your security risk. I recommend that you put the Integrigy website on your Favorites list, and check them out whenever a new quarterly CPU/PSU becomes available. They do a terrific job of evaluating and explaining what the security patches do, and what your risks are if you choose not to apply them. If you missed out on Integrigy’s webcast, they have more coming up: Oracle Critical Patch Update October 2010 E-Business Suite Impact Webinar Thursday, October 21, 2pm EDT Oracle Critical Patch Update October 2010 Oracle Database Impact Webinar Thursday, October 28, 2pm EDT Thursday, September 16. 2010Two Instances - Getting Unstuck from 11i by Chris Busbee, eprentise
Summary Comparison Between Migration and Consolidation
If you are running Oracle® E-Business Suite (EBS) and have an 11i instance and a R12 instance in production, there are two approaches to get from multiple instances and reach the goal of a single EBS R12 instance in which to run an entire business: · data migration plus sunset instance, and · an upgrade of the 11i instance to R12 followed by eprentise Consolidation. Migration is the way things have been done in the past when it was necessary to combine two instances, and it is characterized as relying on highly skilled technical staff, is labor intensive, and is supported by very general purpose software utilities such as Oracle Data Loader. The end result is usually a compromise between schedule, business, and technical constraints. Migration generally takes a long time and is expensive; it has been the only game in town and follows practices and techniques generally used in the Oracle EBS community. Consolidation is new, characterized as relying on a purpose-specific software product that displaces labor, elapsed time, and technical risk. The end result has no business or IT compromises. We have developed the eprentise Consolidation software to meet Oracle EBS customer needs, reduce the costs of EBS, and improve the business results. Migration A migration approach generally calls for data to be extracted from one instance, transformed by custom scripts, and loaded into a new instance. During a migration companies generally only bring master data, open business transactions, and 1 - 2 years of closed transactional data into the new global instance. As a result, one of the byproducts of the migration approach is a recycled instance that must remain in a "sunset" state for 5 - 10 years, the exact duration being based on external regulations for data retention and internal business operational requirements. Consolidation The consolidation approach involves a software-assisted combination of all data from all current instances, whether your current instances include 11i, R12, or both. If you are running both 11i and R12, this approach calls for Oracle's standard upgrade process to transform any 11i instances into new R12 instances. The next step of the approach relies on eprentise Consolidation software to combine the newly upgraded R12 instance data and setup configurations into the existing R12 instance, resulting in a single, global R12 instance. If you are only running two 11i instances, eprentise consolidation becomes the first stage of the process, followed by a single Oracle upgrade to R12. If you are only running two or more R12instances, an upgrade is eliminated and consolidation becomes the only stage of the process. Timewise, it may be difficult to run an upgrade and the eprentise Consolidation in a single cutover weekend. In that case, the newly upgraded R12 instance will have to go into production and all upstream and downstream interfaces will need to be reviewed and possibly modified to work in the R12 environment. So you don't need to rewrite the interfaces twice (once for the upgrade to R12, and once for after the instances are consolidated), it will be necessary to ensure that the interfaces are re-written to utilize the reference data (such as customer number, invoice number, vendor number, item number, etc.) rather than the system identifiers such as customer_id, invoice_id, vendor_id, and item_id.  Comparative Costs of Migration and Consolidation Approaches The following example spreadsheets calculate the minimum and maximum costs for resources for a traditional migration approach compared to the cost of resources for the eprentise Consolidation approach. Neither example includes resources for creating and testing interfaces, project management, or database administration since those would be similar for both approaches. The minimum is based on the shortest time and the fewest resources in the above diagram, while the maximum is based on the longest duration and the most resources. A standard of 12 modules and a consulting rate of $1,000/day were used in the calculations for all examples. All of the examples utilize a 50-week year and five day work weeks. The total cost of resources for a traditional migration approach ranges from a minimum of $9,000,000 (12 months duration and 3 resources) to a maximum of $27,000,000 (18 months duration and 6 resources).  The major difference between the eprentise Consolidation Approach and the Migration Approach is that since there are no scripts to write, test, and revise, the only required resources are business users who would test the functionality of the resulting consolidated instance and make decisions about what rules to include based on the data requirements. Our experience is that the functional users would only be required an average of quarter-time. The cost table below does not include the license costs for the eprentise Consolidation software. The estimated resource costs range from $255,000 for a 4 month project and a single functional resource per module to $1,020,000 for two resources per module on an 8 month project.  Conclusion eprentise Consolidation provides a more complete solution at a lower cost and in a shorter time than the Traditional Migration Approach. With eprentise, the total project costs are a fraction of those of Migration. All history is converted. There is no need to worry about getting the right technical resources or compromising the data integrity. Having the right tools make the users look like pros. Using eprentise Consolidation software is the right choice for a successful project. 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 |