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 »
Day 3 – This was the final day of the normal sessions, and it finished strong. I went to sessions by Alex Fatkulin, Bryn Llewellyn, Kellyn Pot’Vin, Maria Colgan, and Kerry Osborne. I was impressed with all of the speakers, which was no surprise given the people presenting. I definitely enjoyed Bryn’s talk. I really… Read More »
Day 2 Just finished Day 2 of Hotsos Symposium presentations. It has been a great event so far, like always with great content. My presentation was today as well. The room was packed (due to the topic, not the speaker 😉 … SQL tuning always pulls a crowd), so thanks to everyone who came, and… 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 »
For another year, I didn’t do very well on my goals. Again, I didn’t get through the books I wanted to or get my certification. (This is starting to sound too familiar) But again, I would also consider my year quite a success. I have been presenting much more frequently, and got accepted to present… 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 »