Re: Running SQLs betweem RAC Instance
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) ANDs.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