An Index Only Performs How Much Work ??? November 12, 2009
Posted by Richard Foote in Index Rebuild, Oracle Indexes, Oracle Myths.2 comments
One of the main reasons suggested for performing periodic index rebuilds is to improve “performance”. After rebuilding indexes, applications now run so much faster. Users notice a significant improvement in performance. And so on.
There are of course situations when rebuilding specific indexes can indeed improve performance, here’s but one example I’ve discussed previously.
However, the question I always ask when someone claims an index rebuild has made things run faster is to simply ask why. Why is such a simple, but powerful question. Why have things improved ? What has specifically changed as a result of rebuilding the index, that Oracle has now reduced the overall work associated with performing the activity, to the point that things run noticeably faster.
Knowing why is really important because it confirms that indeed there was an improvement and that it was indeed associated directly with the index rebuild. It means when a similar situation arises again, you know how to directly resolve the problem appropriately and effectively next time. Also knowing why means you can determine the specific root cause, perhaps preventing things from deteriorating so bad in the first place, such that rebuilding the index becomes unnecessary. Prevention being the best cure …
Now the most common answer I get for why rebuilding an index has been so beneficial is because the index is now so much smaller after the rebuild that the overheads of reading the index have substantially reduced. If the index is smaller, it means one can read the same amount of index related data with less I/Os. Less I/Os means better performance.
For example, if you can reduce the index by half, you can fit the index into only half the number of leaf blocks and that’s a 50% performance improvement right there.
Well, firstly it assumes that the index has indeed reduced by half. It would actually be a relatively unusual index for it to be so poorly fragmented or for it to have so much “wasted” space that it could be rebuilt into only half the number of leaf blocks.
Possible but somewhat unusual.
However, it also assumes that by having a 50% performance improvement, reading the index blocks constitutes the vast majority of the work. Again possible in some scenarios.
With most index related activities though, reading the index blocks actually constitutes only a small percentage of the overall work. In most cases, the index only contributes a very small percentage of the overall I/O activity. Therefore by potentially only reducing a small percentage of the overall work by rebuilding just the index, the overall impact is generally going to be minimal.
I thought I might perform some basic mathematics to illustrate and put into perspective just what little impact index rebuilding can have in improving performance, even if by doing so the index dramatically reduces in size, because the index itself actually constitutes only a small percentage of the overall costs.
Let say we have one of these more unusual indexes that is so poorly fragmented that it has approximately 50% wasted space throughout the entire index structure. Let’s say rebuilding such an index reduces the overall size of the index by half.
Before the index rebuild, an index has 50% of wasted space and say:
Height of 3
1 Root Block
50 Intermediate Branch Blocks
20,000 Leaf blocks
After the rebuild, the index has no wasted space and has now:
Height of 3
1 Root Block
25 Intermediate Branch Blocks
10,000 Leaf Blocks
Let’s assume the table contains 2M rows and that they fit in 100,000 blocks (i.e. the index is about 1/10 that of the table and the average row size is such that we fit say 20 rows on average per block). Let’s also assume there’s nothing special about this index and that it has an “average” clustering factor of 1M, before and after the rebuild 
1M being somewhere in the middle of possible clustering factor values.
The first thing to note is that the height remains the same after such a rebuild, even though the index is only now half the size. It would be extremely unlikely and the index would have to be particularly small and within a very narrow range of sizes for all the contents of all the intermediate branch blocks to fit within just the one root block. The only way for the index height to reduce down from 3 would be for the contents of all intermediate branches to fit within the root block. Possible, but again quite unusual.
OK, let’s look at the cost of various scans before and after the rebuild, using the index costing formula I’ve discussed recently.
If we’re after just one row (a unique index perhaps), then to read the one row before the rebuild would be:
1 I/O for the root block + 1 I/O for a branch block + 1 I/O for a leaf block + 1 I/O for the table block = 4 LIOs.
After the rebuild, the total cost would be:
1 I/O for the root block + 1 I/O for a branch block + 1 I/O for a leaf block + 1 I/O for the table block = 4 LIOs.
In effect, no change. Well, we’re not likely to have too much of a performance improvement there.
Let’s increase the size of the range scan. What if we retrieve 100 rows (or approx. 0.005% of data):
Before the rebuild it would be
1 I/O for the root block +
1 I/O for a branch block +
1 for all the leaf blocks (0.00005 x 20000) +
50 for all the table blocks (0.00005 x 1M)
= 53.
After the rebuild it would be:
1 I/O for the root block +
1 I/O for a branch block +
1 for all the leaf blocks (0.00005 x 10000) +
50 for all the table blocks (0.00005 x 1M)
= 53.
Again, no difference …
OK, let’s increase the number of rows accessed substantially to 10,000 (or approx. 0.5% of the data).
Before the rebuild it would be:
1 I/O for the root block +
1 I/O for a branch block +
100 for all the leaf blocks (0.005 x 20000) +
5000 for all the table blocks (0.005 x 1M)
= 5102.
After the rebuild it would be:
1 I/O for the root block +
1 I/O for a branch block +
50 for all the leaf blocks (0.005 x 10000) +
5000 for all the table blocks (0.005 x 1M)
= 5052.
Or in percentage terms, a reduction of I/Os of approximately 1%. That’s just 1 tiny little percent …
So even an index that accesses 10,000 rows, a reasonable number and at 0.5% a reasonable percentage of the overall table, even an index that has reduced in size by half, a substantial reduction in size, only reduces the overall number of I/Os by an unimpressive 1% for such a query in the above scneario.
Would reducing I/Os on such a query by 1% really improve performance “substantially” ? Will users really notice much of a difference ?
It’s of course all in the numbers and in how much work the actual index is performing, in how many I/Os are actually performed by the index itself and in how much of a reduction in the overall I/Os an index rebuild will actually contribute. I’ll leave it to you to plug in different ranges of selectivity to see what impact rebuilding such an index with the above characteristics might have.
The point is that for the vast majority of index related queries, most of the work is performed in getting the data out of the table, not the index.
Therefore, reducing the size of an index, even by possibly a substantial amount, may not necessarily reduce the overall I/Os associated with a query if the index only performs a tiny fraction of all the work. You could eliminate the index entirely and providing Oracle could still magically retrieve just the 0.5% of rows of interest in the above example, performance for such a query would unlikely improve “significantly”.
So not only must an index reduce in size but the activities associated with directly reading the index must constitute a significant proportion of the overall work (eg. fast full index scan, index only scans, etc.) or something else must have changed for performance to have improved “significantly”.
Zurich and Paris: Nice Way To Spend A Week November 10, 2009
Posted by Richard Foote in Richard's Musings, Travel.5 comments
I’ve just returned from a bit of whirlwind visit to Europe, teaching my Index Internals Seminar in two lovely cities, Zurich in Switzerland and Paris in France.
Coming from Australia, everything is relatively new with nearly anything over a 100 years old regarded as historically significant. I therefore love visiting European cities which are so rich in history and where you can walk past buildings regarded as “new” but actually built before Australia was even discovered by the Europeans.
I’ve been to Switzerland before but never to Zurich, so I was keen to spend my free day there doing what I enjoy doing best when visiting a city for the first time, just walking and exploring. I was really lucky in that the weather on my free day was next to perfect, clear and sunny, whereas it rained on all the other days I was in Switzerland.
I started my walk from the impressive “Zurich Hauptbahnhof” (Railway Station) which is actually a lovely old building in its own right. I then made my way down the picturesque Limmat, past wonderful buildings such as St. Peter’s Church with what is considered the largest clock face in Europe, Fraumunster Church which has some of the most beautiful stained glass windows I’ve ever seen, the huge 700-800 year old Grossmunster Cathedral and the elegant Opera House.
After walking around the shores of Zurich Lake for a while, I decided to take a cruise where I just sat on the top of the ferry-boat, soaking up the sun and all the amazing views, including the massive Alps looming on the horizon. Zurich Lake certainly puts Canberra’s own little Lake Burley Griffin to shame although I can cycle around Burley Griffin during my lunchtime, not sure I could do the same around Zurich Lake. I then just walked and walked throughout the “old town” district, exploring all the lovely old buildings with their bay windows and balconies and all the narrow alleyways, squares and little market places. I of course bought some Swiss chocolates for the folks back at home.
As it grew dark, Zurich lit up and all the lovely old buildings shone with a new brilliance. The only incident I had of note was on the train back to the hotel, when the train inspector upon checking my ticket gave me a shocked look, full of disdain as I was inadvertently sitting in the first class carriage !! I was led like a naughty boy to where I belonged with all the other second class citizens. The seminar on the following days went really well with an excellent class of folk (pun of course fully intended).
Later in the week, I went to Paris to present my second seminar. Paris is a city I had previously visited, many years ago as the first destination of my honeymoon. So I had to be careful not to have too good a time, else there might be a few problems awaiting me back at home 
Again I was really fortunately, having a perfect sunny day on my free day while it rained on every other day of my visit. I decided to basically walk from the Eiffel Tower to Notre-Dame Cathedral while crossing every single bridge on the Seine in between. Paris is absolutely one of my favourite cities in the world, it’s such a unique and special place. The walk was fantastic (I highly recommend it to anyone who hasn’t done it), with just so much to see and enjoy along the way. And boy are there a lot of bridges (I think I counted 14 bridges along the way), but none as impressive as the grand Pont Alexandre III.
Apart from seeing the Eiffel Tower at the start, I spent time at the charming Place de la Concorde, spent a few hours at the incredible d’Orsay Museum (incredible if you’re like me and love Impressionist Paintings by masters such as Van Gogh, Monet and Renoir), went past the one and only Louvre Museum and then onto Saint Louis Island and the stunning Notre Dame Cathedral where I spent some time just marvelling at the place.
I decided to walk all the way back, but this time exploring primarily the north bank with its shops, restaurants and sites such as Saint-Jacques Tower and Place Vendome. As it got dark, I finally made it up to the Avenue Des Champs Elysees and the famous Arc De Triomphe. To finish what was a fantastic day, I then walked all the way across back to where I had begun and the Eiffel Tower at night, so I could see it in all it’s lit-up glory and ambience, with its search beams circling the clear evening skies.
The only problem I had was being continually hassled again and again by people who kept saying I had just dropped a “golden ring” (I guess in the hope I would buy it as some sort of bargain). There must obviously be a good market for shower curtain rings in Paris …
The seminar was again great with a good crowd asking some really good questions. Everyone was very patient as I tried to not talk too fast for those whom English was a little rusty. For those that know me, talking slowly is not a skill that comes easily to me
I had a great time but thankfully, that’s all my travel over with for the forseeable future. Next year I will definitely reduce my travel commitments, spending much more time at home. Which of course also means perhaps spending more time on this blog as well
Rebuilding Indexes Every Sunday Afternoon !! October 28, 2009
Posted by Richard Foote in Index Rebuild, Oracle Indexes, Richard's Musings.50 comments
I just had to share this amusing article on “Scheduling Oracle Index Rebuilding“.
Regular readers of this blog can no doubt pick the various inaccuracies and strawman arguments with this article. It’s was nice however to reminisce and take a stroll down memory lane back to the 80’s and 90’s when many DBAs indeed did spend every Sunday afternoon rebuilding indexes and the such during so-called maintainance windows.
However, if you’re like me and now work on sites where there is no such Sunday maintainance window because your users actually require and demand 24 x 7 access to their applications, because organisations still want to sell their products and services online during Sunday afternoons, because governments still want border control applications functioning on Sunday afternoons, because consumers still want access to their bank savings on Sunday afternoons, because police still need to access critical information about possible criminal activities on Sunday afternoons, because airlines still want to fly aircraft on Sunday afternoons, etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. ……..
then perhaps the article may not be that useful to you afterall.
Of course, maybe you do have a maintainance window of some description but then have that other really annoying problem associated with modern databases, of them being big, really really big and so more difficult to maintain. Back in the 80’s and 90’s, databases were relatively small by todays standards and it was conceivable for indexes to be rebuilt with little or no thought. There was generally little point, but at least you could get away with it. However, if you’re like me and you have databases with indexes that total in the many terabytes, it just isn’t feasible or practical to rebuild all such indexes, even if you wanted to.
No. If you do have a maintainance window, you may want to make sure the valuable time is spent well, on activities that actually make a difference.
Of course you may also look after databases like I do where the percentage of indexes that actually benefit from a rebuild is not around 30% as suggested by the article but significantly below 1%. Therefore rebuilding 99+% of indexes for no practical gain or purpose may not be considered a “wise” use of maintainance opportunities.
One thing this article did make me ponder though is the number of database sites out there where the DBAs demand and force their businesses and associated end-users into having forced downtimes, into having their applications and business processes impacted and made unavailable (say) every Sunday morning, not because it’s actually necessary or because there’s a business or technical benefit but just because their DBAs say or think they still need to follow the processes and maintainance activities of the 80’s and 90’s. Interesting question that …
One last point I would make with this article (which BTW will change and be modified in the future, I can guarantee that, just save a copy and see). Why would you bother with validating the structure of all indexes if you plan to rebuild them all anyways ? And who is the more inept ? Someone in the Netherlands who attempts to run a script that attempts to validate structure of all indexes or someone who writes a script to validate structure indexes with an ALTER INDEX command
Personally, I spend every Sunday afternoon relaxing and enjoying the weekend, playing sport, perhaps doing a bit of gardening and on a lovely sunny day, just sitting back with family and friends enjoying a barbecue and a few cold beers.
Meanwhile, all my database applications just run on happily along with no impact on business activities at all …
Oracle OpenWorld – Day 5 Hightlights October 16, 2009
Posted by Richard Foote in Oracle OpenWorld, Richard's Musings.9 comments
Started the day desperate again for a coffee at the OTN lounge.
I was looking forward to catching Kevin Closson at the Unconference but unfortunately the session was cancelled at the last minute. So spent some time having a nice chat with Chris Muir and Tim Hall who I had the pleasure to meet when I attended the ODOUG conference in Monterey earlier in the year.
I then attended an excellent roundtable discussion forum with the Oracle folks who write and manage the Oracle Cost Based Optimizer. Some excellent questions (including mine on the use of AUTO with method_opt ), although I wasn’t totally convinced with the answer given. However, I had to clap and clap rather loudly when they made special mention of not setting the OPTIMIZER_INDEX_COST_ADJ paramater and recommending rather strongly not to touch this parameter at all. Something I’ve been suggesting for quite some time. I also got some encouraging feedback on resolving a couple of key issues I have with statistics gathering:
1) A better way of collecting the clustering factor statistics for indexes so that Oracle can remember more than just the last block it visited
2) The ability to collected extended statistics that span more than one table
Both are being actively looked at. However, the highlight for me was the fact they were playing “The Best Of Bowie” album after the presentation, just for me !!
Also had the pleasure of meeting Christian Antognini in person who’s writings I greatly respect.
My final session of the day and of the conference was a round table discussion with the Oracle Real-World Performance folks, including Graham Wood who I had the pleasure to meet earlier in the conference and Greg Rahn who I also met today for the first time. Again, a very good session with some very good questions and answers, especially the question of when should one rebuild an index and the answer that one should only do so in special cases. The fact that these guys promote this best practice in a large public forum such as this (although I was picked out by the panel from the audience that perhaps I should be up there answering this question !!), can only help in getting the message out there.
And so it ends. I had a fantastic time here at Oracle OpenWorld, met some fantastic people and made still more Oracle friends, but I’m now really looking forward to getting home after over 3 weeks on the road.
Oracle Openworld – Day 4 Highlights October 16, 2009
Posted by Richard Foote in Oracle OpenWorld, Richard's Musings.add a comment
I set out to reduce my pace today with only limited success.
My first session was to attended Alex Gorbachev and Paul Vallee’s Unconference session on how to make DBAs highly available. Interesting discussion on some of the various challenges confronting DBAs, such as controlling the balance between the need for stability and availability vs. the need of some organisations for constant change requirements and the importance of delivering such change as quickly as possible. Also, whether the procedures and processes for managing and controlling change management, including industry and governmental governance requirements are a help and a good thing or a hindrance and a threat in just getting the job done. The answer IMHO is that it depends on how such guidelines are implement and policed. It’s somehow comforting to know that other DBAs face similar issues and concerns as your own.
I then attended an excellent presentation on the new features associated with Data Guard in 11g Release 2 (except for the section where an Apple customer described his experiences with Data Guard, during which I simply couldn’t understand a single word he said). This is certainly all of great interest for us at work with the ability of being able to configure a read-only database farm having obvious business potential. However, I was particularly impressed by the ability of Oracle when confronted with a block corruption to automatically fix the corruption by copying a healthy version of the block from a standby database (or indeed the Primary database if the corruption appears on the Standby side). The key word here is automatically and without a single error message being raised, except for a message written to the alert log. Impressive stuff.
The lunch today was simply superb, absolutely the nicest lunch I can ever enjoy having. Please note these comments are addressed specifically to Chris Muir who missed out on lunch and was hungry all afternoon as a result
Then spend some time in the OTN lounge, catching up with a number of folks, including Cary Millsap. Looks like I might very well be presenting at the Hostsos Symposium next year if I can get enough free time organised.
Watched Larry’s Keynote address from the OTN lounge. I had a press pass that enabled me to watch the keynote up close but the free beers on offer at the OTN lounge convinced me this is the place to be There wasn’t a lot new announced that I didn’t already know, although the 10 million dollar Exadata challenge to IBM is typical Ellison. However the highlight for me was the speech by Arnold Schwarzenegger during the keynote, very funny.
After missing out on all the prize draws yet again (I never, ever win these sorts of things, ever), I despondently made my way back to the hotel. Had a lovely dinner with my family who are over doing all the sightseeing things while I’m at the conference beforing making my way to the Oracle Appreciation Event on Treasure Island. Popped into the Aerosmith concert for 5 minutes, tick, seen them now and then over to see dear old Roger Daltrey who performed a really good set of mainly Who classics. Oracle certainly know how to put on an excellent show. However for me, the highlight was actually the coach ride to and from the event with the stunning views of the San Francisco lights as we crossed the Bay Bridge.
sadly, it all comes to an end tomorrow …
Oracle OpenWorld 2009 – Day 3 Highlights October 15, 2009
Posted by Richard Foote in Oracle OpenWorld, Richard's Musings.6 comments
The first thing to note about day 3 is that it’s wet. I mean really really wet and really windy as well. Considering my home town of Canberra has been in drought now for many years with water restrictions in place, I haven’t seen this much rain for a very long time. That said, I believe it’s been raining quite a bit in Canberra since I’ve been traveling so perhaps things are beginning to change for the better at last.
Started the day by attending Dan Morgan’s Unconference session on 11g Rel 2 new features that don’t feature in the marketing material. Some good stuff is in there, with basic little things such as being able to now easily alter database links for example having the potential to make life a little easier.
I then presented my own Unconference Q & A session on Oracle indexes. The turn out was actually quite good and there were some really good discussions including Index Skip Scans, Locally Partitioned Indexes, dangers of Bitmap indexes, coalecing rather than rebuilding and how different it is to drop an index once it’s been created. Thank you to everyone who attended and for making the session a successful exercise, including Jonathan Lewis who started the ball rolling with the first question and some excellent comments and Tim Hall who managed to ask the first David Bowie related question
The heavy rain meant I didn’t have the patience or a strong enough umbrella to wander too far from the main Moscone complex, but I managed to catch a nice presentation by Karen Morton on how to effectively collect database statistics. She presented really well and I really it although perhaps didn’t highlight enough the perils of some of the auto statistics options, especially with regard to method_opt.
I then listened in to Dan Norris in the OTN lounge on the new Exadata V2 offering. Certainly the columnar compression capabilities sound interesting. Of course I also heard that everything runs so fast that you might not need indexes on such a powerful beast, so perhaps my days on covering indexes on this blog are numbered
I next had a look around at the other exhibition hall, practicing my golfing skills and picking up yet more t-shirts and yoyos that light up in funny ways. I’m a bit concerned that my luggage just won’t be able to hold all this stuff I’ve picked up !!
I have an interest in the Total Recall capabilities of 11g (and it’s lack of capabilities) and so attended a presentation on the improvements of Total Recall and flashback archive in 11g Rel 2. It looks like most of my issues with subsequent DDL changes on flashback archive enabled tables now being supported one way or the other. However, I still have concerns with the potential “corruptability” of the archived historical data and the subsequent recoverability implications. It would also be a nice improvement if “who” made a particular change was more readily available without having to purchase yet more expensive database options or have to modify the table structure.
The final presentation of the day was Tom Kyte’s Top 11 new 11g Release 2 New features. His top 11 was basically:
- Do It Yourself Parallelism (with new dbms_parallel_execute feature)
- Analytics Functions (new ones such as LISTAGG and NTH_VALUE)
- Execute on a directory (and the preprocessor capabilities)
- Recursive Subquery Factory (self referencing WITH clause capabilities)
- Improved Time Travel (Total Recall improvements as mentioned above)
- You’ve got mail (new file watchers capabilities that can automatically invoke some code when a file arrives in a directory)
- Deferred Segment Creation (only allocated storage when the first row is inserted, not when segment is created)
- Flash Cache (write blocks back to disk AND an SSD like device so that subsequent reads from the SSD are faster)
- Parallel Improvements (such as better control of parallel process executions and memory parallel execution capabilities)
and both number 10 and 11 best feature, the killer one …
- Edition Based Redefinition (ability to have different versions of the same stored code running at the same time within the database)
As usual, Tom covered this topics with nice examples, in a very clear and concise manner.
On the way back to the hotel, I popped into the Blogger get-together function and had a few drinks and had the opportunity to meet and chat with a number of fellow Oracle bloggers. Thanks to the Alex Gorbachev and Justin and the OTN team for putting the event together.
Aussie Oracle Bloggers, although one of us an Aussie no more
It’s been a looong day …
Oracle OpenWorld 2009 – Day 2 Highlights October 14, 2009
Posted by Richard Foote in Oracle OpenWorld, Richard's Musings.3 comments
It’s been a busy day today.
The effects of drinking perhaps one too many margaritas at the Oracle ACE dinner last night meant I was in desperate need of my morning coffee to start the day. Thank goodness for the OTN lounge
There were a number of announcements today but perhaps the main one from my perspective is the announcement of Oracle’s Enterprise Application Documents offering, an issue we’ve been battling for a long time.
I attended the Jonathan Lewis Unconference presentation on how to be an Oracle expert. It was all really quite interesting and raised a number of points that made one ponder. He asked for example what was the Oracle release that introduced the most significant change. See if you can come up with the same answer he gave (which after thinking about it is probably correct).
Later attended a general presentation that covered both 11g Release 2 and the new OLTP Database Machine Version 2. Usual marketing hype, much of which was already covered during the launch presentations. Discussed features such as RAC One, tiered storage partitioning options and advanced compression which are all certainly on my list of things to investigate.
Caught up with Gareth Llewellyn who mentioned the new OJAM Red Room website that has lots of Oracle information and OpenWorld insights, with an Aussie / NZ emphasis.
After lunch, spent some time in the exhibition hall, stocking up on t-shirts, assorted pens and rubber ducks
Attended Jonathan Lewis’s other presentation on hints on hints. Again excellent, picking up a number of useful little tips, especially the full potential of the qb_name hint. I think he summarised things nicely when he recommended:
1) Don’t use them
2) If you ignore recommendation 1, then be very very careful and precise when using hints
He also explained very nicely the myth that hints can be “ignored” by the CBO when he asked a poor soul from the audience to take off a jacket which he wasn’t wearing. He obviously didn’t perform what was asked of him as it wasn’t possible but did he actually ignore the request …
Later had a nice chat with Jonathan in the OTN lounge, exchanging thoughts mutual acquaintances we unfortunately know and the beauty and magic of Oxford City.
Finally attended the Battle of the DBAs Version 2 presentation which attempted to demo the potential productivity improvements when using the new EM capabilities and features. I don’t think they quite pulled it off but certainly highlighted that EM has some very nice features.
My mission tomorrow is to find out why they’re serving at the conference Seattle’s finest coffee here in San Francisco
Oracle OpenWorld 2009 – So It Begins … October 12, 2009
Posted by Richard Foote in Oracle OpenWorld, Richard's Musings.add a comment
Wow, I’ve had one hell of a 2 week period !!
I’ve been travelling around the southern part of England with my son, as part of a school football tour, with 22 other teenage boys. It’s been a lot of fun but it’s also been extremely tiring, getting up early each morning, packing in as much as possible into each day, living in youth hostels with a room full of snorers, attempting to sleep on lumpy bunk beds, while making sure 20 odd teenagers don’t do what teenagers do naturally which is getting into trouble. On the whole we succeeded, losing just the one boy for just the one afternoon !!
Highlights included watching two English football matches (Tottenham vs. Burnley and the mighty Crystal Palace vs. Blackpool) and just walking around and enjoying the sights of wonderful places such as Oxford, Bath and London. Lowlight (other than losing that one boy for a while) was getting caught trying to sneak into a public toilet without paying the 50 pence fee only to be caught on CCTV and making the mistake of going into the ladies rather than the mens !!
Rather than go home and recover from the trip, I’ve now made my way across the Atlantic to enjoy Oracle OpenWorld here in foggy San Francisco.
Being a Sunday morning, it was a bit of a struggle and somewhat unnatural to get up early but Tom Kyte was doing the first keynote talk so I made the effort to attend. It was as usual with Tom an entertaining and interesting little discussion on what we as developers (and DBAs for that matter) still do oh so wrong. It covered topics such as how we often don’t know how to ask for help and ask questions properly anymore, how we try and make what should be simple as complex as possible (a real theme at my work at the moment), how we try to ignore and pretend mistakes never occur (don’t mention WHEN OTHERS SET NULL with Tom around), how we even fail to back things up properly and finished up on how best practices can often be abused and misinterpreted if we don’t fully understand the reasons and circumstances when something really is a best practice. All good stuff which is always worth reinforcing.
I had a little chat with Tom afterwards and found out to my joy that Tom is also a great fan of “Flight of the Concords”
I was then interviewed and filmed by Gareth Llewellyn and the gang from Australia Oracle Marketing on being at Openworld and the such and it all went rather well only to be told afterwards that they forgot to turn on the mike and would I mind being interviewed and asked the questions again !! I thought it was all so funny that I found it really hard not to giggle and laugh during take 2 of the interview. I’ll post the link once it gets uploaded to youtube or wherever. Here it is.
Later in the day I met up with a whole bunch of my Oracle mates, which of course is one of the main reasons for attending conferences such as this. One of them was Alex Gorbachev who also conducted a little interview with me I think it’s going to be one of those conferences.
Tonight, I’m off to attend the Oracle ACE dinner and catch up with more folk. If I don’t drink too much, I’ll try and answer some of the questions that have been slowly mounting while I’ve been away. Don’t like my chances though …
I’ll post a little list of highlights each day during the conference.
Unconference – Highlight of Oracle OpenWorld 2009 September 23, 2009
Posted by Richard Foote in Richard's Musings.1 comment so far
I’m about to embark on another overseas adventure, this one taking me right around the world. I’ll first visit the country of my birth, England, for a few weeks before making my way across the Atlantic Ocean to attend Oracle OpenWorld in San Francisco.
This will be my second Oracle OpenWorld. I already have a packed schedule with something planned each evening so it’s going to be a really busy period but hopefully a lot of fun as well. With 11g Release 2 and the new Sun OLTP Oracle Database Machine to investigate, I’ll try and target the more technical, less marketing hype presentations and discussion forums if I can.
However this year, I believe one of the real highlights of the whole conference will be the somewhat low key Oracle Unconference (note the photo in the link !!) that will be running for the 3rd time. Held on the 3rd floor of Moscone West, it’s basically a series of presentations that anyone interested in discussing or presenting a topic can just sign up and participate. It’s designed to be a far less “formal” or structured event than the usual conference presentations with more emphasis on discussions and interaction than Powerpoint slides and the such. That said, anyone can present on any topic in any manner they like and if it’s of interest will hopefully attract like interested folk.
With a whiteboard and laptop in hand, this year I’ll be presenting a Q & A session on Oracle Indexes on Tuesday 13 October at 10am where anyone can ask me whatever question relating to Oracle Indexes might be on their minds. If the Oracle questions run out, we can always discuss what David Bowie album is the most influential
This year, there is a truly excellent list of people who are also presenting at the Unconference including many Oracle ACE Directors and well renowned experts such as Cary Millsap, Greg Rahn, Alex Gorbachev, Kevin Closson, Chris Muir to name but a few, who will be covering a wealth of really interesting, technical based topics. It’s a fantastic opportunity to see these folks in a small, informal setting where you can actually ask questions and interact in discussions, rather than just be one of the crowd in a typical “big room” OOW presentation.
For those of you lucky enough to attend the conference, I would strongly recommended spending some of your precious time checking out the various presentations on offer at the Unconference.
Hope to meet some of you at Oracle OpenWorld in a couple of weeks !! If you see me wandering around or at the Unconference, please come or stop me and say hello.
The CBO CPU Costing Model and Indexes – Another Introduction September 16, 2009
Posted by Richard Foote in CBO, Index statistics, Oracle Indexes, System Statistics.10 comments
I’ve previously discussed some basic concepts and formulas regarding how the CBO derives index related costings via the I/O costing model. Time to look at system statistics and the CPU costing model with specific regard to indexes.
The first point I would make is that the CPU costing model has some significant improvements over the older I/O costing method and I would strongly recommend adopting the CPU costing model where possible. I’ll explain some of these improvements and advantages over the coming posts.
The I/O costing model basically looks at the cost of a specific execution plan in terms of the estimated number of physical I/Os. The less I/Os, the less costly and more efficient the execution plan and the faster the expected response times. There are however a number of short falls with this basic I/O costing strategy in that is doesn’t automatically differentiate between the costs associated with different types of I/Os (eg. between single block and multiblock reads), it doesn’t automatically determine a typical or average size of a multiblock I/O and it doesn’t cost and take into consideration the time and overheads associated with likely CPU resources.
The CPU costing model attempts to take into consideration these previous limitations. It automatically takes into consideration discrepancies between the time to complete an average single block I/O versus a multiblock I/O, automatically determines the average size of a multiblock I/Os so it can more accurately determine the likely number of multiblock I/Os in a FTS and automatically determines the expected CPU time for a specific task.
To use the CBO CPU costing model, one needs to collect system statistics so that CBO has this additional information, based on the actual system hardware characteristics (Note: since 10g, the hidden parameter _optimizer_cost_model defaults to ‘cpu’ and so is used by default). You do this with the dbms_stats.gather_system_stats procedure. You can collect “Noworkload” statistics in which Oracle basically randomly reads the database data files to determine base statistics such as the average I/O seek time, the average I/O transfer speed and the CPU speed. However, I would rather recommend the collection of “Workload” stats which are based on the actual workload characteristics of your hardware, based on the real load on your system during the time in which system statistics are gathered (in which case Noworkload statistics are simply ignored).
You can gather Workload system statistics by either running:
dbms_stats.gather_system_stats(‘START’) to start the system stats collection process followed by dbms_stats.gather_system_stats(‘STOP’) to stop the collection process over a typical, workload period, or
dbms_stats.gather_system_stats(‘INTERVAL’, interval=> 120) to say collect system workload stats over a 120 minute period.
To view the collected system statistics, query SYS.AUX_STATS$.
SQL> SELECT pname, pval1 FROM SYS.AUX_STATS$
WHERE pname IN (‘SREADTIM’, ‘MREADTIM’, ‘MBRC’, ‘CPUSPEED’);
PNAME PVAL1 ------------ ---------- SREADTIM 5 MREADTIM 10 CPUSPEED 1745 MBRC 10
The four systems statistics that I’ll focus on for now are:
SREADTIM – time in milliseconds for a single block I/O
MREADTIM- time in milliseconds for a multiblock I/O
CPUSPEED - million of CPU cycles per second
MBRC – average number of blocks actually read during multiblock read operations
In the above figures, just note therefore that a multiblock read on average takes approximately double the time of that of a single block read and that on average, 10 blocks are read during a multiblock read operation. This provides the CBO with vital information regarding how to now cost and compare potential execution plans.
The CBO CPU costing model basically looks at the total time required to complete an execution plan by summing:
total time to complete all single block I/O activity +
total time to complete all multiblock I/O activity +
total time to complete all the CPU activity
This can basically be calculated by:
sum of all the single block I/Os x average wait time for a single block I/O +
sum of all the multiblock I/Os x average wait time for a multiblock I/O +
sum of all the required CPU cycles / CPU cycles per second
In theory, this should provide the total response time to service an execution plan. However, to keep the actual “cost” figures calculated by the CBO consistent with the I/O costing model, the CBO divides this total time by the average time for a single block I/O, such that the full formula becomes:
(sum of all the single block I/Os x average wait time for a single block I/O +
sum of all the multiblock I/Os x average wait time for a multiblock I/O +
sum of all the required CPU cycles / CPU cycles per second)
/
average wait time for a single block I/O
The final “cost” figure, even with the CPU costing model, is therefore still expressed in units of single block I/Os. This is an important point …
So how does the CBO determine the value of the various figures within this formula ? Well as we’ll see, the CBO get’s the required information both from the system statistics and from the costing formulas previously discussed with the I/O costing model.
However, for index related access paths, there’s some good news regarding being able to simplify matters somewhat.
The first bit of good news is that from the perspective of an index access path, there are no multiblock I/Os (except for a Fast Full Index Scan) and so the CPU costing formula can be simplified for indexes to remove the multiblock read component and be just:
(sum of all the single block I/Os x average wait time for a single block I/O +
sum of all the required CPU cycles / CPU cycles per second)
/
average wait time for a single block I/O
Secondly, if the CPU component is relatively trivial, it may not be sufficient enough to count towards the final cost. As smaller index scans are likely to consume little CPU, it means the CPU component can also generally be ignored. This reduces the formula for such index scans to just:
(sum of all the single block I/Os x average wait time for a single block I/O)
/
average wait time for a single block I/O
However, the average wait time for a single block I/O now becomes redundant in this simplified equation, reducing the cost to now be just:
sum of all the single block I/Os
Well the next bit of good news for those that have followed my previous blog entries with regard to the CBO and Indexes is that the previous formulas regarding the I/O costing model are still applicable when determining the sum of all expected I/Os. The sum of all the single block I/Os associated with an index scan is still basically:
sum of all the single block I/Os = index blevel + ceil(index selectivity x leaf blocks) + ceil(table selectivity x clustering factor)
In other words, for smaller index scan execution plans, the cost calculated by CBO using the CPU costing model is the same as with the I/O costing model. So no, I wasn’t wasting everyone’s time discussing the various formulas using the older I/O costing model
If we run the same demo as I ran previously in my initial post regarding the CBO and Indexes where the total cost of the index access plan was 18, but this time using the system statistics listed above:
SQL> alter session set “_optimizer_cost_model” = cpu;
Session altered.
SQL> SELECT * FROM bowie_stuff2 WHERE id = 420;
2000 rows selected.
Execution Plan
———————————————————-
Plan hash value: 134336835
——————————————————————————–
|Id|Operation |Name |Rows|Bytes|Cost (%CPU)|Time |
——————————————————————————–
| 0|SELECT STATEMENT | |2000|36000| 18 (0)|00:00:01|
| 1| TABLE ACCESS BY INDEX ROWID|BOWIE_STUFF2 |2000|36000| 18 (0)|00:00:01|
|*2| INDEX RANGE SCAN |BOWIE_STUFF2_I|2000| | 9 (0)|00:00:01|
——————————————————————————–
We notice that the cost remains exactly the same at 9 for the index range scan component and exactly the same at 18 for the total execution plan when comparing the cost of using the IO costing model vs. the CPU costing model. Introducing system statistics hasn’t changed things for this particular index related execution plan.
And this is a very common observation. As indexes use single block I/Os, as the CBO cost remains as a unit of single block I/Os and as CPU consumption for an index scan is often trivial, the resultant costs for index access paths often remain unchanged with the CPU costing model.
Previously, we looked at how changing parameters such as the optimizer_index_cost_adj impacts the costings of index related execution plans to create a level playing field between index and FTS execution plans.
The key point to make with regard to system statistics and the CPU costing model is that in general, the system statistics and the associated formula will automatically ensure a level playing field. However, unlike the optimizer parameters, it will do so by typically adjusting the associated costs of the FTS (rather than the index accesses) as the true costs and wait times associated with multiblock FTS are calculated, but are divided by and expressed in units of single block reads.
So rather than decreasing the associated costs of an index access path, system statistics and the CPU costing model will typically create a level playing by automatically increasing the associated costs of a FTS as appropriate.
To be discussed further …
Lanyon United Masters Div 3 Football Premiers 2009 (We Are The Champions) September 15, 2009
Posted by Richard Foote in Richard's Musings.7 comments
On Sunday mornings, I play for the mighty Lanyon United Football Club, in the Canberra Masters Division 3 competition. You need to be over 35 to compete, meaning it’s a league specifically for those who are highly skilled, have years of previous experience but not handicapped with youthful exuberance
In the previous 2 years, we managed to get to the final series but lost out in the semi-finals. This year we managed to get to the Grand Final where we met our local rivals, the Brindabella Blues.
This was the first Grand Final I’ve ever played in so I was pretty excited, managing only a couple of hours of sleep the night before. I’ve been struggling with injuries for much of the season but a few sore leg muscles weren’t going to stop me from giving it my all. It was a perfect Canberra Spring morning and I just had a good feeling about the day. There was a decent crowd on hand to support us oldies in action when the whistle blew to start the game.
The first half was a pretty close affair with perhaps Brindabella having the better run of play. Within a few minutes of starting, my left calf was feeling pretty tight and sore and I had their speedster on the left wing to contend with (note that “speed” is a relative word in Masters Div 3). It was with some relief when half-time arrived with the game still tied at 0-0. The second half was equally close although we started to gain more control and have the better of the chances. With about 20 minutes to go, I just couldn’t get the final touch on a ball that slipped through the penalty box.
Then with 15 minutes to go, disaster struck. I was off chasing a ball down the wing when my legs just gave out, first tearing my right hamstring and then with the very next stride, tearing my left calf. Ouch !! Down I went like a sack of lumpy potatoes and off I slowly hobbled with the support of a few teammates. My contribution was over but with the game still locked at 0-0, the game was far from over.
Watching helpless from the sidelines, in a lot of pain wasn’t easy. We had a number of good chances to win late in the game but full-time arrived with still no score. So 10 minutes of extra-time each way it was with then. 5 minutes into the first period and the dead lock was finally broken when a break on the right resulted in a nice cross for Robin, our ever reliable striker, to brilliantly put the ball into the back of the net.
Wooohooo !! We were up 1-0. I was so excited that I forgot I was injured and hurt my legs some more as I jumped up off the chair. Time appeared to just drag for the rest of the game but our superb defence was just impenetrable and we managed to hold on for victory.
Lanyon United 1 Brindabella Blues 0. We had won the game and were Premiers for season 2009 !!
It was such a great feeling, all the hard work and pain was worth it. And a beer had never tasted so good.
Yes indeed, we’re one hell of a good looking team More photos here.
Thanks to all the guys and all our supporters for making it such a fun and rewarding year. I’m already looking forward to defending our title next season.
I visited the physio the next day who was really impressed that I managed to injure 2 different muscles in both legs simultaneously. I should thankfully be OK for my upcoming trips to England and then Oracle OpenWorld in the coming weeks.
Back to Oracle indexes and the CPU based CBO in the next couple of days …
Malaysia and Tall Buildings September 7, 2009
Posted by Richard Foote in Richard's Musings, Travel.3 comments
Canberra is not only the capital of Australia, a fact not particularly well known outside our shores, but it’s also one of the most fabulous cities in the world in which to live. This last fact is not particularly well known within Australia either
It’s a beautiful, peaceful city that offers a wonderful standard of living, with generally lovely weather wrapped within four distinct seasons. Yes, the winters can be a little cool and the summers a touch warm, but having lived in Manchester, England for quite a number of years, the weather isn’t too far from perfect most of the year round.
Another “charm” with Canberra is that it isn’t a big city “Metropolis” with large crowds, big traffic problems and tall buildings. In fact, because of Canberra’s strict building restrictions, the tallest actual building in Canberra is the somewhat unimpressive Lovett Tower, at just 26 stories and 93 metres in height.
When I visit a “big” city, I therefore get easily impressed by tall buildings and usually walk around staring upwards, walking into passersby and lightposts on a continual basis. I love New York for example and can spend the whole day just walking the streets, staring up at all the fantastic tall buildings.
Last week, I had the pleasure of visiting Kuala Lumpur in Malaysia for the first time, presenting my Index Internals seminar. I only had a few days but I made sure I had time to walk around and have a good look at the very impressive and oh so tall Petronas Twin Towers. At an incredible 88 floors and at almost 453 metres, they’re the tallest twin towers in the world and were the tallest buildings until surpassed by the Taipei 101 in 2004.
What’s so spectacular though is the skybridge that connects the two buildings on the 41 and 42 floors. It just seems to hang there suspended in the middle, with what looks like a few fragile legs holding the bridge in place. Although impressive during the day, the buildings seem to sparkle to life in the evenings as the buildings light up in spectacular fashion, like a pair of huge exotic Christmas trees.
I resisted the temptation however to actually go up the towers. Having been up the World Trade Centre in New York, I still get a little nervous about such things.
Shopaholics would also I’m sure be impressed by the 6 levels of shopping available in the massive Suria Shopping Centre, at the bottom of the Petronas Twin Towers. Fortunately, I already had all the David Bowie albums on offer and resisted the temptation to spend too much I particularly loved this view of the towers from within the shopping centre:
I had a great time during my brief visit, ate some “interesting” food and met some lovely folks during the seminar. Kuala Lumpur is well worth a visit if you ever get the chance.
OPTIMIZER_INDEX_CACHING Parameter September 1, 2009
Posted by Richard Foote in Oracle Cost Based Optimizer, Oracle Indexes.7 comments
As previously discussed, the CBO assumes the actual costs and overheads associated with all I/Os to be the same, regardless of the type of I/O, unless told otherwise via the optimizer_index_cost_adj parameter.
Another key assumption the CBO makes by default is that all I/Os will be physical I/Os (PIO) and so be relatively expensive to perform and “worthy” of being costed.
However, this of course is not always the case with many of the blocks being requested and accessed by Oracle already cached in the buffer cache. In specific scenarios, the CBO can take the likely caching characteristics of indexes into consideration and reduce the cost of an index related execution path accordingly. Note however this only applies for I/Os associated for index specific blocks in specific scenarios where the same index is repeatedly accessed.
For example, in the case of a nested loop join where the inner table is typically accessed via an index look-up, the same index may repeatedly access the table many times within the loop. Many of the blocks associated with this index are therefore quite likely to be cached as the index structure is being continually accessed. Same scenario for an index look-up process as a result of an IN list condition. For each element in the IN list, an index is often used to look-up the corresponding value in the table, thereby accessing the specific index again and again for each element in the IN list. As the index is continually being accessed, many of its associated blocks are likely to already be cached in memory.
The purpose of the optimizer_index_caching parameter is to tell the CBO what percentage of index related blocks are likely to already be cached in the buffer cache during these types of operations and so should not be considered in the overall costings associated with the index related execution path. The default is 0 which means by default Oracle doesn’t consider any index blocks to ever be cached and all I/Os associated with an index during an index access path need to treated as PIOs and costed accordingly. If however the optimizer_index_caching parameter is set to say 25, it means that the CBO will consider 25% of all I/Os associated directly with index blocks are likely to already be cached and will therefore reduce the overall cost of index block I/Os by 25%.
As discussed previously, the CBO I/O based costing formula is:
basic index range scan cost = index blevel + ceil(index selectivity x leaf blocks) + ceil(table selectivity x clustering factor)
The optimizer_index_caching parameter adjusts the formula in the following manner by reducing just the index accesses portion of the formula:
basic index range scan cost = ceil((index blevel + ceil(index selectivity x leaf blocks)) x (1- optimizer_index_caching)) + ceil(table selectivity x clustering factor)
but only for specific index scans such as nested loop joins and IN list conditions where an index is likely to be continually accessed within the same execution path.
So if we were to go back to the example I covered in the single predicate demo in the first CBO and Indexes Introduction post as shown below:
SQL> select * from bowie_stuff2 where id = 420;
2000 rows selected.
Execution Plan
———————————————————-
Plan hash value: 134336835
——————————————————————————
|Id| Operation | Name | Rows | Bytes | Cost |
——————————————————————————
|0| SELECT STATEMENT | | 2000 | 36000 | 18 |
|1| TABLE ACCESS BY INDEX ROWID| BOWIE_STUFF2 | 2000 | 36000 | 18 |
|*2| INDEX RANGE SCAN | BOWIE_STUFF2_I | 2000 | | 9 |
——————————————————————————
we notice that the cost of the execution plan is 18.
If we now change the optimizer_index_caching parameter to say 75, meaning that 75% of all index blocks are now likely to be cached and rerun the query:
SQL> alter system set optimizer_index_caching=75;
System altered.
SQL> select * from bowie_stuff2 where id = 420;
2000 rows selected.
Execution Plan
———————————————————-
Plan hash value: 134336835
——————————————————————————
|Id| Operation | Name | Rows | Bytes | Cost |
——————————————————————————
|0| SELECT STATEMENT | | 2000 | 36000 | 18 |
|1| TABLE ACCESS BY INDEX ROWID| BOWIE_STUFF2 | 2000 | 36000 | 18 |
|*2| INDEX RANGE SCAN | BOWIE_STUFF2_I | 2000 | | 9 |
——————————————————————————
we notice that the cost remains unchanged at 18 and parameter has had no effect, as the query was based on a single table equality predicate and did not have processing involving either a nest loop or IN list condition.
However, if we run the second IN list predicate demo involving an IN list condition as shown below (first resetting the optimizer_index_caching parameter back to 0):
SQL> alter system set optimizer_index_caching=0;
System altered.
SQL> SELECT * FROM bowie_stuff2 WHERE id in (20, 30, 420);
6000 rows selected.
Execution Plan
———————————————————-
Plan hash value: 2964430066
——————————————————————————-
|Id| Operation | Name | Rows | Bytes | Cost |
——————————————————————————-
|0| SELECT STATEMENT | | 6000 | 105K| 49 |
|1| INLIST ITERATOR | | | | |
|2| TABLE ACCESS BY INDEX ROWID| BOWIE_STUFF2 | 6000 | 105K| 49 |
|*3| INDEX RANGE SCAN | BOWIE_STUFF2_I | 6000 | | 23 |
——————————————————————————-
We note we had a cost of 49. Remember, the cost of 49 was calculated in the following manner as we have 3 elements in the IN list condition:
cost = index blevel + ceil(index selectivity x leaf blocks) + ceil(table selectivity x clustering factor)
= 2 + 3 x ceil(0.01 x 602) + ceil(0.03 x 852)
= 2 + 3×7 + 26
= 2 + 21 + 26
= 23 + 26 = 49
If we now alter the optimizer_index_caching parameter to 75 and rerun the same IN list query:
SQL> alter system set optimizer_index_caching=75;
System altered.
SQL> SELECT * FROM bowie_stuff2 WHERE id in (20, 30, 420);
6000 rows selected.
Execution Plan
———————————————————-
Plan hash value: 2964430066
——————————————————————————-
|Id| Operation | Name | Rows | Bytes | Cost |
——————————————————————————-
|0| SELECT STATEMENT | | 6000 | 105K| 32 |
|1| INLIST ITERATOR | | | | |
|2| TABLE ACCESS BY INDEX ROWID| BOWIE_STUFF2 | 6000 | 105K| 32 |
|*3| INDEX RANGE SCAN | BOWIE_STUFF2_I | 6000 | | 6 |
——————————————————————————-
we notice the cost has been reduced from 49 down to 32. How has the optimizer_index_caching set to 75 changed the costs:
basic index range scan cost = ceil((index blevel + ceil(index selectivity x leaf blocks)) x (1- optimizer_index_caching)) + ceil(table selectivity x clustering factor)
= ceil((2 + 3 x ceil(0.01 x 602)) x (1-0.75)) + ceil(0.03 x 852)
= ceil((2 + (3×7)) x 0.25) + 26
= ceil((2 + 21) x 0.25)+ 26
= 6 + 26 = 32
So whereas previously there were 23 index block I/Os, this has been reduced down to just 6. Note that the I/Os and associated costs with accessing the actual table blocks within the index scan remains unaltered.
So how to set this parameter in a database ? Well, there are a number of issues with it all.
Firstly, as with the optimizer_index_cost_adj parameter, there’s only the one “global” parameter (for the system or session) which means any value needs to be averaged out for all indexes and for all situations in which this parameter can have an effect. However, some indexes may for example be quite small and heavily accessed and as such quite likely to have most index blocks cached at any point in time (including leaf blocks) whereas other indexes may be quite huge and rarely and randomly accessed which means perhaps only the branch level blocks are likely to be cached even during a (say) IN list operation. As discussed previously, with all averages there will likely be examples where the value is appropriate, too high or too low depending the the characteristics of specific indexes.
Secondly, the poor table related blocks don’t have an equivalent parameter and so Oracle always assumes not only the table blocks within an index scan will be PIOs, but a FTS will only ever consist of PIOs, which conversely might not always be the case. So while we might make a reasonable guesstimate of the likelihood of an index block being cached (say via the buffer cache hit ratio, a study of the v$bh view, etc.), the CBO makes so such allowances for the possible caching characteristics of table related blocks. Yes, index blocks are more likely to be cached, especially during the specific scenarios in which the optimizer_index_caching parameter has an effect, but that doesn’t mean table blocks will always be PIOs. Therefore simply setting this parameter to what might appear a reasonable generalistic index caching value might still run the risk of favouring indexes unduly, even though it only impacts the index accessed blocks in the costing formula, as the CBO doesn’t make any such cost allowances for table blocks that might be cached in a FTS.
In the ideal world, we would have some idea of the caching characteristics of all individual indexes and tables and based on the segments being accessed and their associated caching characteristics, have the CBO make the necessary adjustments to it’s costing estimates in an execution path. Until we reach such an ideal world (which might not be that far away BTW), I basically recommend not to set this parameter at all and again simply ensure you use accurate system statistics and have accurate enough segment statistics.
I recommend setting this parameter if and when you find the CBO is commonly not choosing appropriate indexes for the above mentioned scenarios when perhaps it should and a slight “nudge” of costs in the right direction is sufficient to address the issues. The optimizer_index_caching parameter is not quite as overly “dangerous” if set incorrectly as the optimizer_index_cost_adj parameter can be, as it only impacts the “half” of the formula relating directly to index block I/Os and not the table block I/Os, which often constitute the greater proportion of overall I/Os in many index range scan operations (although as my example above shows, this depends as well).
However, with both of the optimizer_index parameters set, they can both have a hand in reducing the overall costs of an index related execution plan. The optimizer_index_caching parameter first impacts the cost of just the half of the formula relating to index block I/Os as shown above and then the optimizer_index_cost_adj parameter further impacts the overall resultant cost. So if we were to run the IN list query again, but this time also set the optimizer_index_cost_adj to say 25 as well as leaving the optimizer_index_caching to 75:
SQL> alter system set optimizer_index_cost_adj=25;
System altered.
SQL> SELECT * FROM bowie_stuff2 WHERE id in (20, 30, 420);
6000 rows selected.
Execution Plan
———————————————————-
Plan hash value: 2964430066
——————————————————————————-
|Id| Operation | Name | Rows | Bytes | Cost |
——————————————————————————-
|0| SELECT STATEMENT | | 6000 | 105K| 8 |
|1| INLIST ITERATOR | | | | |
|2| TABLE ACCESS BY INDEX ROWID| BOWIE_STUFF2 | 6000 | 105K| 8 |
|*3| INDEX RANGE SCAN | BOWIE_STUFF2_I | 6000 | | 2 |
——————————————————————————-
We note the the cost of the execution plan has further reduced down from 32 to just 8. Basically it’s just the previous cost of 32 x optimizer_index_cost_adj = 32 x 0.25 = 8.
However, rather than setting either of these parameters, I would simply recommend the appropriate use of system statistics and the CPU costing model as I’ll discuss later.
Demonizing Foreign Keys (“Helden”) August 25, 2009
Posted by Richard Foote in Foreign Keys, Richard's Musings.25 comments
My mate Marcel Kratochvil, a fellow Canberran Oracle ACE Director and David Bowie fan, has recently published a paper finally called “Discussing Foreign Keys and their usage in the Database” in which (in his own words) he is “demonizing foreign keys”.
It’s a really good read, well worth the effort, in which covers various interesting topics but primarily questions the general usage of Foreign Keys in the relational (or object-relational) database, especially in the world of multimedia. He’s been arguing against the general use of Foreign Keys in the database for a long time and has finally put down his thoughts on the subject. I would highly recommend giving the paper a read and see if it might alter (or confirm) your own views on the subject.
Although I think Marcel makes many a good point and I agree with much of what he says, I however basically disagree with his general central notion and arguments against Foreign Keys. I guess I still consider myself a ”Foreign Key Dinosaur”
These are just some of the comments and feedback I gave Marcel when I was given an early look at the paper. I suggest you read Marcel’s paper before my comments so they make more sense and can be put into context and perspective.
“Let me say that I agree with much of what you say and you make many valid points. Certainly the world of multimedia is one which we at work are slowing moving into and it certainly has many challenges. Now, with respect to the specific issue of FKs in the database, I suspect we’re actually in some ways in agreement. I agree with you that there isn’t necessarily one correct answer for all situations. One of your closing statements:
“So by all means use Foreign Keys, but don’t blindly use them. Use them when it makes sense, when it solves a tuning issue or when maintaining a relationship between tables is crucial to the application running“
is actually pretty well what I think as well. However, I suspect our interpretation of what this actually means differs somewhat with my opinion being that it makes sense to use FKs far more widely and comprehensively than perhaps you suggest in your paper. So our likely differences of opinion lies mainly with the occurrences of when it makes sense to use FKs. However, your paper doesn’t alter my fundamental view that it almost always makes sense to use FKs in the database where possible.
Firstly, many of your comments suggest DBAs generally find FKs problematic, developers like to push them. As a DBA (and ex developer of nearly 10 years), I find FKs to be essential in many of my activities and find they generally assist, rather than impede my tasks. More on this later but the importance of FKs on DBAs activities and responsibilities is paramount.
I don’t think anyone seriously suggests that FKs (or other constraints) ensure data integrity in the general sense. Yes, someone can enter incorrect details or pick the incorrect menu option. However, constraints and FKs specifically are an important method of reducing the occurrence of data integrity related issues that if not implemented in the database, needs likely to be implemented less effectively elsewhere. More on this to come but whenever I mention data integrity later, I mean it only in the sense of having consistent data relationships that follow key business rules.
Examples are good because the strength of the example can often be telling. Take your deleting an account but keep the purchase order example. Tricky scenario but one which simply requires a correct physical model with FKs to support it to start with. If person accounts must be deleted, then either it doesn’t indeed make sense to use a FK in the purchase order table to point to the person account or the FK needs an “on delete set null” type logic or the FK needs to point to a entity details table that contains data that doesn’t have to be deleted, etc.. Will the tax audit really be happy to have purchase orders associated with unknown entities, how can you prove the purchase was legitimate and not a fraudulent tax reduction if the purchase order can reference entities that don’t exist. A entity number that references nothing is meaningless therefore it either needs to reference something else, perhaps sufficient person details to keep both privacy and auditing requirements satisfied, or it should be removed. A FK would be
critical in such an application but it needs to point to the correct entity and/or be maintained appropriately to ensure the business rules are followed.
The issue of “not trusting” developers is an interesting one. I fundamentally don’t trust developers to perform and code FK functionality outside the database for 4 key reasons:
1) Developers come in sorts of shapes and sizes, have all sorts of experiences and come in varying degrees of quality. There are good developers and not so good developers and as I have no control on their hiring and firing, can’t assume they’re necessarily “trustworthy” when it comes to ensuring their code will maintain necessary data relationship integrity. I’ll trust FKs in the database over developers in this respect any day as FKs in the database are far simpler to implement and control.
2) Even the best of the best, the most brilliant of developers is limited by being human. And no matter how good, humans make mistakes. They occasionally write code that might leave orphaned data, they write code that might not take into consideration a specific business rule, they change the business rule in the application but forget to change it correctly in a specific module. A FK in a database doesn’t make a mistake. If it’s there, it’s centralised and it’s enforced. Period.
3) Note the database itself, with all its clever internal code, must still lock tables when a parent record is deleted/updated/merged. Now Oracle internally can get around this if an index exists on the FK because it can in effect check the index structure to ensure no new associated FKs are inserted. This “trick” is only available to the Oracle code, a developer can’t effectively lock a row in an index that doesn’t exist. Therefore, if Oracle itself is forced to lock a table to ensure the relationship between tables remains consistent, a developer must likewise lock a table during any similar operation. The very best of trustworthy developers only really has the choice between locking tables or potentially allowing “dodgy” data in the system. Give me an indexed FK constraint any day as it will fundamentally be more efficient to implement.
4) Even the best developer, writing the most perfect of error proof code, can’t necessarily control how data is accessed and changed in the database. In highly complex environments (note some of our applications have 2,500+ tables), with these users that make mistakes, it’s virtually impossible for the application to cater for every possible data “cleanup” scenario. Therefore, DBAs (in particular) and other power users are frequently required to go in the database via the “backend” and clean things up. I
don’t know of any of our more complex applications, be they in-built or 3rd part apps, where this hasn’t been necessary. Business rules in the application, with FKs effectively managed in the application are of no use when data is not access via the application. And we can’t wait the 6 months it might take to change the application to prevent these scenarios from occurring or to make the application able to deal with the data spill or data “integrity” issue.
Expanding on this point a little, we have many many Oracle applications. These are “real-world”, modern applications. I can tell you without exception, that the most problematic, worst performing, data integrity error filled atrocities of applications we administer have a common characteristic. They don’t have FKs. I don’t have to imagine an application that has no FKs, we have a number of 3rd party apps, running in production right now that have no FKs, ranging from case management systems to CAD systems that I need to deal with every day. Guess what. They have various performance issues AND all manner of data integrity issues, directly as a result of not implementing FKs in the database. I’m sure these software companies trusted their developers … As a humble DBA, we have little control over the adoption of these various apps, although lessons are being learnt and things are slowly changing.
Not only are data integrity issues much more prevalent in these apps, I also don’t have to imagine the nightmare that is trying to resolve data related issues without FKs in the database. If I have to remove (say) an over classified document after its been incorrectly inserted a few weeks ago into the system and subsequently linked and referenced all over the place, it’s extremely difficult to safely remove it. We have no way of knowing the necessary dependencies, if you delete a row here or there, how to ensure there’s no orphaned data left behind as a result and measure the impact in other parts of the apps. Interestingly, even the software vendors themselves struggle and forever provide scripts that don’t clean data out correctly. Application upgrades invariably result in corrupted data relationships.
Our applications with FKs have far less issues and when we do need to clean things up, the FKs both protect us and clearly document what needs to be done. One can’t “accidentally” delete a parent row, leaving orphaned data behind, the enabled FKs won’t let us. We know the impact of changing this column definition or dropping this column, the FKs tell us all the related dependencies. We don’t go in blind, we don’t have to guess, we know. We know that by removing (say) this document from the system, it will impact all these related cases, that this person will no longer have these details recorded against him. And no, having some data integrity issues due to FKs being left out is not acceptable. The implications of having say orphaned data might mean a vital document containing critical intelligence information is inaccessible by the application, resulting in perhaps a fugitive being able to leave the country or conversely, sensitive data still being accessible. The implications here can potentially be life threatening. It doesn’t get any more real world than that and FKs are a vital component in ensuring the correct data is visible when it should be.
When the help desk forwards an ORA-02292 – child record found error or if we get one during a data spill cleanup, unlike some who grumble and complain, I just thank goodness for the FK. Because someone is attempting something that a business rule doesn’t allow and hasn’t thought through the implications. Without the FK in the database, the operation would have succeeded and caused a data integrity issue. No, the child records need to be removed as well or no, the parent record needs to be logically deleted instead or no, you simply can’t now perform that operation due to the live data in the system, etc. The FKs help to protect us from ourselves, be it at the
application level or indeed via any accesses to the database.
Without FKs, one is blind to the relationships and dependencies between objects within the database. The application is not a pretty place to search and neither is the documentation, assuming it exists and is up to date.
Not only is the DBA and others “blind” to these relationships if the FKs are not enabled in the database, but so is the CBO. A point you’ve missed a little in the paper is the performance implications of the CBO not being able to make important assumptions on the data relationships if FKs are missing. It’s not a good idea to hide information from the CBO, it reduces its options and limits its decision making processes. It can’t for example determine and ignore redundant join conditions, it can’t make use of query rewrite opportunities, it can’t assume it can use an existing index or use an efficient join method, etc. And the CBO gets cleverer and can make use of database constraint information more often with each new release. Without enabled FKs, the CBO can’t use this information and might make sub-optimal decisions as a result.
At the end of the day, I agree with your statement that we should use FKs when they “make sense”. It’s just IMHO, they make sense far far more often than you appear to suggest. I work with applications without FKs in the database, it isn’t pretty !!”
Marcel has started a Foreign Key Discussion website where you can find his paper and make comments on the various subject matters discussed. I’m sure Marcel would welcome any such comments and feedback.
The CBO and Indexes: OPTIMIZER_INDEX_COST_ADJ Part III August 20, 2009
Posted by Richard Foote in Index Access Path, OPTIMIZER_INDEX_COST_ADJ, Oracle Indexes.7 comments
After a bit of a layoff to organise a few upcoming overseas trips, while watching plenty of Ashes Cricket and the brilliantly funny ”Flight Of The Conchords” DVDs, it’s about time I got back to my humble little blog.
In Part II, we looked at a really bad way to set the optimizer_index_cost_adj parameter, by just setting it a really low value and allow indexes to blindly reign supreme in the database.
Remember, the purpose of the optimizer_index_cost_adj parameter is to accurately reflect differences and discrepancies in costs associated with single block I/Os when compared with corresponding multi-block I/Os so that the CBO considers and incorporates these discrepancies in its costings.
A second method of setting the optimizer_index_cost_adj parameter is to set it to a value that attempts to accurately reflect these comparative costs. So if a single block I/O is typically only half as expensive and/or only takes half the time to complete when compared to a multi-block I/O, then a reasonable setting for the optimizer_index_cost_adj parameter would be 50.
So how to set the optimizer_index_cost_adj parameter “intelligently” ?
Well, Oracle has excellent instrumentation and the comparative wait times for each of these types of I/Os are automatically measured and captured by Oracle. A single block I/O as performed typically by an index range scan is measured via the “db file sequential read” wait event while the multi-block I/O as typically performed during a FTS is measured via the “db file scattered read” wait event.
By determining the average wait times for each of these events and comparing the differences, one can determine how much longer it takes on average for one type of I/O to complete versus the other. This will then provide us with a reasonable starting point with which to set the optimizer_index_cost_adj parameter.
One can simply look at these average wait events for the database since startup by querying v$system_event:
SQL> select event, average_wait from v$system_event where event like ‘db file s%read’;
EVENT AVERAGE_WAIT ----------------------- ------------ db file sequential read .59 db file scattered read .78
In order to determine these wait events during a specific time period to perhaps better reflect typical loads during these times, one could also simply run a statspack or an AWR report and look at the wait event section of the report.
So in the above example, a “sequential” read only takes approximately 75% of the time when compared to a “scattered” read. As such, a value of 75 would be an appropriate starting value with which to set the optimizer_index_cost_adj parameter.
With the I/O costing model, the CBO is basing it’s costs on the number of I/Os performed by each possible access path. If an index is only going to take 75% of the time to perform it’s associated I/Os when compared to the time it takes to typical perform I/Os during a FTS, it’s reasonable to adjust the associated costs of an index access down to 75% of its overall costs.
This will hopefully have the desired effect of making it a “level playing field” between an index based access path and a FTS when determining how long all the I/Os associated with each possible execution path might take.
If we plug a value of 75 into the optimizer_index_cost_adj parameter and re-run the demo in Part Iwhere the CBO initially choose the more expensive FTS which had a cost of 65:
SQL> alter session set optimizer_index_cost_adj=75;
Session altered.
SQL> select * from bowie_stuff2 where id in (20, 30, 40, 50, 60);
10000 rows selected.
Execution Plan
———————————————————-
Plan hash value: 2964430066
——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost |
——————————————————————————-
| 0 | SELECT STATEMENT | | 10000 | 175K| 60 |
| 1 | INLIST ITERATOR | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| BOWIE_STUFF2 | 10000 | 175K| 60 |
|* 3 | INDEX RANGE SCAN | BOWIE_STUFF2_I | 10000 | | 27 |
——————————————————————————-
We note the CBO is now choosing to use the index, which is the more appropriate plan as it provides a somewhat faster response that the previous FTS.
However, if we also re-run the demo from Part IIwith the optimizer_index_cost_adj also set to 75, where previously Oracle initially choose to use a FTS quite correctly:
SQL> alter session set optimizer_index_cost_adj=75;
Session altered.
SQL> select * from bowie where id between 1 and 1000;
1000873 rows selected.
Execution Plan
———————————————————-
Plan hash value: 1845943507
———————————————————–
| Id | Operation | Name | Rows | Bytes | Cost |
———————————————————–
| 0 | SELECT STATEMENT | | 1000K| 69M| 16499 |
|* 1 | TABLE ACCESS FULL| BOWIE | 1000K| 69M| 16499 |
———————————————————–
We note that the FTS is still selected as the change in the CBO index related costs were not significant enough to change the execution plan. A really low value of 2 for the optimizer_index_cost_adj parameter really stuffed things up previously, but a more appropriate value of 75 in this database has ensured that the FTS is still chosen when appropriate.
So in both scenarios, the CBO is now choosing an appropriate execution plan. By setting the optimizer_index_cost_adj parameter in a logical manner, consistent with the relative wait time differences between single and mutli-block I/Os, the CBO is more likely to choose appropriate execution plans.
Of course, there are always likely to be some discrepancies when dealing with such “averages”. We only have the one parameter after all which impacts the costs of all index range scan access paths, so we can only deal with averages. Perhaps there are some specific indexes which take significantly more (or less) time to complete than the average, as their associated I/Os are impacted by where the blocks might physically sit on the disk arrays, or on contention issues due to other concurrent activity, or on index caching characteristics (Note: I’ll discuss the optimizing_index_caching parameter at another time), etc. etc.
Same for some specific FTS which have multi-block I/Os that take significantly less (or more) time to complete than the average, as it’s associated I/Os might be also be impacted by similar factors. Perhaps some of these I/O characteristics and timings might change depending on the load on the system at different times of the day or week or month.
But that’s what an “average” value means right, some objects will have a higher (or slower) value while some have a lower (or faster) value.
So setting the optimizer_index_cost_adj parameter is not a precise science although of course the CBO in general is not a precise science either and close enough is usually good enough for the vast majority of cases. The name of the game is ensuring that the parameter is set to a value that’s in the “ballpark” and using the associated wait events to determine comparative wait times for single and multi-block I/Os is a reasonable way to do this.
However, despite being able to set the optimizer_index_cost_adj parameter in a reasonably “intelligent” manner, my preferred method of setting this parameter is still method number 3. That is to simply not set the optimizer_index_cost_adj parameter at all and leave it at the default value of 100 and use system statistics and the CBO CPU costing model instead.
By generating and maintaining accurate system statistics, you can effectively get the desired “level playing field” benefits of a well tuned optimizer_index_cost_adj parameter in a somewhat easier manner but with a few other added benefits as well. I would therefore strongly recommend the use and implementation of system statistics and leave the optimizer_index_cost_adj parameter well alone. IMHO, the optimizer_index_cost_adj parameter is there now only for backward compatibility reasons since the introduction of the CBO CPU costing model.
However, these discussions have not all been in vain because the optimizer_index_cost_adj parameter still has an impact even with system statistics in place. It’s just that the use of the optimizer_index_cost_adj parameter in conjunction with system statistics typically has the effect of screwing up the “level playing field” environment system statistics is meant to create.
Also, the costing formulas for indexes as previously discussed are still very much relevant as the CPU costing model often has little impact on the actual costs associated with using indexes. As I’ll discuss later, system statistics actually achieves a very similar outcome to the optimizer_index_cost_adj parameter. It’s just that it does so in a somewhat different manner by generally increasing the associated FTS costings to a more appropriate comparative value, rather than simply decreasing the index related costs, while taking both I/O and CPU overheads into consideration.
