|SAN FRANCISCO — Oracle database
expert and author Don Burleson hosted the most popular technical
session at this month's OracleWorld, drawing more than 500
experienced DBAs to an advanced session on maximizing the
self-turning features of the newest 9i release.
independent consultant who heads Kittrell, N.C.-based Burleson
Oracle Consulting, is also a SearchDatabase.com contributor. At
Oracle's annual conference last week, Burleson whisked experienced
DBAs through a one-hour session on how to save millions with the
newest 9i database by conserving Random Access Memory (RAM).
Among the new 9i internal features are bitmap-free lists, redo
log based replication, dynamic SGA, and the ability to support
multiple block sizes.
With his trademark showman style, Burleson teased Oracle for
renaming some old database features and introducing them as new.
"It took me a while to figure out that RAC was OPS dusted off," he
said, prompting guffaws from the audience.
In a recent article, Burleson conducted an analysis of Oracle's
new Real Application Clusters and the Oracle Parallel Servers they
replaced, and he reported that RAC was the result of Oracle having
reconstructed OPS so that it can run faster and more reliably.
The new 9i version 2 has impressed Burleson, he said. "The
Oracle 9i (release 2) is super stable," Burleson said.
"Self-tuning Oracle 9i is a reality."
Burleson highlighted one of his favorite 9i self-tuning
features, Statspack. Burleson said that Statspack provides a
complete picture of everything that's going on within the Oracle
The Statspack utility is the evolution of Oracle's utlbstat.sql
and utlestat.sql utilities. In Oracle 7, those utilities were used
to gather an elapsed time report of Oracle performance. In Oracle
8, the concept was enhanced by Statspack to allow for the capture
of the elapsed time report into a set of tables.
"I'm a Statspack bigot," Burleson said. "I'm not selling
anything (although you could buy some of my books)."
The first order of business, Burleson said, had to do with
self-tuning disc I/0.
"All Oracle parameters can now be dynamically modified,"
Burleson said. That means no more PGA memory regions, he told the
audience. "No more init.ora file, no more PGA memory regions, and
multiple block sizes can be supported."
Referring to Oracle 9i's 7 RAM data buffers, Burleson said:
"When a data buffer hit ratio falls, Oracle can 'de-allocate' RAM
from a data buffer and reallocate RAM to another buffer."
Adjusting the RAM frames between the data buffers will reduce disc
I/O and improve RAM efficiency, he said. Changing RAM is easy,
promised Burleson, who guided DBAs through the process.
"When Oracle 9i detects an object with significant sequential
block access, that table, or index, can be scheduled for a move to
a larger tablespace," Burleson said. A simple index move can cause
a huge reduction in physical disc I/O, he said.
A good part of Burleson's presentation was dedicated to
determining optimal data buffer size. "In Oracle 9i, we have a new
view that can predict the benefit of additional data buffers in
the data buffer cache," he said.
Norman Jackson, a DBA with St. Louis-based Amdocs, a supplier
of software systems and applications for the directory-publishing
industry, said he caught some of Burleson's enthusiasm for
Statspack as a monitoring tool. "I've used it a little bit, but
not to the extent he suggested," Jackson said.
Stephanie Palmer, a San Francisco-based developer, took plenty
of notes, and said the presentation was remarkably practical.
"There really is no good documentation on MOSC on how you
tune RAM," Palmer said. Like most attendees, Palmer is dedicated
"I have to test with Sybase, DB2 and mySQL," she said. "But I
will always be an Oracle girl."