Thinking Big

This question came up recently on the Oracle-L list-server

We need to store approx. 300 GB of data a month. It will be an OLTP system. We want to use commodity hardware and open source database. We are willing to sacrifice performance for cost. e.g. a single row search from 2 billion rows table should be returned in 2 sec.

How parallel

Or as Doug Burns put it on his blog: how can I tell the actual DOP used for my parallel query”

As Doug points out, you first have to ask “When are you asking the question”. Immediately after you have run the query you could check v$pq_tqstat to see what happened, but apart from that you have  very little hope of finding out in retrospect what happened in a specific query. 

Methods

The following question appeared a little while ago on comp.databases.oracle.server, with reference to Oracle 10.2.0.1:

drop table t1 purge;        

create table t1
as
select * from all_objects;         

create index t1_fbi1 on t1(lower(object_name));
create index t1_i1   on t1(object_name);         

-- now generate statistics

Push_Subq

Another little warning for those about to upgrade from 9i to 10g. Some hints will change their behaviour. Consider the following SQL.

Transformation and Optimisation

One of the biggest changes in the optimizer in 10g is the arrival of cost-based query transformation.

In earlier versions of Oracle, it was possible to distinguish very clearly between “transformation” and “optimisation”. The transformation engine would accept your SQL, and follow a set of rules to produce a restuctured statement that was logically equivalent but easier for the optimisation engine to handle.

Analysing Statspack (4)

[Back to Part 3] [Forward to part 5]

One of the ways to use statspack is to extract trending information from the data. I published some sample SQL on my website a couple of years ago to show how this could be done – but there are alternatives.

Web Presentation

A few days ago, I recorded a Web presentation at Oracle’s offices in Reading. The topic I chose was the one that I ‘ve just repeated at the Hotsos 2007 event: dissecting one query to see how much you can learn about how the optimizer works, and how you have to think when dealing with a difficult problem in SQL.

The entire presentation – one hour long, audio synchronised with slide-show as a Macromedia Breeze presentation – can be heard at this rather long URL

The generic structure of the query used in the presentation was the one I first introduced in the blog item about Join Orders.

 

Ordering

Here’s a recent question from comp.databases.oracle.server:

Is there any documentation available on the default query result ordering in the absence of an ORDER BY clause? I looked through the Oracle docs and could not find much. I do not plan to depend on this behavior but I am curious on how it works.