CPU usage of Oracle installed Database machine

Well you certainly can find some interesting opinions on the intertubes.

Oracle does not have a true server architecture (others have it).

Rather than performing classic server tasks, such as multi-threading, caching of data pages, parallel processing (split a query across many devices) etc. within itself, it uses the o/s to do all that. That means for each user process (PL/SQL connection) there is one unix process; 1000 users means 1000 unix processes, all competing for the same resources.

You might note that Oracle has had

• a connection pooling architecture (multi-threaded server) since version 7 (1992).
• a cache for data pages (known helpfully as the buffer cache) since forever
• parallel query (splitting a query across many processes) since version 7.1 (1993)
• splitting queries across multiple servers since OPS (version 6) or across distributed databases (version 5)

It's also noteworthy that even if all that was said was correct rather than incorrect it doesn't actually help you in determining root cause.

Especially noteworthy, because it uses file system files (not raw partitions), and the "caching" is outside, it relies heavily on (and is very sensitive to) the file system cache that you have set up. likewise, Oracle needs a massive amount of memory for these processes.

Oracle certainly can use raw partitions again dating back to the last millenium, moreover if you wish to cache within the database - using the buffer cache that PerformanceDBA has forgotten about - and bypass the filesystem cache this feature is available on all current filesystems. Oracle also supplies it's own combined filesystem/volume manager in ASM which you can use if you wish.

Oracle is also rather well instrumented (and if you have access to dtrace so is solaris) and can certainly tell you what sessions, processes etc are using the CPU, what the time the application spends in the database is consumed by (down to individual block read times if you care) and so is very susceptible to profiling. I'd recommend that you check out Thinking Clearly about Performance available at http://www.method-r.com/downloads/cat_view/38-papers-and-articles and written by one of the top Oracle Performance experts in the world. If you have access to the Oracle Diagnostics pack then checking out first of all ADDM reports and secondly AWR reports would be profitable.

Trying to avoid a flame war here.

I should probably have separated out the "how to find out" part of my response more clearly from my responses to the comments about server architecture from PerformanceDBA. I share Stephanie's suspicions about the spring framework, but without properly scoped measurement evidence there is no point in blaming any particular attribute of the environment, that would be just particular bias. Fortunately the instrumentation built into the oracle kernel allows you to trace and then profile the slow sessions to determine exactly where the issue lies. So I would do the following:

1) enable tracing for a representative session (you can use the dbms_monitor package for that). 2) also gather an execution plan for the statement(s) involved with the gather_plan_statistics hint. 3) profile the trace file by time using an appropriate profile (tkprof,orasrp,method-r profiler)

Investigate the problem statements in contribution to response time order.

If you can't carry out the above, then you can use ADDM and/or AWR if licenced as I originally suggested or statspack if not licensed for the diagnostics pack. ADDM naturally concentrates on time consumers, I suggest if you are forced down the statspack route you do the same.

The M3000 is certainly a more powerful machine, but it is more suitable for true servers. The X4170 with hyper-threads is more suited for file servers.

I'm not so certain about that. Have any data to support that claim?

An M3000 has one SPARC64 VII processor with 4 cores (tech specs) while a X4170 has 1 or 2 Intel 5500 "Nehalem-EP" processors each with 4 cores (tech specs). I know that I would expect much more from even a single processor Nehalem-EP system, than the M3000. Obviously data will vary slightly with the workload, but I know where I'd put my money.