Re: Keep CBO plan stable(plan stability)

From: zhu chao <zhuchao_at_gmail.com>
Date: Fri, 14 Oct 2005 17:22:12 +0800
Message-ID: <962cf44b0510140222r2c7f3a55n61a626471b27825d@mail.gmail.com>

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_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-l

Received on Fri Oct 14 2005 - 04:24:32 CDT