Well the purge is working - the v$sql_* tables show no information for any cursors related to my queries once I use dbms_pool.purge. Testing DBMS_STATS.GATHER_SYSTEM_STATS impact: 1. Query execution = Great (sub 30 seconds) 3. Exit 4. export system_stats 5. delete system stats 6. purge v$sql info for query using dbms_pool.purge 7. run query: 8. Query execution = terrible. (12 minutes) 9. Exit 10. import system_stats 11. purge v$sql info for query using dbms_pool.purge 12. run query 13. Query execution = Great (sub 30 seconds) Okay so that clinches it - Apparently the timing of my stopping the SYSTEM_STATS interval gathering coincided with my executing the queries earlier this morning. Some of my runs this morning had to have been occurring during the interval gather period (before I stopped it) and then my query plans changed after and I didn't catch it. Summary: Don't be changing things in more than 1 area at a time or you may confuse yourself when things change unexpectedly (as if I didn't already know that). Chris -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Christopher.Taylor2@xxxxxxxxxxxx Sent: Friday, October 12, 2012 9:43 AM To: jonathan@xxxxxxxxxxxxxxxxxx; mwf@xxxxxxxx; niall.litchfield@xxxxxxxxx; oratune@xxxxxxxxx; breitliw@xxxxxxxxxxxxx Cc: oracle-l@xxxxxxxxxxxxx Subject: Do any of you know? When gathering SYSTEM stats, do those stats take a while to affect the query plans once they are collected? (a "while" being some "x" amount of time - I thought they would be immediate) Last night I had executed: dbms_stats.gather_system_stats(gathering_mode=>'NOWORKLOAD'); Once that completed I kicked off: dbms_stats.gather_system_stats(gathering_mode=>'INTERVAL', interval=>1440); This morning I stopped that interval gathering and ran my queries. It "appears" that the system stats change didn't immediately affect my queries but is now affecting my queries. I could see that happening if I was getting a cached plan versus a new plan but my test script is "supposed" to flush the plan out using: dbms_pool.purge. I guess I need to verify that the purge is actually working... Chris -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l