REPOSTING as got a Delivery Failure Notice,
-----Original Message-----
From: VIVEK_SHARMA
Sent: Tuesday, May 23, 2000 11:19 AM
Steve Wrote > my guess is that it was actually an
> ITL entry shortage (see page 47 in my book).
> If this is 8i, you may want to consider using the
> 'alter table minimize records_per_block'
What does ITL stand for ?
The name of the Book please ( pg 47) ?
On Oracle 7.3.4 with 8Kb DB_BLOCK_SIZE ,
for the respective TableS , should INITRANS & MAXTRANS
be Altered to Control the No. of rows per block ? How ?
Gaja ,
Thanks so much for the Explanation . Was the Greatest
Gaja Wrote >"The "ball park" optimum number of rollback segments
that is
> required for most environments can be derived by the formula :
> "no. of concurrent txns/4".
Qs. 1 Should Number of Rollback Segments be INCREASED to 12,000
approx from 160
or Some intermediate value ?
NOTE That The Transactions OLTP in nature
QUERY :-
select table_name,avg_row_len
from sys.dba_tables
where ...
/
OUTPUT :-
TABLE_NAME AVG_ROW_LEN NUMBER of ROWS INSERTed
per Tran
------------------------- ------------
---------------------------------
DAILY_TRAN_DETAIL_TABLE 186 2
DAILY_TRAN_HEADER_TABLE 91 1
TEMP_DAILY_TRAN_TABLE 73 2
Thus 1 Transaction = 550 BYTES - Correct ?
Gaja Wrote >The size of the extents in the rollback segments
> can be derived by performing an avg(used_ublk)
> in v$transaction during peak periods.
OK -will revert back to you after next Transaction Runs
Will use the Query :-
select avg(used_ublk) from v$transaction;
Gaja Wrote > "Proactive configuration of an optimal number of
freelists and the effective use of the INITRANS parameter "
Qs. How is the Value of INITRANS , MAXTRANS to be determined ? Any
Thumb rule ?
-----Original Message-----
From: Gaja Krishna Vaidyanatha [SMTP:gajav_at_yahoo.com]
Sent: Monday, May 22, 2000 12:24 AM
Friends,
I totally agree with Steve's sentiments. There are many
issues
on the table here, and my response to all of them make this
posting a long one. Thank your for your patience is reading
this through.
The limitation to prevent us from creating more than 50
rollback
segments was lifted in early Oracle7, when Oracle started
supporting the max_rollback_segments parameter. The
rollback_segments parameter itself could not support more
than
50 entries prior to Oracle7, and hence the limitation. The
documentation has not changed with time. This limitation
has
been long lifted. If there is justification and a need to
create more rollback segments, then by all means go right
ahead
and do it. Personally, I have created as many rollback
segments
as the value of the max_rollback_segments parameter.
The "ball park" optimum number of rollback segments that is
required for most environments can be derived by the formula
:
"no. of concurrent txns/4". The size of the extents in the
rollback segments can be derived by performing an
avg(used_ublk)
in v$transaction during peak periods. The number that is
acquired from the above average should then be raised to the
next power of 2(e.g. if the avg(used_ublk) is 14045, then
the
initial and next size for the rollback segments can be 16K).
Obviously, this is not the size for the big_roll rollback
segments that you use for your batch jobs.
The goal here is to try to accomodate the undo entries of a
transaction in 1 extent, to avoid re-reading the rollback
segment header. The rollback segment header contains the
transaction table and the pointers to the location where a
given
transaction's undo entry is located. Reading the
transaction
table over and over again can and will cause contention for
that
rollback segment.
I am not in any way suggesting that multiple transactions
should
not write their undo entries to the same extent. All I am
trying to communicate here is a design to reduce contention
on
the rollback segment header and the transaction table that
it
hosts.
The minimum number of extents in a rollback segment should
be at
least 20. Simulations and tests done in Oracle's Internal
Labs
in the past using this MINEXTENTS value, has shown to reduce
the
probability of the "snapshot too old" error occurring.
Personally, I am not a big fan of the "OPTIMAL" parameter as
this again increases the probability of the "snapshot too
old"
error. I'd rather shrink the rollback segment via a job at
a
time that is appropriate (wee hours of the morning) than
have
"OPTIMAL" kill a long-running query (batch report). I know
if
you are running a database prior to 7.3, then you are out of
luck. I have observed that adhering to the above set of
rules
has helped me proactively manage the rollback segment
problem
very well. Your mileage might vary, but is definitely worth
the
effort.
- - -
- - -
- - -
I know this from having done some tests in 7.3.5 and I am
pretty
sure that this functionality has not changed radically in
Oracle8/8i. Proactive configuration of an optimal number of
freelists and the effective use of the INITRANS parameter
should
take care of block level contention in most cases. Thanks
for
your patience.
Best Regards,
Gaja.
--- Steve Adams <steve.adams_at_ixora.com.au> wrote:
> Hi All,
>
> There is some rather peculiar advice being given here.
> The old recommendation to not create more than 50 rollback
> segments has
> been out of date for nearly a decade.
> You cannot increase freelists to numbers of the order of
1000
> or more, not
> even with a 32K database block size.
> The transactions_per_rollback_segment parameter does not
> impact rollback
> segment usage in any way - only the number of public
rollback
> segments to
> acquire at startup.
>
> Looking down at the initial problem, my guess is that it
was
> actually an
> ITL entry shortage (see page 47 in my book).
> If this is 8i, you may want to consider using the 'alter
table
> minimize
> records_per_block' command to control the number of rows
> allowed in each
> block.
>
> Regards,
> Steve Adams
> > > > >From: VIVEK_SHARMA <vivek_sharma_at_inf.com>
> > > > >Reply-To: ORACLE-L_at_fatcity.com
> > > > >
> > > > >
> > > > >CASE - Benchmarking Application on a Specific
Machine E
> 6.5 K SunOS
> > 5.6
> > > > >
> > > > >AIM - To pass 50,000 OLTP Transactions in 1 Hour
using
> 2500 Oracle
> > > > >sessions
> > > > >(with respective Unix users)
> > > > >
> > > > >HARDWARE setup - Total 3 E6.5K machines = 2
machines Containing APP ,
> > > > > 1 machine Containing DB
> > > > >20 GB RAM in each , 18 CPUs in each ,
> > > > >
> > > > >PROBLEM Started the firing off of OLTP transactions
.
> Till > Completion of 10 Thousand (roughly) OLTP
Transactions , there were NO
Received on Wed May 24 2000 - 01:37:37 CDT
