Home » RDBMS Server » Performance Tuning » How can I prove plan invalidations
How can I prove plan invalidations [message #142209] Thu, 13 October 2005 16:59 Go to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I know that gathering statistics invalidates parsed query plans for associated objects. How can I prove this?

I've tried looking at invalidation counts in v$sql but am not having any luck. I haven't checked the 9i docs yet, but the 10g don't seem to be of much help (or else I'm looking in the wrong place). Anyone point me in the right direction or have a test case?
Re: How can I prove plan invalidations [message #142227 is a reply to message #142209] Thu, 13 October 2005 18:55 Go to previous messageGo to next message
cybotto
Messages: 12
Registered: October 2005
Junior Member
Yep I have this with bind variables as well.

There must be some hidden flag which we can't see in V$... which marks queries/cursors/bind variables invalid. I can't help you but I know what you mean.
Re: How can I prove plan invalidations [message #142444 is a reply to message #142209] Fri, 14 October 2005 10:36 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
According to the oracle docs, and also tom kytes site, gathering stats invalidates plans. But I want to show/prove it, in response to your other topic. I've run some tests on my 10g where the only change I make is gathering stats, but havn't found the right way to prove it yet. There are several views to look, such as v$sql, and there is an invalidations column, but I'm not there yet. I need to keep searching when I get time.
Re: How can I prove plan invalidations [message #142474 is a reply to message #142209] Fri, 14 October 2005 13:52 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Update. It appears my problem was the no_invalidations parameter to the dbms_stats package, which changes default values when moving from 9i to 10g. 10g actually appears to behave better, and as a result it wasn't invalidating my plan when I gathered statistics, which in this case was the right thing to have done (or not done). So here is the proof:

MYDBA@ORCL > 
MYDBA@ORCL > -- watch out for the no_invalidations param to dbms_stats which changes default value
MYDBA@ORCL > -- when moving from 9i to 10g!  Used analyze table command to bypass this issue,
MYDBA@ORCL > -- although could have adjusted the value of this param instead.
MYDBA@ORCL > 
MYDBA@ORCL > col sql_text format a50
MYDBA@ORCL > 
MYDBA@ORCL > alter system flush shared_pool;

System altered.

MYDBA@ORCL > 
MYDBA@ORCL > create table crazy_table(a, b) as select rownum, 'x' from all_objects where rownum <= 1000;

Table created.

MYDBA@ORCL > create index idx on crazy_table(a);

Index created.

MYDBA@ORCL > 
MYDBA@ORCL > --exec dbms_stats.gather_table_stats(user,'crazy_table',cascade=>true,method_opt=>'for all columns size 250');
MYDBA@ORCL > analyze table crazy_table compute statistics;

Table analyzed.

MYDBA@ORCL > 
MYDBA@ORCL > select * from crazy_table where a = 10;

         A B
---------- -
        10 x

1 row selected.

MYDBA@ORCL > 
MYDBA@ORCL > select sql_text, invalidations from v$sql where sql_text like 'select * from crazy_table%';

SQL_TEXT                                           INVALIDATIONS
-------------------------------------------------- -------------
select * from crazy_table where a = 10                         0

1 row selected.

MYDBA@ORCL > 
MYDBA@ORCL > select * from crazy_table where a = 10;

         A B
---------- -
        10 x

1 row selected.

MYDBA@ORCL > 
MYDBA@ORCL > select sql_text, invalidations from v$sql where sql_text like 'select * from crazy_table%';

SQL_TEXT                                           INVALIDATIONS
-------------------------------------------------- -------------
select * from crazy_table where a = 10                         0

1 row selected.

MYDBA@ORCL > 
MYDBA@ORCL > --exec dbms_stats.gather_table_stats(user,'crazy_table',cascade=>true,method_opt=>'for all columns size 250');
MYDBA@ORCL > analyze table crazy_table compute statistics;

Table analyzed.

MYDBA@ORCL > 
MYDBA@ORCL > select sql_text, invalidations from v$sql where sql_text like 'select * from crazy_table%';

SQL_TEXT                                           INVALIDATIONS
-------------------------------------------------- -------------
select * from crazy_table where a = 10                         1

1 row selected.

MYDBA@ORCL > 
MYDBA@ORCL > drop table crazy_table;

Table dropped.

MYDBA@ORCL > 
MYDBA@ORCL > set echo off;

Previous Topic: Analytic function but bad performance (merged 5 cross-posts)
Next Topic: sample tuning help
Goto Forum:
  


Current Time: Fri Mar 29 06:27:48 CDT 2024