Keep CBO plan stable(plan stability)
- From: zhu chao <zhuchao@xxxxxxxxx>
- To: oracle list <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 13 Oct 2005 21:08:28 +0800
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
