Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Keep CBO plan stable(plan stability)
Hi, Tim,
The segment header dump is like: (for the table with wrong plan)
*** 2005-10-14 02:16:50.796
*** SESSION ID:(4612.10519) 2005-10-14 02:16:50.794
Start dump data blocks tsn: 12 file#: 30 minblk 38409 maxblk 38409
buffer tsn: 12 rdba: 0x07809609 (30/38409)
scn: 0x0315.8fb11a2a seq: 0x01 flg: 0x00 tail: 0x1a2a1001
frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
Extent Control Header
Extent Header:: spare1: 0 spare2: 0 #extents: 10 #blocks: 127999 last map 0x00000000 #maps: 0 offset: 4128 Highwater:: 0x06c06003 ext#: 5 blk#: 11770 ext size: 12800#blocks in seg. hdr's freelists: 66
Unlocked Map Header:: next 0x00000000 #extents: 10 obj#: 6749 flag: 0x40000000Extent Map
As for the plan stability, while running CBO, ifI do have statistics, Is there any other case, that CBO is still able to change the plan? Can you give me a test case?
Thanks very much.
On 10/13/05, oracle-l_at_timothyhopkins.net <oracle-l_at_timothyhopkins.net> wrote:
> 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
>
>
-- Regards Zhu Chao www.cnoug.org -- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 14 2005 - 04:24:32 CDT
![]() |
![]() |