Philosophy – 3

The performance of a query should be related to the size of the data set you’re interested in, not to the size of the database.

If this is not the case then you’ve made a mistake in the physical implemenation. Typically this will be poor indexing, but it could be due to errors in other structural choices such as partitioning, clustering, IOTs, or materialized views.

The primary symptom of this error is the complaint: “as time went by the database got slower”.

[The Philosophy Series]

Index Explosion

In Index Quiz 1 and Index Quiz 2 I demonstrated a couple of details of how the ITL (interested transaction list) behaves in indexes. In this note I’m going to explain how these details can result in a nasty waste of space in indexes.

The two points I made in the previous posts were:

1. at high levels of concurrency you can “lose” a lot of space in an index leaf block to the ITL
2. when a leaf block splits the ITL of the newer block is a copy of the ITL from the older block

Empiricism

Here’s a problem I solved for a client recently:

In a test-run of a mixed OLTP system with a bit of degree of concurrency,  the test would stall from time to time with many sessions (at one point 80 of them) waiting on event: “enq: TX – row lock contention”. 

The statement being run by the sessions reporting the wait was always the same: “update tableX set foreign_key_column = :b2 where primary_key = :b1″ – and there was no way that two sessions were trying to update the same row (I know because I set an autonomous trigger to catch all the values on every attempted insert, update, and delete – and because I know the way the code test is supposed to work).

After thinking about it for a bit, I decided to re-run the test after executing: “alter system set shared_servers = 300″. That solved the problem.

So, if you take the empirical approach to tuning Oracle systems, would you now use this method for dealing with waits for that event ?

IQ2 – Answers

I was planning to supply the answers to Index Quiz 2 as a comment – but there’s a lot of block dumps involved, and it’s easier to do that in postings.

Question 1: I’ve created a table and index with initrans 4, then inserted one row into the table. How many ITL (interested transaction list – see glossary) entries will there be in the first block of the index when you dump it.

Answer 1: The table block will show 4 entries in the ITL, obeying your setting for initrans, but the index block will show only two entries – unless you’re using Oracle 8i or earlier (Basically indexes tend to ignore the setting for initrans  except when you rebuild an index, or create it on existing data.):

Index Quiz 2

This quiz is easier than Index Quiz 1 because you can just run the code, dump blocks, and find the answer. But see if you can work out what’s going to happen before you do the test. Note that I’ve set initrans to 4 on both the table and index.

Qualifications

After I had described the way that I can do on-site, real-time, training in trouble-shooting for a group of DBAs I got a few email messages from American companies asking if I could do something of that sort for them.

The two commonest questions were: did I set a minimum number of days for a contract before I would fly to America, and was I allowed to work when I got there.

In the UK or Western Europe, of course, it’s easy for me to travel somewhere for just one or two days – I’ve done day trips to France, Germany, Belgium, Denmark and several other countries before now when the client has been based close to an airport (or Eurostar train station).

Index Quiz 1

I’m encroaching on Richard Foote’s territory here – with plans to write a few details about some of the implementation details of Oracle’s B-tree indexes. My strategy, though, is to entertain by asking a few questions that might prompt a little speculation before giving some answers. So …

After running validate index against a particular index on my system, I select the following columns from view index_stats: (using Tom Kyte’s invaluable “one column per line” routine).

Judgement Day

Thursday was the day for building the agenda for the UKOUG Technology and E-Business conference. 

Last year we had a conference that lasted five days and covered every product that Oracle owned. This year we’re running a series of seven separate conferences covering different areas of Oracle’s offerings;  so the Tech and EBS event is back to the three day event we always used to hold in Nov/Dec.

You would have thought that this would make it easier to produce the agenda – but we had about 640 abstracts for about 200 time slots; and in my particular stream (“Server Technology”) I think we had more than 200 abstracts for 55 slots – so if you’ve got in, you’ve done very well. (There are other “Tech” streams, including development tools, Apex, Middleware and so on).

Statspack Skills

I had a great time a couple of weeks ago at the  UKOUG meeting of the DBMS SIG (reported here by Coskan Gundogar). The range of presentations was good and I had a number of interesting conversations.

Of course, the exciting part for me was sitting down with a batch of Statspack and AWR reports that I had been supplied with in the previous couple of days and doing a “real-time” analysis of them.

Concatenating LOBs

If you have to handle LOBs, it’s worth checking for “unusual” activity. Here’s an example of unexpected behaviour that I came across a couple of years ago.

The client had a table with data that had to be written to a flat file so that a number of other databases could import it using SQL*Loader. The table definition and the query to dump the data are shown below – note, particularly, the CLOB sitting in the middle of the table:

Distributed Queries

Some time ago I wrote a note about distributed DML, pointing out that the driving_site() hint works with distributed queries but not with distributed DML; so insert as select, or create as select and so on will “ignore” the hint.

This is just a little follow-up to give you an  idea of what execution plans for distributed queries look like so that you can tell whether your query is going to work locally or remotely.