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

Advertisements

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

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

Parse Order and Analysis

Over the years I’ve heard and learned quite a lot about how Oracle does stuff. Some of it was logical, some just details to remember (I have a really lousy memory, but somehow, I actually remember some of the details). Not too long ago I wrote a query and got a parse error, and that lead me to write this post (and a couple more that will follow).

Continue reading