Discussion :

[slyv]

Bonjour,

Au sein de ma base de données, je suis en train de paramétrer mes rollback segments.

Est ce que quelqu'un connaitrait les tailles optimales des paramètres INITIAL, NEXT, OPTIMAL, MINEXTENTS, MAXEXTENTS en fonction des paramètre de mes tablespaces ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
CREATE ROLLBACK SEGMENT rbs1TBS_USERS
TABLESPACE TBS_USERS
STORAGE (INITIAL 100K NEXT 100K OPTIMAL 2M
MINEXTENTS 20 MAXEXTENTS 100)

Merci par avance

Slyv

[Pomalaix]

Bonjour

En quelle version d'Oracle êtes-vous ?

[slyv]

Bonjour,

J'utilise Oracle 8i

[orafrance]

Pourquoi ne pas utiliser le UNDO à la place des RBS alors ?

[slyv]

Salut,

C'est pour un projet d'école en faite. On a une image virtuelle d'une base de données ORacle 8i et on doit "s'amuser" à l'administration. Ceci passe par la création des rôles, des tablespaces, d'une méthode de sauvegarde, et des rollbacks segments .

Je ne sais pas ce que c que le UNDO en question mais je dois de toute façon faire des rollback segments

[orafrance]

le UNDO remplace les RBS

http://www.developpez.net/forums/vie...highlight=undo

[bouyao]

il est sous oracle 8i

[orafrance]

ha mince, au temps pour moi

désolé, je passe mon tour

[orafrance]

voila un lien d'oracle à ce sujet :
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=69464.1

How MANY rollback segments do you need to have?
===============================================

Oracle7 keeps a transaction table in the header of every rollback segment.
Every transaction must have update access to the transaction table for its
rollback segment. You need enough rollback segments to prevent transactions
contending for the transaction table.

How do you find out a transaction table contention?
===================================================

Any non-zero value for 'undo header' in the CLASS column of "v$waitstat"
indicates contention for rollback segment header blocks.
Example:
SVRMGR> select * from v$waitstat;

CLASS COUNT TIME
------------------ ---------- ----------
data block 0 0
sort block 0 0
save undo block 0 0
segment header 0 0
save undo header 0 0
free list 0 0
system undo header 0 0
system undo block 0 0
undo header 0 0
undo block 0 0
Note that 'undo header' value is zero, hence NO contention.
Another way to find out is by running the following query .. a non-zero
value for the 'WAITS' column indicates a rollback segment contention.

SVRMGR> select name, waits
2> from v$rollstat s, v$rollname n
3> where s.usn=n.usn;

NAME WAITS
------------------------------ ----------
SYSTEM 0
R01 0
R02 0
R03 0

To calculate the number of rollback segments, you need to know how many
transactions are likely to be active at any given time. This depends on what
users are doing. Note that queries do not need transaction table access, so not
all active users will have active transactions (OLTP applications tend to have
many short transactions).
General recommendation for how many rollback segments:
For OLTP : One rollback segment for every ten users.
For BATCH jobs : One rollback segment for each concurrent job.

How do you decide what SIZE your rollback segments should be?
=============================================================
There are two issues that need to be considered when deciding if your segment
is large enough. First, you want to make sure that the transactions will not
cause the head of the rollback segment to wrap around too fast and catch the
tail. This causes the segment to extend in size. Second, if you have long
running queries that access data that frequently changes, you want to make sure
that the rollback segment doesn't wrap around and prevent the construction of a
read consistent view (look at "Why the ORA-1555 snapshot too old problem?"
above).

Determining the proper rollback segment size:
---------------------------------------------

The size needed for a rollback segment depends directly on the transaction
activity of your database. You need to be concerned about the activity during
normal processing of the database, not with rare or semi-frequent large
transactions. We will deal with these special cases separately.

Same size extents:
------------------

For sizing rollback segments extents, Oracle strongly recommend that each
extent be of the same size.

INITIAL extent size:
--------------------

Choose the INITIAL storage parameter from the list 2KB, 4KB, 8KB, 16KB, 32KB ...
etc. This will insure that when you drop the extent you can reuse all the
freed space without waste.

NEXT extent size:
-----------------

Use the same value for NEXT as INITIAL.

MINEXTENTS:
-----------

