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

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8745319/viewspace-576442/