 |
|
Oracle Tips by Burleson |
OCP Instructors Guide for
Oracle DBA Certification
Chapter 5 - Oracle Database
Objects
Rollback Segments and
Transaction Processing Workloads
SELECT CLASS, COUNT FROM V$WAITSTAT
SEE
CODE DEPOT FOR FULL SCRIPT
Any non-zero value in the count column
indicates rollback segment contention. Consider creating more
rollback segments and begin analyzing rollback segment sizing
specifications.
SELECT rn.Name "Rollback Segment", rs.RSSize/1024
"Size (KB)", rs.Gets "Gets", rs.waits "Waits", (rs.Waits/rs.Gets)*100
"% Waits", rs.Shrinks "# Shrinks", rs.Extends "# Extends" FROM
sys.v_$RollName rn, sys.v_$RollStat rs
SEE
CODE DEPOT FOR FULL SCRIPT
Besides the obvious columns (waits, %waits)
take a look at the number of shrinks and extends that are occurring.
High numbers indicate that the specifications that control the size
of the rollback segment (INITIAL, NEXT, MINEXTENTS, OPTIMAL) may
need to be increased.
SELECT segment_name, bytes, extents FROM
dba_segments
SEE
CODE DEPOT FOR FULL SCRIPT
The simplest query of the three can also be the
most helpful. The query should be run when database activity is
high. Active rollback segments don’t have a chance to shrink back to
their optimal settings. The query will help the DBA to determine if
the rollback segments’ sizing specifications (INITIAL, NEXT,
MINEXTENTS, OPTIMAL) are set sufficiently large enough. If the
report shows that the rollback segments are allocating high numbers
of extents, further sizing analysis is warranted.
The above text is
an excerpt from:
OCP Instructors Guide for Oracle DBA Certification
A Study Guide to Advanced Oracle Certified Professional Database
Administration Techniques
ISBN 0-9744355-3-8
by Christopher T. Foot
http://www.rampant-books.com/book_2003_2_OCP_print.htm
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
