Workgroup Server query performance on NT -- CPU hog?

I'm running Oracle Workgroup Server 7.2.2.4.1 on NT 3.51 on an Alpha workstation (233MHz) with 96MB RAM (admittedly skimpy) and a 5.5GB database spread across four F/W SCSI drives. It's a mini-data-warehouse, with occasional large queries involving some fairly complex joins.

Surprisingly, I'm getting a better than 90% hit rate on my 45MB SGA, and little or no page faulting. But the CPU is frequently maxed out by a single query. When I watch the NT Performance Monitor graph during a query, I typically see an intial spike or two of I/O (700KB/sec or so for a few seconds), followed by a long period, sometimes several minutes, of 100% CPU utilization and almost no I/O (no single disk goes above 30KB/sec).

It seems like something is radically wrong here. Is Oracle really this much of a CPU hog during queries? I'm looking for a magic bullet to fix this. Anybody got any ideas?

I'm going through "Tuning Oracle" from Oracle Press. So far, some small incremental improvements, but no magic bullets. If anyone could recommend a good tuning book, especially one oriented towards tuning large queries, that would be great!

Thanks,
Barry Schader Received on Thu Apr 10 1997 - 00:00:00 CDT