Last year I wrote a few articles for Simpletalk, a web service created by Redgate for users of SQL Server. This year, Redgate is setting up a similar service called “All things Oracle” (I’ve added a link in my blogroll) for Oracle users, and I’ve volunteered to write articles for them occasionally.
Some of the stuff they publish will be complete articles on their website, some will be short introductions with links to the authors’ own websites. My first article for them has just been posted – it’s an article that captures a couple of key points from the optimizer presentation I did at the UKOUG conference a couple of weeks ago.
In our application we extensively use a function-based index on an important table. Couple of days ago I’ve seen an interesting issue associated with this FBI, view and a GROUP BY query. I have to say I don’t have an explanation what exactly it is and how I should call it properly, hence just “trouble” in the subject line.
Here are a few thoughts on dbms_stats – in particular the procedure gather_index_stats.
The procedure counts the number of used leaf blocks and the number of distinct keys using a count distinct operation, which means you get an expensive aggregation operation when you gather stats on a large index. It would be nice efficiency feature if Oracle changed the code to use the new Approximate NDV mechanism for these counts.
I have always asked myself why Oracle doesn’t package their software as an RPM-surely such a large organisation has the resources to do so!
Well the short answer is they don’t give you an RPM, except for the XE version of the database which prompted me to do it myself. The big problem anyone faces with RPM is that the format doesn’t seem to support files larger than 2GB. Everybody knows that the Oracle database installation is > 2G which requires a little trick on our side. And the trick is not even obscure in any way as I remembered: some time ago I read an interesting article written by Frits Hoogland about cloning Oracle homes. It’s still very relevant and can be found here:
Now that gave me the idea:
This blog post covers day 0 of UKOUG 2011 — Sunday, 4th of December, 2011. Since there were so many of us from Pythian at the conference, I’m adding my name in the blog post title. I think I will be doing it for all conference posts as I think I’ve been doing for some [...]
In a recent question on OTN someone asked why Oracle had put some columns into the overflow segment of an IOT when they had specified that they should be in the main index section (the “IOT_TOP”) by using the including clause.
The answer is simple and devious; there’s a little trap hidden in the including clause. It tells Oracle which columns to include, but it gets applied only after Oracle has re-arranged the column ordering (internally) to put the primary key columns first. The OP had put the last column of the primary key AFTER the dozen columns in the table that he wanted in the index section, but Oracle moved that column to the fifth position in the internal table definition, so didn’t include the desired 10 extra columns.
I was ribbing Cary about missing his unconference session (due to migraine and alarm malfunction). Lenz Grimmer and I both tweeted him to see where he was and in return we received this message in reply.
Very generous indeed. Unfortunately Lenz was flying home so he couldn’t make it. I had a conversation with Cary about it saying I wanted to come, but felt weird about accepting a freebie, so I thanked him for the offer and left it at that.
I went to lots of good presentations over the three days.
My recent post entitled Recent SPARC T4-4 TPC-H Benchmark Results. Proving Bandwidth! But What Storage? provoked the following comment/question from a reader:
Does this summarize your point(s)?
TPC-H produces a number which is a reflection of (hourly?!?) system throughput.
System throughput may not be indicative of system “performance” to its uses b/c users are typically most intersted in response time. Thus, TPC-H is a easily mis-used benchmark for comparing real world performance.
Here’s a lovely Candy Striped pattern in an Explain Plan. Looks like the traditional Christmas candy canes. Just in time for the Holiday Season!