I was recently watching this SQL “Tune-Off” webinar that Jonathan Lewis and Kyle Hailey put together a few years back. As part of this webinar, Jonathan briefly mentions a script he has for looking at the number of keys that exist per leaf block of an index. The idea being that if the majority of… Read More »
In the last post we took a look at how to get execution plans using DBMS_XPLAN.DISPLAY_CURSOR. In the post before that we looked at how to get the estimated execution plan using DBMS_XPLAN.DISPLAY. I mentioned that DISPLAY_CURSOR allowed you to get additional statistics, so let’s take a look in more detail. We’ll start with some… Read More »
I always enjoy learning new things.. especially when it is something that comes as a complete surprise. A developer was updating some code from using literals to instead use bind variables. So far, fantastic! However, during testing, the code no longer worked as expected. Before, when using literals, it returned rows. Now that it used… Read More »
In the last post, we looked at the DBMS_XPLAN package and the DISPLAY function. Now we will start to look at my much preferred DISPLAY_CURSOR function. DBMS_XPLAN.DISPLAY_CURSOR – The DISPLAY_CURSOR function has been around since 10g. It is a table function that displays the execution plans for any cursor that is currently in the cursor… Read More »
If you are following along from Part 0, at this point we have found a query that we need to tune, as we have found that we need to get a 43 second query down to 3 seconds to meet the requirements given. So where do we start? The first thing I like to do… Read More »
The first step in any tuning project should be obvious, but is often overlooked. You need to clearly understand the problem as well as the desired result. More specifically, the problem and desired result need to be defined from the perspective of an end user and defined by wall time. Anything else will lead to… Read More »
I have recently been doing presentations about SQL tuning, but haven’t really written anything on the topic here. Therefore, I have decided to put together a series of posts over the next few weeks on the process I use for tuning SQL. I plan to cover: – Viewing and interpreting the current execution plan for… Read More »
I have finally caught up on my backlog of blog posts I have been meaning to read, and am quite glad I did. One of the posts I ran across was by the Oracle Optimizer team about SQL Patching. This allows you to have Oracle apply a hint to a query without having to modify… Read More »
I ran across this post today with good information about some differences in how to reset a password in 11g using the hash values, and didn’t want to lose it: http://blog.flimatech.com/2011/07/17/changing-oracle-password-in-11g-using-alter-user-identified-by-values/ Short and to the point 🙂
I have just started looking into SQL Plan Management, so the next few posts will probably be focused around it. As a quick background, SQL Plan Management lets you “lock in” a plan for Oracle to use for a specific SQL statement. This way when stats change, or data is added, Oracle will continue to… Read More »