I came across another example today where I was positive I knew how something worked, but was proven wrong. This is why it is so critical to be able to make quality test cases. This specific case had to do with definer rights in PL/SQL. Let me set up an example to explain. First as… Read More »
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 finally got around to getting a few pictures I took during my time at GLOC uploaded so I thought I would share. First, the hotel that the conference had a discount for was the Hyatt Regency Cleveland at The Arcade. Now, typically I don’t care much about what hotel I stay in, but this… Read More »
The conference was a great event. I learned about several new things, and now I have some homework to do to get further into the details. My favorite sessions were the 2 from Carlos Sierra on SQLT and Adaptive Cursor Sharing as well as Craig Shallahamer’s session on Child Cursors. It was great to hear… Read More »