I'm looking into ways of improving the overall speed of our server (same as usual, want it running quicker by yesterday!).
One option were not utilising is the Parallel Query option (the degree of all tables is 1).
I've never used this before, but I know were licensed for it (in fact it's included as standard in 8i EE now I think).
Can anyone give any hints, pitfuls on how to implement this? (The option is listed in v$option). Is it just a case of changing the degree of tables? (do they need rebuilding for this). Would I be looking at a fair performance gain in going for this?
Our application is a Hybrid batch processing - lots of loading, followed by processing on the data into the main tables.
Configuration is: 36 CPU, Oracle 7.3.4 - may be decreasing CPU's down to 20 at some point in the future (don't know if that will have an impact on any settings).
Has anyone any practical experience in running PQO?
Do I set Degree to no. of processors, can it be set too high/low? etc.
Can I expect big gains from setting this?
11-09-2000, 01:00 PM
11-09-2000, 01:09 PM
parallel query option is installed (if you want) by the default cd (oracle server). The number of degree must me less or equal to the number of the cpu.
your cpu usage may be strong, on static tables i found this good.
11-09-2000, 01:13 PM
Yes it's already installed.
So literally all i need to do to start using this is set the degree on each of the tables...
What about the init.ora paramaters?
Are there any side effects of using this?
11-09-2000, 01:19 PM
I have read the degree is a relatioship between CPU utilization and disk drives.
What is your average CPU usage? <=30% then go for it, > then think about add more CPU.
If your table is in only one disk PQO will increase the contention on that disk.
A very good combination is Partitioning by range (fewer records) and subpartitioning by hash (spread in disks) with parallel degree=d*p where d=disks & p=processes running in each disk.
Hope it helps.
I drop PQO because a horrible bug in Datawarehouse.
We are planning PQO as I said in Billing system (huge)
11-09-2000, 01:26 PM
Hi Ramon -
I have no idea how many disks we have unfortunately - we basically get allocated a veritas disk group which is made up from a lot of striped disks in an EMC array. All I know is that I/O is pretty fast. I guess that means I have no way of using the formula you gave since the only info I have is volumes (and is not representative of spindles).
Processor usage seems to be around 60%+ for most of the processors down to about 30% - Although our system is actually quiet in the daytime (server shared with other DB's). The bulk of our work is done overnight while other apps are backing up.
11-09-2000, 01:36 PM
If you hope for parallel ,statistics-run is a must.
11-09-2000, 01:43 PM
I think you have already a lot of CPU usage, if you put PQ I guess it will increase.
If you have 8i use PARALLEL_AUTOMATED or automated_parallel in init and put parallel degree as DEFAULT in tha table.