Parallel query performance worse than without it

Parallel query performance worse than without it

Post by And » Sat, 04 May 2002 07:24:23



Oracle V806.

I am doing some testing with parallel query:

optimizer_mode=rule, parallel_max_servers = 8, parallel_min_servers =
4 in the init.ora file.
The server has 4 cpus.

These are the results:

select * from cptrancopy;              
281079 rows selected            

                    No Parallel Parallel=4
No statistics       02:10.3     02:38.3
Statistics=10%      01:57.0     02:14.8
Statistics=50%      01:55.2     02:22.7
Compute statistics  02:35.1     02:39.2

Explain plan extract for parallel queries:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE (Cost=1679 Card=281079 Bytes
          =78702120)

   1    0   TABLE ACCESS* (FULL) OF 'CPTRANCOPY' (Cost=1679 Card=28107
:Q28000
          9 Bytes=78702120)

   1 PARALLEL_TO_SERIAL            SELECT /*+ ROWID(A1) */
A1."PTRAN_KEY",A1."P

TR",A1."POSTFL",A1."CLNO",A1."ACT",A

The Oracle manual states "Parallel execution is useful for operations
that access a large amount of data by way of large table scans."

So, why the worse performance? It's a straightforward test, I expected
it to work.

Thanks

 
 
 

Parallel query performance worse than without it

Post by Jonathan Lewi » Sat, 04 May 2002 07:50:53


Your test case is too simple - it is getting
all the overheads of PQ with none of the
benefit.

Although PQ is accessing the table in parallel,
all the rows ultimately are being passed through
the single pipeline of the front_end process.

Try something like:
    select column_with_few_values, count(*)
    from table group by column_with_few_values.

The target is
    parallel scanning
    parallel number crunching
    small number of messages to pass around

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

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

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


>Oracle V806.

>I am doing some testing with parallel query:

>optimizer_mode=rule, parallel_max_servers = 8, parallel_min_servers =
>4 in the init.ora file.
>The server has 4 cpus.

>These are the results:

>select * from cptrancopy;
>281079 rows selected

>             No Parallel Parallel=4
>No statistics     02:10.3 02:38.3
>Statistics=10%     01:57.0 02:14.8
>Statistics=50%     01:55.2 02:22.7
>Compute statistics  02:35.1 02:39.2

>Explain plan extract for parallel queries:

>Execution Plan
>----------------------------------------------------------
>   0      SELECT STATEMENT Optimizer=RULE (Cost=1679 Card=281079 Bytes
>          =78702120)

>   1    0   TABLE ACCESS* (FULL) OF 'CPTRANCOPY' (Cost=1679 Card=28107
>:Q28000
>          9 Bytes=78702120)

>   1 PARALLEL_TO_SERIAL            SELECT /*+ ROWID(A1) */
>A1."PTRAN_KEY",A1."P

>TR",A1."POSTFL",A1."CLNO",A1."ACT",A

>The Oracle manual states "Parallel execution is useful for operations
>that access a large amount of data by way of large table scans."

>So, why the worse performance? It's a straightforward test, I expected
>it to work.

>Thanks


 
 
 

1. Finding the worst Performance queries

Hi,All.

I am new to SQL profiler, use the wizard to generate a 'Finding the worst
performance queries'. Found some interesting results.

I set the filters to: Internet Information Server (to track our web farm),
Database ID 5 (main database),change the duration minimum to 30,000ms. To my
suprise, a lot simple select queries appear in the trace window. For
example, one select query is against 50 rows table (our product table), all
the data are either varchar(<200) or datetime.

We are using SQL7(SP2) Clustered (Active/Active), 2 G Memory. We are getting
a lot traffic, 40-45 Million hits per month, most of the web pages query the
main database.

Questions:

On the ASP pages if the ADO command timeout set to 30s (default), will the
worst performance queries send the users SQL timeout expired errors?

What options I have to improve the query performance? Can one SQL server
handle 40-50 millions queries per month?

Appreciate your time and help,

Thanks,

Wenlei

2. US-CA-SOFTWARE ENGINEER OPENING IN SAN DIEGO, CA

3. Parallel Query Performance

4. SW Help Desk Analysts JAX FL

5. Parallel Server and Parallel Query Processing

6. Tips wanted about VB, ASP and MS-SQL

7. Parallel Server and Parallel Query

8. SQL7.0 and Disk Defragmentation

9. simulating query-by-example without horrible performance

10. F_SETLK is looking worse and worse...

11. Dual CPU box has worse SQL Server performance than single

12. Performance worse after upgrading from 6.5 to 7