Slow parallel query on Oracle

[Naoyuki]

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?

[Graham Cameron_1]

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

[Stan_17]

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]

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]

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

[Yogeeraj_1]

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

[Naoyuki]

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]

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]

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]

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]

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

[Stan_17]

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.