filter IS NOT NULL

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

Introduction

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


Introduction

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

Continue…


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;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL > create table t1 compress
  2             as
  3         select rownum as id,
   (more...)

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 11.2.0.2

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

There is 11.2.0.2 database

SQL> select * from v$version where rownum = 1;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 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...)

Dynamic tracing of Oracle logical I/O: part 2. Dtrace LIO v2 is released.

What’s new in DTrace LIO?
Jump to introduction: Dynamic tracing of Oracle logical I/O

1. The list of supported functions performing logical I/O is extended:
Note: some function names below are my suggestions.

Consistent gets:

  • kcbgtcr – Kernel Cache Buffer Get Consistent Read. This is general entry point for consistent read.
  • kcbldrget – Kernel Cache Buffer Load Direct-Read Get. The function performing direct-path read. Interesting detail: 10.2 the function kcbldrget is called just after kcbgtcr, in 11.2 by (from) kcbgtcr.

Current gets (db block gets):

  • kcbgcur – Kernel Cache Buffer Get Current Read
  • kcbget – Kernel Cache Buffer (more...)