Re: "snapshot too old" and undo_retention

On 9/30/05, Roger Xu <roger_xu_at_dp7uptx.com> wrote:
> Anybody partition this table? How? Thanks.

SAP has notes on partitioning.

Before springing for partitioning, (10k U.S. per CPU) you may want to determine if partitioning is worth the money and time.

I'm not familiar with BSIS table, but we have a similarly large table in our implementation, the COEP table.

Was 800m rows, now down to about 400m rows after archiving. (SAP transaction archiving)

These large tables can sometimes be a performance problem, but that may be only indirectly related to the size of the table.

There is a transaction for which we run a nightly job, and it was performing very poorly. If an old transaction that is no longer needed is not marked for deletion, this SAP job will examine the transaction, exploding BOMs and everything else along the way.

Once this business problem was corrected, performance was fine.

Partitioning would be useful from a storage perspective in this case if we could create a useful partition key that would let us drop a partition after archiving.

As it is, we are just going to rebuild the table. Archiving will be regularly run to clean it up, and the existing space in the table reused.

If you have an 4/8 way server, partitioning may not have a good ROI.

--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
--
http://www.freelists.org/webpage/oracle-l