Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Why event "global cache lock open x " wait forever?
Do you have enough information to determine whether this is a single very long wait, or a large number of quite short waits, many of which timeout in a loop. Also, is the volume of data to be inserted large, and how many indexes exist on the table into which you are inserting ?
The trouble with OPS is that the configuration of your system, and nature of the data manipulation may make this a totally expected occurrence.
Global locks are required to cover (amongst other things) buffered data blocks. If you are using fine grain locking, oracle could be acquiring and releasing locks at a tremendous rate - and it's a (relatively) slow process.
The "select from bbb.t1" may be doing lots of delayed block cleanout, and be subject to contention from users on the other node who are changing the data at the same time. If the other node is busy this node may also have to keep demanding rollback segment headers and blocks from the remote node to check transaction states and produce read-consistent blocks - and so on ..
An OPS 'insert / select' can be vastly slower than a single-instance doing the same.
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) ____USA__________November 7/9 (Detroit) ____USA__________November 19/21 (Dallas) ____England______November 12/14 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html melocy wrote in message <92A2664ABmelocy21cncom_at_130.133.1.4>...Received on Wed Oct 09 2002 - 02:35:08 CDT
>Evironment:2 * RS6000 AIX4.3.2 Oracle805 OPS(db1,db2)
>on db2,one transaction is that:
>
>set transaction use rollback segment rbig;
>delete from aaa.t1;
>insert into aaa.t1 select * from bbb.t1;
>commit;
>
>WHEN execute statement "Insert into ....",
>process hang.
>query v$session_wait,event is "global cache lock open x "
>query v$session_event,event "global cache lock open x"
>total_waits is adding continually and total_timeouts is adding too.
>
>Help me ,Pls.
>Thx.