Re: Keep CBO plan stable(plan stability)

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

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