Top 60 Oracle Blogs

Recent comments

August 2013

12c join defect

This is a brief, temporary, note to draw attention to an item I wrote about 3 years ago demonstrating an anomaly with the plan Oracle could produce if the order of predicates in your where clause changed. I’ve just added a note to the original article to point out that the anomaly is still present in 12c.

TCP Trace Analysis for NFS

How do we know where latency comes from when  there is a disparity in reported I/O latency on  the I/O subsystem and that of the latency reported on the  client box requesting the I/O.

For example if I have an Oracle database requesting I/O  and Oracle says an 8Kb request takes 50 ms yet the I/O storage subsystem says 8Kb I/Os are taking 1ms (averages) , then where does the 49  extra ms come from?

When the I/O subsystem is connected to Oracle via NFS  then there are a lot of layers that could be causing the extra latency.

Screen Shot 2013-08-23 at 1.35.20 PM

Where does the difference in latency come from between NFS Server and Oracle’s timing of pread?

Oracle time units in V$ views

Oracle has a crazy mix of units of time in various v$ views

  • seconds
  • centi-seconds
  • milliseconds
  • microseconds

Some are straight forward such as time_waited_micro, but what unit is “TIME_WAITED”  or “WAIT_TIME” in? For example

WAIT_TIME -  centi

WAIT_TIME – centi




Latency Heat Maps in SQL*Plus

This is so cool !

Screen Shot 2013-05-10 at 1.13.15 PM

The above is so cool.

The graphic shows the latency heatmap of “log file sync” on Oracle displayed in SQL*Plus! SQL*Plus ?! Yes, the age old text interface to Oracle showing colored graphics.

How did I do this? All I did was type

Fast refresh of aggregate-only materialized views with MAX – algorithm

In this post I will illustrate the algorithm used by Oracle (in to fast refresh a materialized view (MV) containing only the MAX aggregate function:

create materialized view test_mv
build immediate
refresh fast on demand
with rowid
select gby        as mv_gby,
       count(*)   as mv_cnt_star,
       max  (dat) as mv_max_dat
  from test_master
 --where whe = 0
 group by gby

The where clause is commented to enable fast refresh whatever type of DML occurs on the master table, in order to investigate all possible scenarios; the case having the where-clause is anywhere a sub-case of the former and we will illustrate it as well below.

As usual, the MV log is configured to "log everything":

Recovering a hacked wordpress site

A friend’s wordpress site just got hacked, so reposting this info which I have found useful a number of times.

OK,  so wordpress got hacked. I’ve had problems with this in the past and tried tactical surgery, but this time decided to do a full re-install. My first attempt today left me with the wordpress blank screen of death, so here I’m outlining the steps I took that finally got the new version working:

Oracle 12c Multitenant Option : CDBs and PDBs…

I started trying to play with the Oracle multitenant option (all that pluggable database stuff) a little while ago and gave up. It’s wasn’t that it was that difficult. More than anything my problem was I didn’t know what to focus on first. There is so much to it and it’s all interrelated, so you start to write about one small piece and before you know it your article has lost focus and is growing too big. As a result I decided to let it simmer in the background and start looking at some of the other smaller 12c new features first…

My next stumbling block was just about everything in 12c seems to relate back to pluggable databases in some way. So you either pretend it doesn’t exist and have gaping wholes in everything you write, or you have to bite the bullet and get to grips with pluggable databases. So back I came to pluggable databases…

Scalar Subqueries in Oracle SQL WHERE clauses (and a little bit of Exadata stuff too)

My previous post was about Oracle 12c SQL Scalar Subquery transformations. Actually I need to clarify its scope a bit: the previous post was about scalar subqueries inside a SELECT projection list only (meaning that for populating a field in the query resultset, a subquery gets executed once for each row returned back to the caller, instead of returning a “real” column value passed up from a child rowsource).

I did not cover an other use case in my previous post – it is possible to use scalar subqueries also in the WHERE clause, for filtering the resultset, so let’s see what happens in this case too!

SQL joins visualized in a surprising way

Saw a good posting on SQL joins today that echoes a classic image of SQL joins:


I loved this graphic when I first saw it. Seeing the graphic made me think “wow, I can actually wrap my mind around these crazy SQL joins.”

But there is more to SQL joins than meets the eye, at least in these pictures. These pictures leave out the effects of projection and amplification. For example, just taking the simplest case of a two table join (an inner join):

Creating a 12c Container Database from scripts

If you are curious how to create a CDB without the help of dbca then the “generate scripts” option is exactly the right approach! I am a great fan of creating databases with the required options only-the default template (General Purpose) is dangerous as it creates a database with options you may not be licensed for and additionally opens security risk.

The best^H^H^Heasiest way to understand how a Container Database (CDB from now on) is created is to let dbca create the scripts. The process is the same as with an interactive installation except that at the very end you do NOT create the database but tick the box to generate the scripts.

The resulting scripts will be created in $ORACLE_BASE/admin/${ORACLE_SID}/scripts. Change directory to this location and you will be surprised about the sheer number of files ending in *.sql