Re: Keep CBO plan stable(plan stability)
- From: zhu chao <zhuchao@xxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxxxxxxx>
- Date: Fri, 14 Oct 2005 17:22:12 +0800
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
#blocks below: 75769
mapblk 0x00000000 offset: 5
Unlocked
Map Header:: next 0x00000000 #extents: 10 obj#: 6749 flag: 0x40000000
Extent 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@xxxxxxxxxxxxxxxxxx <oracle-l@xxxxxxxxxxxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
