Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Slow parallel query on Oracle

Naoyuki
Advisor

Slow parallel query on Oracle

Hi there.

I'm using oracle 8.1.6 on HP-UX 11.11.
I'm trying to do parallel query and tried something like

select /*+ parallel(AAA, 4) count(*) from AAA;

It worked and query process spreaded out on 4 cpus.
However, it's slow! It's slower than it is on single query. I'm wondering why.

I saw an explanation somewhere that parallel query doesn't use buffer cache but physical access. Is it true?

Each query process doesn't use cpu enough and cpus are almost idle.
I assigned 1GB buffer cache so that data of table AAA should be in buffer cache.

Does anyone have any idea?
11 REPLIES
Graham Cameron_1
Honored Contributor

Re: Slow parallel query on Oracle

I think you answered your question when you said "Each query process doesn't use cpu enough and cpus are almost idle".

Your query is doing a full table scan of AAA and will be disk bound. You need to find a more practical real life example. Partitioned tables are always good for this, you can get a separate query process for each partition.

-- Graham
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
Stan_17
Valued Contributor

Re: Slow parallel query on Oracle

Hi,

Is your tables striped across multiple disks ?

Note : parallel execution will not be efficient if you just have multiple cpus, the data should also be in multiple disks, so io's are processed concurrently by multiple processes.

your problem looks like an io contention. look at v$session_event, v$session_wait and see whats its waiting for.

-
hth,
Stan
Naoyuki
Advisor

Re: Slow parallel query on Oracle

I assigned enough block buffer cache so that all data of the table can reside in cache.

I attached sql trace. It seems no physical acccess occurred during fetch phase.
I'm not sure how to evaluate this sql trace.
Can anyone help?
Stan_17
Valued Contributor

Re: Slow parallel query on Oracle

I can't open the attached file. Is it just me or for everyone.

Yogeeraj_1
Honored Contributor

Re: Slow parallel query on Oracle

hi,
-
Stan: this is a text file -> a tkprof output
-
Indeed it is doing a full scan on the table. The value of the ELAPSED time when compared to the CPU time only means that you spent a lot of time waiting for something. A sQL query that performs lots of physical disk I/O might have lots of wait time for I/O to complete.
-
You may use your raw trace file to determine the cause of the wait!
-
hope this helps!
regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Naoyuki
Advisor

Re: Slow parallel query on Oracle

Hi.

I finally found what was causing this.
I just created tablespace with multiple data files then parallel worked ok.
It's effective now.

These data files are not spreaded among different disks but on the same disk.

I guess probably parallel query is exeuted based on extent and these extents have to be on different files.

Thanks for all yor replies.
Stan_17
Valued Contributor

Re: Slow parallel query on Oracle

Hi,

Good that you fixed the problem. look at the note i had mentioned earlier in this thread.

"Note : parallel execution will not be efficient if you just have multiple cpus, the data should also be in multiple disks, so io's are processed concurrently by multiple processes" This should have given you an hint.


Stan_17
Valued Contributor

Re: Slow parallel query on Oracle

Hi,

I re-read your post, you say multiple datafiles but still on one single disk gave you good performance than before. Did you change anything else other than creating tbs with multiple datafiles. Are you sure the volumes aren't striped across multiple disks ?

AFAIK, "parallel query is exeuted based on extent and these extents have to be on different files." thats not the case.
Brian Crabtree
Honored Contributor

Re: Slow parallel query on Oracle

1. You want to terminate your hint:
select /*+ parallel(AAA, 4) */ count(*) from AAA;

2. Generally, a "count(*)" will want to use an index. This option might work better for your test.

select count(*) from
(select /*+ parallel(AAA, 4) */ * from AAA);

Thanks,

Brian
Naoyuki
Advisor

Re: Slow parallel query on Oracle

I'm sure it's on the same physical disk.
The data is in cache and there is no physical access anyway.
Stan_17
Valued Contributor

Re: Slow parallel query on Oracle

hi,

foremost, parallel slaves do direct reads to fetch data, it doesn't rely on buffer cached data. now, you say there is no phyiscal io.

do this and attach the trace file it generates.

alter session set events '10046 trace name context forever, level 8';

run your parallel query;

alter session set events '10046 trace name context off';

you can find the trace file under udump directory.

This is should help to clear things once for all.