Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle WareHouse - Need for more CPUS even if CPU Utilization is Low!?

SOLVED
Go to solution
Alzhy
Honored Contributor

Oracle WareHouse - Need for more CPUS even if CPU Utilization is Low!?

Our Oracle Warehouse Team claims the weay their procesing work is such that it depends very much on how many CPUs are allocated -- not how much tose CPUs are used.

Is this true for Oracle Warehouse?

They've been claiming that say with 8 CPUs, even if it's just 30-40% total CPU utilization - they still need to have those CPUs bumped up as they claim - their apps use parallel hint and a different execution plan of sorts.

Hakuna Matata.
8 REPLIES
Hein van den Heuvel
Honored Contributor

Re: Oracle WareHouse - Need for more CPUS even if CPU Utilization is Low!?

Hello Nelson,

IMHO this is a false request born out wishfull thinking and a desire for 'performance at all cost'.

Unless you have an ulimited hardware and software licence budget you'll have to put them straight.

Of course they use paralization hints, but like you suspect, unless the total process becomes largely CPU bound you do not need as many cpus as there are parallel executing threads.

Typically a Wharehouse application is IO bound. So as long as there are enough CPUs to turn aroudn and launch the next IO they should be all set.

"They" will probably say "But what if a CPU is NOT immediately available" right when it is needed again. Well, that's why you want SOME idle time, but just a portion of a CPU will of.

Just imagine that there are 8 threads each triggering 1000 repeats of 0.1 millisecond IO setup, 3.0 millisecond IO, 0.5 millisecond processing.
For the first cycle, if you had just 8 CPUs, then it would be done in 0.1 + 3.0 + 0.5 = 3.6 ms. With 1 CPU it would be done in 0.1 + 3.0 + 8*0.5 = 7.1 ms and you would be CPU bound forever after.

With 2 CPUs it would be done in 0.1 + 3.0 + 4*0.5 = 5.1 ms, but after that first round of serialization the process would keep up with about 30% cpu time idle and every cycle just taking 3.6 ms.

After a few 100 iterations is all equalizes.

Sure, every now and then you'll miss a beat, but is that worth a doubling in license cost? Maybe yes, maybe not.
Furthermore, if the system became truly IO bound, then launching the next IO quicket does not help any.
As long as the CPU power is there to keep more IOs in the pipeline than the IO subsystem can handle there will be no delay due to CPU scheduling

Hope this helps some,
Hein van den Heuvel (at gmail dot com)
HvdH Performance Consulting




Duncan Edmonstone
Honored Contributor

Re: Oracle WareHouse - Need for more CPUS even if CPU Utilization is Low!?

Nelson,

I guess your DBAs are refrring to Oracle Parallel Query.

I know very little about PQ, but you can quickly learn about the basics here:

http://www.orafaq.com/faq/parallel_query

From what I do know, I would strongly suspect that your DBAs are groslly simplifying the issue.

Its true that PQ benefits from having more CPUs, but it doesn't follow that a query running with a PQ degree of parallelism of 8 on an 8 CPU system that generated a CPU load of 40%, wouldn't in fact run just as well on a 4CPU system generating a CPU load of 80%. The whole point of PQ is to give Oracle something else to do whilst other threads are stalled on IO etc. If there are idle timeslices on your CPUs, I think Oracle will use them. I don't know whether this gets more complex when considering ccNUMA systems like Superdome configured with CLM, where I'd imagine a context switch to a different CPU on another cell board is more costly than say a context switch on a rx6600...

HTH

Duncan

HTH

Duncan
Tim Nelson
Honored Contributor

Re: Oracle WareHouse - Need for more CPUS even if CPU Utilization is Low!?

Throw this at them.

If you have 24 processors and only 23 processes then the next one will not have to wait, but wait, I have 200 processes running, I guess we will have to have 201 CPUs ? but wait, how much time is Oracle waiting on CPUs ?
:)

Everyone is going to go through the scheduler and wait at least the 10ms slice until the scheduler decides who is next.

How about an Oracle RAC solution ?

How much money do we have to work with ;)
Steven E. Protter
Exalted Contributor

Re: Oracle WareHouse - Need for more CPUS even if CPU Utilization is Low!?

Shalom,

Sounds like marketing hype to me.

I say if you need the product proceed with the installation if you exceed minimum requirements.

You can always add CPU's later if utilization is high.

Sounds to me like they are covering for some CPU hungry software.

Its the Oracle way.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Alzhy
Honored Contributor

Re: Oracle WareHouse - Need for more CPUS even if CPU Utilization is Low!?

One thing I notice is on this Oracle Warehouse environment (normal 8 CPUs, a230-4% Util, they ask for another 4 to 8 CPUs), Sempahore Operation (sema/s under sar -m) goes astronmically high as follows. Do you folks think this is an indication of an inefficient warehouse app/processing?

HP-UX whs001 B.11.11 U 9000/800 03/17/08

00:00:02 msg/s sema/s
..
..