Set MINEXTENTS to 20, this will make it unlikely that the rollback segment
needs to grab another extent because the extent that should move into is still
being used by an active transaction.
To find out the size of the rollback segments needed to handle normal
processing on the database you need to some testing. A good test is to start
with small rollback segments and allow your application to force them to extend.
Here are the steps to run such test:
1. Create a rollback segment tablespace.
2. Select a number of rollback segments to test and create them in the
tablespace.
3. Create the rollback segments so that all extents are the same size.
Choose an extent size that you will suspect will need between 10 to 30
extents when the segments grow to full size.
4. Each rollback segment should start with two extents before the test is
done. This is the minimum number of extents any rollback segment can have.
5. Activate only the rollback segments that you are testing by making the
status "online". The only other segment that should be "online" is the
system rollback segment.
6. Run transactions and load data typical of the application.
7. Watch for rollback segment contention. How to find out?
8. Watch for the maximum size a rollback extends to.
The maximum size any one of the rollback segments reaches during the test is
the size you want to use when configuring. We will call this size the "minimum
coverage size." If you see rollback contention, adjust the number of the
rollback segments (increase) and rerun the test. Also, if the largest size
requires fewer than 10 or more than 30, it is a good idea to lower or raise the
extent size, respectively, and rerun the test.

Sizing rollback segments for STEADY AVERAGE transaction rate:
-------------------------------------------------------------

For databases where the transaction rate base has NO fluctuation, there is a
straightforward way to configure the tablespace:
Create a tablespace that will fit your calculated number of rollback
segments with the "minimum coverage size" you have determined. Follow the
guidelines above for INITIAL and NEXT extents.
As a safety net, allocate some additional space in the tablespace to allow
segments to grow if necessary. If you select to do this, use the OPTIMAL
feature to force the rollback segments to free up any additional space they
allocate beyond their determined size requirement.

Sizing rollback segments for FREQUENT LARGE transaction rate:
-------------------------------------------------------------

A large transaction is one in which there is not enough space to create all
rollback segments of the size necessary to handle its rollback information.
Since we can't depend on the segment shrinking in time to allow repeated large
transactions, OPTIMAL is not really an option for this environment. There are
basically two options that you can choose from for your rollback segment
tablespace.
A) Reduce the number of segments so that all are large enough to hold the
largest transactions. This option will introduce contention and will cause
some degradation in performance. It is a reasonable choice if performance is
not extremely critical.
B) Build one or more large rollback segments and make sure that large
transactions use these segments. The SET TRANSACTION USE ROLLBACK SEGMENT
command is necessary to control the placement of these large transactions.
This option is difficult to implement if large transactions are being run
with adhoc queries and there is no systematic control of large transactions.
This option is recommended in an environment where the large transactions
are issued from a controlled environment. In other words, an application
which will set the transaction to the appropriate rollback segment.

Sizing rollback segments for INFREQUENT LARGE transaction rate:
---------------------------------------------------------------

Use the OPTIMAL feature to set up a flexible rollback segment scheme, one
in which you are not concerned about which rollback segment the large
transaction falls upon. The key is to leave enough free space in the rollback
tablespace that the largest transaction's rollback information can fit entirely
into it. To do this, create the rollback tablespace with the space needed for
your calculated number of segments and their "minimum coverage size" plus this
additional space. Set the OPTIMAL for each segment equal to the minimum
coverage size.
What you will see is that the large transaction will randomly make one of the
segments grow and consume the free space, but the segment will release the
space before the next large transaction comes along. Note that you are
sacrificing some performance for this flexibility.

What are the guidelines on setting the OPTIMAL parameter for rollback segments?
===============================================================================

When you create or alter a rollback segment, you can use the storage
parameter OPTIMAL, which applies only to rollback segments, to specify the
optimal size of the rollback segment in bytes. You should carefully assess the
kind of transactions the system runs when setting the OPTIMAL parameter for
each rollback segment.
For a system that executes long running transactions frequently, OPTIMAL
should be large so that Oracle does not have to shrink and allocate extents
frequently. Also, for a system that executes long queries on active database,
OPTIMAL should be large to avoid "snapshot too old" ORA-1555 errors.
OPTIMAL should be smaller for a system that mainly executes short transactions
and queries so that the rollback segments remain small enough to be cached in
memory, thus improving system performance. You should not make OPTIMAL smaller
than the "minimum coverage size". Otherwise, performance will suffer due to
excessive segment resizing.

[slyv]

Ok,

Merci à tous.

Je vais lire tout ça et essayer de trouver la bonne taille pour mon paramètre initial

Slyv