- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Oracle WareHouse - Need for more CPUS even if ...
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-17-2008 05:07 AM
тАО03-17-2008 05:07 AM
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-17-2008 07:37 AM
тАО03-17-2008 07:37 AM
Re: Oracle WareHouse - Need for more CPUS even if CPU Utilization is Low!?
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-17-2008 08:03 AM
тАО03-17-2008 08:03 AM
Re: Oracle WareHouse - Need for more CPUS even if CPU Utilization is Low!?
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
I am an HPE Employee
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-17-2008 09:47 AM
тАО03-17-2008 09:47 AM
Re: Oracle WareHouse - Need for more CPUS even if CPU Utilization is Low!?
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 ;)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-17-2008 10:04 AM
тАО03-17-2008 10:04 AM
Re: Oracle WareHouse - Need for more CPUS even if CPU Utilization is Low!?
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
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-17-2008 10:58 AM
тАО03-17-2008 10:58 AM
Re: Oracle WareHouse - Need for more CPUS even if CPU Utilization is Low!?
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-17-2008 01:02 PM
тАО03-17-2008 01:02 PM
SolutionIf 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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-17-2008 11:14 PM
тАО03-17-2008 11:14 PM
Re: Oracle WareHouse - Need for more CPUS even if CPU Utilization is Low!?
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-18-2008 08:41 AM
тАО03-18-2008 08:41 AM
Re: Oracle WareHouse - Need for more CPUS even if CPU Utilization is Low!?
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