Oracle and Availability: Illustrated Downtime Scenarios - Cyclical Database Resource Requirements
(Page 4 of 9 )
User needs often cycle over the course of time, based on business needs, such that some users will not find the database to be inaccessible until there is a massive amount of activity in a certain area, and then suddenly there are not enough resources.
Accounts Receivable, at Horatio's Woodscrews, has this problem. As the end of each month comes to a close, they have to close out the previous month's accounts and run massive reports on the status of all opening and closing accounts. This month-end processing sends the database processing needs of the AR department through the roof—but only for a week or so, before it settles back into a more routine usage pattern.
At the same time, Human Resources typically finds its peak at the lead-up to the end of the month, as it processes employee hours, salaries, and payments. They will need the most resources, then, at a different time than the AR department.
The reporting of these two groups always affects the Order Entry group, as they have a relatively steady database resource usage pattern over the entire course of the month. However, as the summer approaches, the hardware stores begin to increase the stock of woodscrews, and so orders will steadily increase as the summer approaches, and then steadily decrease as weather cools.
Use Services to Allocate Workloads to Higher Priority Applications The concept of using different services for different applications in Oracle Database 10g can allow the DBA to set up differing resource limits and thresholds for different applications. Those thresholds can be modified easily using the Resource Manager, allowing different limits to apply to applications at different times. So, at the start of each month, the reporting group can get more resources than at other times during the month, the paycheck group gets month-end, and the orders group gets an annual bump in resource allocation. By combining services and real application clusters, different applications can be allowed access to a differing number of nodes, so that higher priority applications can scale up faster when needed. Services are discussed in more detail in Chapter 6. |
What Reports Were Those, Exactly?
The problem with attempting to allocate the correct resources to the correct applications at the correct time of month or year is that often it is nearly impossible to get trended data on usage over time.
The DBA at Horatio's Woodscrews is faced with this dilemma. The different application groups have been complaining about the performance of their reports at different times, but the DBA has not been able to get accurate data on which problem reports or application batch processes are truly in need of more resources. He's been reading up on his performance tuning techniques, but the month is coming to an end again, and he can already hear his pager beginning to beep.
Use AWR and ADDM to Quickly Identify and Resolve Bottlenecks With Oracle Database 10g, the Automatic Workload Repository (AWR) collects performance information every hour, and stores it in the Workload Repository for a week so that the HA DBA can go back and review historical information and past performance issues. In addition, baselines can be maintained indefinitely, so that the HA DBA can compare problem periods to points in the past when things were running smoothly. The Automatic Database Diagnostic Monitor, or ADDM, runs at the end of every AWR report period and proactively reports on any discovered problems, as well as recommending solutions or running additional tuning advisors. We discuss this in more detail in Chapter 3. |
 This chapter is from Oracle Database 10g High Availability with RAC, Flashback & Data Guard, by Hart and Jesse. (McGraw-Hill/Osborne, 2004, ISBN: 0072254289). Check it out at your favorite bookstore today. Buy this book now.
|
Next: Out of Space in the Woodscrew Tablespace >>
More Oracle Articles
More By McGraw-Hill/Osborne
