[Unix/SQLPlus] Script parallel draaien

[xs4reus]

Hallo,

Ik heb een script gemaakt in unix die een stored procedure aanroept via sqlplus.

In het script staat niet meer dan:

code:

sqlplus user@database
exec insert(20) ;

En de Stored Procedure ziet er ongeveer als volgt uit:

SQL:

create or replace procedure insert(nr_of_inserts number)
as
  xx number ;
begin
  xx:=1;

loop
  insert into test_tabel(id, sample) values (xx, 1234) ; 
  xx:=xx+1;
  exit when xx>nr_of_inserts;
end loop;
  commit;
end ;
/

Wat ik echter wil is dat er niet 20 maar een paar miljoen inserts gedaan worden en deze wil ik daarom parallel uitvoeren. Om even bij het voorbeeld te blijven zou ik nu graag parallel 4 * 5 inserts doen in de tabel test_tabel, met als wens dat mijn id uniek blijft (want ik verwacht dat als ik ze parallel start dat ik dan 4 * id 1 tm 5 krijg?).

Kan iemand me hierbij helpen?

[Voor 1% gewijzigd door BtM909 op 06-12-2011 10:29]

[Jitse H]

M.b.t. het uniek blijven van het ID:
Kent de database een auto_increment functie? Dan hoef je namelijk geen id mee te geven dan pakt hij automatisch de eerstvolgende.

Parrallel draaien van het proces kan ik je geen advies over geven.

[nielsl]

xs4reus schreef op maandag 28 november 2011 @ 16:31:
Hallo,

Ik heb een script gemaakt in unix die een stored procedure aanroept via sqlplus.

In het script staat niet meer dan:

code:

sqlplus user@database
exec insert(20) ;

En de Stored Procedure ziet er ongeveer als volgt uit:

SQL:

create or replace procedure insert(nr_of_inserts number)
as
  xx number ;
begin
  xx:=1;

loop
  insert into test_tabel(id, sample) values (xx, 1234) ; 
  xx:=xx+1;
  exit when xx>nr_of_inserts;
end loop;
  commit;
end ;
/

Wat ik echter wil is dat er niet 20 maar een paar miljoen inserts gedaan worden en deze wil ik daarom parallel uitvoeren. Om even bij het voorbeeld te blijven zou ik nu graag parallel 4 * 5 inserts doen in de tabel test_tabel, met als wens dat mijn id uniek blijft (want ik verwacht dat als ik ze parallel start dat ik dan 4 * id 1 tm 5 krijg?).

Kan iemand me hierbij helpen?

Voor wat betreft de uniqueness van je IDs, dat los je, afhankelijk van je database engine, op met keys. In MySQL is dat bijvoorbeeld een primary met auto increment. Daarnaast de vraag waarom je het script parallel wilt laten draaien? iets gaat je bottleneck zijn (ik gok disk, maargoed, ik weet niet op wat voor hardware je draait), die bottleneck haal je toch wel, of je dan scripts parallel draait, of gewoon 1 sequentieel script met een loop van 4 miljoen iteraties? Ik zou niet moeilijk doen en gewoon 4 miljoen opgeven. Multi core usage wordt als het goed is "afgedwongen" door je DBMS

[Voor 4% gewijzigd door BtM909 op 06-12-2011 10:30]

[FatalError]

Ik verplaats dit topic naar Programming, Daat past het beter lijkt me

[JaQ]

nielsl schreef op maandag 28 november 2011 @ 19:18:
Voor wat betreft de uniqueness van je IDs, dat los je, afhankelijk van je database engine, op met keys. In MySQL is dat bijvoorbeeld een primary met auto increment.

Maar dit is Oracle (gezien het sqlplus), dus de primary key kent geen auto increment. De TS moet dan een sequence aanmaken om een autonummer uit te delen.

SQL:

create sequence test_table_seq start with 1 maxvalue unlimited nocycle;

Een mogelijke oplossing is een trigger op de tabel te zetten die de primary key vult met de sequence

SQL:

create trigger test_table_bir before insert on test_table for each row
declare

begin
  :new.id := test_table_seq.nextval;
