Reconstruction of cache pin and procedure in Oracle Library

As mentioned earlier, the processing of the ORACLE10G reconstruction procedure has increased, and when I first saw this enhancement, I wondered if this enhancement would reduce the already-troubled library cache competition.

Let's take a look at the following tests, first performing the action in the first session:

sql> Create or replace PROCEDURE pining

2 is

3 BEGIN

4 NULL;

5 end;

6/

Procedure created.

Sql>

Sql> alter session set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss ';

Session altered.

sql> Create or replace procedure calling

2 is

3 begin

4 pining;

5 Dbms_lock.sleep (60);

6 end;

7/

Procedure created.

Sql>

Sql> Col object_name for A30

Sql> Select Object_name,last_ddl_time from Dba_objects where object_name in (' pining ', ' calling ');

object_name Last_ddl_time

------------------------------ -------------------

Calling 2007-04-02 09:12:57

Pining 2007-04-02 09:12:57

Sql>

Sql> exec calling;

At this point calling's reference to pining will get a shared pin on the body of pining, at which time a rebuild procedure operation is performed on another session:

sql> Create or replace PROCEDURE pining

2 is

3 BEGIN

4 NULL;

5 end;

6/

This operation will be suspended until the first session is completed, and the library Cache pin competition can be observed in the third session:

Sql> Select Sid,event from v$session where username= ' Eygle '

2/

SID EVENT

---------- ----------------------------------------------------------------

137 Library Cache Pin

139 Pl/sql Lock Timer

157 Sql*net Message to Client

When the first session is finished, the second session completes, and we can see that the last_ddl_time has not changed:

Sql> exec calling;

Pl/sql procedure successfully completed.

Sql>

Sql> Select Object_name,last_ddl_time from Dba_objects where object_name in (' pining ', ' calling ');

object_name Last_ddl_time

------------------------------ -------------------

Calling 2007-04-02 09:12:57

Pining 2007-04-02 09:12:57

In fact session 2 executed a meaningless library cache pin, ideally, Oracle was able to determine the pattern of the previous library cache pin, and if it was shared mode, you could skip pin requests, and if it was exclusive, you'd have to wait. The current deal does not change the competition in substance.

Not altogether, we found that Oracle could skip the request for a library Cache pin for another type of DDL operation that was grant

In oracle10g, the grant authorization operation eliminates the need to obtain exclusive locks on the library Cache pin, and we look at the following tests:

Execute in Session 1:

09:40:18 sql> drop procedure calling;

Procedure dropped.

09:40:18 sql>

09:40:18 sql> drop procedure pining;

Procedure dropped.

09:40:18 sql>

09:40:18 sql> Create or replace PROCEDURE pining

09:40:18 2 is

09:40:18 3 BEGIN

09:40:18 4 NULL;

09:40:18 5 End;

09:40:18 6/

Procedure created.

09:40:18 sql>

09:40:18 sql> alter session set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss ';

Session altered.

09:40:18 sql> Create or replace procedure calling

09:40:18 2 is

Back to the column page: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/