04:55:00 1.07 53.66
05:00:00 1.09 83.76
05:05:00 1.08 482.70
05:10:00 1.08 328.99
05:15:00 1.07 145.60
05:20:00 1.07 121.69
05:25:00 1.07 343.39
05:30:00 1.08 159.09
05:35:00 1.07 247.63
05:40:00 1.07 412.32
05:45:00 1.09 174.86
05:50:00 1.06 91.83
05:55:00 1.07 224.09
06:00:00 1.07 4893.97
06:05:00 1.07 10383.00
06:10:00 1.07 9742.08
06:15:00 1.11 11279.94
06:20:00 1.09 11494.36
06:25:00 1.06 10490.05
06:30:00 1.07 10752.57
06:35:00 1.06 10388.84
06:40:00 1.06 11555.59
06:45:00 1.08 17057.53
06:50:00 1.08 16411.32
06:55:00 1.06 16708.09
07:00:01 1.07 18058.43
07:05:00 1.06 24262.85
07:10:00 1.06 27548.93


Thanks!

Hakuna Matata.
TwoProc
Honored Contributor
Solution

Re: Oracle WareHouse - Need for more CPUS even if CPU Utilization is Low!?

While it is true that their program will spawn more parallel workers with more CPUs IF you've got parallel server count set to CPU count or a multiple of CPU count - I believe that if CPU count is only at 40% then this isn't your problem. I think they are wanting more speed from queries by being more parallel, right? Well, if that's the case, with free cpu cycles laying about, I'd just increase the max parallel server level in the database (double it), and see if the proc load goes up, and see if the query time reduces.

If your last posting about semaphore locks is the issue (and saying so without the benefits of a good statspack report), then I don't think more CPUs and more parallelism won't fix the issue, it would only increase the problem of semaphore management, which would probably show up in their database as latch contention, and if they have enough ram to cache loads of it, then more specifically - buffer chain latches.
These types are quite common in databases with loads of full table scans (like data warehouses).

I'd look at
a) increasing multi block read count
b) increasing cache in the buffer cache until db misses are small, and you've not got problems with latch busies, and at that point -
c) increase the size of the inittrans on the problematic tables,
d) increase the freelists on the most problematic tables
e) using some materialized views to support the work queries
f) increasing the size of the redo_buffer cache - only because in general most people run this too small to get any throughput.
g) partitions on the data and the indexes relevant to the worst tables.
h) Since you're do cooked file systems, make sure that your tablespaces exist in lots of tablespace files. So instead of having a 10G
tablespace in a single file - use a tablespace with 10 1G files, or 20 1/2 gig files to spread I/O among files. Or...
to eliminate semaphore problems more fully - switch to raw disk.

The problem is, more cpus with more parallel servers will deepen your obvious semaphore (ne' concurrency) issues, and I've not seen your I/O waits - but I can only imagine that if your doing data warehousing with multiple requests and users, then your either at 40 % because a) you're only running 3 queries (.4*8) at once whiche I find unlikely), or b) you've got a full complement of concurrent queries running but the cpus are yielding (as someone else said) because of waits for I/O.

Data Warehouses in both their official and unoffical forms - need query tuning, etc to perform better for their users. Unless they are looking to buy their way out of having to do this.

From what you've told me, and the problems they are having (semaphore contention) the next bottleneck to free up, without having to tune queries would be obtained by going with raw volumes.

Lastly, keep in mind that even the most fully tuned system, MUST wait on something, as all processes will not be spontaneous - thus, you'll should always be able to identify something which could errouneously be misinterpreted as a bottleneck.... when in truth, it may be that the system is merely working as hard as it can for you. But I wouldn't expect to see that case, at 40% cpu usage on an 8 way, unless you're only three processes wide!
We are the people our parents warned us about --Jimmy Buffett
Yogeeraj_1
Honored Contributor

Re: Oracle WareHouse - Need for more CPUS even if CPU Utilization is Low!?

Hi nelson,

Allow me to also add that you also need to do some finetuning at the database level so as to take the maximum out of the available resources. For instance, you may wish to set the PARALLEL_MAX_SERVER initialisation parameter to power of two - based on number of cpu's. e.g. if you have 8 cpus, then 16.


Also, you need to benchmark the current performances so that you do not do an overkill.

How many concurrent users will you be having during the peak period?

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
TwoProc
Honored Contributor

Re: Oracle WareHouse - Need for more CPUS even if CPU Utilization is Low!?

Nelson,

Interesting article on "asktom.oracle.com"
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:708214000346242461

Note the section near the bottom that starts with:

Out of curiosity, why the aversion to parallel operations?

Read the whole thing, but an interesting part follows:
--- AskTom Excerpt
when parallel query first came out, 7.1.6 was the production release of Oracle.

A data warehouse was a new concept.

They typically had about one user running queries. So, for that one user, using parallel query made sense.

Once you have many more users than CPU's/IO channels - parallel stops making perfect sense. It is very good for administrative tasks, but todays "warehouse" with 16 cpu's and 100 concurrent users - parallel is many times not reasonable (my preference is to ask for it via a parallel hint without a degree - if sufficient resources exist, it'll do it - else it won't)
-- End of Excerpt


HTH, John
We are the people our parents warned us about --Jimmy Buffett