Interested in Solving Some Problems

        I am a programmer who has experience writing applications using SQL (primarily with Ingres) and loading databases (again Ingres).

        Currently, I am in the process of 'deploying' an Oracle based application (written by other people). I am hearing several things which surprise me. I am interested in verifying whether these are correct or not and 'improving' the system based on the information I get.

        During normal day-to-day operation, the application creates rows (i.e., performs inserts into the database). Occasionally, people will retrieve the rows while running reports. Also, during normal day-to-day operation, another application will retrieve a small number of rows at a time, to check the value of some fields.

        Weekly, an 'archive' step is done, when the data is moved from main tables to other long-term tables. Recently, we had a situation where this 'archiving' has not been done for a month (for various reasons). A part of the problem is that the application is run 7 x 24. At this point, for a variety of reasons, no-one is using the reports - i.e., the only use of the data is the other application.

(1) The database is fairly full. (I realize that I have not
defined what 'fairly' is. There appears to be definitely room for adding the rows.) This seems to slow the application - i.e., the inserts - down. I am surprised by this. As long as all that is happening is adding new rows, will the size of the database have an effect, as long as there is room ? I hear that this is because Oracle is trying to fit in rows where there are gaps in files - where rows have been created and deleted. Shouldn't the solution be to get rid of the gaps by running a maintenance utility ?

(2) I suggested getting rid of all but the last two day's data.
I am hearing that even 'deletes' will take time - of the order of
a couple of hours; the only fast operation is 'dropping' tables.

(3) Even though we are not running reports now, when we have
tried to run reports, I understand that the main application
(i.e.,

the 'inserts' slow down).

(4) I understand that about an 8 hour window is needed for the
archiving each week. I am surprised by this. The database is about 3 gigs.

If I were 'God', I would (a) push a button and have all the problems
go away or (b) get experts and have them take care of the problem. :-) :-) Unfortunately, (b) is simply not an option for us.
(They won't pay for experts.) [ So, I am left with option (a)
:-) :-) :-) ]

I am sure that I have not asked all the questions I have wanted to ask. I wanted to get a dialog started on our problems. (It helps
me understand the problem, by attempting to write it like this). It is a fact of life that the quality of discussion one gets on the net
is several times superior to what one can get with one's colleagues.
At the same time, it is also a fact that sometimes somebody is going to get upset at a person asking too many questions or getting 'free' education / solutions on the net. (I am saying both
of these based on experience in other groups.) I am clearly looking to
solve these problems; I am clearly looking to be educated; I am clearly planning on doing all these for 'free'. If you don't mind these,
please go ahead and help me. If there are specific sources, I should
consult for a specific question, please let me know. I certainly appreciate the knowledge in the net and the exchange of ideas that happens.

Shaker Received on Sun Aug 17 1997 - 00:00:00 CDT