Performance Issues on Solaris 10

[rgaskell]

Ok scenario

Script to hammer CPU:

DECLARE
LIM NUMBER;
BEGIN
LIM := 100000000;
FIBONACCI ( LIM );
COMMIT;
END;
/



Oracle 9.2.0.8 on windows 4 CPUS --> 4 mins
Oracle 9.2.0.8 on Solaris 10, 32 CPUS --> 22 mins


For some reason CPU per oracle process on Solaris 10 isn't going above 3%-4% whereas on windows it spikes straightaway (60%-70%)and runs a lot quicker.

Any ideas? Are the processes cpu capped somehow?

Thanks

[davey23uk]

trace it, see where the time is being taken

[rgaskell]

Windows/Oracle 9i takes 4 mins
DECLARE
LIM NUMBER;
BEGIN
LIM := 100000000;
FIBONACCI ( LIM );
COMMIT;
END;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 260.09 268.77 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 260.10 268.77 0 0 0 1

Misses in library cache during parse: 1
Optimizer mode: RULE
Parsing user id: 47

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 183.55 183.55



Oracle9i/Solaris 10 take 20 mins+


DECLARE
LIM NUMBER;
BEGIN
LIM := 100000000;
FIBONACCI ( LIM );
COMMIT;
END;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.01 0 0 0 0
Execute 2 1592.53 1555.19 0 0 0 1Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1592.54 1555.20 0 0 0 1

Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 61

I'm convinced there is some sort of capping on cpu going on Solaris10.
Windows goes to 100% when the sql is run. Solaris 10 sits there at 4% CPU.

[davey23uk]

and your wait events are?

[rgaskell]

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 930.47 943.94
SQL*Net break/reset to client 1 0.00 0.00



We are having the same issue as http://forums.sun.com/thread.jspa?threadID=5339427

[marist89]

I've left Solaris behind at version 9, but from my very limited experience (~15 years) the total CPU a single process can consume is equal to 100/number of CPUs you have. For example, if you have a four CPU box, any one CPU will never get past 25%. You have a 32 CPU machine, so any one process will never get past 3.2%.

Now, that's not to say you don't have some throttling software on your box, but I doubt it.

Part of your problem is this:

Execute 1 260.09 268.77 0 0 0 1
Execute 2 1592.53 1555.19 0 0 0 1

In the second case, you're executing the PL/SQL block twice, or double the work of the first. In theory, that puts your comparison of 4.5 mins to about 12 mins.

The strength of your Solaris box isn't it's raw computing power. It's the near linear scalability of the CPUs and the rock-solid reliability of the OS/Hardware combination. But you're seeing why so many people, myself included, have moved away from Sun and onto commodity servers.

[gandolf989]

I took a "C" class last year and wrote a program that created a dynamic array from a file. one of the files had 255K words, the program did an insert in ordedr with a binary search. On my Centrino Duo it ran in 2 minutes 10 seconds, on my dual core Debian box it ran in one minute 37 seconds. I compiled the same code for Solaris 10 and after 12 minutes it was still running.

I would have to agree with Jeff with his comments about Sun. Where I work, we are in the process of migrating towards Dell boxes.