I am fairly new to Oracle and I would appreciate if some one can point me to
a right direction.

I have store procedure that takes about 3 minutes to run. If I run the same
procedure again suddenly it takes 7 minutes to un and some time up to thirty
minutes to run. There is no traffic on this server at this time and its a
test server.

And then suddenly it takes 3 minutes to run again.


Any help is appreciated it.


Gene

Hi,
Have you:
+ analyzed your tables : big, not big, histograms???
+ got the right indexes : have a look at the "where" clause
+ traced your query and examined the explain plan (full table scan,
....) and
the statistics
+ set the optimizer_mode parameter correctly
+ any functions used in the left side of the "where" clause
+ any "order by ... desc" directives on an index that is not reverse
at all
+ detected any disk sorts, high physical IO's level
+ ...

As you can imagine there plenty of other potential reasons. At least
be sure to check these basic points regarding the query itself. After
there is the load of your database...
Have fun,

Ph.


"Gene Ariani" <karadur (AT) comcast (DOT) net> wrote

"Gene Ariani" <karadur (AT) comcast (DOT) net> wrote

How much physical/virtual memory do you have configured?

Could be a resource bottleneck.