Library cache lock and library cache pin wait events found

First, the database location analysis database Oracle 10g r 2 01

Secondly, two processes are developed for the moment called attribute process and transaction process. Obtain related data from the same big table and insert different tables. We should say that the two processes do not lock related objects!

Second, both processes run well.

 

Later, we used toad to run the process log table.

Alter table ba. t_base_sp_runlog cache;
Alter table ba. t_base_sp_runlog storage (buffer_pool keep );

 

It wasn't long before em saw that there was already a process running and it was an attribute process. Start the transaction process again and find from Em that the attribute process has blocked the transaction process.

The transaction process waits for the event libcache lock and library cache pin

 

The attribute process is dB file sequential read!

 

This is quite confusing. Although they access the streaming water meter at the same time, they are only select and cannot conflict with each other!

The following statement shows the locked object

Select xidusn, object_id, session_id, locked_mode from V $ locked_object;

 

The attribute process is 151 when the session is performing update B set time = (select Min (addtime) from user_pay_bak where f_username =: B)

Only table B is locked.

 

Select * from V $ session_wait where Sid = 64 library cache lock in transaction process

 

The transaction process is waiting for the object p_base_day_ I _sprun_log

Select kglnaown, kglnaobj
From x $ kglob
Where kglhdadr in (select p1raw from V $ session_wait where Sid = 64 );

 

It is just a process of writing the running information of the process into the log table!

 

 

Attribute process:

 

Update B set time = (select Min (addtime) from user_pay_bak where f_username =: B)

The first four transactions

1 insert into... commit; p_base_day_ I _sprun_log (.....);

 

2 For cur () loop update B set custime = () end loop; commit; p_base_day_ I _sprun_log (.....);

3 For cur () loop update B set emailtime = () end loop; commit; p_base_day_ I _sprun_log (.....);

4 For cur () loop update B set mobiletime = () end loop; commit; p_base_day_ I _sprun_log (.....);

 

Now that step 5 has been executed: Update B set time = (select Min (addtime) from user_pay_bak where f_username =: B)

The first four operations should have been completed, and the log writing process is also called.

 

 

Is the log writing process occupied by the attribute process and not released?

If you use toad to release the running log table, you can stop the transaction table and run it again.

Finally, terminate the attribute process and run it again.

 

 

Is the table cached ......