Re: Locking Question on OPS??

If this is Oracle8 why don't you use GV$session_wait to see which session is waiting on what.
That is the starting point !

Anjo Kolk

markp7832_at_my-deja.com wrote:

> In article <943378174.3999.0.nnrp-08.9e984b29_at_news.demon.co.uk>,
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
> >
> > Although you are using two separate schema
> > on the two nodes, you do not say that these
> > are guaranteed to be stored in independent
> > tablespaces.
> >
> > In OPS, 'pinging' occurs between instances
> > when both instances wish to update data
> > in the same file - and if objects are in the same
> > tablespace, then they may be in the same file.
> >
> > Did you check the v$SESSION_wait view on
> > the two separate instances, or v$sesstat
> > (again on the two instances). v$file_ping
> > may also help (if your version of Oracle
> > supports it).
> >
> > In version 8 there are also some v$DLM_xxx
> > views which contain further information, and
> > in fact version 8 supports GV$ views which
> > cover all instances from one node if you
> > have enabled parallel query slaves.
> >
> > --
> >
> > Jonathan Lewis
> > Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
> >
> > ccdicky wrote in message <80vfkd$76m1_at_imsp212.netvigator.com>...
> > >Hi there,
> > >
> > >Recently, I get a big question on an OPS system. My OPS database
> contains
> > >two schema. A process updating the tables on Schema #1 only is
> always
> > >running on Node A and triggered to update every minutes in
> everyday. An
> > >other daily process inserting data into the tables on Schema #2 is
> running
> > >on Node B. At first, they were running very well, but over time,
> the daily
> > >process on Node B is hung sometimes. And while the daily process is
> > >hanging, the update process on Node A also get hung until the daily
> process
> > >is killed.
> > >
> > >I queried several system views, including v$lock, v$system_wait ..
> on both
> > >nodes but cannot find any clue. Anyone can tell me what else system
> views
> > >can figure out what's wrong on the OPS system. In addition, is
> there any
> > to
> > >enable Oracle session trace while the process want to be traced is
> running?
> > >
> > >Any help would be appreicated. Thanks!
> > >
> > >Dicky
> > >
> > >
> Dicky to add to what Jonathan said:
> If you did not configure gc_files_to_locks then a round robin locking
> scheme is in use so one pcm lock may cover multiple blocks in multiple
> files. This can lead to problems. If you did not use this parameter I
> recommend that you add it to all your init.ora's.
>
> You can trace a running session using dbms_system. Here is a cover
> package I wrote so my developers could use the procedure:
> rem
> rem PL/SQL script to mask rdbms internal package while allowing access
> to
> rem useful routine -- mdp
> rem
> grant execute on dbms_system to system
> /
> connect system/**********
> set echo on
>
> create or replace procedure system.trace_sql
> (
> v_session in varchar2,
> v_serial in varchar2,
> v_switch in varchar2 default 'START'
> )
> is
> v_pass_session v$session.sid%type ;
> v_pass_serial v$session.serial#%type ;
> --
> -- Cover procedure for use of Oracle provided package that can only be
> -- executed by sys. m d powell 15 Oct. 1997 for ver. 7.2.3
> --
> begin
> v_pass_session := v_session ;
> v_pass_serial := v_serial ;
> if upper(v_switch) = 'START' then
> sys.dbms_system.set_sql_trace_in_session
> (v_pass_session,v_pass_serial,TRUE);
> else
> sys.dbms_system.set_sql_trace_in_session
> (v_pass_session,v_pass_serial,FALSE);
> end if;
> end;
> /
> grant execute on trace_sql to xxxx
> /
> drop public synonym trace_sql
> /
> create public synonym
> trace_sql
> for trace_sql
> /
>
> And lastly you can gather locking and session information from all
> instance by creating a database link to the other session and doing a
> union of the code. I suggest adding a constant for the instance to
> each query to aid output. Example:
> set echo off
> rem
> rem filename: session_locked.sql
> rem SQL*Plus script to display selected sessions and related process
> infor-
> rem mation for all Oracle sessions blocked from processing due to a
> lock
> rem held by another session.
> rem
> rem 11/27/95 s3527 m d powell new script
> rem
> set verify off
> column machine format a08 heading "APPL.|MACHINE"
> column sid format 99999 heading "Sid"
> column serial# format 99999 heading "Serial"
> column spid format 99999 heading "ORACLE|PROCESS"
> column process format 99999 heading "APPL.|PROCESS"
> column username format a12
> rem
> prompt Checking Both DDC1 and DDC2 for locks. Col. I is the side, 1 or
> 2.
> rem
> select s.username, s.status, s.sid, s.serial#,
> p.spid, '1' "I", s.machine, s.process, s.lockwait
> from v$session_at_ddc1_lk s, v$process_at_ddc1_lk p
> where s.lockwait is not null
> and s.paddr = p.addr
> union
> select s.username, s.status, s.sid, s.serial#,
> p.spid, '2' "I", s.machine, s.process, s.lockwait
> from v$session_at_ddc2_lk s, v$process_at_ddc2_lk p
> where s.lockwait is not null
> and s.paddr = p.addr
> /
>
> Note - my db_links do not match my database names which in ver 8 is an
> init.ora parameter
> --
> Mark D. Powell -- The only advice that counts is the advice that
> you follow so follow your own advice --
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Nov 25 1999 - 03:38:59 CST