Oracle Challenge #3 – SQL

I debated quite a lot before writing this post. When I wrote the post about interviewing a DBA, in the “technical questions I do ask” part I just gave a general explanation of what I ask, but didn’t reveal the real questions. Now, more than 3 years later, I decided to give one of the questions as a challenge here.

Continue reading “Oracle Challenge #3 – SQL”

V$LOCK Missing Information

In our first BCOUG Tech Day conference, I presented my session “Look Inside the Locking Mechanism”. I presented this topics before a few times and prepared a few demos to show different locking scenarios.

During the BCOUG Tech Day I did the same, while the only difference was that for the demo I used Oracle 12.2 PDB (I think in previous times I always used 11.2). During one of the demos I noticed something strange.

Continue reading “V$LOCK Missing Information”

Tool to Assist with Basic SQL Analysis

I just came back from RMOUG Training Days conference. It was my first time in Colorado (and obviously my first RMOUG training day) and it was really great (I wrote about it in another post).

During my second session (From 4 Minutes to 8 Seconds – about a real SQL tuning case I had quite a few years ago), I mentioned that one thing that I usually do when I see a query and need to analyze it, is to take a piece of paper and draw the tables and relations between them. When I later look at the execution plan and try to understand what Oracle does, it helps a lot if I know the structure of the tables. There is a big difference between queries built like a “star” (a single table in the middle, while the others are joined to it) or a “line” (each table is joined to the next one), or any other structure.

Continue reading “Tool to Assist with Basic SQL Analysis”

Importing Into Existing Tables

I did some testing with impdp for a client. They asked me to write a procedure to import a set of tables from a production environment to a testing database. The import will include only a few tables, but not all, and will be performed to an already existing test environment that contains a full schema. And this process should be performed on a regular basis, so I looked for the best and easiest solution.

Continue reading “Importing Into Existing Tables”

Order of Predicate Execution #1

In my previous post, I wrote about the parsing operation and what happens first. In the footnote I said that the order doesn’t affect performance, the cost based optimizer doesn’t care about the order of stuff in the query, right? Well, not quite.

Continue reading “Order of Predicate Execution #1”