Unused cpu's on Windows2000 Advanced Server?

[efrijters]

Hello,
I'm a Oracle9i dba-trainee and currently working with two clients.

Both have multi-cpu servers (HP), running MS Windows 2000 Advanced Server sp3 with Oracle 8.1.7. Both have RAID5-disk arrays with fiberoptics.

When I copy files via the OS, it performs great. But when I execute jobs or run an export, the server is 98% idle. When I start a second job (concurrently), the first job accelerates. During the normal use, it's the same thing: when servicing a small amount of people it is slow, with larger number of users it performs better.

The strange thing is, that only one cpu is used (for max 50%) and 7 others are doing almost nothing...

The people there are getting angry, because "it doesn't perform..."

What am I overlooking?

Thanks in advance,

Erik

[ggnanaraj]

What are your sga parameters?

   show sga

So also, what about your database hit ratio?

 select round(((1-(sum(decode(name,'physical reads', value,0))/
               (sum(decode(name, 'db block gets', value,0))+
                (sum(decode(name, 'consistent gets', value, 0))))))*100),2)|| '%' "Buffer Cache Hit Ratio"
   from v$sysstat;

Post all these results, including this one..

select * from v$waitstat;

HTHU.

[efrijters]

result SHOW SGA:

Total System Global Area 158230556 bytes
Fixed Size 75804 bytes
Variable Size 124522496 bytes
Database Buffers 33554432 bytes
Redo Buffers 77824 bytes


Buffer Hit Cache Ratio : 97,98%


result v$waitstat: :

CLASS COUNT TIME
------------------ ---------- ----------
data block 15486 0
sort block 0 0
save undo block 0 0
segment header 25 0
save undo header 0 0
free list 0 0
extent map 0 0
bitmap block 0 0
bitmap index block 0 0
unused 0 0
system undo header 0 0
system undo block 0 0
undo header 270 0
undo block 17 0

[ggnanaraj]

