Oracle 9i Database Scalability Enhancements

This is one of a series of articles on the new features of Oracle 9i and their impact on business and technical issues .    For more in-depth training, we offer high quality, instructor-led Oracle training courses for developers and dbas. The biggest change in terms of scalability in Oracle 9i is undoubtedly the introduction of Real Application Clusters  for Windows, Linux and UNIX. This feature provides load-balancing, scalability and hot-standby all rolled into one without requiring any application redesign or database shutdown to add more resources to the cluster. The enhancement of resource management to include undo pool, execution time and active session quotas is also useful for heavily used systems as is the reduction in resources required to support a user. Let’s look at these new features in more detail.

Oracle 9i Real Application Clusters

Oracle have always boasted of the ability of their databases to support large numbers of users, but scalability is more than just being able to support many users. It is also a case of how easy it is to increase the number of users that can be supported. Oracle 9i has performance enhancements that reduce resource requirements but there is also the need to be able to add more resources to support more users and to provide redundancy in case of failure. Real Application Clusters allow exactly that, thereby making scalability simply a case of adding more servers to the cluster. Oracle 7 and 8 supported a form of clustering called Oracle parallel server, but this was complex to implement because the application had to be aware that it was running in a parallel server configuration. Real Application Clusters in Oracle 9i are a replacement for and an enhancement of the previous parallel server technology. Applications no longer have to be aware that they are running in a cluster and new servers can be added to or removed from the cluster at any time without having to shut down the database at all. The major business benefits of Real Application Clusters (RAC) are : • scalability on demand - extra servers and disks can be added as and when required • low cost of ownership - RAC can be implemented using low-cost Intel servers running Linux • high availability included - each server in the cluster acts as a hot standby for every other server and "fail over" can occur in less than one minute • transparent to the application - applications need not be aware of the cluster, everything is handled by the database mangement software In technical terms the new features include a new "cache fusion architecture" which as the name suggests treats the memory caches of all the servers in the cluster as a single cache. This means that reads from disk only have to take place when none of the servers has the requested data in memory. If one of the servers does have the required data in memory, it is transferred across the high-speed bus connecting the servers, to the memory of the server that made the request. Other RAC-related new features include : • enhancement of Oracle Enterprise Manager to be able to start and shut down RACs • extension of automatic undo management to the whole cluster

Scalable Session State Management

The amount of resources required by Oracle 9i databases for each user has been reduced by improvements to the network layer, shared  (multithreaded) server enhancements, improvements for the support of Java sessions and shared memory improvements. Together these improvements allow more users to be supported on the same hardware.

Automatic Resource Management

Oracle 8i introduced the facility to allocate resource quotas to various groups of users (known as consumers) by use of the database resource manager. This enhanced the ability of the dba to share resources appropriately amongst the users by giving them the ability to manage mixed workloads (e.g. reporting and transaction processing) and to control system performance. The resources that could be controlled were the percentage of CPU time, the degree of parallelism and priorities Oracle 9i improves on this by providing the dba with more granular control over resources, adding the following new resource plan directives: • undo pool quotas • active session quotas • estimated execution time quotas • automatic consumer group switching

Undo Pool Quota

The undo pool quota controls the maximum amount of undo allowed for a consumer group. When the limit is reached, the active DML statement is terminated and no other members of the consumer group can perform any DML until undo space is released from the pool by other sessions within the group or the dba manually increases the undo quota for the group.

Active Session Quota

This directive controls the maximum number of active sessions allowed for a consumer group. When the limit is reached, new sessions can be queued until a session becomes available. A time-out period can also be specified to determine the maximum length of time a new session will wait until it is aborted with an error. Estimated Execution Time Quota This enables the dba to set an execution-time limit for an operation. If the database estimates that an operation will exceed this limit, the operation is aborted and an error returned to the user. This error can be trapped and the operation rescheduled if needed.

Automatic Consumer Group Switching

Analogous to the execution time quota is the ability to specify that a session should be switched automatically to a different consumer group if it is active (i.e. running and consuming resources, not idle) for more than the specified time. This switch will occur even if the active session quota for the new group has already been reached. One the previously switched session becomes idle again, it is switched back to its original consumer group. The session can also be switched prior to starting execution if the database estimates that the operation will take longer than the specified time limit and the use of estimates is specified. See the Oracle9i Database Administrator's Guide for more details on using these features or see our overview of the new features. ----------------------------------------------------------- Looking to sky-rocket productivity, slash costs and accelerate innovation? Training is a highly cost-effective, proven method of boosting productivity leaving time, money and staff available for more innovation. Smartsoft offers instructor-led training in Oracle and related technologies on or off site in cities across the UK as well as self-study online training.  See our training course schedule, or let us know your requirements. Oracle tips and tricks Subscribe to our newsletter, jam-packed full of tips and tricks to help you slash costs, sky-rocket productivity and make your systems better, faster and smarter. Smartsoft Computing Ltd, Bristol, England Tel: 0845 003 1320 Contact Us View our privacy policy This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way linked to individuals. View woopra privacy policy.  Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.