Database performance

Over the last couple of days I have been attending an Oracle 10g/11g performance tuning course given by iTrain, which had been booked by another team of programmers in the University where I work.

I have always been reluctant to be labeled as a relational database programmer or even worse as a part-time dba, since it has never appeared to be the most thrilling element of web development. Along with the need to keep it in perspective with other storage options such as RDF or object databases like the ZODB.

As a result I have probably under prioritized acquiring knowledge of database theory and skills, so it was a real eye opener to go on this course and realise just how little I knew about coding for database high performance. So my query optimization was pretty much limited to trying rewrites or the odd view, maybe adding the default type of index, and very occasionally running explain plan - but not really knowing how to fully interpret it!

Although the course was one focused on Oracle wrt. the details, the general principles were entirely applicable to any other relational database such as Postgres or MySQL.
For example the fundamental rdbms architecture of the optimizer (rule or cost based) that generates the query execution plans that govern query performance. How indexing, analyze statistics and other factors determine this. Are all common to Postgres and MySQL, and the process of writing and tuning your schema and queries based on this is one that should really be applied to all but the smallest scale data projects - ie a few hundred rows or less per table.

Some parts had wider applicability than that, for example the majority of the indexes information could apply just as well to the indexes used within the ZCatalog that comes as part of zopes object database.

So I aim to finish writing up a quick tips summary of the course for database performance tweaks that is fairly non-db platform specific.