Category Archives: SQL

Index Health Check

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 »

SQL Tuning – Part 1.3 – DISPLAY vs. DISPLAY_CURSOR

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 »

“Fun” with CHAR fields

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 »

SQL Tuning – Part 1.2 – Intro to DBMS_XPLAN.DISPLAY_CURSOR

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 »

SQL Tuning – Part 0 – Getting started

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 »

SQL Tuning – Intro

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 »

SQL Patching

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 »

Resetting passwords on 11g to existing pw

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: Short and to the point 🙂

SQL Plan Management – Adding a desired plan

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 »