RE: Do any of you know? (system stats) related to earlier 10053 Trace

From: <Christopher.Taylor2_at_parallon.net>
Date: Fri, 12 Oct 2012 10:23:57 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E88562532912_at_NADCWPMSGCMS10.hca.corpad.net>

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)
2. Exit
3. export system_stats
4. delete system stats
5. purge v$sql info for query using dbms_pool.purge
6. run query:
7. Query execution = terrible. (12 minutes)
8. Exit
9. import system_stats
10. purge v$sql info for query using dbms_pool.purge
11. run query
12. 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Christopher.Taylor2_at_parallon.net Sent: Friday, October 12, 2012 9:43 AM
To: jonathan_at_jlcomp.demon.co.uk; mwf_at_rsiz.com; niall.litchfield_at_gmail.com; oratune_at_yahoo.com; breitliw_at_centrexcc.com Cc: oracle-l_at_freelists.org
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
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Oct 12 2012 - 17:23:57 CEST