_serial_direct_read is not a criteria for cursor [non-]sharing

October 22, 2018 Leave a comment

Those who are on Exadata and change _SERIAL_DIRECT_READ on session or system level may be interested in the issue described below. The issue exists in all versions from 11g and above.
Read more…

filter IS NOT NULL

November 16, 2013 Leave a comment

Although Oracle is smart enough it is always helpful to double check. Even in cases where you expect that Oracle aware about nulls and it will apply necessary filters itself, result can be unexpected.
Below are several cases where you would get a benefit from manually added filter which could look unnecessary and redundant.
Read more…

Timing: rowsource statistics. Part 2: Overhead and inconsistent time

December 24, 2012 7 comments

Timing: query execution statistics (rowsource statistics). Part 1: How it works

December 24, 2012 6 comments

This is the first (but not last) blogpost of my explanation of timing. This stuff is about timing in query execution statistics which also called rowsource statistics.

Here I am explaining

  • why reported actual time (“A-Time” or “time=” in sql trace) can be inconsistent,
  • why execution time with statistics_level=all can be longer,
  • and how exactly it works.

This stuff covers versions 10g and later if another is not mentioned.

Merry Christmas and Happy New Year 2013! 😉

Read more…

The Digger: additional notes. DTrace output can be shufled in multi-CPU environment.

November 5, 2012 Leave a comment

Below are important additions about the Digger tool.

1. DTrace output can be shufled in multi-CPU environment.

This means that output can be in not chronological order. It is not something Digger specific, it is how DTrace works.

When DTrace script is being executed there are two parts to DTrace: the in kernel part and the DTrace script process in userland. When a dtrace probe fires, the data traced is placed in a per CPU buffer. Then, periodically, the DTrace script reads the buffers (in round-robin style) and continues processing the data for final output. While the data from any single CPU was entered into its buffer in order the probes are firing asynchronously with respect to all the CPUs.

If probe1 fired on CPU3 and after that probe2 fired on CPU2, and after that probe3 fired on CPU1 it is possible to get output as, for example, as following:

The most common case where we can see this are the cases when a process(thread) migrated from one CPU to another and DTrace consumer reads and prints current CPU-buffer before previous. It does not happen always but sometimes you can see it.

So, do not panic if some part of the output is not chronological in multi-CPU environment. It is expected.

If the output order is important, then it is strongly recommended to print such columns as timestamp or relative timestamp in order to be sure that the output is chronological, otherwise to fix it manually or using tools like sort (1). Column CPU# also is helpful because allows to see places where a process(thread) migrated from one CPU to another.

2. How much is performance impact of the Digger?

It depends on the usage. When you trace everything – all application functions or all library calls – performance impact can be significant and amount of printed info can be huge.

The Digger allows to restrict
a) traced area – where tracing is enabled
b) traced contents – which functions are traced

For example, in order to trace kcb* functions and all syscalls *read* inside function qertbFetch
command line should be:

digger.sh -p PID -Fcdeo -f qertbFetch -t kcb* -s *read*

Then generated dtrace script will be containing only required probes:


Note: “a.out” is synonym of executing binary.

It helps to trace only relevant information, only required functions. It decreases performance impact and decreases probability of drops.
So, if you need to decrease impact of the tracing just try to restrict traced area and contents.

3. Important bug is fixed.
There was an issue related with replacement tab symbols on spaces during generating html wordpress page with source code (thanks Zhenx Li for let me know). The issue led to possible error “syntax error at line n: `end of file’ unexpected” when the source of the tool was copied/pasted. Now links on html pages with source are replaced on direct download links.

Buffer is pinned count

July 26, 2012 2 comments


There are many cases where Oracle revisit some buffer in the buffer cache many times inside one database call. It such cases it can pin the buffer and hold the buffer pinned and just read pinned buffer in consequences visits. It allows to avoid redundant logical reads.

There are statistics “buffer is pinned count” and “buffer is not pinned count”.
The concept is simple Read more…

Digger: the tool for tracing of unix processes

July 26, 2012 Leave a comment

I would like to introduce new tool – Digger.
This tool allows to see tree of process’ calls such as application calls, library, system calls (and even kernel functions and OS scheduler actions) with additional information as function arguments, result, cpu & elapsed time.
This is not something Oracle specific, the tool can be used for tracing of any unix process (DTrace is required).