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

• Dozens more answers to tough Oracle questions from Karen Morton are available.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.