Hotsos Symposium, Day 1

by Jonathan Gennick

I've just finished day one of my hands-down, favorite Oracle conference: href="http://hotsos.com/events/SYM05.php?event_id=36">The Hotsos Symposium.
Focused on issues of performance optimizaton, the Symposium is held yearly near
Dallas, Texas. Speakers are top-notch and include leading lights in the Oracle
performance space: Dan Tow, Tanel Põder, href="http://hotsos.com/e-library/oop.html">Cary Millsap, Wolfgang Breitling,
Lex de Haan, href="http://asktom.oracle.com/">Tom Kyte, and many more. It's not a large
conference, so there's plenty of opportunity to mingle and talk one-on-one
with the different speakers.



Tom Kyte gave a fascinating talk entitled "SQL Techniques". SQL is
one of my favorite tech topics. During his talk, Tom showed a technique for
writing a row-generator, a problem I've written about href="http://five.pairlist.net/pipermail/oracle-article/2004/000008.html">once or twice myself. The technique Tom showed came from an "Ask Tom" reader named
Mikito Harakiri and is the result of some far-out-of-the-box thinking about
CONNECT BY. For example, here's a query that uses CONNECT BY to generate a
list of days in the current year:



SELECT TRUNC(SYSDATE,'YEAR') + LEVEL - 1
FROM DUAL
CONNECT BY 1 = 1
AND LEVEL < TRUNC(SYSDATE+366,'YEAR') - TRUNC(SYSDATE,'YEAR') + 1;

I've collected several row-generator techniques over the years, and I've
seen one or two interesting applications of CONNECT BY, but this particular
application of CONNECT BY might be the most creative solution to the
row-generation problem that I've yet seen.



Next up was Tanel Põder who spoke on "Advanced Research Techniques in
Oracle". Tanel demoed a technique that uses a Unix pipe and some external
programs to cause trace results to display automatically after executing a
query in SQL*Plus. The technique works for any sort of trace, including 10046
traces. Tanel further could specify criteria restricting the trace results
displayed to only those lines he was interested in. Tanel also demoed a way
to invoke an external debugger in response to an event.



Guđmundur Jósepsson offerred the following advice to database
administrators and developers on resolving performance problems:



  • Work together

  • Know what an application is doing

  • Collect facts

  • Don't fight symptoms, solve the problem!


Gudmunder also presented a case study involving the use of the href="http://hotsos.com/products/profiler.html">Hotsos Profiler to
identify the problem portions of a complex and business-critical query
involving eight views nested seven levels deep that originally scanned over
80,000 rows for each row returned. The DBAs and developers worked together to
rewrite the query in a way that avoided using such complex and generic views.
I wish I had written down the magnitude of the improvement; it was
significant.



Lex de Haan gave a detailed review of all the various Flashback
features in Oracle Database 10g. These include flashback queries,
version queries, and the ability to "rewind" the changes on a specific table
or on the database as a whole. Flashback can be very useful in certain types
of situations that might otherwise require more complicated, point-in-time
recovery. Interestingly, when you flashback a table, if you record the
original system-change-number, you can flashack your flashback, effectively
flashing forward back to the table's original state.



The last presentation of the day was from Dan Tow, author of the
O'Reilly SQL Tuning
book. Dan contrasted Oracle's cost-based-optimizer (CBO) versus human
optimization, enumerating ways in which humans can tune that the CBO cannot
approach. For example, the CBO is not able to make any optimization that might
change the semantics of a query. However, a human can look at the larger
picture, and, knowing the characteristics of the data, might be able to spot
corner-cases that can be safely changed. For example, consider the following
query similar to one of Dan's examples:



SELECT *
FROM a, b
WHERE a.primary_key = b.foreign_key
AND a.primary_key = :x;

The issue in this case is that a.primary key is a numeric column while
b.foreign_key is a character column. This is the sort of suboptimal design
that you sometimes just have to deal with. Oracle will implicitly convert as
follows:



WHERE a.primary_key = TO_NUMBER(b.foreign_key)

However, converting in the other direction is, in the case that Dan was
discussing, far more efficient:



WHERE TO_CHAR(a.primary_key) = b.foreign_key

The semantics of these two predicates are not the same! Consider that
b.foreign key might contain a value such as "14.0". However, in the specific
application that Dan was tuning, such values represented a corner case that
never occurred, that could be safely ignored, and that he could protect
against with a constraint. Thus, he was able to achieve a significant
performance gain that the optimizer would not have been able to do, because
Oracle's cost-based optimizer, unlike humans, is not allowed to change the
meaning of a query, not even in the corner cases.



The day ended with an excellent dinner and a Mardi Gras themed party during
which Mogens Nørgaard gave an
absolutely hilarious bit of impromptu, stand-up comedy. I love this
conference.




4 Comments

pga-eti
2005-03-08 02:37:21
row generator
There seems to be some kind of problem with the row generator query in 9i. In 10g, it works like a charm, but in 9i, the resultset is a single row containing this year's 1st January. I replaced DUAL with another table which contains only one row and now the query works.


-Attila

amolinaro
2005-03-08 05:19:32
row generator
you just need to use WITH to unnest the hierarchy:


with x as (
SELECT TRUNC(SYSDATE,'YEAR') + LEVEL - 1
FROM DUAL
CONNECT BY 1 = 1
AND LEVEL < TRUNC(SYSDATE+366,'YEAR') - TRUNC(SYSDATE,'YEAR') + 1
)
select * from x;


run that and you'll get your rows.


- Anthony

gennick
2005-03-08 06:05:40
row generator
I spoke with Tom about this issue of the row generator returning only one row in 9i. He's already filed a bug on that behavior, which is likely due to Oracle's special handling of dual. In this case, he believes the problem might actually be in SQL*Plus, not in the database itself, but he's not sure, and I have no way at the moment to test that theory.


In 9i, the workaround is to place the CONNECT BY query into a WITH clause.


In 10g the workaround is not necessary.

pga-eti
2005-03-08 06:34:17
row generator
Here's a quick script which I think shows if the error is in SQL*Plus:



DECLARE
TYPE dateList_t IS TABLE OF DATE INDEX BY PLS_INTEGER;
l_dateList dateList_t;
BEGIN
SELECT TRUNC(SYSDATE,'YEAR') + LEVEL - 1 dt
BULK COLLECT INTO l_dateList
FROM DUAL
CONNECT BY 1 = 1
AND LEVEL < TRUNC(SYSDATE+366,'YEAR') - TRUNC(SYSDATE,'YEAR') + 1;


DBMS_OUTPUT.PUT_LINE( l_dateList.COUNT );
DBMS_OUTPUT.PUT_LINE( l_dateList( l_dateList.FIRST ) );
DBMS_OUTPUT.PUT_LINE( l_dateList( l_dateList.LAST ) );
END;
/


SELECT TO_CHAR( TRUNC(SYSDATE,'YEAR') + LEVEL - 1, 'YYYY-MM-DD' ) dt
FROM DUAL
CONNECT BY 1 = 1
AND LEVEL < TRUNC(SYSDATE+366,'YEAR') - TRUNC(SYSDATE,'YEAR') + 1;


And the output from 9i:



365
05-JAN-01
05-DEC-31


DT
----------
2005-01-01


So it seems probable that the bug is in SQL*Plus.


Thanks for the WITH tip to both of you!