Re: Problem with SET TRANSACTION USE ROLLBACK SEGMENT

Dave wrote:
> "Sybrand Bakker" <sybrandb_at_hccnet.nl> wrote in message
> news:i17at0h323jgf357nf98nbrchu0p537hbs_at_4ax.com...
> > On 31 Dec 2004 08:38:45 GMT, Holger Marzen <holger_at_marzen.de>
wrote:
> >
> >>Oracle 8i / Solaris SPARC
> >>
> >>We gather statistics about the tables with a pl/sql script that
looks
> >>like
> >>
> >>|for lrec_Table in lcur_Tables
> >>|loop
> >>| dbms_stats.gather_table_stats(...)
> >>|end loop;
> >>
> >>But sometimes it crashed, probably due to heavy activity of the
> >>database (ORA-01555: snapshot too old). So I created a very big
rollback
> >>segment and told Oracle to use it:
> >>
> >>|SET TRANSACTION USE ROLLBACK SEGMENT RBSBIG1;
> >>|@/export/home/oracle/scripts/stats_gather.sql;
> >>
> >>What I don't understand is the fact that it still crashes sometimes
with
> >>
> >>|ORA-01555: snapshot too old: rollback segment number 29 with name
> >>|"RBS17" too small
> >>
> >>Why does Oracle use RBS17 (a small one) and not RBSBIG1? RBSBIG1 is
> >>definitvely online.
> >>
> >>Any ideas?
> >
> >
> > The logical explanation for this is each call to any dbms_stats
> > procedure is probably considered a transaction.
> > Which means you acquire the correct rollback segment for the first
> > table, and for the other tables revert to the usual round robin
> > mechanism.
> > So either you should:
> > - use gather_schema_stats
> > or
> > - set all rollback segments equally big
> > (Yes I know, I was forced to do this for export)
> >
> >
> > --
> > Sybrand Bakker, Senior Oracle DBA

>
> or set the rollback segment before each call to dbms_stats

The OP is using PL/SQL to loop through the table names, and, as such, cannot issue such direcives from within a PL/SQL loop. This was clearly stated in the original text; this is , therefore, not an availanle option. The only other option the OP has is to ensure that the ONLY rollback segment online is RBSBIG1 when this script is run, an option I do not suggest be used. Sybrand has already indicated that, outside of using gather_schema_stats in place of gather_table_stats, the only VIABLE option is to resize the remaining rollback segments to match that of RBSBIG1.

Personally, I prefer using dbms_stats.gather_schema_stats(). David Fitzjarrell Received on Fri Dec 31 2004 - 08:27:32 CST