Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: 9i parallel query is slower than 8i
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
![]() |
![]() |