Contention for Undo Header

[anuragmin]

Hi,
I am having a high contention for undo header. I used the following queries to find that out.

Do i need to increase the rollback segment size or increase the number of rollback segments to tune this.

Also what is Undo Header??


select class, count
2 from v$waitstat
3 where class in ('system undo header', 'system undo block',
4 'undo header', 'undo block' ) ;

CLASS COUNT
------------------ ----------
system undo header 0
system undo block 0
undo header 62579
undo block 2

select 'Contention for undo header = '||
2 (round(count/(&xxv1+0.00000000001),4)) * 100||'%'
3 from v$waitstat
4 where class = 'undo header' ;
old 2: (round(count/(&xxv1+0.00000000001),4)) * 100||'%'
new 2: (round(count/( 85473+0.00000000001),4)) * 100||'%'

Contention for undo header = 73.21%


Thanks
Anurag

[Halo]

An undo header is the segment header for an rbs. I believe you may have to increase the # rbs. Plz confirm this with someone. Also, run this script and post the output here:
-
SELECT name, waits, gets, waits/gets "Ratio"
FROM v$rollstat a, v$rollname b
WHERE a.usn = b.usn;

[itsravi]

Check out your rollback work (v$rollstat/rollanme join using USN & see for extents in dba_rollback_segs) if things are not bright, increase number of rollback segs either small or large but do analysis before you'd create 'em

[anuragmin]

Hi,
Thanks for the reply.

The output of the script is below.Please note that I added rollback segments RB6 and RB7 just a few minutes ago.

Thanks again.
Anurag


SQL> SELECT name, waits, gets, waits/gets "Ratio"
2 FROM v$rollstat a, v$rollname b
3 WHERE a.usn = b.usn;

NAME WAITS GETS Ratio
------------------------------ ---------- ---------- ----------
SYSTEM 0 12382 0
RB0 4918 1324201 .003713938
RB1 5121 2584849 .00198116
CTXROL 13474 1808441 .007450616
RB2 4880 2333087 .002091649
RB3 5080 1481145 .003429779
RB4 4684 2132723 .002196253
RB5 4782 1620563 .002950826
RB6 0 23 0
RB7 0 18 0

[anuragmin]

Hi Ravi,

Can you please give me a script to analyse the rollback segment work.

Thanks
Anurag

[Halo]

Well, the good part is that there does not seem to be any contention for the rbs.

[anuragmin]

I have read on the metalink site for oracle that if a non zero value existed for 'undo header ' under the class column this means that there is a contention for rollback segment header blocks. Please refer Note: 69464.1

And my values for the undo header is very high. see below

select class, count
2 from v$waitstat
3 where class in ('system undo header', 'system undo block',
4 'undo header', 'undo block' ) ;

CLASS COUNT
------------------ ----------
system undo header 0
system undo block 0
undo header 62579
undo block 2

I am a little confused. Please suggest.

Thanks
Anurag

[gpsingh]

What is ur database block size.
what are the values for consistent gets and db block gets.
what is the current and max value for transactions in v$resource_limit.

how many write,get and waits per rollback segment.
What is the size of ur rollback segments.

Problem seems to be the number of transactions hitting single rollback segment, if transactions are high , then u need to have more rollback segments available .

Take Care
GP

[anuragmin]

Hi,

DB_BLOCK_SIZE = 8192
consistent gets = 16592427
db block gets = 3088987


SQL> select RESOURCE_NAME ,CURRENT_UTILIZATION,MAX_UTILIZATION from v$resource_limit;

RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION
------------------------------ ------------------- ---------------
processes 20 22
sessions 21 24
lm_procs 0 0
lm_ress 0 0
lm_locks 0 0
enqueue_locks 12 17
enqueue_resources 15 34
dml_locks 2 21
temporary_table_locks 0 9
transactions 55 56
sort_segment_locks 0 1

RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION
------------------------------ ------------------- ---------------
max_rollback_segments 8 8
distributed_transactions 0 1
mts_max_servers 0 0
parallel_max_servers 0 0


I am not sure how to get the waits,gets and writes per rollback segments.Please let me know how to get them.


Thanks
Anurag

[gpsingh]

U query v$rollstat,
Ur details tell that u have enough rollback segments,
now do the queries or procedures use particular rollback segments.