eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 进京四周年记 | Blog首页 | library cache pin与PROCEDURE的重建 »

Oracle10g中过程(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

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

然而这个变化是否有效呢?请看我接下来的另外一个测试...

-to be continued ....


历史上的今天...
    >> 2013-04-02文章:
    >> 2005-04-02文章:
    >> 2004-04-02文章:

无觅

By eygle on 2007-04-02 09:23 | Comments (4) | SQL.PLSQL | 1397 |

4 Comments

确实是一个好的变化

另一篇文章在哪里呢?

还是学习呀``

学无止境!


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com