Oracle10g中過(guò)程(PROCEDURE )重建的增強(qiáng)

2024-08-29 13:33:47

字體：大中小

供稿：網(wǎng)友

在Oracle9i中，即使一個(gè)完全相同的過(guò)程的重建，Oracle也需要重新編譯過(guò)程，這個(gè)可以從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 - 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中，這個(gè)LAST_DDL_TIME不再變化，這說(shuō)明在10g中，當(dāng)我們執(zhí)行create or replace PROCEDURE 時(shí)，Oracle現(xiàn)在先嘗試進(jìn)行過(guò)程檢查，假如內(nèi)容沒(méi)有變化，則不需要對(duì)過(guò)程進(jìn)行重新編譯，這可以減少Cache中的Invalidation，從而可以減少競(jìng)爭(zhēng)：

$ sqlplus eygle/eygle SQL*Plus: Release 10.2.0.1.0 - Production on Sat Mar 31 17:44:46 2007Copyright (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