For performance testing in our performance environment we want a means to turn over SGA the way production does. Our performance environment is a fraction of production and production's very nature makes it difficult to simulate on the performance environment.
When we test on the performance environment we tend to be very focussed on the type of workload we are testing and on the data that we have configured for the scenario. The result of all of this is that after a few runs we get performance improvements that we are not likely to see on production because, on the performance environment we are churning over the same data again and again in cache.
Clearing SGA (its an Oracle environment) also clears system level buffers for table definitions and schemas that negatively impact performance the first time we test. The second time we test the cache is too full of data and are not simulating the IO properly.
The solution we have imagined is to have an app read data from some purpose built table to 'turn over' SGA in the same way diverse data and workloads turn over SGA in production.
The imagined solution has this app connect to each database on the cluster in turn (bypassing the connection load balancer) and reading a row from the database from a purpose built table. The app round-robbin's the connections reading a row in turn and returning some small portion of the data but forcing Oracle to read the block. Given that the purpose built table contains more row data in KB's than the total SGA of the cluster, and given that we throttle the reads we can then say that we turn over all SGA in N minutes, which is what we want. With this we should be able to simulate SGA turn over at different rates and thereby simulate how our test load behaves under the varying load levels that production experiences.
The above spec imagines a row of fixed size that will be small enough to fit into a block but large enough that no more than one row will fit into a block. This will make the calculations easier when calculating for how many rows are required to turn over SGA or what rate to read the rows at to turn over SGA say in 5 or 10 minutes.
My question is this - what will the imagined row definition look like given that we use 8K block sizes. What other parameters will be advised and what gotchas do we have to consider in this imagined solution?
Any competing ideas are most welcome.