Enhancement of process procedure reconstruction in Oracle 10g

DCBA a new discovery last week that, in oracle10g, Oracle behaves differently than before when rebuilding a stored procedure.

In Oracle9i, Oracle also needs to recompile the process even if a completely identical process is rebuilt, as can be seen from the 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

In oracle10g, this last_ddl_time no longer changes, which means that in 10g, when we execute the Create or replace PROCEDURE, Oracle now tries the process check, and if the content doesn't change, You do not need to recompile the process, which reduces the invalidation in the cache, which can reduce competition:

$ sqlplus Eygle/eygle

More Wonderful content: 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, +, 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

Author: 51cto Blog Oracle Little Bastard