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

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.

As you probably already know from 11g Oracle decides whether to do a serial full scan (table full scan, index fast full scan, mat view full scan) via buffer cache or by direct path reads. This behavior is controlled by hidden parameter _SERIAL_DIRECT_READ which (more...)

filter IS NOT NULL

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 (more...)

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

Timing sampling frequency:
Number of calls getting timestamp depends of parameter _rowsource_statistics_sampfreq (default is 128).

  • If this parameter is set to 0, there are no time calculations in rowsource statistics. The functions qerstSnapStats()/qerstUpdateStats()do not get timestamp
  • If this parameter is set to 1, time is calculated always. Every pair qerstSnapStats()/qerstUpdateStats() gets timestamp (this is the case in the excerpts above).
  • If this parameter is set to N (as default value 128) then timestamp will be got every N tuples. It means that only N call of qerstSnapStats()/qerstUpdateStats() on some rowsource level will get (more...)

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

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! ;-)


The rowsource statistics are figures as time spent during execution of rowsource (a step of execution plan), number of returned rows, number of (more...)

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

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 (more...)

Buffer is pinned count


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: there is the function kcbispnd (“Kernel Cache Buffer Is Pinned” as I suggest) where Oracle checks if a buffer is pinned or not. If a buffer is pinned then statistic “buffer is pinned count” (more...)

Digger: the tool for tracing of unix processes

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).


Don’t forget about column projection

Note: this post is not about some exact statement, but about importance of column projection which should not be ignored, especially in cases as operations requiring workareas, data access optimization, Exadata offloading and others.

Let’s consider merge of two simple tables.

 merge into t1
 using t2 on (t1.id = t2.id)
  when matched 
  then update set n = 1;

The tables:

SQL> select * from v$version where rownum = 1;
Oracle Database 11g Enterprise Edition Release - 64bit Production

SQL > create table t1 compress
  2             as
  3         select rownum as id,

A small change in parallel insert with serial data access between 11.1 and 11.2

There is an interesting small change in parallel insert between 11.1 and 11.2 in queries with parallelized insert, but serialized data access. On a practice the simplest case where we can see this it is CTAS or parallel insert as select from remote table.
As an example I will use CTAS from remote table.

create table tbllocal parallel 2
    as select *
  from tblremote@dblink;

Note: even if remote part of this query (select from tblremote@dblink) will be parallelized, it will be run in parallel on remote side. Communication between remote and local servers will be performed in one (more...)

Serial direct read for small tables in

Today I have fixed an issue related with serial direct path reads.

There is database

SQL> select * from v$version where rownum = 1;
Oracle Database 11g Enterprise Edition Release - 64bit Production
SQL> SELECT a.ksppinm  "Parameter",
  2         b.ksppstvl "Session Value",
  3         c.ksppstvl "Instance Value"
  4    FROM x$ksppi a, x$ksppcv b, x$ksppsv c
  5   WHERE a.indx = b.indx
  6     AND a.indx = c.indx
  7     AND a.ksppinm = '_serial_direct_read';
Parameter               Session Value           Instance Value
----------------------- ----------------------- -----------------------
_serial_direct_read     auto                    auto

There is (more...)