During a performance problem I had with a query I wanted to create an index on the table. Since this was a large table I wanted the index to include all the needed columns so I won’t need to access the table at all. The query also used GROUP BY and MAX, so I thought a descending index would be best, as it will easier for Oracle to find the highest value (as it will be the first one).
Here is an interesting optimizer case where updated statistics and histograms cannot solve the performance problem. This might be an uncommon case, but it happened for one of my clients and this post is the result of some research on this.
I had a bad night last Thursday.
After patching two test RAC databases and one production RAC with 180417 DB Bundle Patch (and some one-offs), I got to the point where it was time to update the most critical RAC system.
We were really looking forward to this as we had hit a few bugs that this DBBP and one-offs should fix. But boy, did that go wrong…
I just came back from RMOUG Training Days conference. It was my first time in Colorado (and obviously my first RMOUG training day) and it was really great (I wrote about it in another post).
During my second session (From 4 Minutes to 8 Seconds – about a real SQL tuning case I had quite a few years ago), I mentioned that one thing that I usually do when I see a query and need to analyze it, is to take a piece of paper and draw the tables and relations between them. When I later look at the execution plan and try to understand what Oracle does, it helps a lot if I know the structure of the tables. There is a big difference between queries built like a “star” (a single table in the middle, while the others are joined to it) or a “line” (each table is joined to the next one), or any other structure.
In the previous post I talked about the order of predicate execution based on the predicate position and inline view.
As promised, in this post I’ll add statistics and see what happens.
In my previous post, I wrote about the parsing operation and what happens first. In the footnote I said that the order doesn’t affect performance, the cost based optimizer doesn’t care about the order of stuff in the query, right? Well, not quite.