Parallel Query

Printable View

10-02-2002, 01:49 AM
grjohnson

Howdy!... Hey, I'm doing some testing on Parallel Queries and received some interesting results. As anyone got any advice for me.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> set timi on
SQL> select count(*) from crp_sio;

COUNT(*)
----------
658768

Elapsed: 00:00:01.26

SQL> alter session force parallel query;

Session altered.

Elapsed: 00:00:00.00

SQL> select count(*) from crp_sio;

COUNT(*)
----------
658768

Elapsed: 00:00:05.91

I mean a parallel count(*) took 5 seconds over a normal unparallel query of 1 seconds... it this a normal thing.

Thanks party people!
10-02-2002, 01:56 AM
pando

hi

how many CPUs do you have?
10-02-2002, 06:56 PM
grjohnson

Hey Pando,

The server in which this test was performed has 10 CPU's.

Cheers,
10-02-2002, 11:21 PM
Shestakov

Did u get explain plan of both queries?
Seems like in a first case cbo used fast scan index but
in a second case it used n-flow full scan table with
io contention between disk operations.
10-03-2002, 12:11 AM
grjohnson

Well... now I get a different result from yesterday...

SQL> alter session disable parallel query;

Session altered.

Elapsed: 00:00:00.04
SQL> select count(*) from crp_sio;

COUNT(*)
----------
658768

Elapsed: 00:00:07.05

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=599 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'CRP_SIO' (Cost=599 Card=691601)

SQL> alter session force parallel query;

Session altered.

Elapsed: 00:00:00.01

SQL> select count(*) from crp_sio;

COUNT(*)
----------
658768

Elapsed: 00:00:04.26

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=67 Card=1)
1 0 SORT (AGGREGATE)
2 1 SORT* (AGGREGATE) :Q19000
3 2 TABLE ACCESS* (FULL) OF 'CRP_SIO' (Cost=67 Card=691601 :Q19000
)
2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F
ROM (SELECT /*+ NO_EXPAND ROWID(A2)

3 PARALLEL_COMBINED_WITH_PARENT

[Edited by grjohnson on 10-03-2002 at 12:14 AM]
10-03-2002, 02:24 AM
anandkl

At what point of time did u execute this query.Means did u exeucte this query when the activity on the database was high.If so you would have got very low response time, on the other had if the activity on the database is low you would get a fast response time.

regards
anandkl
10-03-2002, 09:02 AM
chrisrlong

A couple of thoughts:

- When timing queries, there are so many variables - one of the most important of which is I/O. Run any statement twice and it will run faster the second time because data was read from disk the first time and put in cache. Therefore, the second run did not have to read that data from disk and ran much faster. Any time you are using actualy time to compare the efficiency of statements, you need to run *each* version enough times to eliminate caching as a variable. You must run each several times because, since they probably have different execution plans, they will be caching different data.

- Setting something to parallel is no silver bullet. A lot of variables are involved here as well. What are your configuration options? How many threads are attacking the problem? What is your I/O spread? How much did the plan change? Is this a statement that *should* be parallel? A good analogy I've heard is home repair: If you try to use 40 contractors to put in a bathroom sink, it's going to take longer than if you have just one. You don't actually want the 40 contractors until you're building an addition.

- Chris
10-03-2002, 09:42 AM
marist89

Quote:

Originally posted by chrisrlong

- Setting something to parallel is no silver bullet. A lot of variables are involved here as well. What are your configuration options? How many threads are attacking the problem? What is your I/O spread? How much did the plan change? Is this a statement that *should* be parallel? A good analogy I've heard is home repair: If you try to use 40 contractors to put in a bathroom sink, it's going to take longer than if you have just one. You don't actually want the 40 contractors until you're building an addition.

This is kind of what I was thinking as well. There's a certain amount of overhead when setting up a parallel query. In cases where your query runs for 5 seconds, that overhead is a significant portion. When your query takes 4 hours, that overhead is insignificant.
10-03-2002, 07:02 PM
grjohnson

Quote:

Originally posted by chrisrlong
A couple of thoughts:

- When timing queries, there are so many variables - one of the most important of which is I/O. Run any statement twice and it will run faster the second time because data was read from disk the first time and put in cache. Therefore, the second run did not have to read that data from disk and ran much faster. Any time you are using actualy time to compare the efficiency of statements, you need to run *each* version enough times to eliminate caching as a variable. You must run each several times because, since they probably have different execution plans, they will be caching different data.

- Chris

Thanks Chris, yeah, I realise this, I actually ran the query a number of times after each other to take into account the caching from disk.

Anyway, I agree about parallel not being a silver bullet, I actually thik it can cause serious performance decreases in some issues. I parrallel should only be used in certain processes. I have people around here thinking that Parallel processing will automatically improve performance on the database. Unformtunatly, on our server we have 6 database instances, so if one instance starts to consume excess resources via parallel processing, it'll affect the other instances.

Also good point Jeff, I'll take that onboard.

Thanks people...
10-03-2002, 10:10 PM
OraMan

I am also, 100% agreement with following statement:
"Setting something to parallel is no silver bullet", every thing depends on CPU, number of instances, Memory, and more over it depends on free server resources at time of your execution.

Also, how you setting up your Parallelism: instances or object level.

On the parallel issue, I was talking to an Oracle guru about the Oracle Parallel processes, and he was advocating to have 4-10 (based on degree of PARALLEL) datafiles for each tablespace, so when any one write or query to that tablespace, it will write/access simultaneously 4-10 datafiles as parallel processes.

Now, I am looking for any oracle documentation to verify his statement. He could not guide me to any doc, and saying it is from his experience. Does any one in this forum have any clue about this?

Thanks in advance,

OraMan