Shared Pool December 13, 2007
Posted by Duncan in Oracle, PeopleSoft, SQL.trackback
Oracle Databases contain a ‘shared pool’, which is a repository for recently run SQL. If you attempt to execute the same piece of SQL repeatedly you’ll probably notice that the execution time decreases, this is because of how Oracle parses the SQL (i.e. it’s a soft parse, not a hard parse) and the execution plan is already calculated for the SQL. I’ve spoken about this before in an App Engine context here.
This cache (or to use the correct term the ‘Shared Pool’) is all very useful, but what if you’re trying to tune a piece of SQL? You want to get a ‘natural’ idea of how long it’ll take to run, not the artificially quick result you’ll get if it’s in the shared pool. (Execution Time is of course not the only measure to consider when tuning, examining the execution plan is critically important.)
We need to flush any mentions of our SQL from the shared pool. DBAs can clear it by issuing the ‘ALTER SYSTEM FLUSH SHARED_POOL’ command, but us un-privileged developers don’t have that luxury (and neither would we want to clear the entire cache).
Instead, re-calculating the statistics on a table means that “all shared SQL areas that contain statements referencing the analyzed schema object are flushed from the shared pool”.
A fuller (and slightly more expertly worded) explanation is available here:
http://download-west.oracle.com/docs/cd/B12037_01/server.101/b10743/memory.htm
Comments
Sorry comments are closed for this entry
The PeopleSoft Tipster Blog 
Cacheing is what the database does. Cacheing will happen in production environments. People pay good money for the software and the RAM to let cacheing happen properly and effectively.
So to declare that you should run a piece of SQL in a non-cached way because that’s its “natural way” of being run is, to me, utterly bizarre. Not cacheing something is about as UNnatural a way of doing things as you can get.
The statement, “We need to flush any mentions of our SQL from the shared pool” is, in other words, seriously loopy!
On the contrary, you definitely WANT to time your SQL in a cached environment that is similar in size and stress to what would exist in a production setting. Indeed, timings based on non-cached results are nigh-on meaningless.
I think I understand what Duncan is saying here. When developing SQL I want to know what my users will experience the first time they run it not the “artificial” run times I get as a developer because I have execeuted this SQL repeatedly and ended up with all my data cached.
>> Indeed, timings based on non-cached results are nigh-on meaningless.
Nort necessarily. In development I might run some SQL statment hundreds of times a day (because I’m developing it) but in production the same SQL might only get run once or a few times a day. I have 16Gb of RAM in my production DB server and old data will get thrown out of cache very rapidly.
Knowing how to flush cached data and cached SQL statements in development is very useful in order to better predict run times in production.