| T.R | Title | User | Personal
Name | Date | Lines |
|---|
| 1549.1 | check this | ALFAM7::GOSEJACOB | | Wed Apr 23 1997 06:10 | 34 |
| re .0
First thing to check: is the optimizer_mode set to 'choose' the default?
A simple way of checking if parallel query actually works:
select count (*) on one of your bigger tables with a parallel degree
set to 4. This type of query will do a full table scan.
Now if that doesn't use the 4 CPU's force the optimizer to use a
parallel full table scan with something like:
select /*+ FULL(tab1) PARALLEL(tab1, 4) */
count (*)
from tab1;
Now if that still doesn't work: are you sure you have the ORACLE7
Parallel Query option installed?
Hint:
Even if you have set the parallel degree on tables and analyzed them
the cost based optimizer may still decide that using existing indexes
is the faster way of getting to the data. It may not always make the
right decision though :-).
One way of changing the optimizers behavior is setting the parameter
optimizer_percent_parallel to a value (between 0 and 100) higher
than the default 0. BUT this may have negative effects also. With a
higher value the optimizer will favor table scans instead of indexes.
You may or may not want this effect.
Hope this helps
Martin
|
| 1549.2 | Not much gained after optimizer percent = 100 | TROOA::HANDY | L. Handy, MCSE DTN 626-3210 | Thu Apr 24 1997 11:16 | 20 |
| Update: Ok, we were able to test the 'select count(*) from tab1'...
and determined that parallel query was NOT being used, but it would
work if we forced a full table scan with parallel degree 4.
We set optimizer_percent_parallel = 100, and found the following
strange behaviour.
Using performance solution (Unix 3.2G), we noticed that instead of
using 1 cpu 100% of the time, our queries were now using all available
cpu's, but only one at a time. The overall cpu consumed then was
identical (ie. 25/25/25/25 versus 0/0/0/100 %). 1 query ran faster, but the
others took exactly the same time to run. I did some checks using
'select * from v$pq_sysstat;', and it appeared that parallel query was
being used slightly. Also 'monitor top processes' indicated that only
one parallel query server being used at any time (same pid).
Any suggestions here? Should we drop the indexes and retry?
Lyndon
|
| 1549.3 | | COMICS::CORNEJ | What's an Architect? | Thu May 01 1997 05:30 | 5 |
| Could this be related to AdvFS and single threading?
Jc
(only a guess - I don't even know if you have AdvFS:-)
|
| 1549.4 | Ask for patch 497617 | SPANIX::JULIANR | ALPHAbet = Our bet on ALPHA | Thu May 29 1997 16:05 | 11 |
| For the large benchmark we are running in Spain (1 TL with 12 CPUs
@440, 8 GB RAM, 34 KZPSA, 1.4 TB), we managed to get an Oracle patch,
number 497617, which helped us somehow in improving the parallelism in
reading operations, although it does not help much in writing.
Give a call to Oracle CSC and ask for that patch. It may help you.
Regards,
Juli�n Rodr�guez
Digital Spain
|