Oracle RDBMS

Questions

Answers

Depending on what you are trying to do, you don't even need to be that complex.

1) You can have your procedure drop the progress info to a DB field that could be read and displayed.

(This would require you to commit unless you were to somehow access it from the same session.)

2) You can drop the progress info to a text file in OS.

3) Break your code into chunks and show the milestones as each sub procedure terminates.

4) In Forms, this won't work since the procedure needs to stop to refresh the screen. You can get the same effect by making a timer start when you pause (for .01 of a second) and when the timer expires, it restarts the procedure.

The normal solution that most of use is to give up and not show a status

bar.

Gaurav Saini asked:

a) What and how much advantage is there to analyzing tables (with Indexes) to provide the query performance for CBO?

b) How frequently should table(s) be analyzed? Is there some rule (based on your experience) that if x% of total rows in a table are changed/newly added then the table has to be analyzed?

a) If you don't analyze tables you are telling the cost based optimizer to guess what the best optimization is for your queries assuming that there is no data.  Of course, the CBO will guess poorly in that case.  In general, you will see dramatic improvement after analysis if you have never analyzed.

b) You need to re-analyze when the data in the tables changes materially in size or structure.  How often that needs to be done depends upon the volatility of your data.  In a volatile warehouse environment, you should analyze after every load.  In a normal OLTP environment, analyzing once weekly is a good rule of thumb.  In a stable, not very volatile environment, monthly is enough.

Be aware that re-analyzing can actually make performance worse in some cases. Sometimes the CBO guesses wrong.

Also, if you have frozen the parse, reanalyzing will have no effect.

Finally, if you don't have at least thousands of rows of data, it doesn't matter if you analyze or not.

The real answer is that you analyze when the structure of the data in the tables changes enough that the optimal query execution path is different from what it used to be.  The CBO will use the statistics from the last time that the schema was analyzed.  So if the correct path is now different, then you need to analyze to use it.

The rule of thumb is empirical.  If things start slowing down, then the first thing to try is an “Analyze.”  If there is a substantive improvement, then it was the right decision. 

If you are going to start tuning, then you must analyze first.  Otherwise, your work will all go away (or become irrelevant) when you analyze. You don’t need to so a full analyze each time.  An estimated analyze is enough.