Re: Keep CBO plan stable(plan stability)
- From: oracle-l@xxxxxxxxxxxxxxxxxx
- To: zhuchao@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Thu, 13 Oct 2005 11:24:59 -0400 (EDT)
Hi Zhu,
If you have the time, could we please just check everything is as expected
with the segment header for that table:
Grab the file and block id from the following query:
SELECT header_file,
header_block
FROM
dba_segments
WHERE segment_name = 'USER_INFO';
And substitute the returned values into the following query:
ALTER SYSTEM DUMP DATAFILE &header_file BLOCK &header_block;
This should produce a file in your user dump directory, which contains a
line like the following:
#blocks below: 19
It's this value the CBO uses for the NBLK stat. We just need to check that
it's correctly recorded as a large number for your table.
In relation to your other question; no you can't guarantee plan stability
without stored outlines. As discussed, the segment header will change over
time and if someone collects system statistics these could also alter your
execution plan.
Cheers,
Tim
--
//www.freelists.org/webpage/oracle-l
Other related posts:
