I am doing performance tuning, after the first run, the data is loaded into buffer cache, so the second run will take shorter time, my question is, how to clear the buffer cache so the second run can be compared at the same basis with the first run? Maybe this is too simple for you, but I am new in this area, please help.
Find out the tables (and the tables behind the views) that the data in the buffer cache is loaded from. Using this info, get the tablespaces these tables belong to. Bring these offline - 'ALTER TABLESPACE <tablespace_name> OFFLINE;' for all the tablespaces. And bring them online - 'ALTER TABLESPACE <tablespace_name> ONLINE;' for all the tablespaces. This will invlidate all the blocks belonging to the above tablespaces.
take your results with a grain of salt. Remember in the "real world", your system may have some of these blocks cached, stats may be different, the planets may not be aligned.
Try to validate your performance tweaks in an environment that is as close as possible to your production system.
