Re: 9i parallel query is slower than 8i

This sounds like an issue with I/O contention between slaves - possibly because of a change in the granule allocation, possibly because of an unlucky placement on disc - perhaps because oracle 9 may be adopting a different strategy for direct reads and something is happening with (e.g.) O/S-level direct I/O, or O/S-level async I/O.

One thing I would check from the Oracle perspective is the 10391 trace to identify granule allocation and slave timings -

    level 64 lists the granules
    level 2048 clocks granule in and out from slaves.

This may give you some clues.

The problem may be related to the (relatively) small size and large number of extents.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____Finland__September 22nd - 24th
____Norway___September 25th - 26th
____UK_______December (UKOUG conference)

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____USA__October
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"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.