Re: Interested in Solving Some Problems

V. Chandrasekhar wrote:
>
> V. Chandrasekhar <shaker_at_netusa1.net> wrote in article
> <01bcab29$0ef3d180$45d896ce_at_shakerv>...
>

Is this the same 'shaker' who is a world-famous table tennis player from rec.sport.table-tennis?

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

Theoretically, the process to find these 'gaps' is efficient enough that it outweighs the overhead of
constantly re-organizing the data in the table. I think you can find a detailed description of it in the Concepts Manual - if I remember right, all the information on where there are blocks with free space available is recorded in the segment header block. From my experience, the size of the tables or database has no effect. I know certain RDBMSs will add all new rows at the end of the table, but this requires frequent re-orgs to prevent the tables from growing huge if you do alot of inserts/deletes, and Oracle's scheme avoids this problem. You can always do an export/import to clean the tables up and see if it helps performance at all in the short term.

>
> Following up on my own posting, I just realized that there are
> about 40 instances of the 'one' application; all of these write
> (i.e., insert) to the same tables. Each instance is a separate
> unix process. I wonder whether the real problem is the processes
> locking each other out.

There are many,many factors that can affect performance(like how many indexes are on the tables?), but if you have that many processes doing inserts into the same tables there is a good chance you are suffering from freelist contention. A rule of thumb is to have as many freelists as the number of processes that will be doing concurrent inserts into the tables. You could try something like this to find out:

Get the number of waits for free blocks:

SELECT class, count
FROM v$waitstat
WHERE class = ’free list’;

Get the number of requests for data:

SELECT SUM(value)
FROM v$sysstat
WHERE name IN (’db block gets’, ’consistent gets’);

If the number of waits is greater than about 1% of the number of requests, you may benefit from adding more free lists. This information can be found in the 7.3 Server Tuning guide - I strongly recommend getting your hands on a copy. Hope this helps!

>
> Shaker

/*+ Opinions expressed are mine and do not necessarily reflect those of Oracle Corporation
*/ Received on Mon Aug 18 1997 - 00:00:00 CDT