-
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).
Thanks,
Terry
-
I can't speak from experience, but I found this
http://www.orafaq.com/faqopq.htm
-
Thanks, that was quite helpful... :)
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?
-
-
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.
-
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?
Thanks..
-
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)
-
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.
-
If you hope for parallel ,statistics-run is a must.
-
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.
-
Thanks, I'll probably wait for the 8i upgrade then. Although I'm fairly sure CPU usage is a lot lower at night when our app is active.
-
Since the disks are stripped, definitely PQO will improve performance, whenever the optimizer chooses full table scan.
-
Have been playing around with PQO a little (using the /*+ PARALLEL (TABLE, DEGREE) */ Hint).
On the migration machine, it made little difference...
On production.... Oh yes!..
Without PQO
-----------------------
select count(cartype)
from t_entity
Tkprof totals
CPU: 50.57
Elapsed: 51.11
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 11 (MS2000)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
3165654 SORT (AGGREGATE)
With PQO
-----------------------
select /*+ PARALLEL(T_ENTITY, 25) */
count(cartype)
from t_entity
tkprof totals
cpu 0.03
elapsed 6.49
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 11 (MS2000)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
24 SORT (AGGREGATE)
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'T_ENTITY' [:Q21000]
SELECT /*+ ROWID(A1) PIV_SSF */ COUNT(A1."CARTYPE") FROM
"T_ENTITY" A1 WHERE ROWID BETWEEN :B1 AND :B2
***************************************************
In fact, everything I've run on a Full Table scan is in a magnitude of 5-10 times quicker.
So my question now - is it sufficient to just change the degree on the tables to have parallel query running?
(I am still reading up on it btw... just that if experience is always better than manuals - and I can't really test on my other servers since is has little impact on them).
Thanks for all the input so far....
Terry
-