Unix controller script
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Unix controller script

  1. #1
    Join Date
    Feb 2001
    Location
    Atlanta, USA
    Posts
    131
    Dear gurus

    We have a pl/sql procedure, which is used to process for datapumping, it processes 20 records per min, ... we would like to improve the performance... we have tuned the code as much as possible to improve the performace with various aspects...

    We have an idea, to post 4times same procedure with different arguments simultaneously, so it may improve performance 4 times (theoretically)...

    Example:

    Procedure1(ID1,Filename1) 20 rec processing the same time
    Procedure1(ID2,Filename2) 20 rec processing
    Procedure1(ID3,Filename3) 20 rec processing
    Procedure1(ID4,Filename4) 20 rec processing

    this is something like, the procedure has to be assigned in different worker / processor...

    so, we need to have a controller script, which will control the operation of all those procedures, and once this process is completed for , it has to take next argument and processes..


    Could someone throw some light on this...

    regards

    anandharaj















    Anandharaj
    a_anandharaj@yahoo.com

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Start each of your procedures in the background and wait for them to finish. For example:
    Code:
    #!/bin/ksh
    
    sqlplus /@mydb @script1.sql > /dev/null 2>&1 &
    sqlplus /@mydb @script2.sql > /dev/null 2>&1 &
    sqlplus /@mydb @script3.sql > /dev/null 2>&1 &
    sqlplus /@mydb @script4.sql > /dev/null 2>&1 &
    
    wait  # will wait for all your bg jobs to finish
    echo "Done!"
    script1-4 will contain your Procedure call. Make sure script1-4 are self contained meaning they spool to different files and quit at the end.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Feb 2001
    Location
    Atlanta, USA
    Posts
    131
    Hi Jeff Hunter

    Thank you very much for your prompt reply...

    I have a question that, how can i pass the arguments of other records once the stage one is completed..., so that the entire record could be processed stage by stage automatically...

    i will appreciate you that if you could explain with more details about the same..?

    thanks and regards

    anandharaj
    Anandharaj
    a_anandharaj@yahoo.com

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I need more information in order to intelligently answer your question....
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    if u plan to use a dbms_job to automatically execute ur procedure .

    try preparing script in the following lines .
    - i guess u are executing a pl/sql procedure which accepts same number of arguments with similar datatype .

    SQL> create table xyz
    ( id varchar2(10) ,
    filename varchar2(30) ) ;

    SQL> insert all arguments as values into this table .

    create procedure automate
    cursor c1 is select id , filename from xyz where .... ;
    begin
    for r1 in c1
    loop
    Procedure1(r1.id,r1.filename ) ;
    end loop ;
    end ;

    ----
    automattically submitting the job

    declare
    x binary_integer;
    begin
    dbms_job.submit(x, 'begin automate; end;',
    sysdate , 'SYSDATE + FREQUENCY ' , FALSE);
    commit;
    end;



    DOES THIS ANSWER UR QUESTION ??

    CHECK SYNTAX

    siva prakash
    DBA

  6. #6
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    consider earlier post if ur intention is to automate in other way it is the database way not the unix way ...

    siva prakash
    DBA

  7. #7
    Join Date
    Feb 2001
    Location
    Atlanta, USA
    Posts
    131
    Hi Siva Prakash

    Thank you very much... Your update comes very close to my question.. Thanks once again...

    and i need a clarification that if i use dbms_job, i need to wait till my first record to complete the job... then it will proceed for next record and so on... am i correct ?

    In the unix environment, is there any way to do as below:
    -assign 1st record to worker1,
    -assign 2nd record to worker2,
    -assign 3rd record to worker3,
    -assign 4th record to worker4 and
    restart the workers..
    -assign 5th record to worker1,
    -assign 6th record to worker2,
    -assign 7th record to worker3,
    -assign 8th record to worker4 and.. so on repeat till last record to complete...

    if i do like wise, performance will be improved to 4 times...

    thanks and regards


    and second recordeed, multi-process/worker to process my job simultaneousely, i mean

    Anandharaj
    a_anandharaj@yahoo.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width