We have developed an application which was running under DB2 v7.2.5
quite well for some years. Four weeks ago we installed DB2 v8.1.5
Express Fixpak 5 on a new Server (hardware is nearly the same as on the
DB2 v7.2.5 machine). The new Server runs on Windows 2003. Last week we
installed another server with the same hardware with DB2 8.1.5 Workgroup
Server Fixpak 5 under Windows 2000 Server. Both servers running DB2 v8
databases are about 10x slower than our old server with DB2 v7.2.5.
On our DB2 7.2.5 server we made an offline backup. On the DB2 8.1.5
machines we made a restore to import and migrate the database.
The parameters for the database manager and the parameters for the
database are the same under v7 and v8.
The database under DB2 v7.2.5 was tuned very well. We used a lot of
indexes and we have allocated enough RAM for the buffer pool.
The DB2 v8.1.5 seems to ignore most of our indexes when executing SQL
statements. We have already tried to use another optimization level
rather than the default level, but the performance problems are the same.
We are NOT using runstats because all of our SQL statements were tuned
against v7.2.5 with indexes. In the past we have seen that runstats
would lead to a non predictable performance behavior of the database.
The database has about 150 tables (5 tables > 3 million records, 5
tables > 100.000 records, the other tables are small). All tablespaces
are SMS. The database is used in an OLTP environment, so we have to
assure that the SQL statements have always the same performance behavior.
Hundreds of our SQL statements within the application are extremely slow
under DB2 v8. It is not a suitable solution for us to tune all these
statements against v8, because this would take some months of work.
We rather think that we have overlooked some important new
feature/parameter in the DB2 v8 software. Or maybe the DB2 v8 SQL
optimizer is really that worse .....??
We have also tried to use the Configuration Advisor. We used the
suggested parameters - without success.
We also know that the problems are caused by single (and quite simple)
SQL statements and not by huge sorts or by huge inserts/updates.
The following example is a simple UPDATE statement which should update
one single record. The columns of the where clause are the same as the
primary key index columns. The snapshot utility show us that the
database is making a full table scan, because it reads 115599 rows (the
complete table). Normally we think the database should use the primary
key index to find the row in this simple example. When we try to use the
same where clause within a SELECT statement, then the database uses the
primary key index and reads exactly 1 row.
Anzahl Ausführungen = 1
Anzahl Kompilierungen = 1
Schlechteste Vorbereitungszeit (ms) = 21
Beste Vorbereitungszeit (ms) = 21
Intern gelöschte Zeilen = 0
Intern eingefügte Zeilen = 0
Gelesene Zeilen = 15599
Intern aktualisierte Zeilen = 2
Geschriebene Zeilen = 5
Sortiervorgänge für Anweisungen = 0
Logische Lesevorgänge im Pufferpool = 15389
Physische Lesevorgänge im Pufferpool = 0
Logische Lesevorgänge in temporären Daten des Pufferpools = 6
Physische Lesevorgänge in temporären Daten des Pufferpools = 0
Logische Lesevorgänge im Pufferpoolindex = 15527
Physische Lesevorgänge im Pufferpoolindex = 0
Logische Lesevorgänge im temporären Pufferpoolindex = 0
Physische Lesevorgänge im temporären Pufferpoolindex = 0
Gesamte Ausführungszeit (sek.ms) = 0.318457
Gesamte Benutzer-CPU-Zeit (sek.ms) = 0.312500
Gesamte System-CPU-Zeit (sek.ms) = 0.000000
Anweisungstext =
UPDATE ag_produktion SET stueck_gesamt = stueck_gesamt + ( 1 * 1 ),
takte = takte + 1 WHERE werkscode = 5 AND
maschinennummer = 'abc' AND auftragsnummer = 'anr' AND
splittnummer = 0 AND arbeitsgangnummer = 30 AND (beginn <
'2005-06-05-15.00.00' AND status = 'A')
Any kind of answers or hints would be appreciated.
