The internet is full of information about indexes, and for a reason. Indexes in a database is probably the most important performance related topic. There are so many cases, properties, and different ways to use indexes that there is simply a lot to write about. In this post I’d like to talk about a specific use case that I’ve seen a few times, and is related to index scans and performance.
As you know, Oracle Open World is over (for me at least), and one of the major topics there was Oracle 12cR2 (or 12.2). It looks like a really cool version, the downside is that is currently available on Oracle cloud only. We are all waiting for the on-premise release so we can install and play with it.
There are few ways to see the execution plan of a SQL statement. One of these ways is the autotrace option in sqlplus. It is a very easy-to-use feature and people use it quite often. But there is a risk here. The autotrace option doesn’t always show you the correct execution plan.
Lately I prepared a demo and had a simple case that showed the incorrect info from autotrace, so here it is.
My previous post that showed a real life troubleshooting was a hit. I guess people like to hear these kind of things, so I decided to write another post, this time a real life SQL tuning.
People ask me quite a lot about how I analyze performance issues. While I have some guidelines, there is no complete answer to this question. The actions I take depend on the situation, and there are many many different scenarios when talking about performance problems. Continue reading
Over the years I ran into many Oracle bug related to the optimizer (the last one was yesterday with join push predicates). Many times this is related to Optimizer new features or new behavior and can be disabled using a hidden parameter (in the case from yesterday, the “_optimizer_push_pred_cost_based” parameter). Continue reading