Oracle 10g中過程PROCEDURE重建的增強

dcba上周有了一個新的發現，在Oracle10g中，當重建一個預存程序時，Oracle的行為和以前有所不同。

在Oracle9i中，即使一個完全相同的過程的重建，Oracle也需要重新編譯過程，這個可以從LAST_DDL_TIME看出：

[oracle@jumper oracle]$ sqlplus eygle/eygle

SQL*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 - Production

SQL> 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:58

SQL> 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/eygle

更多精彩內容：http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

SQL*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 options

SQL> 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:25

SQL> 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

作者：51cto部落格 Oracle小混子