Re: Performance deteriorates on a dynamic db

Phil

Look at the following:

If you are changing the size of the individual rows in your table by adding information after the row is created look at the storage clause that applies to the table and look at the values for PCTFREE (I might have the name wrong). Basically what you want to do is leave enough free to accommodate all of the growth of the rows loaded into the block. For example, if at initial load time you have 100 bytes and during processing you add 200 bytes you want a PCTFREE of at least 67%.

The next thing to do is see how many extents the offending tables and their indexes occupy. If more than 5 (some people will say if more than 2 and others if more than 10) increase the values in the storage clauses for initial and next. The objective here is to make the initial extents large enough to hold the full table and for each of the indexes.

The third thing to look at is the amount of disk space available for temporary storage and for rollback segments. As shipped the default values tend to be on the low side. With the version of Oracle you are running you have the option of extending your datafiles. If you are doing a lot of sorting (order by clause) increase the size of your temporary tablespace. If your largest rollback segment is not large enough to contain your largest table expand it and then tell Oracle to use that particular rollback segment for operations against the table.

To answer your specific questions:

When properly tuned Oracle does not have any problems with a dynamic database.

It is unlikely that your stored procs are causing the problem so you shouldn't have to recompile.

Jerry
Phil Tsao wrote:
>
> Hi, Oracle Experts,
>
> We have an Oracle 7.3 on NT 4.0. There is one schema we use a few
> stored procs to insert, update, and then delete a lot of data all the
> time
> (10,000 to 40,000 rows on half dozen tables). It seems Oracle gets
> slower and slower each we do another batch of insert.
> Truncate and delete all the tables manually did not help.
> Drop the schema did not help.
> Uninstall and reinstall the Oracle server would regain the better
> performance but it would then deteriorate over the same operation.
>
> Does Oracle have problem with a dynamic database?
> Do I need to recompile all the store procs ?
> Thanks in advance for the help.
> --
> Phil Tsao
> philt_at_technologist.com
Received on Fri Oct 10 1997 - 00:00:00 CEST