Re: Running SQLs betweem RAC Instance

From: ibodogan <idogan_tech_at_yahoo.com>
Date: Thu, 3 Jan 2008 12:38:54 -0800 (PST)
Message-ID: <432e8bd8-258f-433e-8255-f9c6c665d2a4@p69g2000hsa.googlegroups.com>

On Jan 3, 2:06 pm, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
> On Jan 3, 2:38 pm, ibodogan <idogan_t..._at_yahoo.com> wrote:
>
>
>
>
>
> > On Jan 3, 1:23 pm, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
>
> > > On Jan 2, 3:44 pm, ibodogan <idogan_t..._at_yahoo.com> wrote:
>
> > > > Is there a way to run a SQL in a RAC instance #1 from instance # 2 ?
>
> > > > thanks,
> > > > i.d.
>
> > > Yes if you want to setup via a job/schedule ... you can control which
> > > instance it is executed on.
>
> > > Google around for something like "dbms_job run on instance RAC" or
> > > similar.  I think orafaq.com has some examples as well as oaktable ...
>
> > Thanks hpuxrac this was closest for what I asked but unfortunatelly I
> > need to run this manually and as you see part of the code is running
> > on instance # X while other part is running on another instance of
> > RAC.
>
> > i.d.
>
> There are several ways to get parameters passed into things running
> under jobs/scheduler ... are you sure you can't get something workable
> by this mechanism rather than having to run it manually?
>
> I didn't take a good look at your sample code ... can you explain what
> it is exactly you are trying to accomplish because perhaps there is a
> less complicated alternative available?- Hide quoted text -
>
> - Show quoted text -

The code basically displays the following for ACTIVE PIDs:

• header info for PID. user name, elapsed time, program name, sql_id etc.
• text of SQL being run
• execution plan of SQL. This is the tricky part, if the PIDs instance # is the same as local instance # then no problem, dbms_xplan works fine but if PIDs instance is different then display_cursor procedure does not recognize the sql_id.

here is a cleaner version:

DECLARE

l_sqltext       VARCHAR2(3000);
l_rows          PLS_INTEGER;
p_indentlen     PLS_INTEGER := 2;
p_linelen       PLS_INTEGER := 100;
p_linecount     PLS_INTEGER := NULL;
l_linestr       VARCHAR2(200);
l_strlen        PLS_INTEGER;
l_strindex      PLS_INTEGER;
l_linecount     PLS_INTEGER;
l_instid    PLS_INTEGER;

BEGIN  DBMS_OUTPUT.ENABLE(1000000);
 DBMS_OUTPUT.PUT_LINE('ACTIVE SESSIONS & SQL');  SELECT instance_number
 INTO l_instid
 FROM v$instance;

 l_rows := 0;
 FOR l_rec IN (

       SELECT s.username, TO_CHAR(s.sid) || '-' || TO_CHAR(s.serial#) AS sidserial,

                      s.last_call_et AS elapsed, s.machine || '-' ||
s.osuser AS machineosuser,
                        s.process, p.spid, s.sql_id, s.program,
s.inst_id
     FROM   gv$session s, gv$process p
     WHERE  s.paddr = p.addr AND s.status='ACTIVE' AND s.type !=
'BACKGROUND' AND
                   s.username IS NOT NULL AND s.sid != (SELECT sid
FROM v$mystat
                                     WHERE rownum=1) AND

s.inst_id=p.inst_id) LOOP

        IF l_rows = 0 THEN
                DBMS_OUTPUT.PUT_LINE(RPAD('USER', 30) || ' ' ||
                          RPAD('SID-SERIAL', 15) || ' ' ||
RPAD('ELAPSED', 11) || ' ' ||
                          RPAD('CLIENT MACHINE - OS USER', 25) || ' '
||
                        RPAD('CLIENT SPID', 11) || ' ' || RPAD('SERVER
SPID', 11) || ' ' ||
                        RPAD('SQL ID-PROGRAM', 20) );
             DBMS_OUTPUT.PUT_LINE (RPAD('~~~~', 30) || ' ' ||
RPAD('~~~~~~~~~~', 15) || ' ' ||
                        RPAD('~~~~~~~', 11) || ' ' ||
RPAD('~~~~~~~~~~~~~~~~~~~~~~~', 25) || ' ' ||
                        RPAD('~~~~~~~~~~~', 11) || ' ' ||
RPAD('~~~~~~~~~~~', 11) || ' ' ||
                        RPAD('~~~~~~~', 20) );
        END IF;


        l_rows := l_rows + 1;


        DBMS_OUTPUT.PUT_LINE(RPAD(l_rec.username, 30) || ' ' ||
              RPAD(l_rec.sidserial, 15) || ' ' || RPAD(l_rec.elapsed,
11) || ' ' ||
              RPAD(l_rec.machineosuser, 25) || ' ' ||
RPAD(l_rec.process, 11) || ' ' ||
              RPAD(l_rec.spid, 11) || ' ' || RPAD(l_rec.sql_id || '-'

|| l_rec.program, 20) );

       FOR l_rec2 IN (SELECT sql_text
                        FROM gv$sqltext
                        WHERE sql_id = l_rec.sql_id AND inst_id =
l_rec.inst_id
                        ORDER BY piece) LOOP
               DBMS_OUTPUT.PUT_LINE('   ' || l_rec2.sql_text);
        END LOOP;


   IF l_instid = l_rec.inst_id THEN
        FOR l_rec3 IN (SELECT plan_table_output
                        FROM table
(DBMS_XPLAN.DISPLAY_CURSOR(l_rec.sql_id))    ) LOOP
           dbms_output.put_line(l_rec3.plan_table_output);
        END LOOP;
   ELSE
        DBMS_OUTPUT.PUT_LINE(
          '- Run DBMS_XPLAN.DISPLAY_CURSOR in INSTANCE #: ' ||

l_rec.inst_id);

   END IF;    DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ' || l_rows || ' active sessions listed.');

END; Received on Thu Jan 03 2008 - 14:38:54 CST