This is not a joke.
I have a SQL statement which I want to make execute as slow as possible.
What are the ways to do so without changing the statement?
I tried the following:
alter system flush shared_pool;
alter system flush buffer_cache;
After that performance slowed considerably (1-2 minutes) but not as much as I want.
Background story:
For a performance tuning project we started with a very slow running query.
We managed to make it extremly fast (< 1 sec). So that was a full success.
During the tests sometimes the following happend.
For the original problem statement the very first run on that day was extremly slow (like 4-6 minutes).
The second next run was clearly faster (less than a minute). This indicates some kind of caching effect.
Executing the above mentioned commands slowed the statement down to 100 secs.
I can not fully explain where this difference (100 secs << 4-6 minutes) is coming from.
Ideally I want to be able to recreate the same problematic environment and then run my fast version of the statement and see how it behaves under those circumstances.
The statement itself accesses several XML documents stored in clobs (secure file). There are structured xmlindizes providing fast access paths. They are not used for the problem statement. Part of the optimization effort had to do with allowing the optimizer to find and use those indexes.
Problem is we can not really recreate the "extremly slow" scenario.
Any ideas / statements that I can try to make it slow again?
Is there any other type of cache that I overlook?
I am considering to drop all statistics too.
exec DBMS_STATS.PURGE_STATS;
But I do not see yet, why having no statistics would result in a slow first and a considerbly faster second run.