Long Running Count Function
586595Jul 5 2007 — edited Jul 10 2007I'm new to the Windows environment for running Oracle and I'm having some performance issues with a report that I'm reviewing....
I found that one of the tables being utilized by this report has 100,000,000 rows in it....When I run a SELECT COUNT(*) FROM T_FIELD, it takes 75 seconds to return the result....
Although this seems like a lot of data to simply count and return shouldn't take that long....In my experience on Unix environments, a similar request would take 3 - 4 seconds....
Is there a way to tell, if this is a performance issue with the box, Oracle configuration, or just not enough stuff in the box to properly power our Oracle environment with sufficient speed....
I've set up STATSPACK and run while executing this COUNT......And compared the report against the idle database.....But I'm not sure what it's telling me....
Here are some specifics about my environment.....
Server: Windows Server 2003 - Service Pack 1
Processor - Xeon 3.4 GHz(1)
Memory - 3.5 Gig
Disk - C:\ 67.8 Gig
D:\ Raid 5 - 838 Gig
Oracle 9.2.0.8.0 Enterprise Edition
SGA Environment
Shared Pool - 152 MB
Buffer Cache - 256 MB
Lagre Pool - 104 MB
Java Pool - 80 MB
SGA Max Size - 617.071 MB
Any help in determining why this COUNT function takes so long would be greatly appreciated....
JDB