Oracle performance loss from 10.1.0.2 to 10.2.0.5

oracleoracle-10gperformance

I am testing an application on two database servers with the same data :

10.1.0.2 on Windows (for development)
10.2.0.5 on Solaris (for validation).

We have a script which recreates a huge table (> 100 millions of rows) and its index. The performance for doing this is OK on both platforms and we get almost the same times (about 1 hour).

The structure is created as follows:

CREATE TABLE "PART" 
(
    "PARTNUMBER" VARCHAR2(9 BYTE) NOT NULL ENABLE, 
    "SUBPARTNUMBER" VARCHAR2(12 BYTE) NOT NULL ENABLE, 
    "ISSUE" VARCHAR2(3 BYTE), 
    "PROG" VARCHAR2(1 BYTE) NOT NULL ENABLE, 
    "TYPE" VARCHAR2(1 BYTE) NOT NULL ENABLE, 
    "TYPE_NAME" VARCHAR2(25 BYTE), 
    "FROM" NUMBER(4,0) NOT NULL ENABLE, 
    "TO" NUMBER(4,0) NOT NULL ENABLE
);

CREATE INDEX "PART_INDEX" ON "PART" ("PARTNUMBER", "TO", "FROM", "TYPE_NAME") COMPRESS;

Then we gather statistics on the table (and implicitly on the index):

dbms_stats.gather_table_stats(null,'PART');

There is a performance issue on a query which takes less than 5 second on the 10.1.0.2 but more than 5 minutes on the 10.2.0.5:

SELECT DISTINCT 
    p.SUBPARTNUMBER,
    p.PROG,
    p.TYPE,
    p.TYPE_NAME
FROM PART p, VW_VEHICLES v
WHERE p.PARTNUMBER= 'A12345678'
    AND p.PROG      =   v.PROG
    AND p.TYPE      =   'V'
    AND v.TYPE_NAME =   p.TYPE_NAME
    AND v.RANK BETWEEN p.FROM AND p.TO
ORDER BY p.PARTNUMBER;

I looked at the statistics tab in SQLDeveloper on both platform, it is similar for the TABLE, but for the index there is a big difference on the key DISTINCT_KEYS:

 -  10.1.0.2 : DISTINCT_KEYS = 144800
 -  10.2.0.5 : DISTINCT_KEYS = 44404445

Do you know what can explain these differences ?

The second server (Solaris) should be more powerful and more efficient than the Windows one which is a workstation with less memory and inefficient HDD.

Here are the explain plans for both platforms:

10.1.0.2

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                          |    11 |   759 |  8291   (1)| 00:01:40 |        |      |
|   1 |  SORT ORDER BY                 |                          |    11 |   759 |  8291   (1)| 00:01:40 |        |      |
|   2 |   SORT UNIQUE                  |                          |    11 |   759 |  8290   (1)| 00:01:40 |        |      |
|*  3 |    HASH JOIN                   |                          |    11 |   759 |  8289   (1)| 00:01:40 |        |      |
|   4 |     VIEW                       |                          |  2795 | 81055 |   114 (-12)| 00:00:02 |        |      |
|   5 |      UNION-ALL                 |                          |       |       |            |          |        |      |
|   6 |       REMOTE                   |                          |     1 |    32 |     1   (0)| 00:00:01 | VEHIC~ | R->S |
|   7 |       REMOTE                   |                          |  7790 |   281K|    14   (0)| 00:00:01 | VEHIC~ | R->S |
|*  8 |     TABLE ACCESS BY INDEX ROWID| PART                     |  6579 |   256K|  8159   (1)| 00:01:38 |        |      |
|*  9 |      INDEX RANGE SCAN          | PART_INDEX               |     1 |       |    41   (0)| 00:00:01 |        |      |
---------------------------------------------------------------------------------------------------------------------------

