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
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:
A 32-bit word size
can only address approximately four gigabytes of RAM (2 to the
32nd power). All 64-bit servers have a larger word
size that allows for up to 18 billion gigabytes (2 to the 64th
power or 18 exabytes). These servers allow for huge scalability
as the processing demand grows.
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
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
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 E8870 Chipset supporting the Itanium 2 processor
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.
16-way Itanium 2 architecture (Courtesy UNISYS)
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:
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
The 32-bit limitations prevent continued growth beyond a certain
point. The 64-bit architecture raises the ceiling on that
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.
– 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:
“If you want the
world’s faster processors then you will be forced to pay
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:
Offers large scale uptake but is hindered by non-open source
costs and lackluster support.
Increasing in popularity but suffering from unreliable past
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
SEE CODE DEPOT FOR FULL SCRIPTS
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: