Re: Keep CBO plan stable(plan stability)

Hi,
  Thanks Wolfang/Karen/Lex for your answers!   Let me explain with more detail.
  The major application is still using Rule Based Optimizer, as you see our optimizer_mode is rule.

   For that specific SQL, As RBO inable to use in-list efficiently, we added the no_expand hint. it works fine in oracle 8.1.7 for several years. But after we deploy the application in oracle 9.2, it seems have some trouble. I am suggesting we analyze the underlying two tables, but as this is the key tables of our application, Management/Other DBA are very cautious to this change. Without the hint, oracle use concat, which make big use of shared pool. We don't like it.

--The plan could change every time it is hard parsed. Could you please explain more about this? What kind of factor can lead to the plan change? The table size(from segment header, the NBLK? anything else?)

Wolfgang, thanks for your clear explaination. --the optimizer will get the actual number of blocks from the table's segment header
But in our case, it seems oracle is not getting the NBLK correctly from the segment header. The actual table size is like : If you are interested, I can send you the two trace files from different hosts. SQL> exec show_space('USER_INFO')

Free Blocks.............................62
Total Blocks............................128000
Total Bytes.............................1048576000
Unused Blocks...........................41535
Unused Bytes............................340254720
Last Used Ext FileId....................25
Last Used Ext BlockId...................12809
Last Used Block.........................9665

SQL> 1* select
table_name,num_rows,partitioned,blocks,empty_blocks,last_analyzed from dba_tables where table_name='USER_INFO'
SQL> / TABLE_NAME NUM_ROWS PAR BLOCKS EMPTY_BLOCKS LAST_ANALYZED

-------------------- ---------- --- ---------- ------------ ---------------
USER_INFO                  NO

Another question about CBO:
If we do keep have the statistics for CBO, and after some time's running we feel satisfied with current execution plan/database performance, we don't analyze any table again (to keep the plan stable, even with data distribution change, we want to reuse current plan), will CBO keep the plan unchanged afterwords? of course other things like optimizer related parameter does not change.

Thanks!
On 10/13/05, Karen Morton <Karen.Morton_at_hotsos.com> wrote:
> Love that analogy Lex!
>
> To add another bit, you said you have your instance set to RULE and don't analyze your tables but use the NO_EXPAND hint which, as you noted, causes the CBO to kick in. My question is why would you put a hint in that causes the CBO to kick in at all when you seem to want rule-based optimization? What happens without the hint? I suspect you're trying to avoid some query transformation that is being done. But, as Lex said, you're forcing the CBO to try and do it's job with a severe handicap and unless you give it statistics to help it, your results will be unpredictable.
>
> Also, your last question was "When we do not analyze tables, when will CBO change the plan?". The plan could change every time it is hard parsed. How often that happens depends on how often the current plan gets aged/flushed out.
>
>
>
> Karen Morton
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
>
>
>
>
>
>
>
> -----Original Message-----
> From: Lex de Haan [mailto:lex.de.haan_at_naturaljoin.nl]
> Sent: Thursday, October 13, 2005 9:55 AM
> To: zhuchao_at_gmail.com
> Cc: oracle list
> Subject: Re: Keep CBO plan stable(plan stability)
>
> let me answer with a counter question:
> why do you ask the CBO to optimize your SQL statements without statistics?
> that's like forcing someone without legs to run the marathon ...
> you might at least allow the CBO to perform dynamic sampling.
>
> to answer your question, in the absence of statistics, the CBO uses a mix of hard-coded built-in values, information from the segment header, and information from the row cache.
>
> cheers,
>
> Lex.
>
> > hi, all,
> > Recently we have a SQL changed the plan, without any init
> > parameter change, and table analyze.
> > Oracle version is 9.2.0.5, and optimizer_mode=rule. Tables are not
> > analyzed, but we have a no_expand hint, so actually SQL is using CBO.
> > We have 3 database running the identical application, and 1
> > database's plan is good, but two database have their pan
> > changed(previously it should be fine, as we see no timeout in
> > application, and no heavy SQL in statspack). Plan seems changes as
> > data volume changed.
> > I tried to understand why the plan is different on two different
> > database with exact setting (the data volume is a bit didfferent, say,
> > one db the table is 5gb, and another db the table is 2gb). I tried to
> > trace with 10053, and I found Oracle saw different "number of blocks"
> > for the two tables in two database. Table is not analyzed.
> > Table with correct plan:
> > TOTAL :: (NOT ANALYZED) CDN: 2000 NBLKS: 2530 AVG_ROW_LEN: 100
> > table with wrong plan:
> > TOTAL :: (NOT ANALYZED) CDN: 2000 NBLKS: 60 AVG_ROW_LEN: 100
> >
> > Both data for NBLKS are wrong.
> >
> > So I have two questions:
> > 1. How does CBO get this information?
> > 2. When we do not analyze tables, when will CBO change the plan?
> >
> > Thansk
> >
> >
> > --
> > Regards
> > Zhu Chao
> > www.cnoug.org
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
Regards
Zhu Chao
www.cnoug.org
--
http://www.freelists.org/webpage/oracle-l