1. Set event 10052 at parameter file to disable cleanup of OBJ$ by SMON
EVENT="10052 trace name context forever, level 65535"
2. Startup database in restricted mode
3. Delete from OBJ$, COMMIT
SQL> delete from obj$ where (name,owner#) in ( select o.name,u.user# from user$ u, obj$ o
where u.user# (+)=o.owner# and o.type#=10 and not exists (select p_obj# from
dependency$ where p_obj# = o.obj#) );
SQL> commit;
SQL> Shutdown abort.
4. remove event 10052 from init.ora
5. Restart the database and monitor for the message in the ALERT LOG file
SMON的作用还包括维护col_usage$列监控统计信息基表。
最早在9i中引入了col_usage$字典基表,其目的在于监控column在SQL语句作为predicate的情况,col_usage$的出现完善了CBO中柱状图自动收集的机制。
create table col_usage$
(
obj# number, /* object number */
intcol# number, /* internal column number */
equality_preds number, /* equality predicates */
equijoin_preds number, /* equijoin predicates */
nonequijoin_preds number, /* nonequijoin predicates */
range_preds number, /* range predicates */
like_preds number, /* (not) like predicates */
null_preds number, /* (not) null predicates */
timestamp date /* timestamp of last time this row was changed */
)
storage (initial 200K next 100k maxextents unlimited pctincrease 0)
/
create unique index i_col_usage$ on col_usage$(obj#,intcol#)
storage (maxextents unlimited)
/
在10g中我们默认使用’FOR ALL COLUMNS SIZE AUTO’的柱状图收集模式,而在9i中默认是’SIZE 1′即默认不收集柱状图,这导致许多9i中正常运行的应用程序在10g中CBO执行计划异常,详见;。’SIZE AUTO’意为由Oracle自动决定是否收集柱状图及柱状图的桶数,Oracle自行判断的依据就来源于col_usage$字典基表,若表上的某一列曾在硬解析(hard parse)过的SQL语句中充当过predicate(通俗的说就是where后的condition)的话,我们认为此列上有收集柱状图的必要,那么col_usage$上就会被加入该列曾充当predicate的记录。当DBMS_STATS.GATHER_TABLE_STATS存储过程以’SIZE AUTO’模式执行时,收集进程会检查col_usage$基表以判断哪些列之前曾充当过predicate,若充当过则说明该列有收集柱状图的价值。
SMON会每15分钟将shared pool中的predicate columns的数据刷新到col_usage$基表中(until periodically about every 15 minutes SMON flush the data into the data dictionary),另外当instance shutdown时SMON会扫描col_usage$并找出已被drop表的相关predicate columns记录,并删除这部分”orphaned”孤儿记录。
阅读(1112) | 评论(0) | 转发(0) |