Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2005
    Posts
    41

    Unanswered: parallel execution ??

    I have 5 *.sql files that need to be executed in a parallel mode. Each *.sql file is a pl/sql script that has 1 or more scripts in it. How do I do this?

    Sample *.sql file
    -----------------

    DECLARE
    T_CNT NUMBER := 0;
    P_CNT NUMBER := 0;
    F_CNT NUMBER := 0;
    COND_0 NUMBER := 0;
    BEGIN
    SELECT COUNT(1) INTO COND_0 FROM DUAL;
    T_CNT:= T_CNT + 1;
    IF COND_0 = 0 THEN
    P_CNT:= P_CNT+1;
    ELSE
    F_CNT:=F_CNT+1;
    DBMS_OUTPUT.PUT_LINE('TC # '||T_CNT||' FAILED - Verify the count is 0 - Result is '||COND_0);
    END IF;
    END;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I have 5 *.sql files that need to be executed in a parallel mode.
    Please elaborate on the business justification for this assertion.
    What happens if/when they run sequentially?
    Start up 5 separate SQL*Plus sessions & launch them.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2005
    Posts
    41

    Business Justification

    5 independent sql files when run sequentially takes a long time to produce results. Each sql file has scripts that are hitting different table so it is better to execute them in parallel mode.

    opening 5 sqlplus sessions wouldn't cut it as I am trying to automate the parallel execution with one master sql script that calls the 5 files.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I'd do them as 5 separate SQL*Plus streams, but since you deem this option as not viable then you can deal with the complxity of DBMS_JOB or DBMS_SCHEDULER.
    Running them in parallel, will likely reduce the total run time when compared to running sequentially,
    but likely still longer an the duration of the single longest running job.
    Once you totally consume the system limiting resource, saturation will have occurred & total elapsed time will increase.
    You're On Your Own (YOYO)!
    Last edited by anacedent; 03-24-07 at 00:12.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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