Problem solve Get help with specific problems with your technologies, process and projects.

Oracle.exe taking 99% of CPU

I have Oracle 8i 8.1.7 on Windows 2000 Advanced Server. The size of the database is 3 GB and the machine has 512 MB of RAM and 40 GB of HDD. 20 users access the database concurrently. Sometimes the Oracle process (oracle.exe) takes 99% of CPU even without any user access. It comes to normal level once I restart the database. Sometimes the 99% persists even after restarting the database. The tablespaces have enough space and each datafile in the tablespace doesn't cross more than 500 MB in size. From my analysis, I couldn't find any resource (hardware) scarcity in that. Could you please suggest me why this CPU utilization shoots up to 99%?

What you really need to look at is how is your database configured and used? What is the size of your SGA? What kinds of SQL statements are being ran and are they properly tuned? You need to shift your focus from the OS/hardware side to what's happening with Oracle. You may need to do some tracing and use explain plan to check out high-cost queries. You may need to increase/decrease the size of the shared pool or database buffer cache.

In the end, CPU utilization in and of itself is not the problem. It's the symptom of a problem located elsewhere. My guess is that you've got some bad SQL out there that needs to be tuned.

For More Information

Dig Deeper on Oracle database design and architecture