Long Running Query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Long Running Query

  1. #1
    Join Date
    Jan 2001
    Posts
    515
    I have table that tracks the records being inserted into our database. Each record that gets inserted has a sequence number attached to it , usually 1 through 85000 for the day. I have a procedure that runs every hour that checks to make sure we are not missing any records by creating a cursor of the records in that table and then going through them to see if any sequence numbers are missing. Here is the problem. When we are not missing any transaction numbers it usually runs in 1 minute. If we are missing some transactions , say 500 of them it runs for an hour. I increased the sort_area_size for that session and it didn't do any good. Any ideas? How could I find out what the problem is?

  2. #2
    Join Date
    Feb 2001
    Posts
    389
    what is the code u r using to check the missing records.

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    may you post the code :-?
    Are you using not between or you check record by record in the cursor :-? (a for loop?)

  4. #4
    Join Date
    Jan 2001
    Posts
    515

    Code

    cursor c1 is
    select file_seq from file_sequence;

    begin

    for v1 in c1 loop

    i = 2;

    loop

    if i = v1.file_seq then
    i := i + 1;
    exit;
    elsif i < v1.file_seq then
    -- if sequence number came in with in the last 2 hours
    v_cnt := 0;
    select count(*) into v_cnt from file_sequence where trunc(msg_date) = trunc(v_date)
    and file_seq = i;
    if v_cnt = 0 then
    v_string := i;
    utl_file.put(v_output,v_string);
    utl_file.new_line(v_output,1);
    i := i + 1;
    else
    i := i + 1;
    end if;
    else
    double := i-1;
    exit;
    end if;

    end loop;

    end loop;

    end file_seq;

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    heh I dont understand the code coz I am pretty bad in programming. Isnt it easier store two variables like

    var1 := sequence from two hours ago
    var2 := max(sequence)

    then say

    file_seq not between var1 and var2 :-?

  6. #6
    Join Date
    Jan 2001
    Posts
    515

    ??

    I'm not to sure what you are saying. I want to write any missing file_sequnece numbers to a file. I check up to two hours ago because the numbers to do not come in order so anything within the last two hours might be missing some sequence numbers but they still might be on the way.

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I mean letīs say

    the last sequence from two hours ago is 10, the actual sequence is max(sequence), letīs say itīs 13000. During this period we missed 1 sequence, letīs say 10000

    so if we use query

    select * from your_table
    where sequence not between 10 and 13000 you will get 10000 no? Then write 10000 to your file or whatever

  8. #8
    Join Date
    Jan 2001
    Posts
    515

    That wouldn't work

    That wouldn't work because you are asking oracle to return a file_sequence number from the table is not there. Oracle doesn't know that all the numbers in this column are incremented by 1 and if there is one missing it should be between those two numbers.

  9. #9
    Join Date
    Jan 2001
    Posts
    515

    I figured it out

    I fixed it. I took out the count and it runs in 30 seconds. The program did a count on the table everytime it was missing a transaction. If it was missing 500 transactions that was 500 counts. The table gets pretty big. I should have caught that earlier. Thanks for your help.

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