Tom Kyte – 2 day seminar in Zagreb
I’m fresh from Tom Kyte seminar held in Zagreb from 25-26’th of January 2010 in conference center of Hotel Antunović. [Many thanks goes to Oracle Croatia for delivering for fifth (hmm…maybe sixth?) year in a row a well known and respected speaker to Zagreb. Seminar was (again) well organized and executed!]
All powerpoint files and sql scripts used by Tom are available on asktom.oracle.com in files section (look for croatia.zip).
Tom covered six sessions in his two day seminar covering:
Session 1: Top 11 things about 11g (R1)
- 1 Encrypted tablespace,
- 2 Cache more stuff,
- 3 Standby Just got better,
- 4 Real Application Testing,
- 5 Smaller more secure DMP files,
- 6 Virtual Column,
- 7 Partitioning just got better
- 8 The long awaited pivot
- 9 Flashback Data Archive
- 10 Finer Grained Dependency Tracking
- 11 OLTP Table Compressiong
At the beginning of this session Tom showed us a photo of IBM disk (likely from seventies) compared to 1GB SD card:
I would say a big difference not only in size, but also in weight. :-) What’s interesting about this photo is that it’s taken in computer museum in Slovenia (description on paper under the disk says: IBM disk za velike IBM sisteme, kapaciteta 1GB).
One interesting fact about 11g R1 is how little it is actually present in production environment. When Tom asked how many of us are using 11g in production not a single hand was raised, out of 70+! Approximate the same result as on Oracle Technology day in Slovenia (Hotel Mons) last year. I’m not convinced that this is purely because of the fact that majority are waiting for 11g R2 to be released on all platforms and then, and only then, they’ll (we’ll) all happily jump on that band wagon. It’s something bigger behind that, but about that perhaps another time.
Session 2: All about Binds
It was nice to (finally) hear Tom presenting bind variables live, after reading about them in his articles and books — …and he still shows how passionate he is about them. Despite the fact that I’m no a stranger to bind variables I learned a thing or two from this session (thanks to example case triggered by someone from the audience). Tom pointed out that his quest for reasonable bind usage will not likely end in the near future. Why? Because each year a bunch of new graduates come fresh from Universities, knowing something about first, second, third, fourth…normal form, but are at the same time clueless about importance of bind variables. Lot’s of those youngsters starts their careers by developing web based applications – meaning that those applications are likely plagued with SQL injection vulnerability (at least some).
Session 3: Storage Techniques
Tom started with a good point of how important data is. Hardware come and goes — five year old hardware is considered a history. Applications also come and go, a bit less frequently than hardware but still, apps are usually replaced every 5-10 years with something new and shiny. Guess what doesn’t change so frequently (if ever): data! That’s why databases are at the core of any application and that’s why data model and proper storage techniques are so important.
[That reminds me….complete projects fails because of the data(base) — seeing this by myself several times in the last decade. It’s astonishing to see ignorance (especially among .NET and/or Java) programmers about importance of good database design. My personal experience with “talented” .NET programmers is – the more they know about coding framework and the more they’re confident in their programming skills – the more likely the project will fail. The worst case that I was dealing with was the application developer who proposed a “farm” of servers on mid-tier on which he would pull and process data from Oracle, thus helping colleagues to “tune” the database back-end; basically leaving database to serve as a dummy dumpster for data. Yes, this was for real! Some people simply know too much and at the same time too little, they’ll likely nail the every screw they see with the hammer they have.]
In the spirit of above said Tom presented: picking a proper data types and tables (B*Tree index clusters, Hash clusters, sorted hash clusters, IOT’s), partitioning and compression.
Session 4: Effective Indexing
In this session Tom nicely explained B*Tree, Bitmap and Function Based Indexes — what they’re, how the internal structure looks like, their strengths and weaknesses, when and when not to apply particular index type etc. Overall the session was about index facts. Since indexes are perhaps one area with the most myths floating around them on the net (unfortunately even on site(s) with very high Google scores, but I can’t named them because I risk a sue;-), I liked the last part of this session – “Mythology and other interesting anti-facts…”.
Session 5: Materialized Views, Caching
This session covered materialized views from non-replication point of the view. Nonetheless, one or two details that Tom mentioned might help you better understand refresh process, no matter if you’re using mviews for replication or for other (more interesting) stuff: such as data “caching”, data “indexing”, query rewrite etc.
First, I was not aware that Oracle in 10g R1 changed the default complete refresh algorithm that was in efect in Oracle9i R2 and before.
- Oracle9i R2 and before: complete refresh = truncate table + insert /*+append*/
- Oracle10g R1 and above: complete refresh = delete + insert
So why they replaced default algorithm with more expensive one? Because Oracle support demanded from kernel developers to implement atomicity, too many customers were confused by non-transactional nature of complete refresh in 9iR2 and before. Hopefully we can override new default with parameter ATOMIC_REFRESH=>False.
Another interesting point Tom told us was about direct path inserts. Since direct path inserts are not recorded in snapshot logs, the common miss-conception is that Oracle in such cases will simply refresh all rows instead of doing fast refresh. This is wrong. Oracle is using special table (SYS.SUMDELTA$) for recording direct path operations (this is possible because direct path operation targets blocks above high water mark), so Oracle can insert ranges of rows that were inserted with direct path during fast refresh.
Query rewrite was another interesting topic covered in this session. I started using query rewrite feature in 8i (ok, I was actually goofing around with QR feature back then), then continued in 9i R2 with one of our largest database. My faith in production quality query rewrite capabilities of Oracle, beyond simple cases almost diminished by the time 10g was released (with constraints and dimensions in place, but admittedly not always with the cleanest star schema design). After listening to Tom presentation and some think-time of why query rewrites so often failed for us, I think I’ll give them another try. It’s also encouraging to know that Oracle is making kernel smarter about query rewrites from version to version. What didn’t work in 9iR2 might in 10gR2 or 11gR2. Fingers crossed.
Session 6: Reorganizing objects – when and how
The last section was basically dedicated to debunking the common myths about all sorts of reorganizations that might get you into groundhog day*. You know, that feeling that you must reorganize something, being the purpose to reclaim some space, to “improve” performance by “rebuilding” tables, indexes, introducing bigger block sizes etc. I’ll leave it to you to read all the slides from this session. It’s not that you never have to rebuild some index or reorganize some table, you might actually need to to that and you’ll be on a safe side as long as you know why is something happening and what are (could be) the consequences of reorganization. And don’t forget to measure and compare the “before” statistics with the new ones, “after” reorganization is done.
Btw. if you didn’t watch that movie yet, you better do. It’s likely you would spend your time more productive watching that movie again and again, then writing that index rebuild script that will run on midnight during the weekend. ;-)