« 进京四周年记 | Blog首页 | library cache pin与PROCEDURE的重建 »
Oracle10g中过程(PROCEDURE )重建的增强
作者：eygle |【转载时请以超链接形式标明文章出处和作者信息及本声明】链接：http://www.eygle.com/archives/2007/04/oracle10g_last_ddl_time.html
dcba上周有了一个新的发现，在Oracle10g中，当重建一个存储过程时，Oracle的行为和以前有所不同。
在Oracle9i中，即使一个完全相同的过程的重建，Oracle也需要重新编译过程，这个可以从LAST_DDL_TIME看出：
[oracle@jumper oracle]$ sqlplus eygle/eygleSQL*Plus: Release 9.2.0.4.0 - Production on Sat Mar 31 17:52:55 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - ProductionSQL> create or replace PROCEDURE pining
2 IS
3 BEGIN
4 NULL;
5 END;
6 /Procedure created.
SQL> select object_name,last_ddl_time from dba_objects where object_name='PINING';
OBJECT_NAME LAST_DDL_TIME
------------------------------ -------------------
PINING 2007-03-31 17:52:58SQL> create or replace PROCEDURE pining
2 IS
3 BEGIN
4 NULL;
5 END;
6 /Procedure created.
SQL> select object_name,last_ddl_time from dba_objects where object_name='PINING';
OBJECT_NAME LAST_DDL_TIME
------------------------------ -------------------
PINING 2007-03-31 17:54:35
在Oracle10g中，这个LAST_DDL_TIME不再变化，这说明在10g中，当我们执行create or replace PROCEDURE 时，Oracle现在先尝试进行过程检查，如果内容没有变化，则不需要对过程进行重新编译，这可以减少Cache中的Invalidation，从而可以减少竞争：
$ sqlplus eygle/eygleSQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 31 17:44:46 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining optionsSQL> create or replace PROCEDURE pining
2 IS
3 BEGIN
4 NULL;
5 END;
6 /Procedure created.
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> col object_name for a30
SQL> select object_name,last_ddl_time from dba_objects where object_name='PINING';OBJECT_NAME LAST_DDL_TIME
------------------------------ -------------------
PINING 2007-03-31 17:45:25SQL> create or replace PROCEDURE pining
2 IS
3 BEGIN
4 NULL;
5 END;
6 /Procedure created.
SQL> select object_name,last_ddl_time from dba_objects where object_name='PINING';
OBJECT_NAME LAST_DDL_TIME
------------------------------ -------------------
PINING 2007-03-31 17:45:25
然而这个变化是否有效呢？请看我接下来的另外一个测试...
-to be continued ....
历史上的今天...
>> 2005-04-02文章:
>> 2004-04-02文章:
------
这篇 【Oracle10g中过程(PROCEDURE )重建的增强】来自 eygle.com | CSDN网摘| del.icio.us|Google订阅 | 鲜果订阅 | 抓虾订阅
By eygle on 2007-04-02 09:23 | Comments (4) | Posted to SQL.PLSQL | Edit |
留言 (4)
确实是一个好的变化
Posted by: edeed at April 2, 2007 10:09 AM
另一篇文章在哪里呢？
Posted by: Jack at August 8, 2007 11:04 PM
还是学习呀``
Posted by: 鬼故事 at August 21, 2008 4:06 AM
学无止境！
Posted by: eygle at August 21, 2008 2:19 PM