Re: 9i parallel query is slower than 8i

h.de.boer_at_itcg.nl (Herman de Boer) wrote i

> 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)
<snipped>

Herman, first the obvious before we get into the murky water.. :-)

How sure are you that the query does indeed run in parallel on the 9i instance? There could be a tiny little typing mistake that causes PQ either not to work or the max PQ slaves set to 2. Or maybe the block size differs between the two instances.

On a similar track. PQ works best when there is equal load distribution. This means that each of these 4 PQs get a quarter of the data to process. Are you sure this is the case on 9i?

Consider the scenario when using uniform allocation. The tablespace is for VLT's and thus you make the extent size large. This results in the table to be physically bigger on the 9i instance than on the 8i instance. In turn this means that the 9i PQs have to scan more data blocks (many of which are empty) than the 8i PQs.

My gut re-action is that speed *must* be at least the same - the fact that it is not points not to an Oracle-9i-slower-than-Oracle-8i "issue", but rather to something that is very *different* between those two instances. And the fact that you are using 9i and 8i already point to several differences.

Run the test on the 9i instance. Set sql trace on. While the query is running, select one of the PQs and look at its SQL and its events and wait states. I'm pretty sure that this will make the picture a lot clearer.

--
Billy