Oracle: 300 times difference in performance between databases on same server.

Question

Hi!

I'm working with a new and an old database on an oracle 9.2.0.3.0 server.

Queries against tables in the new database are about 300 times slower than identical queries against tables in the old database.

These databases are quite similar but there are some differences:

The table I'm testing in the old one has one CHAR column (indexed) and 12 NUMBER columns. The table I'm testing in the new one has one NUMBER column (indexed) instead of the CHAR column, 13 NUMBER columns and 10 new VARCHAR2 columns limited in size to 40-100. Both tables are indexed in the same way (except for the type of the column then...)

Both tables have about 1900000 records. My query get the same execution plan in both cases (the index is used, no full scan) The databases are in separate tablespaces but on the same disk. The old tablespace has about 70% usage and the new one 94%, otherwise setup identically (as far as I can see). The fragmentation within the tablespace is not bad but worse in the old one (yes!) Since the new table have more columns it uses three times more blocks than the old table.

Any ideas on how to proceed?

Update 1: After running query 10 times on new db it went down to about 80 times slower. Improvement! Still not solved however.

Update 2: The queries do differ slightly due to the column type change. First the old one (fast) and then the new one (80-300 times slower).

SELECT fhin, SUM(cost) 
FROM olddb.oldtable 
WHERE month in ('1004 ') AND fhin IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '22', '23', '24', '25', '26', '27', '28', '29', '30', '40', '99') 
GROUP BY fhin

SELECT fhin, SUM(cost) 
FROM newdb.newtable 
WHERE month IN (201004) AND fhin IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '22', '23', '24', '25', '26', '27', '28', '29', '30', '40', '99') 
GROUP BY fhin;

Please don't ask why the query looks like it does, it's not mine ;-)

Update 3: Explain statistics with old query:

execution schema (my translation to English)
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE
1    0   SORT (GROUP BY)
2    1     TABLE ACCESS (BY INDEX ROWID) OF 'OLDTABLE'
3    2       INDEX (RANGE SCAN) OF 'OLDTABLE' (NON-UNIQUE)

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
    182  consistent gets
    101  physical reads
      0  redo size
    903  bytes sent via SQL*Net to client
    398  bytes received via SQL*Net from client
      3  SQL*Net round trips to/from client
      1  sorts (memory)
      0  sorts (disk)
     28  rows processed

explain statistics for new query:

execution schema (my translation)
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=36 Card=30 Bytes=360)
1    0   SORT (GROUP BY) (Cost=36 Card=30 Bytes=360)
2    1     TABLE ACCESS (BY INDEX ROWID) OF 'NEWTABLE' (Cost=11 Card=13857 Bytes=166284)
3    2       INDEX (RANGE SCAN) OF 'NEWTABLE' (NON-UNIQUE) (Cost=1 Card=22502)

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
  11019  consistent gets
  11018  physical reads
      0  redo size
    906  bytes sent via SQL*Net to client
    398  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
     28  rows processed

I'd guess they weren't that similar after all. Any ideas?

Update 4: Thanks for all the awesome help! The issue is still unsolved but my client is not available for the next two weeks. I will try everything out then and get back here with further updates!

Update 5: I'm back at the site! I ran the clustering factor analysis suggested by A. Musch and found this:

Table   Clustering Factor   Rows    Blocks
Old     12633               1930000 12645
New     938379              1890000 39677

I'd guess that the problem is that we have a bad cluster factor in the new database. Any ideas or links on how to fix that?

Update 6: Thanks to Adam's hint I found this article on Oracle B-tree indexes and clustering factor http://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth.pdf and followed the instructions for optimizing the clustering factor by reordering the table by the indexed column. Problem solved!

Answer 1

Are the statistics up to date for the new db?

Answer 2

1.Is it consistently 300 times slower? Or could the old database have had everything cached?

2.Also, you said something which makes me wonder.

The databases are in separate tablespaces but on the same disk.

Of course they are in different tablespaces -- they have to be. Did you mean to say something else?

3.Is the comparison made when both databases are running? In other words, does the old database have the same amount of resources available? Did you allocate all memory to the old DB and leave nothing for the new? I would shut down the old DB, up memory for the new one, and try the comparison again.

Answer 3

If the query is identical, is the column type change forcing an implicit to_char() of the index data? That can slow things down rather a lot; although if the execution plan says it's still using the index then that seems unlikely. It sounds like you're doing a simple query on one table, but if not, is there a join from the indexed (number) column to one in another table which is still char? I would have thought that changing a column type would need at least some tweaking of the query.

Answer 4

Is fhin the column that changed type? In this case, the in list still comparing the values to chars would explain the issue.

Answer 5

Did you notice that the bad query is doing 50-100 times the I/O of the good query?

Fast Version I/O:
    182  consistent gets 
    101  physical reads 

Slow Version I/O
  11019  consistent gets 
  11018  physical reads 

I'd be very interested to see the clustering factor (ALL_INDEXES.CLUSTERING_FACTOR) of the driving index (the one on month?) for each system and how it compares to the number of rows (COUNT(*)) and blocks (DBA_SEGMENTS.BLOCKS) from the underlying table.