Originally posted by efrijters
[Bresult v$waitstat: :

CLASS COUNT TIME
------------------ ---------- ----------
data block 15486 0
sort block 0 0
save undo block 0 0
segment header 25 0
save undo header 0 0
free list 0 0
extent map 0 0
bitmap block 0 0
bitmap index block 0 0
unused 0 0
system undo header 0 0
system undo block 0 0
undo header 270 0
undo block 17 0 [/B]

Waits on data block is more, you can get the exact data file the wait is on using the following query...

SELECT count, file#, name
FROM x$kcbfwait, v$datafile
WHERE indx + 1 = file#
ORDER BY count;

Based on this you have to trouble shoot further. The results on the following queries will also shed some light on the bottleneck that you are facing...

select (sum(pins - reloads)) / sum(pins) "Lib Cache" from v$librarycache;

select (sum(gets - getmisses - usage - fixed)) / sum(gets) "Row Cache"  from v$rowcache;

SELECT * FROM V$SYSTEM_EVENT WHERE EVENT LIKE '%buffer%';

select count(*) number_of_waiters
from v$session_wait w, v$latch l
where w.wait_time = 0
and  w.event     = 'latch free'
and  w.p2        = l.latch#
and  l.name      like 'library%';

HTH.

[efrijters]

...and here are the results:

SELECT count, file#, name
FROM x$kcbfwait, v$datafile
WHERE indx + 1 = file#
ORDER BY count;
The result:

COUNT FILE# NAME
---------- ---------- ------------------------------------
0 | 5 L:\ORACLE\ORADATA\LITP\TOOLS01.DBF
0 | 6 L:\ORACLE\ORADATA\LITP\INDX01.DBF
0 | 7 L:\ORACLE\ORADATA\LITP\DR01.DBF
165 | 8 L:\ORACLE\ORADATA\LITP\MICRO.DBF
233 | 3 L:\ORACLE\ORADATA\LITP\USERS01.DBF
288 | 2 L:\ORACLE\ORADATA\LITP\RBS01.DBF
608 | 1 L:\ORACLE\ORADATA\LITP\SYSTEM01.DBF
16091 | 9 L:\ORACLE\ORADATA\LITP\APPLICATION.DBF

Note: The APPLICATION.DBF holds the applications data and is used the most.



select (sum(pins - reloads)) / sum(pins) "Lib Cache" from v$librarycache;
The result:

Lib Cache
----------
,998807336

Looks good to me


select (sum(gets - getmisses - usage - fixed)) / sum(gets) "Row Cache" from v$rowcache;
The result:

Row Cache
----------
,992224433

Not bad i guess...


SELECT * FROM V$SYSTEM_EVENT WHERE EVENT LIKE '%buffer%';
The result:

EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
--------------------- ----------- -------------- ----------- ------------
free buffer waits | 1 | 0 | 0 | 0
buffer deadlock | 3 | 3 | 0 | 0
buffer busy waits | 17385 | 5 | 0 | 0
log buffer space | 19427 | 1 | 0 | 0



select count(*) number_of_waiters
from v$session_wait w, v$latch l
where w.wait_time = 0
and w.event = 'latch free'
and w.p2 = l.latch#
and l.name like 'library%';
The result:

NUMBER_OF_WAITERS
-----------------
0


This server doesn't go down. We make hot backups. Concurrent users: about 50-80.


Question: on this server, that has 2 cpu's, the parameter "db_block_lru_latches" is ommitted so it has the default value of 1. Must this be the number of cpu's? (so 2 in this case and 8 at the other firm?)

Thanks in advance,
Erik

[ggnanaraj]

For 'buffer busy waits' check this out...
http://metalink.oracle.com/metalink/...showHelp=1#DEF


You may have to reorganize and share I/O to different disk/tablespaces to overcome waits on the datafiles based on the size of the objects (160k, 5m, 160m). Check this out...

1 Tablespaces with initial=next = 160K, pctincrease=0
2 Tablespaces with initial=next = 5M, pctincrease = 0
3 Tablespaces with initial=next = 160M, pctincrease = 0

Will place the objects that require total space less than 5M in tablespace 1

All medium size tables that require total space >5M and <=160M in tablespace 2

All large size tables that require total space >=160M in tablespace 3

Reference: http://dbasupport.com/forums/showthr...60k+tablespace

HTH.

[efrijters]

Okay, thanks so far. I will look into this solution.

The remaining questions are:
- What makes Oracle use more cpu's?
- Which parameters are involved in this?

Thanks in advance,
Erik

[adewri]

Originally posted by efrijters
Okay, thanks so far. I will look into this solution.

The remaining questions are:
- What makes Oracle use more cpu's?
- Which parameters are involved in this?

Thanks in advance,
Erik

Oracle as a database doesnot consume CPU, its negligible, its the application that uses CPU. Oracle uses RAM(SGA).

HTH

[efrijters]

Okay, point taken...

The application is build in Forms6 and runs via IAS.

Maybe MS Windows 2000 Advanced Server (sp3) is the problem?

I just started to work as a DBA and I only encountered MS Windows 2000 Advanced Server as the OS up to now.

As far as I know, the OS's were installed with basic configurations.

Any suggestions?
(No, they won't buy Unix/Linux/whatelsehaveyounix)

Thanx,
Erik

[DaPi]

Originally posted by efrijters
During the normal use, it's the same thing: when servicing a small amount of people it is slow, with larger number of users it performs better.

The strange thing is, that only one cpu is used (for max 50%) and 7 others are doing almost nothing...

As Amar says, it's rare for a db server to be CPU bound (Tony AJW_ID01 has a counter-example). AFAIK, Oracle.exe will normally use just one CPU - except when a parallel query (in Full Table Scan) will use several CPU's in parallel. (So 2x2GHz would be much better than 8x500MHz.)

Could the improvement in performance as the number of users increases be due to the accumulation of data in buffers and sharable queries in the shared pool?

My only comment is that 33Mb for database buffers looks small for such a "hairy" machine. But a 98% hit rate suggests this is enough . . .