Kaley's (mostly) Oracle Database Blog
Hi! I'm Kaley Crum, Oracle DBA.
Welcome to my (mostly) Oracle database blog.

Author: Kaley

KC Database Blog Ending

Well. Sort of.

Hello friends and followers!

Looks out across an empty auditorium, crickets chirping.

So I have good news and bad news.

Bad news is that my KC Database blog is going away.

Well, sort of!

I’ve actually started another blog  (blog.tuningsql.com).  And all my blog entries from here (kcdatabase.com) are likely going to be migrated over there (blog.tuningsql.com).

So the same content should be available, with some changes.

It should be all good changes, though!

  • I plan to blog more frequently, and take blogging more seriously.
  • I plan to increase the number of blog entries towards explaining how things in Oracle work, Oracle troubleshooting/problem solving, new database features, and shiny scripts and queries.
  • I plan to do fewer “useless scripts”….things like trying to write a query that will generate fibonnaci sequences using the model clause, or calculate the golden ratio using recursive CTEs…random theoretical stuff that few people likely care about.
  • I plan to make my blog more “findable” by google and actually pay attention to SEO stuff.
  • The new blog should have better performance in your browser than the old blog.

All good changes I think.

The new site is actually a serious blog and I’m going to try to put work towards it close to every day.

I won’t work on it every day.  But I’ll do work on it a lot more frequently than what I do here.

And btw you won’t see a new blog entry every day I’m working on it–for example I may take 3 different days to write 1 entry.

But I will try to dedicate a serious amount of time to it each day (this won’t always happen because life is unpredictable but I’ll do what I can).

SO!  If a useful blog entry goes missing from here, search for it over on blog.tuningsql.com.  It was probably migrated.

As for the kcdatabase.com domain? I’m not sure what to do with it really.  Eventually I may have it just point to the new blog.  I may allow it to expire.  Not really sure. :/

Check out the new blog! I’m pretty proud of it already, and it’s just in its infancy!!

 

SQL Tuning: Join Order, and Selectivity

When tuning SQL, one of the first things I like to check in an execution plan is to verify that I have the correct join order.

Join Order is considered by many to be one of the fastest and most reliable ways to tune SQL.  Dan Tow, one of the pioneering innovators in SQL tuning wrote a book that is considered by many to be the foundational book on tuning SQL–the entire book focuses on join order.  The main concept that Tow conveys in his book is you want to start with your most filtered table first.  Allow me to explain a peculiar situation.  I have the following query:

Continue Reading

Migration!

I’ve migrated to DigitalOcean for a significantly cheaper hosting experience.  Having said this, I’m still in the process of getting all the bugs worked out.  If pages disappear or things don’t work, know that I’m working on it.  Please be patient with me.

Costing a Full Table Scan – Adjusted Multiblock Read

Reading in Johnathan Lewis’ book, or on Martin Widlake’s blog, they both talk about “adjusted” or “modified” multi-block read counts.

What is this number?  Where does it come from?

Basically, what’s previously been said about this number is that Oracle “adjusts” the speed of your multi-block read count to compensate for time differences.

Continue Reading

Access and Filter – Is There an Echo in Here? (Why did Oracle Repeat the Same Filter Twice?)

There are lots of resources that will tell you about the “access” and “filter” lines you see in the Predicates section of your execution plan.  Most resources will tell you…”access is more efficient than filter.” And, generally, that’s true.  Access tells us where to go in our index (thereby eliminating rows before we reach them) whereas filter tells us once we’ve arrived at a row that “it’s no good and must be discarded.”

But you may have noticed times when there seems to be and echo in your plan, and it repeats the same predicate filter in both the FILTER and ACCESS section of your execution plan.  What then?  Why does Oracle need both?  Shouldn’t just one be enough?

Continue Reading

Quickly Creating a Billion-Row Test Table

Lots of times, when I’m testing theories, I need a large test table to see how things scale.  Here’s a script that will quickly create such a table with a primary key/unique index, along with a bitmap index.  The table has 10 partitions (this is necessary to allow 10 concurrent “append” inserts).  DBMS_PARALLEL_EXECUTE is used to build the table in parallel, and DBMS_PCLXUTIL is used to build the bitmap index in parallel.

Continue Reading

Nested Loops and Join Order

There’s actually a lot of subtleties to nested loops.  When they’re explained, they’re most commonly represented in the following fashion:

But!  That’s clearly overly simplified.

For example, if the Oracle code looked like that, then join order clearly wouldn’t matter for nested loops if both tables were being full scanned.  But, as you’ll see shortly, join order matters a lot!

Continue Reading

Parallel Inserts and Direct Path

Here’s a fuzzy memory of mine: I thought I remembered reading where Tom Kyte once said “all parallel inserts are direct-path inserts.”

I don’t remember where I read it (I can’t even remember if that’s what was said, or if Tom said it).  But today I wanted to see if that was true by playing around with APPEND and PARALLEL hints.

We have two different “places” we can hint.

  • We can hint the insert.
    insert /*+ hint goes here */ into ...
  • We can hint the select.
    select /*+ hint_goes_here */ ...

There are 4 different hints that can be placed in the insert (append, noappend, parallel, no_parallel).

There are 2 different hints that can be placed in the select clause (parallel, no_parallel).

Additionally, you can alter your session 3 different ways  (enable parallel DML, disable parallel DML, force parallel DML).

So the number of possible scenarios we can make with all this mess:

  • 3 possible ways to alter session
  • 3 possible ways to hint the select (parallel, no_parallel, or no hint at all)
  • 8 intelligent ways to hint the insert (no hint, append, parallel, noappend, noparallel, append + parallel, noappend + parallel, noappend + noparallel).
    I specify “intelligent” because /*+ append noappend */  doesn’t make sense; neither does /*+ parallel noparallel */

8 x 3 x 3 = 72 different possible combinations.  I don’t know that it makes sense to try them all, but I’ll highlight some points of interest.

Continue Reading