9i parallel query is slower than 8i

Hello,

I am encountering the following strange situation. I am interested in any thoughts or similar experiences.

One of our customers is in the process of upgrading from 8.1.7.3.0 to 9.2.0.3.0, on a 4-cpu HP-UX machine attached to Hitachi storage. The application is in the DWH-area, totally batch-wise.

We created 2 databases for testing purposes, one 817, one 920, on the same filesystem. Same parameters settings – where possible. The application loaded and derived a bunch of tables, equal for both databases.

Strange behavior occurs with the following statement: Select /*+ full(t) parallel(t, 4) */ count(*) from a_table t

With the 9i database, it takes about 1:15 (mm:ss) With the 8i database, it takes about 0:25 (mm:ss)

So, the 9i database is about 2.5 times slower for this statement.

If the same statement is run without parallelism, duration times differ from 1:10 to 1:30, on both databases. No significant variations. Notice that for this statement, there is no benefit in using parallel query at all, in the 9i database.

In the parallel case, most time is spent in the direct path read event, in 9iR2. For the 8i database, there are no events regarding physical I/O (…)

If the statements is changed to something like: Select /*+ full(t) parallel(t, 4) */ count(distinct some_column) from a_table t
Then there is no significant difference in duration times between the 2 databases.

Interesting table metadata both databases: Table size is about 110.000 16k blocks, 12.5M rows. No indexes exist for this table. No partitioning. No chained rows. About 850 extents for this table, in a tablespace with local extent management, uniform size 2Mb. Buffer cache size is 2000 blocks.

Kind regards,

Herman de Boer. Received on Mon Sep 01 2003 - 03:10:28 CDT