Re: Locking Question on OPS??

From: <markp7832_at_my-deja.com>
Date: Wed, 24 Nov 1999 16:30:42 GMT
Message-ID: <81h3ri$lp$1@nnrp1.deja.com>

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 Wed Nov 24 1999 - 10:30:42 CST