Oakies Blog Aggregator

The Core Performance Fundamentals Of Oracle Data Warehousing – Introduction

At the 2009 Oracle OpenWorld Unconference back in October I lead a chalk and talk session entitled The Core Performance Fundamentals Of Oracle Data Warehousing. Since this was a chalk and talk I spared the audience any powerpoint slides but I had several people request that make it into a presentation so they could share [...]

The CPU Costing Model – A Few Thoughts Part II

As previously discussed, the formula used by the CBO using the CPU costing model is basically:
(sum of all the single block I/Os x average wait time for a single block I/O +
 sum of all the multiblock I/Os x average wait time for a multiblock I/O +
 sum of all the required CPU cycles / CPU cycles per second)
average [...]

2 Year Anniversary !!

A little anniversary slipped by unnoticed this past weekend. Which isn’t really too surprising considering how busy things have been lately, with Christmas just around the corner.
I’ve now been at this blogging lark for 2 whole years !!
Hopefully, there are a few people out there just that little bit wiser now regarding how indexes work [...]

Measurement Error Trap In Trace File (event 10046)

Some time ago I had an interesting case which I can use to clearly describe how one can be caught in measurement error trap.

But let us start at the beginning with this response time analysis:

Response Time Component Time % Elap AvgEla
---------------------------------------- ----------- ------- ---------
CPU service 3934.97s 48.39% 0.000716
un-accounted for time 1363.01s 16.76%
db file sequential read 1122.00s 13.80% 0.032253
gc buffer busy 451.73s 5.56% 0.011746
log buffer space 451.64s 5.55% 0.123974
buffer busy waits 176.79s 2.17% 0.029579
gc cr block 2-way 156.49s 1.92% 0.003287
gc cr grant 2-way 100.20s 1.23% 0.006541
latch: cache buffers chains 98.92s 1.22% 0.005708
gc current grant 2-way 69.68s 0.86% 0.006728
latch: library cache 30.10s 0.37% 0.010030
row cache lock 28.95s 0.36% 0.018727
gc current block 2-way 26.72s 0.33% 0.003828
gc cr block busy 19.35s 0.24% 0.006802
gc current grant busy 15.30s 0.19% 0.004999
latch: row cache objects 14.28s 0.18% 0.006165
gc cr block 3-way 11.73s 0.14% 0.002952
gc current block 3-way 11.34s 0.14% 0.003440
log file sync 10.71s 0.13% 0.315066
enq: SQ - contention 9.14s 0.11% 0.060911
My first thought was that there is an I/O problem as the average single block I/O took 32 milliseconds.

After digging for a while I have produced the following graphs which represent the I/O timing.

Let's first look at some facts:
- 3 node RAC
- Same storage
- Single block read time for Instance 1 was substantially different from read times for other instances during off hours
- 5 batch jobs during off hours
- The timings for Instance 1 are obviously not correct

I used the fact that I was performing the analysis on a 3 node RAC to check what are the timings on other two nodes and as you can see from the second graph, they were quite different.

Here is now the explanation:

The output from strace showed:

gettimeofday({1159440978, 931945}, NULL) = 0
pread(14, "\6\242\0\0\375\23\0\2+\254.\0\0\0\1\6\0054\0\0\1\0\5\0"..., 8192, 455 057408) = 8192
gettimeofday({1159440978, 944159}, NULL) = 0

Oracle records time just before performing a system call (pread). When the system call completes Oracle again records the current time and the difference reports as a wait time. Unfortunately due to high CPU load the process was for quite a while waiting at the system level to get on CPU and only then was able to read the current time and therefore the reported elapsed time was quite exaggerated.

Conclusion: Waiting in runque for CPU exaggerates all wait times of the process.

It is always a good practice to confirm findings with a different method. In this case I could use also the operating system tools to measure I/O timings. The same situation one can have on any other kind of wait event but there is not always a possibility to check it independently.

Finally, there was not really an I/O problem but the system was quite CPU bound. Of course the average single block I/O time over 5 - 10 ms shows that probably we are experiencing also I/O bottleneck. The customer later on replaced disk storage with a faster one together with the HW used for RAC.

UKOUG 2009 - The Slides

As promised in one of my comments here are the slides of the presentations I did at UKOUG 2009.

I've already considered some of the valuable feedback I got - in particular for the FIRST_ROWS_N presentation since it was the first time I did this one.

So those downloads are even valuable for those who attended my sessions - they offer a couple of goodies:

1. The "CBO fundamentals: Understanding System Statistics" slides have a part "The gory details" right after the official end of the presentation where you can find the really "gory" details, if anyone is interested

2. The "Everything You Wanted To Ask About FIRST_ROWS_N But Were Afraid To Ask" presentation has been revised - in particular I have worked on those parts of the slides that were "suboptimal" so far. The complex pagination queries are now better readable and explained step-by-step which I think makes them much simpler to understand, but also some other parts have been re-worked and extended.

Furthermore the slides for this presentation are available in two versions: The presentation slides, and additionally the notes where I explain some more details for those who are interested.

Here are the links to the downloads:

"CBO fundamentals: Understanding System Statistics"

"Everything You Wanted To Ask About FIRST_ROWS_N But Were Afraid To Ask"

"Everything You Wanted To Ask About FIRST_ROWS_N But Were Afraid To Ask" - Notes

Anydata and anytype in 9i

An introduction to generic types in Oracle 9i. October 2002 (updated July 2007)

The collect function in 10g

Using the new 10g COLLECT group function, including string aggregation. June 2004 (updated July 2008)

Binding in-lists in 10g

Alternative IN-list binding in 10g using the new MEMBER OF collection condition. June 2004 (updated September 2008)

Collection extensions in 10g

A brief overview of the new collection operators, functions and conditions in 10g. June 2004

Emulating string-to-table functionality using sql

Turning delimited strings into multiple records without PL/SQL. July 2005 (updated August 2007)