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”.
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:
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 ? 
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.):
(more…)
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.
(more…)
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).
[Forward to Index Quiz part 2]
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).
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).
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.
Just a quick note to point out that Greg Rahn has just published some useful information on parallel execution (including an important addition to monitoring in 11.1) [Edit 12/7/09: originally typed in error as 11.2]
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:
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 distrtibuted queries look like so that you can tell whether your query is going to work locally or remotely.
Postings by RSS Comments by RSS