end;
/

Als je een oudere database dan 11R2 gebruikt mag je een sequence niet rechtstreeks aan een variabele toewijzen, dan moet je trigger er ongeveer zo uit zien:

SQL:

create trigger test_table_bir before insert on test_table for each row
declare

begin
  select test_table_seq.nextval
    into :new.id
    from dual;
end;
/

en vervolgens de tabel vullen met

SQL:

insert into test_table(sample) values (1234)

en daarna je pl/sql code:

SQL:

create procedure doubler(pp_interations number)
as
  lv_counter pls_integer := 0;
begin 
  while (  lv_counter < pp_interations )
  loop
    insert into test_table(sample) ( select sample from test_table)
  end loop;
end;
/

Stuk minder iteraties nodig. Performance is nog steeds ruk (net zoals de initiële testcase), het gaat wel werken binnen afzienbare tijd. Bottleneck uiteindelijk zal I/O zijn..

Ow, code is uiteraard ongetest.

Ow2, het is uiteraard bad practice om je procedure als naam een reserved word (zoals insert) te geven, sterker nog: ik gok zomaar dat Oracle dat niet pikt

[Voor 5% gewijzigd door BtM909 op 06-12-2011 10:30]

[xs4reus]

JaQ schreef op maandag 28 november 2011 @ 22:38:
[...]

Maar dit is Oracle (gezien het sqlplus), dus de primary key kent geen auto increment. De TS moet dan een sequence aanmaken om een autonummer uit te delen.

code:

create sequence test_table_seq start with 1 maxvalue unlimited nocycle;

Een mogelijke oplossing is een trigger op de tabel te zetten die de primary key vult met de sequence

code:

create trigger test_table_bir before insert on test_table for each row
declare

begin
  :new.id := test_table_seq.nextval;
end;
/

Als je een oudere database dan 11R2 gebruikt mag je een sequence niet rechtstreeks aan een variabele toewijzen, dan moet je trigger er ongeveer zo uit zien:

code:

create trigger test_table_bir before insert on test_table for each row
declare

begin
  select test_table_seq.nextval
    into :new.id
    from dual;
end;
/

en vervolgens de tabel vullen met

code:

insert into test_table(sample) values (1234)

en daarna je pl/sql code:

code:

create procedure doubler(pp_interations number)
as
  lv_counter pls_integer := 0;
begin 
  while (  lv_counter < pp_interations )
  loop
    insert into test_table(sample) ( select sample from test_table)
  end loop;
end;
/

Stuk minder iteraties nodig. Performance is nog steeds ruk (net zoals de initiële testcase), het gaat wel werken binnen afzienbare tijd. Bottleneck uiteindelijk zal I/O zijn..

Ow, code is uiteraard ongetest.

Ow2, het is uiteraard bad practice om je procedure als naam een reserved word (zoals insert) te geven, sterker nog: ik gok zomaar dat Oracle dat niet pikt

Thanks voor je reactie.

Het gaat idd om een oracle Database. Trigger is een goed idee, had ik niet aan gedacht.

Code in mijn eerste post is slechts een voorbeeld. In werkelijkheid heten de procedures anders (en idd geen insert haha) en gaat het om meer code die parallel moet worden gedraaid, maar ik wilde t voorbeeld simpel houden.

Nu nog zien dat ik t parallel krijg :-)

[xs4reus]

nielsl schreef op maandag 28 november 2011 @ 19:18:
[...]


Voor wat betreft de uniqueness van je IDs, dat los je, afhankelijk van je database engine, op met keys. In MySQL is dat bijvoorbeeld een primary met auto increment. Daarnaast de vraag waarom je het script parallel wilt laten draaien? iets gaat je bottleneck zijn (ik gok disk, maargoed, ik weet niet op wat voor hardware je draait), die bottleneck haal je toch wel, of je dan scripts parallel draait, of gewoon 1 sequentieel script met een loop van 4 miljoen iteraties? Ik zou niet moeilijk doen en gewoon 4 miljoen opgeven. Multi core usage wordt als het goed is "afgedwongen" door je DBMS

