The European Cannon Is Here (Station To Station) November 25, 2008
Posted by Richard Foote in Travel.6 comments
I’ve recently returned from a very full on, action-packed 3 week tour of Europe (although it took an additional 36 hours for my luggage to return as well).
After traveling for 30 hours, I finally arrived at my first destination, Helsinki to present at the Finland Oracle User Group Conference. Taken out to dinner that evening by the charming Heli Helskyaho for a Lapland feast that included Smoked Reindeer, Bear Meat Balls and plenty of strong Finnish liqueur in my own personal Kuksa Cup. I recovered in time to present at the conference the next day in the wonderful “Mirror Room” at the Kamp Hotel, definitely the most impressive venue I’ve ever presented at. Feedback after the presentation suggested it was well received and I had the pleasure of attending the fabulous Finland User Group anniversary dinner afterwards.
I was then off to Denmark to attend the Miracle 8th Anniversary Party. A big thank-you to Mogens and Annette for their hospitality, supply of Miracle Beer and a loooonnnng, entertaining party.
Next stop Brussels, for the first of my Index Internals Seminar. But first, I had the opportunity to explore this beautiful city with its narrow cobbled streets and historic buildings. The Grand Place was just stunning, definitely one of the most beautiful man-made places to visit. I just stood there for an hour or so just soaking in all the history. Went to see the Manneken Pis of course and just had a great time walking around and looking at all the wonderful old buildings. The seminar went really well although sadly the venue itself was definitely the worst I’ve come across, a small training room stuffed with far too many people, looking at the presentation via too few monitors with the worst coffee imaginable on offer (yes coffee is important).
Unfortunately, the cold European weather and far too little sleep due to jet lag was catching up with me and by the time I left Brussels to return to Helsinki for the next seminar, I had a really bad cold and high fever to contend with. I woke up the first morning on the seminar in Helsinki feeling like death warmed up and all I wanted to do was stay curled up in bed. However, duty called and I managed to drag myself to the seminar venue. It was a much more appropriate venue, a large presentation room with a big screen display and quite decent coffee on offer (most important). Managed to get through the seminar successfully even though my voice just about disappeared by the end. After a few hot saunas each evening, I began to feel well enough to explore the city of Helsinki and do a little early Christmas shopping.
Next stop, Dusseldorf in Germany. Here the seminar venue was at the same hotel where I was staying so it made life nice and easy. Had a free day to explore the old part of town and had a great time just walking around checking out Dusseldorf. Highlights included walking along the Rhine, going up the Seattle like Dusseldorf Rhine Tower for some great views of the city, down along the picturesque Konigsallee moat and through the charming Marktplatz. The seminar itself went really well with the 40+ attendees having a good time in the plush surroundings of the Linder Congress hotel. In a first, taught the last session whist drinking a German beer in hand, a gift from one of the attendees !!
Final destination was Turkey and the amazing city of Istanbul. Will never forget the taxi ride to the hotel, with a sea of buildings, wave after wave, on hill after hill as far as the eye can see, with various Mosque towers sprinkled throughout. It’s probably the most crowded city I’ve ever visited, with a population of about 1/2 that of all of Australia. The seminar was again held at a really nice venue with it’s own facility to order your own custom coffee (hey like I said, it’s important !!). The attendees were again a real nice bunch and a big thank-you to Tonguc Yilmaz who showed me around town and took me for a lovely traditional Turkish dinner and a fabulous, strong strong Turkish coffee.
After nearly 3 weeks on the road, it was great to finally see lovely Sydney harbour through the window of the plane and returned home to sunny Canberra. Perhaps next time, I might even be lucky enough to arrive at the same time as my luggage !!
A big thank-you to all the 100+ attendees who managed to get to one of the seminars and made them such a success, will eventually get to all your emails I promise !!
Visible Invisible Indexes (The Invisible Band) November 20, 2008
Posted by Richard Foote in 11g, Invisible Indexes.9 comments
After spending the last few weeks traveling throughout Europe, it’s about time I wrote a new post on the Blog !!
I’ve previously posted a simple demo on Invisible Indexes, a new 11g feature that allows you to quickly make an index “invisible” to the CBO.
Recently, a comment on the above post by Michael Sorensen mentions a nice post by Christian Antognini in which he demonstrates how so-called Invisible Indexes on Foreign Keys can still be used by Oracle to prevent locking and performance related issues when delete/update operations are performed on the parent records.
I previously posted how Index Monitoring on FK indexes doesn’t pick up the fact so-called “Unused Indexes” can actually be “used” by Oracle for exactly this reason, quickly determining whether there’s an existing FK record that needs to prevent the update or delete operation on the parent record from proceeding.
I also previously discussed how the 11g CBO can now use index statistics to determine the appropriate cardinality of a query and how Index Monitoring doesn’t detect an index being “used” in this context as well.
It should therefore come as no surprise that Invisible Indexes might not be so invisible after all when it comes to the CBO potentially using the index statistics of an Invisible Index to determine the correct cardinality of a query.
This demo on Invisible Indexes and Index Statistics clearly shows that Oracle can still use the statistics of an index, even after they’ve been made invisible, to determine a more accurate cardinality for a query.
This means you need to be very careful how you use and apply Invisible Indexes and have a clear understanding on how Invisible Indexes are implemented by Oracle and the purpose for their use.
As I explain in my Indexing Seminar, Invisible Indexes are primarily intended to be applied when an application has suddenly and inexplicably started to use an index inappropriately and the database has significant performance issues as a result. The scenario is such that the performance of the database or application is so bad, it has caused the drain of resources to such an extent that normal business functions can’t continue and drastic action is required to address the issue.
The drastic action is to make the problematic index invisible so that the CBO can no longer see and use the index within an execution plan. This is drastic because it means other queries that could currently be using the index appropriately will also be prevented from using the index but performance is so bad that such a consequence can’t make matters any worse. By making the index invisible at least the resource drain can be prevented and hopefully most of the other business activities can now continue within the database.
By making the index invisible rather than simply dropping it, it means some sessions can investigate what the problem might be that’s causing the inappropriate use of the index (by altering sessions to OPTIMIZER_USE_INVISIBLE_INDEXES = true). Once the issue has been solved and addressed, the index can quickly be made visible again, rather than having to recreate the index from scratch.
Invisible indexes could prove to be a handy option in such scenarios.
Invisible indexes are also often viewed as being a useful method of determining whether it’s safe to drop an existing index that may not actually be used by an application. An unused index is a waste of both storage and resources in maintaining the thing but it’s often difficult to determine what the ramifications might be if an index were to be dropped. By making an index invisible, it may be possible to “test the waters” so to speak and determine how an application may suddenly behave without the index.
However, making an index invisible may not necessarily provide an accurate determination of the ramifications of dropping such an index because as has already been demonstrated, there are a number of important and key areas in which invisible indexes are still being used by Oracle. Dropping such an index is not the same as simply making an index invisible and may result in the application suddenly behaving in an unexpected manner that could cause significant performance issues.
Invisible indexes are not entirely invisible if you see what I mean 😉
Richard Foote's Oracle Blog 