>What is an Oracle Database Tune-up / Health-check?

Over time the performance profile of any Oracle database changes due to increased data volume, changes in the application profile, the addition of new users or custom applications, and changes in business processes. Very often a small set of applications and their associated SQL queries account for a disproportionate amount of database server resource utilization. The DBA, CNC, or Systems Administrator may see it, but unless the performance of the application is adversely impacted this type of performance degradation is simply a quiet killer... slowly, but most assuredly, it eats away at available capacity.

This slow but steady performance degradation is especially true for an Oracle database that services the JDE application, but there is likely a fate far worse than wasted capacity that lurks in the shadows of your server room. From subtle optimizer nuances due to optimizer "bind peeking" to abusive full-table SQL queries that seem to appear out of thin air, the performance of an Oracle JDE database can go from acceptable to dreadful in a heartbeat. Queries, UBEs, and interfaces that typically run in seconds may begin to run in minutes, hours, or even days. The Business Analysts may not know why, the JDE Developers might not be able to explain it, and from the traditional DBA perspective the database will "look just fine." But the database is not fine at all. Not by a long shot. Just ask your Finance Department at month-end.

Whether you feel your JDE Oracle database is healthy, suffers from a bit of wasted capacity, or has reached a point where poor performance is costing your company money, we can help you proactively tune, or quickly optimize, your Oracle database performance for JDE. In a very short engagement (typically one week) we are able to leverage a set of processes and tools developed over the last decade to quickly assess database architectural issues and performance bottlenecks, and provide you with specific recommendations and corrective actions to align your database performance and management with a "JDE Oracle Best Practices" philosophy.

>And what is this philosophy?

Traditional Oracle database performance assessments focus on the macro, “big picture” capacity view of the database. These assessments look at hardware, including CPUs, memory, and storage area networks, and rationalize existing (and/or additional) hardware in light of overall database throughput. This approach tends to assume that the application software and Oracle database are already optimized. Software vendors add to this view by continually marketing the new features and auto-managing processes built into their software -- think Cost-Based Optimizer for starters.

We believe the macro view of performance management is completely backwards, and we like to turn it upside down.

In order to accurately assess an Oracle database and its underlying technology stack in the JDE EnterpriseOne environment, it is important to identify the presence of any database bottlenecks within the context of a system that is assumed to have sufficient capacity, even if the system appears to be inadequate. What this means is that the application or end-user of an Oracle query may be experiencing large amounts of latency or perceived slowness even though all macro system-level metrics may appear quite normal. This can happen, for example, if a JDE work table or a “Z” table grows excessively large and high amounts of disk I/O are required to read through the table. This can become a challenging area to manage, however, when there are no explicit complaints from end-users or application support staff. This can, unfortunately, be a side effect of running on a system with excess capacity. On the other hand, users of a system with occasional performance spikes or limited areas of slowness can become accustomed to the variability in performance and simply learn to work-around or live with the issues. On a system that is 100% utilized, however, performance issues are much more readily apparent, even though it is still quite possible for interactive users to be happy with database performance while batch jobs or ad hoc queries suffer from horrendous performance.

In order to perform an accurate and unbiased assessment, therefore, one must start the review with the assumption that there is sufficient capacity on the database server, regardless of the perceived performance of the database. The starting point for the assessment then is inside the database, where the DBA must answer one simple question:

Why are my Oracle processes waiting?

Only when the DBA can answer this question can s/he determine if additional hardware is needed, or if there is a simple configuration or tuning change that will fix the problem. Failure to take this approach to tuning the JDE Oracle database will often result in unnecessary hardware upgrades and additional frustration as the end users still suffer from performance problems on their new and improved hardware. Instead of waiting a long time for queries to complete on the old hardware, they now wait faster on the new hardware. But wait they will.

DBConnect Solutions has 15 years of JDE Oracle database experience answering the question Why are my Oracle processes waiting? You might be very surprised when you learn the answer to this question.

If you'd like our assistance answering this question on your behalf, please contact us at 574-527-9286, Submit a Request for Information or you can email rcarter@dbconnect.com