| How can I prove plan invalidations [message #142209] |
Thu, 13 October 2005 16:59  |
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 #142444 is a reply to message #142209] |
Fri, 14 October 2005 10:36  |
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 |
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;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
