Top 60 Oracle Blogs

Recent comments


Fetch as Much as You Can

In my “Everything DBAs Need to Know About TCP/IP” presentation, I discussed the issue of fetch size – tuning the number of rows your application will get from Oracle in a single “fetch” call.

The problem is interesting when you want to get a large number of rows from the DB to the application. Cary Millsap would say that no one should want to do that, but Cary obviously never had to work with statistical analysis application. These things demand large amounts of blood raw data to do their thing.

If this is too slow for your users, you should look at statistics like “round trips”. If the number of round-trips looks completely off the wall, like 200,000 round trips for 2M rows, someone might have left JDBC’s default fetch size of 10.

Oracle Session Snapper v3.10

Hi all, long time no see!  =8-)

Now as I’m done with the awesome Hotsos Symposium (and the training day which I delivered) and have got some rest, I’ll start publishing some of the cool things I’ve been working on over the past half a year or so.

The first is Oracle Session Snapper version 3!

There are some major improvements in Snapper 3, like ASH style session activity sampling!

When you troubleshoot a session’s performance (or instance performance) then the main things you want to know first are very very simple:

  1. Which SQL statements are being executed
  2. What are they doing, are they working on CPU or waiting.
  3. If waiting, then for what

Often this is enough for troubleshooting what’s wrong. For example, if a session is waiting for a lock, then wait interface will show you that. If a single SQL statement is taking 99% of total response time, the V$SESSION (ASH style) samples will point out the problem SQL and so on. Simple stuff.

However there are cases where you need to go beyond wait interface and use V$SESSTAT (and other) counters and even take a “screwdriver” and open Oracle up from outside by stack tracing :-)

When I wrote the first version of Snapper for my own use some 4-5 years ago I wrote it mainly having the “beyond wait interface” part in mind. So I focused on V$SESSTAT and various other counters and left the basic troubleshooting to other tools. I used to manually sample V$SESSION/V$SESSION_WAIT a few times in a row to get a rough overview of what a session was doing or some other special-purpose scripts.

However after Snapper got more popular and I started getting some feedback about it I saw the need for covering more with Snapper, not just the “beyond wait interface” part, but also the “wait interface” and “which SQL” part too.

Sane SAN


The Random Acronym Seminar (or RAS for short...)
James Morle, Scale Abilities, Ltd.

This paper talks about storage within SANs. It is easier than ever to implement a badly laid out SAN, with the levels of abstraction and data sharing made possible through this technology. We will look at ways this can be simplified though careful planning, and discover why its a good idea to lie to your boss. Before that, though, it's worthwhile taking a journey into the past to find out why all this stuff exists, and why there are so many acronyms in the storage industry.

Brewing Benchmarks

 Creating an accurate simulation of your own application can be a difficult task. This paper details methods and software that can make this process considerably more straightforward and much more accurate that many other methods. 

Investigation of collecting Cost Base Optimizer statistic


  • Objectives
  • Background Information
    • When does Oracle's CBO consider Partition-level statistics
  • Oracle bugs that can impact the statistics collection
  • Statistic collection tests on small to medium sized data volumes
  • General Guidelines
  • CBO statistic collection guidelines for small to medium sized data volumes
  • Statistic collection tests for large data volumes
  • CBO statistic collection guidelines for large data volumes
  • Plan stability
  • DBMS_STATS tests for Oracle Release and
  • Automatic Workload Repository and CBO statistic collection in 10g
  • A writers naive conclusions

This article looks at some of the questions and challenges you need to consider when you are planning statistics collection for Oracle's Cost Based Optimizer (CBO). Many businesses require 24 by 7 operations which often put constraints on the available timeframe and hardware resources for collecting CBO statistics.

RAC synchro reader - beta

I had a problem (way back in April 2007) on a 4 node RAC. I spread out the alert logs from the 4 instances in front of me and tried to see a timing correlation across the nodes. So, "X" occured at 12:54:13 on NodeA, what happened at NodeB; so I search (with my editor) for a "close" timestamp in the other alert file... this was tedious!! So I wrote a quick program to try and do this automtically. A text browse of the alert file(s) with an automtic implict search of the timestamps.
The program is freely available (no guarantee, your milage may vary, professional driver on a closed course, do not wash, mangle or wring, not valid about 5600ft, etc and so forth) here at the oaktable site.

Hotsos 2010 – A Training Day with Tanel Poder

My last day already, so I hereby leave you with some impressions from Tanel’s Training Day. Maybe until next year.

If you also want to learn from him, then here’s your chance in Holland. Scripts and tools used by Tanel can be found here.


Hotsos 2010 – Is it a Chicken, a Duck? No wait it’s Bob!

Bob Sneed being a hero and not to afraid to dance during the Hotsos Disco Night were others would chicken out…

Hotsos 2010 – Presenters, Presentations, Presenting

I never find it very easy to try to capture the atmosphere during a conference, the presenting part, the presentations or the discussions, for example, you could have with the presenters. Hotsos is such a cool and unique event were you have the opportunity, to listen but also to interact. The amount of people that attend isn’t that big, only a few hundreds, but they share all the same passion, the passion to improve on performance, mostly Oracle related. It has only two tracks and its not uncommon that people present and than go afterwards to a presentation to listen in what the other presenter has to say.

Due to the fact that it is manageable in terms of choice, located on a convenient location and well organized, you have the opportunity to pick just the thing you like and most of the time not miss out on “the other” presentation you would have liked to see. Besides that people stay in (overnight in the Hotel), so discussions about the technology, the method or an example during a presentation will be discussed in far more detail than you normally would do, from presenter to presenter or from presenter to the guy that attended and vice versa. The fact that all have the passion for performance or that they realize that performance is a beast with various angles to approach, bounds, and every point, every question is one to be heard and/or discussed. On equal terms. If you have seen my video impressions of Hotsos in 2009, you get a bit of what I am trying to say.

Hotsos 2010 – About swag, the Oscars and other stuff

Its Sunday and its raining outside. The nice weather on Saturday (approx. sunny / 20 degrees Celsius) has gone. After a decent flight on Friday where I actually made it to switch in Houston from the international Continental flight, going through customs and pick the next one, a domestic Continental Express flight, within the boundaries of 1 and 1/2 hour. I was so fast that switching for one flight to the other, that apparently my luggage didn’t manage to travel with the last flight. So after I found out that my luggage was still somewhere in Houston, I got from Dallas Love Field to the Omni Hotel in Las Colinas, Irving, where the Hotsos conference will be held again. The whole area is a bit in shambles because they are rebuilding a lot of the environment. While getting to the Grapevine Mills mall yesterday, I noticed that they also a building a new Irving Convention Center along the highway. Another addition to the already crowded Dallas/Plano/Irving Metroplex.