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

From: <Christopher.Taylor2@xxxxxxxxxxxx>
To: <Christopher.Taylor2@xxxxxxxxxxxx>, <jonathan@xxxxxxxxxxxxxxxxxx>, <mwf@xxxxxxxx>, <niall.litchfield@xxxxxxxxx>, <oratune@xxxxxxxxx>, <breitliw@xxxxxxxxxxxxx>
Date: Fri, 12 Oct 2012 10:23:57 -0500

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

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

Other related posts: