Re: Interested in Solving Some Problems

On 17 Aug 1997 17:16:17 GMT, "V. Chandrasekhar" <shaker_at_netusa1.net> wrote:

>(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.

If your tables, tablespaces or indexes are fragmented then this isn't so strange anymore.

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

These so called gaps are segments, which are maintained by the database. Each time you want to extent your table, index or tablespace, a segment will be used for this purpose. Maybe a larger initial extent size can solve the fragmentation. As you will probably know, a table, index and tablespace has an initial extent and when this space has been used, the table, index or tablespace will be extended by the size you have designed it to extend. If you also use a growth percentage, each extent will be larger than the last one. This can cause problems when your database is almost full. Suppose your table is initially 400M large and has an extent size of 50M with a growth percentage of 10%, the first extent will be 50M, the next 55 and then 55*110%, etc. After 30 extents, the extentsize will be 870M !!! This space may not be left in your database. On the other hand, if the largest free extent (as you called gaps) is not big enough to hold your next extent, you'll have a problem too. So, even if you have 1 GB available and an free segment of 500M, you still have a problem. Oops, got a bit carried away by the subject.

[By the way, if you wan't to get rid of the fragmentation in your table/index, you'll have to export it, drop the table, create one with another storage clause and import the data again]

>(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.

The fastest way to empty a complete table is to truncate it (this way your table with all its extents stay untouched). In your case it could be interesting to examine if it isn't faster to copy the last two day's data to a new table (create table <...> as select * from ... where app_date > (sysdate - 1) or something like this), truncate the original table and re-insert the values from the temporary table (which you can then drop). Of course, this creating and dropping of temporary tables fragments your database (solution: the temporary table is not dropped but truncated).

Another fast way to delete data is to add an extra column to the table which will be used as an indicator to delete that specific record. The next step is to create an index solely on that column. Since indexes don't record null values, the only records in the index will be the ones in the table with data in the 'indicator' field. First of all, you're index will be very small if you haven't much records with that indicator filled. Second, this will give you very fast acces to those specific records. Of course you have to take action to fill the 'indicator' properly. Maybe you can use this strategy for your problem.

[By the way, truncate can be uses with the option "REUSE STORAGE". In that case all extents won't be affected, otherwise the table will be truncated to it's designed size (without extents). ]

>(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).

Maybe the table needs to be unfragmented, remains the index on the same tablespace as the table itself, are there too many indexes on the table, etc, etc.

>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.

Nonsense. I think you have the wrong colleagues... ;-) Besides, aren't we all 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.

You can email me directly. I'd like to help if I can. I think the most common problem is that most people are too lazy to turn around and get the manual and use the newsgroups instead.

>please go ahead and help me. If there are specific sources, I
>should
>consult for a specific question, please let me know. I certainly

Try the Oracle's helpdesk. I have good results consulting them (in the Netherlands).

• Martin Jesterhoudt ---
• martinj(replace this by @)worldonline.nl ---

  ---