As always Hotsos started off with a nice keynote, this time done by Tom Kyte. Tom Kyte was introduced by Hotsos president Gary Goodman after the HOTSOS 2010 opening. Tom’s keynote theme was “Should we be less smart some times”. Tom told about own experiences, that he in the past gave sometimes too fast an answer. It is very important to think about an answer before giving it… Why? Well some things applied in the past or for a specific version, and now they don’t anymore… this can be a problem, a real issue. Always make sure you talk about the same definitions, and agree on them. Make sure talking about the same version and of course about similar circumstances. When you start giving answers in general be sure to work with facts and not some assumptions which might be wrong. So you should always think about the information, about the circumstances and the assumptions you do, it means “Continuous Thinking”.
After the keynote Tom Kyte had a talk on “All about Metadata”. Tom Kyte told about how wrong metadata in the database is influencing the performance. So actual explaining the different reasons the performance can be wrong only due to wrong metadata or the lack of it in the database. It is important to feed the database with data you know of the data, because you know your database the best. That can mean adding dimensions, adding materialized views, etc. His most important points were, “Datatypes Count”, “Proper Lengths matter”, “Constraints are important” and “Metadata Matters”, so ensure they are right and there, and the optimizer can do its job the best way possible.
Tom Kyte’s talk was followed up by a talk from Richard Foote. His talk subject was “Oracle Indexing Myths”. When he started his presentation he seemed to be a big fan from “David Bowie” and “Radio Head”, from who he used a lot of times, names to refer to database objects, like table “BOWIE”. Richard told about a lot of different index myths and some of them where quite interesting. The myths he talked about were:
• Deleted Index Space Is Deadwood
• Indexes Always Prevent Sort Operations
• CBO won’t consider an index when retrieving > x% of rows
• Put Most Discriminating Column 1st In Concatenated Index
• Small tables Don’t Benefit From Being Indexed
• Bitmap Indexes Help With Low Cardinality Conditions
• Bitmap Indexes Inefficient With High Cardinality Columns
Just to give a small impression I will give a few of the answers. In general we can say that deleted index space will be reused, so the idea of ending up with a lot of deadwood in our indexes is really a myth. For indexes and sorts it is always important to realize that “nulls” in columns are not put in the index and so having the index is not always a guarantee that the sorting will actually happen. For indexes it is important to know that the clustering factor is highly involved in choosing an index for an execution plan. Also the amount of rows which will be expected to be retrieved from the index in compare with the table are from influence. So will the index actually been used or not?
The myth of putting the most discriminating column first in a concatenated index, looks very challenging, but in fact it is really a myth. I was even a bit surprised to find this out, so why was my second thought. From base the most discriminating column should be first because of the to be accessed leaf blocks in the index are less, but in fact this doesn’t matter. So if another column is first in the index it will not influence the amount of the to be accessed leaf blocks. Richard showed with some small test cases that it really doesn’t matter, index performance stayed the same. Ok, this said what will happen if we place the most discriminating column at the first, it will actually give problems, like the “Index skip scan”, it will work only efficient as a little distinctive column is first in the index, instead of the other way around. Another index option like index compression will only work as the little distinctive column is first. This has to do with the way an index compression works.
This was just a few examples of the myth coverage Richard did. I think it is very good before starting designing index to check out the work Richard did in this area.
After having had lunch together and making new contacts the presentation room was split in half for two tracks of presentations. I had the choose to go to Alex Gorbachev with “Battle Against any Guess” or Dan Norris with “Consolidation Strategies for Oracle Database Machine”. I choose for Dan and got a good impression on how the Exadata was operating within the database machine. Also things like Oracle I/O Resource Manager and Oracle Database Resource Manager were discussed. After the presentation from Dan I had a small discussion with Christian Antognini, regarding the way a “smart scan” was operating. In my opinion the database is sending a “Bloom Filter” signature to the disk controllers were with the “Bloom Filter” a lot of the not interesting data is pre-filtered before sending it to the database instance. The sent data to the database instance will not be a normal database block format, so it will pass the database buffer cache. The passing of the database buffer cache was also mentioned by Dan in his presentation.
My next presentation I attended was done by Doug Burns with “How I Learned to Love Pictures- Oracle 10g/11g Performance Analysis Using OEM”. Doug tried to do a lot of demos, but due Murphy’s Law he had a lot of issues giving them. To do the load testings on his laptop system he used “Swingbench” as the load-tool. The demo showed how you can use in a very clear way Oracle Enterprise Manager (Grid). Doug showed that the first “Performace Home” screen was based on measured data and the “Top Activity” screen was based on sampled data (ASH). Although a lot of the demos were able to be shown Doug really tried hard to give the message, and in my opinion he did.
Next stop was Kevin Closson with “Ten Years After Y2K, and We Still ‘Party Like It’s 1999’”. Kevin showed a lot of development over the past ten years. He explained about how the techniques over the years have changed and how bottlenecks changed for cpu, memory and I/O. He talked about experiences with Oracle Database File System (DBFS) and how he actually had installed on this file system a complete database including Oracle Home. So he built a database in a database, from a geek point of view like me that is pretty cool. While presenting he had a little buddy with him, the pics will show you how…
The final presentation of the day was for me Kerry Osborne with “Controlling Execution Plans (Without Touching the Code)”. In his presentation he talked about the possibilities of influencing the execution plan of a query, without actual change the query itself. A suggestion could be changing database parameters, well this can be seen as “Big Knob Tuning” and would be very unadvisable. Some other ideas are start adding new access paths, or remove some, in plain Oracle language this means add indexes or remove some. The other idea could be by start changing stats on objects by the DBMS_STATS package. Well all these suggestion will not be very specific and a lot of different queries are influenced. Lucky Oracle did some other things too. Kerry told that Oracle made three ways, Outlines, Sql profiles and Sql Baselines. These three ways where created with different goals by Oracle, but do all basically the same, with some small twists. The use of outlines was a half baked product, one of the issues was to set every time “use_stored_outlines” to true, with every restart of the database instance. SQL profiles were three quarter baked but are not perfect either, one of the advantages over outlines is the fact that they can be applied to multiple statements. The SQL Tuning Advisor (STA) Profiles (SQL Baselines), are still not perfect but do multiple things for the stability, first of all they can add hints and second they can store metadata for the query to give the optimizer a better understanding of the data and data distribution.
The way Kerry showed the possibilities were very nice and a very good closing of HOTSOS day 1.