10.2.0.5

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                          |     1 |    69 |   302   (1)| 00:00:05 |        |      |
|   1 |  SORT ORDER BY                |                          |     1 |    69 |   302   (1)| 00:00:05 |        |      |
|   2 |   SORT UNIQUE                 |                          |     1 |    69 |   301   (1)| 00:00:05 |        |      |
|*  3 |    TABLE ACCESS BY INDEX ROWID| PART                     |     1 |    40 |     3   (0)| 00:00:01 |        |      |
|   4 |     NESTED LOOPS              |                          |     1 |    69 |   300   (0)| 00:00:05 |        |      |
|   5 |      VIEW                     |                          |   100 |  2900 |     5 (100)| 00:00:01 |        |      |
|   6 |       UNION-ALL               |                          |       |       |            |          |        |      |
|   7 |        REMOTE                 |                          |       |       |            |          | VEHIC~ | R->S |
|   8 |        REMOTE                 |                          |       |       |            |          | VEHIC~ | R->S |
|*  9 |      INDEX RANGE SCAN         | PART_INDEX               |     1 |       |     2   (0)| 00:00:01 |        |      |
--------------------------------------------------------------------------------------------------------------------------

I am also trying to investigate on a 11g…

Best Answer

You say

Then we gather statistics on the table (and implicitly on the index):

but statistics on indexes aren't gathered unless you do this:

dbms_stats.gather_table_stats(null,'PART',cascade=>true);

What appears to be happening is index statistics are not being gathered (or not gathered correctly) on 10.2.0.5; if specifying cascade=>true doesn't help, you may want to consider gathering the index statistics separately via dbms_stats.gather_index_stats(null, 'PART_INDEX', estimate_percent=>20). You might also need to set a higher value for estimate_percent.

You may also want to ensure you have set system statistics (which tell the optimizer how fast your disks are, how much CPU you have, etc.) via the dbms_stats.gather_system_stats and dbms_stats.set_system_stats procedures. I think that 10.2 is more sensitive to system statistics.

Related Solutions

Installing oracle RAC 10.2.0.1 install and patch to 10.2.0.5 directly or install CRS ASM and RDBMS then patch

I would go "CRS / ASM / RDBMS 10.2.0.1 and then patch to 10.2.0.5 in the same order" just to be sure I follow the most typical way that others went before 10.2.0.5 became available. But it's just me, your first proposition will work as well.

Both alternatives guarantee that at all times:

CRS version >= DB version
CRS version >= ASM version

MySQL looking up more rows than needed (indexing issue)

Your indexes are fine for the two types of queries you mentioned.

This query will be satisfied by traversing the clustered index on the primary key...

[...] WHERE participant_id = x AND question_id = y AND given_answer_id = z;

...and this one is satisfied by the index on 'question_id':

[...] WHERE question_id = x;

The output of EXPLAIN SELECT is not telling you what you think it is telling you, because the value shown in rows is an estimate of the number of rows the server will need to consider, not the actual rows it will examine. For InnoDB these are based on index statistics.

rows

The rows column indicates the number of rows MySQL believes it must examine to execute the query.

For InnoDB tables, this number is an estimate, and may not always be exact.

^{— http://dev.mysql.com/doc/refman/5.5/en/explain-output.html#explain_rows}

The optimizer gathers information about different possible query plans, and chooses the one with the lowest cost. The information shown in EXPLAIN is the information the optimizer gathered about the plan it selected.

When type is ref and key is not NULL, this means that the name listed in the key column is the name of the index that the optimizer has chosen to use to find the desired rows, so your query plan looks exactly as it should.

Note, sometimes you will see Using index in the Extra column and a lot of people assume that this means an index is being used, or that no index is being used when that doesn't appear, but that's not correct, either. Using index describes a special case called a "covering index" -- it does not indicate whether an index is being used to locate the rows of interest.

It's possible that running ANALYZE [LOCAL] TABLE would cause the numbers in rows shown by EXPLAIN to differ, but this is a simple query and selecting this index is an obvious choice for the optimizer to make, so ANALYZE TABLE is unlikely to make any actual difference in performance.

It is possible, however, that your overall performance might see some marginal improvement with an occasional OPTIMIZE [LOCAL] TABLE, because you are not inserting rows in primary key order (as would be the case with an auto_increment primary key)... but on large tables this can be time-consuming because it rebuilds a new copy of the table... but, again, I wouldn't expect any significant change.

Best Answer

Related Solutions

Installing oracle RAC 10.2.0.1 install and patch to 10.2.0.5 directly or install CRS ASM and RDBMS then patch

MySQL looking up more rows than needed (indexing issue)

Related Question