Top 60 Oracle Blogs

Recent comments

February 2013

VirtualBox 4.2.8…

I just noticed VirtualBox 4.2.8 has been born. The downloads and changelog are in the usual places.

Happy upgrading.



VirtualBox 4.2.8… was first posted on February 28, 2013 at 9:23 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

"Cost-free" joins - 1

Recently I came across some interesting edge cases regarding the costing of joins. They all have in common that they result in (at first sight) unexpected execution plans, but only some of them are actual threats to performance.

Outer Joins

The first one is about outer joins with an extreme data distribution. Consider the following data setup:

create table t1
rownum as id
, rpad('x', 100) as filler
, case when rownum > 1e6 then rownum end as null_fk
connect by
level <= 1e6

exec dbms_stats.gather_table_stats(null, 't1')

create table t2
rownum as id
, rpad('x', 100) as filler
connect by
level <= 1e6

Presentation Updates

Those pesky technical posts are so much more difficult to write, particularly when you realise you're about to confuse two different deadlock issues in the one post! But they're coming soon ... I promise! In the meantime, I should mention some upcoming presentations.


Last week I attended the Rocky Mountain Oracle User’s Group Training Days Conference in Denver, Colorado. RMOUG TD is one of the largest independent Oracle User’s Groups for Oracle database technology and draws a large number of Oracle Aces and Ace Directors, Oracle OakTable members and senior Oracle corporate technology leaders.

On Monday I attended a session by Cary Millsap from Method-R Corporation. In this full day session, Cary talked about how to use Oracle’s trace files to accurately diagnose performance problems related to specific business transactions by profiling the execution of database activity at the detailed call level. Using a variety of tools, Cary sliced and diced Oracle trace files to pinpoint problems related to disk, network and cpu performance for a variety of business transactions. Cary’s company, Method R, develops tools that make it easy to analyze trace files both from the command line as well as from within Oracle SQL Developer.

In-memory PQ and physical reads

In my previous post I've demonstrated how in-memory PQ can access the table directly from the buffer cache even when you're using manual DOP.

One interesting question, however, is what happens when PQ slave needs to read some blocks from disk given that object has been qualified for in-memory (cached) access? Would the slave do it using direct or buffered I/O?

The answer becomes somewhat clear once you realize that in-memory PQ is enabled by simply utilizing buffered reads. Since direct path reads can not take advantage of any blocks stored in the buffer cache (local or remote), trying to do direct path reads will defeat the whole point of in-memory PQ.

To demonstrate the point here is the excerpt from a tkprof output for one of the parallel query slaves:

Rows (1st) Rows (avg) Rows (max)  Row Source Operation

TCP Trace Analysis for NFS



When there is a disparity in reported I/O latency between the NFS server and Oracle database on the NFS client the question arises:

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

One strategy is to take tcp traces on NFS server and NFS client at the same time, during a period of load that shows the disparity.

The two trace files can then be analyzed to show latency at each side and the delta of latency between packets found in both traces


Files Required


Parsing script is ( thanks to Matt Amdur from Delphix for the  core code)

Pythian - Data Experts Blog » Jeremy Schneider

Official Pythian Blog - Love Your Data

Fixed stats

Some time ago I had two questions about fixed objects statistics for which I couldn’t quickly find the answers. Questions are:

At the time of asking these question I’ve read CBO development team blog post on the topic, but still I was unable to answer them distinctly. Well, it appears the questions are simple and could probably be deduced after careful reading (first two questions for sure). The core phrase is:

The DBMS_STATS.GATHER_FIXED_OBJECTS_STATS procedure gathers the same statistics as DBMS_STATS.GATHER_TABLE_STATS except for the number of blocks