Re: 9i parallel query is slower than 8i

From: Telemachus <telemachus_at_ulysseswillreturn.net>
Date: Mon, 1 Sep 2003 13:07:42 +0100
Message-ID: <jEG4b.29992$pK2.48039@news.indigo.ie>

Here is 8174 result (no parallelism )

SQL> set autotrace on exp stat
SQL> set timing on
SQL> select count (*) from data_holder.indiv;

  COUNT(*)

---

   1000000

Elapsed: 00:00:00.81

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=127 Card=1)    1 0 SORT (AGGREGATE)

   2    1     INDEX (FAST FULL SCAN) OF 'PRIKEYINDIV' (UNIQUE) (Cost=1
          27 Card=1000000)

Statistics

---

          0  recursive calls
         16  db block gets
       8174  consistent gets
          0  physical reads
          0  redo size
        367  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Here is a 9201 example - with a different index.

SQL> select count (*) from data_holder.indiv;

  COUNT(*)

---

   1000000

Elapsed: 00:00:00.01

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=52 Card=1)    1 0 SORT (AGGREGATE)

   2    1     BITMAP CONVERSION (COUNT)
   3    2       BITMAP INDEX (FAST FULL SCAN) OF 'INDIV_EXD_BM'

Statistics

---

          0  recursive calls
          0  db block gets
         54  consistent gets
          0  physical reads
          0  redo size
        371  bytes sent via SQL*Net to client
        430  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Both indexes are in memory.

please post the same info ..
"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.
Received on Mon Sep 01 2003 - 07:07:42 CDT