Re: 9i parallel query is slower than 8i

"Herman de Boer" <h.de.boer_at_itcg.nl> wrote in message news:bde5777e.0309010010.26860480_at_posting.google.com...
> 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.

Evidently the obvious questions to which you left the answers out are: How did you create the database: migration? export/import? if you used export/import, how did you deal with statistics? IIRC on import you will have a standard estimate. This may or may not be equal to the statistics you had in 8.1.7

As far as I have found out Parallel Query is useless, if you didn't stripe your data, and your stripes are on multiple disks.

-- 
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address