In answer to Arthur Merar:
- What does 'Optimal' parameter do for rollback segments?
Optimal sets a size in bytes that Oracle checks against when if extends a
rollback segment. If the size of the rollback segment exceeds the optimal
then Oracle frees unused extents from the rollback segment. Normally, a
rollback segment in treated like a circular queue. New data goes to the
end and when the last allocated extent is filled the first extent is reused
if it does not contain information for an active transaction. If has
active data then a new extent is allocated. Note that extent number two
for the segment may have no active transaction data. So optimal allows
Oracle to release extent number two at the same time that it allocated a
new extent to the rollback segment. The space held by extent two is now
available for use by a different rollback segment or maybe a future
allocation of the same segment.
- What is a cluster?
A cluster is allocated space, an extent, that contains one or more tables
that are stored together in the same Oracle data blocks based on a common
column value or hash value. If I have two tables that are almost always
referenced by join SQL on a certain column like department_number then I
can tell Oracle to store the associated rows for these two tables that
have the same department number in the same Oracle data blocks so that when
I retrieve one row I have all the rows associated with a specific key
value. Oracle uses one or two clusters to hold portions of its dictionary,
but for the most part you should avoid trying to use clusters (opinion) as
they are not usually necessary; you really need to be certain that the data
will be retrieved via the expected join for the cluster benefits to
outweigh the costs. The department number would be the cluster key in this
example.
A hash cluster substitutes a math formula that randomizes (hashes) the key
into a specific block within the allocation. Hashing is very efficient for
referencing data that is always retrieved by key, but the data needs to be
very static from a total number of rows standpoint since the hash area is
fixed at creation time. Hash tables are normally less densely packed than
standard tables and full table scans suffer due to this.
3) When dealing with 'Profiles'....what is meant by a 'composite limit'
The composite limit is a summed value of the profiles cpu_per_session,
connect_time, logical_reads_per_session, and private_sga parameters. It is
an overall limit based on an estimate of resource usage for a session. See
'alter resource cost' in the SQL manual for an explanation of how to adjust
this puppy.
Received on Mon Aug 24 1998 - 09:00:54 CDT
