CPUs idle 3-5% !!!

Hi all, I work for one of the Stock Exchanges, and at the market open time there are a lot of inserts into multiple tables and selects from multiple tables for a matter of 2-3 mins. At this time the idle time per CPU (on our 8 CPU UNIX box) is about 3-5% !!! Bad, Very BAD !!!

So, any suggestions as to what I should look at to tune the DB so that the CPU will be more efficiently utilized ?

BTW, we just upped our CPUs from 4 to 8.

Thanks in advance for the help.

What happens after the 3rd minute?

Are there a lot of log switches when this happens? If so, bigger redo logs would probably help with the inserts.

After the 3rd min, the CPU average utilization goes down to 40-60%;

and yes there are alot of REDO log switches: 1 250MB file every 5-7 mins.

another thing you can look into with the inserts is make sure that you really need all of your indexes. Indexes slow down inserts.

also, you might look into your app and see if it is possible to do array binding. what this means is that if your query looks like this :

insert into mytable(col1, col2, col3, col4)
values (:val1, :val3, :val4);

your 'val' variables contain ARRAYS instead of just single
values. If your arrays contain 100 values, then you execute
a query 1 time instead of 100 times.

If your inserts look more like this :

insert into mytable(col1, col2, col3, col4)
values ('x', 'y', 'z', 'zz');

with hard-coded values then you should definately switch to variables. You will notice a big improvement.

any other suggestions of decreasing the CPU utilization ?

Are you having any freelist contention? run this query just after or during your peak periods...

SELECT round((sum(W.COUNT) / SUM(S.VALUE)) * 100, 2) "Free List Wait Ratio"
FROM SYS.V_$WAITSTAT W, SYS.V_$SYSSTAT S
WHERE W.CLASS = 'free list'
and s.name in ('db block gets', 'consistent gets')

Oracle Recommends that this ratio be less than 1. If it is greater than 1, then you might want to recreate some of your most active tables with a greater value for FREELISTS.

that's all I can come up with....

Hrm you said that 2-3 minutes the cpu util is very high, but the huge logs are created every 5-7 minutes. By that I assume that all the inserts/selects continue longer than the 2-3 minutes, but only the preliminary 2-3 minutes are the problem, Is that a correct assumption?

What else is happening for those 2-3 minutes? 100 people opening up applications/sessions to start doing the inserts/selects? Other preliminary work?

I guess I need to dive a little deeper into the activity that happens that 2-3 mins. I think there are several selects that take place at that time to set up some daily tables, but no, no users login to this db, it is more for the trading system inserts.

Thank you all for your input.

Take a look at sar -q and vmstat during the high-cpu period.

If the run-queue doesn't go up dramatically, it may not be a problem.

Is it possible a lot connections are being made ? Creating a connection is very expensive on memory and CPU.

Do you have enough memory ?

using sar -u, when you have the low cpu-idle, what's high ?
user ? system ? waiting for i/o ?

If waiting for i/o is high, it's possible it's paging.
if user is high, I think it the creation off connections.

Regards
Gert