Long Running Query

[lesstjm]

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?

[gpsingh]

what is the code u r using to check the missing records.

[pando]

may you post the code :-?
Are you using not between or you check record by record in the cursor :-? (a for loop?)

[lesstjm]

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;

[pando]

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 :-?

[lesstjm]

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.

[pando]

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

[lesstjm]

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.

[lesstjm]

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.