Tracking External Server Metrics with AWR

Oracle sets several important initialization parameters based on the number of CPUs on the Oracle sever and is now more mindful of the costs of CPU cycles and I/O operations.  Indeed, with each new release of Oracle, the database becomes more in tune to its external environment. 

Further, 64-bit Oracle servers have changed Oracle server metric tuning activities for the DBA.

Oracle and the 64-bit server technology

The advent of 64-bit CPUs has lead to a dramatic change in the way that Oracle databases are managed and tuned.  To understand the issues, one must understand the advantages of a 64-bit server, especially the ability to have large data buffer caches.  The following are the architectural benefits of the 64-bit processors listed in order of importance to Oracle shops:

Improved RAM addressing:  A 32-bit word size can only address approximately four gigabytes of RAM (2 to the 32^nd power).  All 64-bit servers have a larger word size that allows for up to 18 billion gigabytes (2 to the 64^th power or 18 exabytes).  These servers allow for huge scalability as the processing demand grows.

Faster Processors:  Intel's 64-bit Itanium2 architecture is more powerful than the older 32-bit chipsets.  While faster chips are not a direct result of the 64-bit architecture, they are an important consideration for shops with computationally-intensive databases.

High parallelism:  Multiple CPU and SMP support allows large scale parallel processing. For example, the Unisys 64-bit ES7000 servers support up to 32 processors which yields large parallel benefits.

Cluster architecture:  The 64-bit servers, such as the Unisys 64-bit ES7000 servers, are generally cluster-ready.

While having a 64-bit processor might be an attractive option, a large number of Oracle shops continued to run 32-bit versions of the Oracle database on their servers.

The new Intel Itanium2 processor architecture now rivals the proprietary UNIX systems with the ability to house CPUs and over 20 gigabytes of RAM capacity as shown in Figure 12.6.  This architecture can support thousands of users while providing sub-second response time.

Intel also allows architecture to be scaled to a 16-way SMP configuration as shown in Figure 12.7, and it is apparent that Intel will continue to pursue the hardware-level expansion of this architecture.   

With the 16-way processors using Itanium2, there exists server architecture reminiscent of the larger servers offered by Sun, HP, and IBM.  As all the vendors are offering 64-bit servers, the greatest benefit to Oracle shops occurs in these areas:

High transactions processing rates:  For systems with more than 200 disk I/Os per second, disk I/O is reduced by caching large amounts of data and system performance skyrockets.

Declining performance: The 32-bit limitations prevent continued growth beyond a certain point.  The 64-bit architecture raises the ceiling on that growth.

Anticipating rapid growth: For systems that require uninterrupted growth and scalability, the 64-bit architecture allows almost infinite scalability.  Many large enterprise resource planning (ERP) systems have been able to scale successfully on Windows 64 platforms.

Computationally intensive system – If an Oracle database is CPU-bound or if it performs multiple parallel full-table scans, the faster processors in a 64-bit architecture are very appealing.

What does this mean to the Oracle professional?  Larry Ellison, CEO of Oracle, noted at OracleWorld in 2003 that:

He was referring to the Intel Itanium2 chips which appear to be making strong advances in the displacement of the proprietary UNIX environments, especially HP/UX and Solaris.  The major operating environments for Itanium2 servers are Linux and Microsoft Windows:

§       Linux:  Offers large scale uptake but is hindered by non-open source costs and lackluster support.

§       Windows: Increasing in popularity but suffering from unreliable past performance.

These large inexpensive servers provide the ultimate in resource sharing.  With many Oracle instances on a single server, processes that need more CPU will automatically be allocated cycles from the server run queue.  Likewise, an instance that requires additional RAM for the SA or PGA can easily get the resources without cumbersome manual intervention.

In summary, 16-way and 32-way SMP servers are leading the way into a new age of Oracle database consolidation. 

---

This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts: