Re: Running SQLs betweem RAC Instance
Date: Thu, 3 Jan 2008 14:17:39 -0800 (PST)
Message-ID: <19945a6b-43e3-4f3b-9a7f-0ac6999cfb19@c55g2000hsc.googlegroups.com>
On Jan 3, 2:38 pm, ibodogan <idogan_t..._at_yahoo.com> wrote:
> 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('||l_rec.sql_id||') in INSTANCE #: ' ||
> l_rec.inst_id);
> END IF;
>
> DBMS_OUTPUT.PUT_LINE(' ');
> END LOOP;
> DBMS_OUTPUT.PUT_LINE(' ' || l_rows || ' active sessions listed.');
>
> END;- Hide quoted text -
>
> - Show quoted text -
Interesting that this doesn't work quite as expected, as the '& SQL' text is taken as a variable (unless, of course, define is set to OFF or to a different character than &):
SQL> /
Enter value for sql:
old 16: DBMS_OUTPUT.PUT_LINE('ACTIVE SESSIONS & SQL');
new 16: DBMS_OUTPUT.PUT_LINE('ACTIVE SESSIONS ');
ACTIVE SESSIONS
0 active sessions listed.
PL/SQL procedure successfully completed.
SQL> [There were no active sessions on the test database I first ran this on.]
Using chr(38) instead eliminates this 'problem':
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 '||chr(38)||' 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('||l_rec.sql_id||')in
INSTANCE #: ' || l_rec.inst_id);
END IF;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ' || l_rows || ' active sessions listed.');
END;
/
It does do a very nice job of reporting active sessions, the associated sql and execution plans. [One other change I made was to output the sql_id in the 'Run ...' text to allow for copy and paste to whichever node ran the SQL.] I can understand the desire to have this run on the various nodes from a central point, however I doubt such a feat is possible without resorting to the use of db links. I would suspect using them might be worth a try.
David Fitzjarrell Received on Thu Jan 03 2008 - 16:17:39 CST