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

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

ROWID – What are the first 6 bytes?

Quick experiment: As mentioned in a previous post, the an Oracle Physical ROWID is 18 bytes laid out in the following manner:

  • Letters 1-6 = the OBJECT_ID
  • Letters 7-9 = the relative FILE_ID
  • Letters 10-15 = the BLOCK within the file
  • Letters 16-18 = the ROW within the block

But!  There are TWO different object ID’s associated with a table…one for the table itself, another for the table segment ( select object_id, data_object_id from dba_objects ).

So which one does a ROWID point to?

Continue Reading

Are You Getting “Real” Read Consistency in Oracle updates? (Part II)

Earlier, I ran an experiment to see if we got what I considered to be true read consistency on a table.  Initially, admittedly, I didn’t think it was possible without first locking the entire table.  And I still believe that’s the case with updates I envisioned them initially.

I was, however, thrown for a loop when I discovered the SCN of the update that was being performed was after the SCN of an update that had been started after the update, which made me devise the theory that if an update reaches a row that has been updated since the start of the statement, the read consistency of the row is foiled and the query has to start back at the beginning to achieve true read-consistency.

Today, I’ll prove that theory either true or false.

Continue Reading

Are You Getting “Real” Read Consistency in Oracle Updates? (Part I)

I read one of Tom Kyte’s books awhile back, and he mentioned that in Oracle, everything is done with read consistency.  Everything from SELECTs, INSERTs, UPDATEs, to DELETEs…the whole 9 yards is done with a read consistency that looks at what the table looked like at the beginning of the query.  But is this true for update statements, where you have to both read and modify the data?

Continue Reading

DBMS_PARALLEL_EXECUTE Example

You may know that pulling data over a database link is serial, which is a great opportunity to take advantage of DBMS_PARALLEL_EXECUTE.

Here’s an example of how to pull something over a database link in parallel.

 

 

Oracle Java Procedure Example

I had a book tell  me that Java procedures couldn’t be scheduled using DBMS_SCHEDULER, which I didn’t buy because I had just read in another book that you *could* schedule Java procedures using DBMS_SCHEDULER.  I tried it for my self…turns out, you can, in fact, schedule Java procedures.  Here’s the proof (from Oracle 11.2).