ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 不要轻易改变对象的结构-概念

不要轻易改变对象的结构-概念

原创 Linux操作系统 作者:jason_wang2002 时间:2009-03-23 15:54:37 0 删除 编辑
Oracle数据库中的objects之间是有关联的,通过查询dba_dependencies得到所有objects的层次关系
当对一个object改变结构时,例如alter、drop,recompile等,都会使它的dependent object变成invalid状态。如果dependent object变成invalid状态,会在运行时进行重新解析和重新编译。重新解析(硬解析)和重新编译会消耗系统的资源,造成竞争。下面使用实验的方式说明改变reference object的状态后,dependent object会重新解析(硬解析)


创建表和视图

SQL> create table t(a int,b char);

Table created.

SQL> create view v_t as select * from t;

View created.


第一次查询V_T视图,并查询parse count (hard)数量和EXECUTIONS次数

SQL> select * from v_t;

no rows selected

SQL> select n.NAME,m.VALUE
  2  from v$statname n,v$mystat m
  3  where n.STATISTIC# = m.STATISTIC#
  4  and n.NAME = 'parse count (hard)';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse count (hard)                                                     1061

SQL> select sql_text,t.EXECUTIONS,t.INVALIDATIONS
  2  from v$sqlarea t where sql_text like 'select * from v_t%';

SQL_TEXT                                           EXECUTIONS INVALIDATIONS
-------------------------------------------------- ---------- -------------
select * from v_t                                           1             0


第二次查询V_T视图,并查询parse count (hard)数量和EXECUTIONS次数

SQL> select * from v_t;

no rows selected

SQL> select n.NAME,m.VALUE
  2  from v$statname n,v$mystat m
  3  where n.STATISTIC# = m.STATISTIC#
  4  and n.NAME = 'parse count (hard)';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse count (hard)                                                     1061

SQL> select sql_text,t.EXECUTIONS,t.INVALIDATIONS
  2  from v$sqlarea t where sql_text like 'select * from v_t%';

SQL_TEXT                                           EXECUTIONS INVALIDATIONS
-------------------------------------------------- ---------- -------------
select * from v_t                                           2             0

从上面查询可以看出,parse count (hard)没有增加,也就是说没有硬解析,只有EXECUTIONS变成了2

下面改变表T的结构,增加一个字段C

SQL> alter table t add(c char);

Table altered.

SQL> select n.NAME,m.VALUE
  2  from v$statname n,v$mystat m
  3  where n.STATISTIC# = m.STATISTIC#
  4  and n.NAME = 'parse count (hard)';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse count (hard)                                                     1062

SQL> select sql_text,t.EXECUTIONS,t.INVALIDATIONS
  2  from v$sqlarea t where sql_text like 'select * from v_t%';

no rows selected

SQL> select object_name,status from user_objects where object_name in ('T','V_T');

OBJECT_NAME                    STATUS
------------------------------ -------
T                              VALID
V_T                            INVALID

从上面查询可以看出,增加字段C使硬解析增加到1062,查询v$sqlarea,这个sql_text已经被踢出去了,并且V_T状态变为INVALID。再次查询V_T,数据库会重新硬解析这个语句。

SQL> select * from v_t;

no rows selected


SQL> select n.NAME,m.VALUE
  2  from v$statname n,v$mystat m
  3  where n.STATISTIC# = m.STATISTIC#
  4  and n.NAME = 'parse count (hard)';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse count (hard)                                                     1164

SQL> select sql_text,t.EXECUTIONS,t.INVALIDATIONS
  2  from v$sqlarea t where sql_text like 'select * from v_t%';

SQL_TEXT                                           EXECUTIONS INVALIDATIONS
-------------------------------------------------- ---------- -------------
select * from v_t                                           1             1

SQL> select sql_text,t.EXECUTIONS,t.INVALIDATIONS
  2  from v$sqlarea t where sql_text like 'select * from v_t%';


OBJECT_NAME                    STATUS
------------------------------ -------
T                              VALID
V_T                            VALID


查询V_T时,数据库重新解析了这条语句,parse count (hard)增加到了1164,v$sqlarea中又查询到了这条语句,EXECUTIONS变为1,INVALIDATIONS从0变为了1

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8745319/viewspace-576442/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-03-05

  • 博文量
    35
  • 访问量
    40885