First of all, you want to ensure that you have the tuning and diagnostic pack for your database–if you do not have the tuning and diagnostic pack, Oracle does not authorize you to create SQL Monitor Reports. Important Information About… Read More
How to Tell If Statistics are Stale in Oracle
There are two ways to tell if statistics are stale in Oracle. One way is to allow Oracle to tell you if it thinks the statistics are stale. The other way is to compare the statistics of what Oracle thinks… Read More
Index Range Scan vs. Leading Wildcard
Can you use an index range scan with a LIKE if you have a leading wildcard in your expression? e.g. Most people would say “no” but, I actually found a way to “trick” Oracle into doing so using bind variables,… Read More
The *LOWEST* Possible Cost For a Query
Quick: If you had to pick the least-expensive-cost query you could think of, what would that query look like? …(hums Jeopardy theme)…. Ding! Time’s up!! Most people respond with something “select from a one-block table.” Let’s give it a shot:… Read More
Are Single-Table Hash Clusters USELESS?!?
I remember being at an Oracle conference a few years ago, and I attended a session that was related to SQL Tuning. One of the things that was discussed in this lecture was Oracle Single Table Hash Clusters, and I… Read More
Calculating Scalar Subquery Cache Size
Whenever you have a scalar subquery in Oracle, there are a few different options that Oracle can use for executing the subquery. Oracle can either run the query once for each row returned by the records in the from clause.… Read More
How to Accidentally “Untune” a Query
Suppose one day that you’re at your desk, when you get a telephone call from someone complaining that a query is running slow. You’re given the following setup: And the query that you’re trying to tune looks like this: If… Read More
Filters in the JOIN Clause vs. WHERE Clause: Does It Matter?
Here’s a question I’ve been asked multiple times from multiple people: “Does it matter I put filters in the join clause vs in the WHERE clause? And if so, which one is better?” Example: vs. So does it matter? Answer:… Read More
When to Use APPROX_COUNT_DISTINCT
Since Oracle 12.1, users have had the ability to use the APPROX_COUNT_DISTINCT() function. This function is supposed to be significantly faster than a COUNT(DISTINCT …). Does it really offer that big of a payout? Let’s see: First, let’s make a… Read More
Over-The-Fence Tuning
One of the best tips on SQL Tuning I can offer you is this: DO NOT DO OVER-THE-FENCE TUNING. It’s taken me years to finally figure this out. What do I mean by over-the-fence SQL Tuning? It goes like this:… Read More