Just a short impression of a few sessions today, and the day isn’t even over…
I’m really loving it being here and seeing all these people thinking and acting about Performance
Alex Gorbachev – Workload Management with Oracle RAC
This presentation contained a lot of demo’s on Alex’s MacBook, so he couldn’t do anything wrong with me because I love working on Mac’s too 
.
It was a very well prepared presentation about load balancing etc. for Oracle RAC 10g and 11g with the options available in Oracle. I’m not too familiar with RAC and all the options but I got a good feel of the possibilities available within Oracle for failover/load balancing and some nice ideas to implement some extra features (If I wanted to). Like if a service fails over to your node you might want to have some non critical services running on that node switched to an other node or just stopped. Alex showed how to do this with a simple script he prepared. Ofcourse it just looks simple when you look at the end product. But it probably wasn’t that simple to come up with the idea and implement it.
I also liked that he was wearing the wooden shoes my colleague Marco had given him. Unfortunately my camera’s battery was dead. So I couldn’t get a picture of that. Due to some work related distraction in the morning I forgot to change it. Alex’s presentation took a bit longer then expected so I wasn’t able to get the battery changed before attending the next presentation.
Tanel’s second presentation was about Oracle Performance Trending and Charting Using Sesspack and Excel. This was a real exciting presentation and again with lot’s of demo’s. He has developed a nice tool that enables you to take snapshots of session statistics. So unlike perfstat which uses system statistics this tool Sesspack can give you insight about what’s going on for your session or sessions. It’s a work in progress and you need to install it into your database (it will create SAWR$ tables). So if you cannot install or are not allowed to do so you can probably get similar results with Snapper. But for analysing trends or getting information about all sessions originating from a certain machine you’d be better of with Sesspack.
It also can be a valuable tool for developers to quickly see what the effect is, of changes in their code, in the database. For performance troubleshooting or capacity planning it is a nice tool.
In combination with PerfSheet (also available from his website Now the direct link since it is uploaded ) it can be amazing. PerfSheet is a flexible tool in the form of an excel sheet that enables you to make graphs dynamically from any query you can run against a database. Once set up (with your new query) you can get a graph with one press on a button. If you have saved your graph settings then that graph is created automagically as well. And it stays fully flexible, just use the pivot-charting options available to change what you want to see and you will get your new graph.
Really cool! I can’t wait to start using it back home.
This presentation is all about a generic theory on how to make succesfull indexes. An excellent book has been written about this by Tapio and Mike Leach. It is called:
“Relational Database Index Design and the Optimizers“. I bought a copy of it fall 2007 and am working my way through it (It takes a bit longer because I want to read too many books simultaniously, not because the way it is written). Because I already read some chapters of this book I thought that I could skip the first session so I could attend Tanel’s second presentation (see above). And I’m glad I did that.
The book and the presentation are not Oracle specific but it is a general theory that can be applied to all relational databases.
This theory will allow you to predict and explain why a certain index will be better then an other one and it will teach you how to create better indexes for your queries. That is “must attend stuff” for each developer that works with a database. A review by J.P. Lewis probably tells you a lot more (Thanks Marco for the tip about the review)
An other presentation I went to was by Wolfgang Breitling about Active Statistics (Again an other Oakie that’s presenting at Hotsos, what’s up with all that wood )
He presented a lot of information about the accuracy of gathering statistics by Oracle which I’m sure took a lot of hard work to get and analyse. He tested the statistics gathered by Oracle for a random uniform distribution, normal distribution and a log normal distribution. Some of the results were that in 11G the gathering of statistics are surprisingly accurate when using the auto sample method while the elapsed time is better then whatever percentage you would have used manually. He also pointed out some problems you need to take care of when cloning statistics (for partitioned tables only? or all, I’m not sure) and when rebuilding an index in 10G. That automagically generates new statistics for that index and might not use the options you would have wanted to use.
The last presentation of this day that I attended was the one by Anjo Kolk. Although I opted for Henry Poras’s presentation at first but this speaker couldn’t present due to an illness. It’s not that I didn’t like to hear Anjo’s presentation but I thought I had heart it all before at a meeting last October. Well that might have been so but a lot had been added to his presentation and he can capture his audience so it was very much worth my while. He has a website as well but I’m not sure he has a lot of time to maintain it.
What his presentation really comes down to in my opinion is that you need to stop looking for magic bullets (there aren’t any around) and need to start using your brain (If you didn’t already). Ask the right questions (Why are you doing this. Why do you want this. etc etc.
He also showed that bandwidth has been increased a gazillion times over the last few decades but that latency still hasn’t improved much. Not in the amount that bandwidth has improved. Bandwidth probably isn’t your problem nor your solution but latency will be. He also talked about the ‘running in parallel trap’ which will lead to contention on resources. If you do not start using bigger tasks and keep using small tasks. And he showed that improving latency will improve bandwidth but improving bandwidth will not improve latency. (It’s not a visa versa relation).
As a last remark: If you switch to 64 bit you will need to buy lots and lots of memory to keep that processor busy. Or it will be driving a Ferarri in the city.
An other interesting day it was. Can’t wait for the Biker Alley party tonight.
Patrick
“for partitioned tables only? or all, I’m not sure”.
My apologies if I didn’t make something clear enough. I can only guess that you are referring to the problem of ending up with an incorrect low or high value for a predicate column when cloning statistics. How the CBO calculates the selectivity of equality predicates has changed dramatically from 9i to 10g. If the predicate value is outside the low_value-high_value range, 9i used num_distinct or density to calculate the selectivity which is used for row source cardinality and index access cost calculation. 10g detects the “out-of-range value pred” and assigns as selectivity of 1/(2*num_rows) to the predicate. Ultimately that leads to all indexes which have that column anywhere in the column list, even if it is the last column, to be assigned the same cost: blevel+2. If the cost of two or more indexes is the same, in this case if they have the same height, the index tie-break by name decides which index is being chosen. That can be very bad for the performance.
Back to your question “for partitioned tables only? or all”. In principle it applies to all cloning. However, you will likely encounter it more often with partitions simply because if you clone statistics you are more likely to clone the statistics of an existing partition to a new partition because that falls under regular maintenance. But it could also happen if you clone your production statistics to development, or your QA statistics to production.