Oracle queries running quick then running slowly
Posted on 2006-11-28
I have an application that uses Oracle as the backend database. I ran a series of tests this morning and the average runtime was 1.9 seconds. I then refreshed Oracle's statistics for the schema and now the same tests are taking over 3 minutes. Can anyone help me understand this behaviour please? The two tests are exactly the same, the only change was that I ran these commands:
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => '&schema', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => DBMS_STATS.AUTO_DEGREE, cascade => DBMS_STATS.AUTO_CASCADE);
The tests only read data, they never write data. Between each test iteration I issue this command:
ALTER SYSTEM FLUSH BUFFER_CACHE
to reduce the impact of data caching.
I'm at a complete loss. We're trying to performance test our application and results changing dramatically like this really throw a wrench in the works.
EDIT: I am running the tests on the same hardware each time. Nobody else is using the test servers. When I check the CPU usage on the database server (Windows) oracle.exe seems to be hovering around 7-8%