Hotsos Symposium, Day 2
by Jonathan Gennick
For me, day 2 of the
href="http://hotsos.com/events/SYM05.php?event_id=36">The Hotsos Symposium began with the incrongruous sight of a
man wearing a jester hat, with blinking lights no less, speaking in detail
about the practice of performance profiling to an audience of expert DBAs who
were all paying rapt attention. It was all in good fun though, and helped
to raise several hundreds of dollars for href="http://www.nba.com/mavericks/community/Donnie_Nelsons_Assist_Youth_Foundation.html">Donnie Nelson's Assist Youth
height="325" border="0" alt="Cary Millsap wearing a jester hat to raise money|
(Photo courtesy of Carel-Jan Engel)
Steve Adams gave two of his usual,
high-quality and technically-deep presentations. The first covered the
internals of hash-join processing. His second was on single-table
hash-clusters, which provide the most efficient possible Oracle data access
path. You can create such a table, designate the primary key column as the
hash key, and Oracle can then translate a primary key value directly to a hash
value that leads directly to the slot (block and location within the block)
for the row in question. For all this to work, it's important to have:
- A single row per hash key.
- One row slot per key.
Without the above, you take a hit on performance as Oracle will need to scan
all slots in the given block. Thus, if you cannot achieve the one-to-one
between hash keys and rows, you should specify the minimum possible block size
of 2K, to minimize the size of the blocks to be scanned.
Karen Morton gave an excellent presentation on setting up an effective,
Oracle test environment. She spoke on the need to record test runs, and to
also record before and after values of key statistics for each run. She
demonstrated scripts to do these things. Her scripts also recorded trace data
for each run by using the external table interface to load trace files into
LOB columns. Karen also spoke on the need to eliminate, in the test
environment, the need for DBAs to intervene. For example, it's best if
developers can get their own trace files. The easier it is for developers to
use the test environment, the more benefit will accrue from it. Karen also
talked about things to look for when testing the performance of SQL
statements. For example, higher latch usage translates into lower
scaleability. Towards the end, Karen walked through an actual case-study in
which high redo generation from a statement ultimately led to the discovery of
a data skew problem that was adversely affecting performance.
And speaking of data skew, that was the subject of Dominic Delmolino's
presentation in which he recounted problems encountered by his company in
implementing a billing software package that had originally been designed for
a different type of industry. It turned out that the design of the software
itself was biased for the type of data skew typically encountered in telephone
billing. Since he wasn't doing telephone billing, his data was skewed the
wrong way, the application assumptions did not apply, and significant
performance problems were the result.
Bruce McCartney showed off an
innovative technique (IMHO) by which he can store ALTER SESSION statements in
a table, link those statements to certain users and session characteristics,
and then those statements are executed by a logon trigger when a match occurs.
Getting back to skew again (must be a common problem this year!), Bruce talked
about how he used the technique to enable stored outlines for only for those
users of a packaged application who happened to be dealing with data skewed
differently from what the rest of the users were dealing with. Bruce talked on
a number of other ways to do session-level tracing and tuning, showing how to
write data to the database alert log, how to write data into trace files, how
to use DBMS_APPLICATION_INFO to log application modules and actions, and
Day 2 was also the day for the Oracle-L list dinner. Alas, I didn't go. I had
eaten too much lunch and was tired, and I had phone calls to return, so I
mostly relaxed in the hotel room, in between those pesky phone calls.
Speaking of food though, let me just end today's report my complimenting the
Hotsos food selection person. Meals at this conference have been very
satisfactory. Everything is well-choosen and well-prepared too. Several whom
I've sat with at lunch have commented on how happy they are with the
Ok. That's it. It's morning of Day 3 as I write this, and I'm off to
breakfast and another full day of great sessions...