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...)
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...)
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...)
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...)
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…
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...)
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...)
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...)
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...)
I would like to provide one more option to investigate Oracle logical I/O with DTrace.
This method allows to see each consistent/current gets with details about block, object and location from which function is called.
This is the DTrace script: dtracelio.d
Note: Dtrace LIO with new features is released
Short description:
This tool allows to see:
– details of each call (consistent/current get)
– details of blocks been read
– function from which call been performed without investigating of call stack (“where”)
– aggregation of performed calls
The tool can be especially helpful in 11g because x$kcbuwhy (x$kcbsw (more...)
RSS