Performance Issues

[kanthbethi]

Hi all

I have a procedure to create a extract file (through utl_file). It runs fine when the number of records extracted is less, but the perfomance problems arise when the number of records increase, it is kinda exponential. For example if the required number of records is say around 10,000 then the extract is finished in say 1 min, but when the required number of records is say around 300,000 then it almost takes 15hrs. Can any one suggest some ideas to deal with this issue?

Thanks in Advance

[hrishy]

Hi

Post the proc with the code and teh table structure here.

regards
Hrishy

[abhaysk]

and also the plan of the query that retirves the recs.. (both)

[DaPi]

Are you doing anything with UTL_FILE other than a PUT for each row? (I have seen someone do a CLOSE and OPEN in append mode for each row ! ! ! that produces "exponential" behaviour.)

[ldanicsk]

Hi,

I suspect that while your queried data fit in the memory(buffer_cache) the query is quick (no matter if you have full table scan(s)). But if they don't then the exponential slowing comes.
Examine whether you use index(es). I think you don't.

It is only a tip.

[slimdave]

Originally posted by ldanicsk
Hi,

I suspect that while your queried data fit in the memory(buffer_cache) the query is quick (no matter if you have full table scan(s)). But if they don't then the exponential slowing comes.
Examine whether you use index(es). I think you don't.

It is only a tip.

I don't think that it is necessarily a good tip, because it implies that using indexes is always a good thing. that is not always the case.

As hrishy and abhaysk say, more information is needed before a diagnosis and fix can be suggested.