Hey,

Het script is in het echt wat ingewikkelder dan ik laat zien in de code. In werkelijkheid genereer ik 100.000 blobs welke elk uit 6 miljoen inserts bestaan die weer geselecteerd worden uit een aantal andere tabellen. Het script gaat draaien in een rekencentrum, geen idee wat voor hardware ze daar precies hebben, maar in elk geval zou dat niet 123 de bottleneck mogen zijn.

[Ravefiend]

Let op dat je sequence geen default cache mee krijgt want dan loop je nog het risico op gaps. Lees dit er ook even op na:

http://docs.oracle.com/cd...r.111/b28310/views002.htm

[xs4reus]

Ravefiend schreef op dinsdag 29 november 2011 @ 00:11:
Let op dat je sequence geen default cache mee krijgt want dan loop je nog het risico op gaps. Lees dit er ook even op na:

http://docs.oracle.com/cd...r.111/b28310/views002.htm

dank je wel voor de tip!

[JaQ]

xs4reus schreef op maandag 28 november 2011 @ 23:56:
Code in mijn eerste post is slechts een voorbeeld. In werkelijkheid heten de procedures anders (en idd geen insert haha) en gaat het om meer code die parallel moet worden gedraaid, maar ik wilde t voorbeeld simpel houden.

dbms_job of dbms_scheduler is de gebruikelijk route om pl/sql code paralleliseren (procedure meerde malen aanroepen). Als je gaat updaten is DBMS_PARALLEL_EXECUTE een 11R2 feature die je kan helpen (maar let op, pas vanaf 11R2).

Mocht je het perse op unix willen, dan zou zoiets als dit werken:

run.sql:

code:

  execute doubler(pp_interations => 10);

run.sh:

code:

#!/bin/bash
#
export ORACLE_SID=$naam_van_je_database
ORAENV_ASK=NO
source /usr/local/bin/oraenv
ORAENV_ASK=YES

sqlplus username/password @run.sql &
sqlplus username/password @run.sql &
sqlplus username/password @run.sql &

Niet erg fraai, maar het werkt wel (denk ik). Persoonlijk ben ik nooit zo'n voorstander van allerlei dingen op het OS schedulen. Je krijgt dan namelijk teveel afhankelijkheden: en cron, en dbms_job / dbms_scheduler, eventueel nog een enterprise scheduler (EM of die HP meuk of zo). Beetje onoverzichtelijk.

Je lokale DBA zou je in ieder geval moeten kunnen helpen met het unix werk.

Ravefiend schreef op dinsdag 29 november 2011 @ 00:11:
Let op dat je sequence geen default cache mee krijgt want dan loop je nog het risico op gaps. Lees dit er ook even op na:

http://docs.oracle.com/cd...r.111/b28310/views002.htm

Theoretisch klopt dat. In de praktijk valt dat erg mee. Sterker nog, door een dik cache aan de sequence te knopen haal je flink hogere performance (scheelt weer een drukke latch).

Overigens mag je nooit op een sequence vertrouwen als je doorlopende nummering wilt. Je kan namelijk vanuit een andere (willekeurige) sessie de sequence aanroepen (maar niet inserten) en zoals je ongetwijfeld weet kan je een sequence nummer maar 1 keer uitdelen. Sommige auditors vereisen een doorlopende nummering. Wat ze eigenlijk eisen is een verklaring waarom er gaps zijn. Dat kan je ook op andere manieren oplossen. (bijvoorbeeld achteraf het "logische nummer" dat de auditor gebruikt toekennen, of door auditing op de sequence te zetten waardoor je weet welke user de sequence ophoogt).

[xs4reus]

Het is me gelukt!

Ik gebruik nu een sequence voor het genereren van de id, gaat prima!

En ook het parallel opstarten middels een loop doet nu wat ik er van hoopte.

Bedankt voor jullie input!

[Voor 7% gewijzigd door xs4reus op 01-12-2011 12:59]

[BtM909]

xs4reus, ik hoop dat je nog even gaat reageren, maar was het probleem primair het aanroepen van je SP's en SQL statements of de statements zelf?