 | | | question regarding automatic undo | question regarding automatic undo 2005-06-21 - By Goulet, Dick
Jeffery,
Yes the database does things like that. It will create additional
segments as needed, offline them when apparently no longer needed until
they are no longer referenced, see undo_retention, and then drop them.
It's perfectly normal behavior. Check out V$UNDOSTAT for further
information. Oh heck, I'll be nice:
V$UNDOSTAT
V$UNDOSTAT displays a histogram of statistical data to show how well the
system is working. The available statistics include undo space
consumption, transaction concurrency, and length of queries executed in
the instance. You can use this view to estimate the amount of undo space
required for the current workload. Oracle uses this view to tune undo
usage in the system. This view is available in both automatic undo
management mode and manual undo management mode.
Each row in the view keeps statistics collected in the instance for a
10-minute interval. The rows are in descending order by the BEGIN_TIME
column value. Each row belongs to the time interval marked by
(BEGIN_TIME, END_TIME). Each column represents the data collected for
the particular statistic in that time interval. The first row of the
view contains statistics for the (partial) current time period. The view
contains a total of 1008 rows, spanning a 7 day cycle.
Column Datatype Description
BEGIN_TIME
DATE
Identifies the beginning of the time interval
END_TIME
DATE
Identifies the end of the time interval
UNDOTSN
NUMBER
Represents the last active undo tablespace in the duration of time. The
tablespace ID of the active undo tablespace is returned in this column.
If more than one undo tablespace was active in that period, the active
undo tablespace that was active at the end of the period is reported.
UNDOBLKS
NUMBER
Represents the total number of undo blocks consumed. You can use this
column to obtain the consumption rate of undo blocks, and thereby
estimate the size of the undo tablespace needed to handle the workload
on your system.
TXNCOUNT
NUMBER
Identifies the total number of transactions executed within the period
MAXQUERYLEN
NUMBER
Identifies the length of the longest query (in number of seconds)
executed in the instance during the period. You can use this statistic
to estimate the proper setting of the UNDO_RETENTION initialization
parameter.
MAXCONCURRENCY
NUMBER
Identifies the highest number of transactions executed concurrently
within the period
UNXPSTEALCNT
NUMBER
Number of attempts to obtain undo space by stealing unexpired extents
from other transactions
UNXPBLKRELCNT
NUMBER
Number of unexpired blocks removed from certain undo segments so they
can be used by other transactions
UNXPBLKREUCNT
NUMBER
Number of unexpired undo blocks reused by transactions
EXPSTEALCNT
NUMBER
Number of attempts to steal expired undo blocks from other undo segments
EXPBLKRELCNT
NUMBER
Number of expired undo blocks stolen from other undo segments
EXPBLKREUCNT
NUMBER
Number of expired undo blocks reused within the same undo segments
SSOLDERRCNT
NUMBER
Identifies the number of times the error ORA-01555 (See ORA-01555.ora-code.com) occurred. You can use
this statistic to decide whether or not the UNDO_RETENTION
initialization parameter is set properly given the size of the undo
tablespace. Increasing the value of UNDO_RETENTION can reduce the
occurrence of this error.
NOSPACEERRCNT
NUMBER
Identifies the number of times space was requested in the undo
tablespace and there was no free space available. That is, all of the
space in the undo tablespace was in use by active transactions. The
corrective action is to add more space to the undo tablespace.
__ __
From: Jeffrey Beckstrom [mailto:JBECKSTROM@(protected)]
Sent: Tuesday, June 21, 2005 1:03 PM
To: oracle-l@(protected); oracle-db-l@(protected);
ORACLE-L@(protected); oracledba@(protected); Goulet, Dick;
oracle-rdbms@(protected)
Subject: RE: question regarding automatic undo
Am I correct in that possibly database 2 was very busy and decided it
needed more segment so dynamically created them. Sometime thereafter,
not needed so they were offlined????
>>> "Goulet, Dick" <DGoulet@(protected)> 6/21/05 12:54:27 PM >>>
Yes. When you enable automatic undo management you leave all of that to
the database engine.
__ __
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of Jeffrey Beckstrom
Sent: Tuesday, June 21, 2005 12:41 PM
To: oracle-l@(protected); oracle-db-l@(protected);
ORACLE-L@(protected); oracledba@(protected);
oracle-rdbms@(protected)
Subject: question regarding automatic undo
Two identical databases.
database 1 has 10 undo online and 2 offline
database 2 has 10 undo online and 23 offline
Why does the second have more offline undo segments. Does Oracle
automatically create them if needed and when no longer needed offline
it??
Jeffrey Beckstrom
Database Administrator
Greater Cleveland Regional Transit Authority
1240 W. 6th Street
Cleveland, Ohio 44113
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2800.1479" name=GENERATOR></HEAD>
<BODY style="MARGIN: 4px 4px 1px; FONT: 10pt Tahoma">
<DIV dir=ltr align=left><SPAN class=678010517-21062005>Jeffery,</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=678010517-21062005></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=678010517-21062005> Yes
the database does things like that. It will create additional segments as
needed, offline them when apparently no longer needed until they are no longer
referenced, see undo_retention, and then drop them. It's perfectly normal
behavior. Check out V$UNDOSTAT for further information. Oh heck,
I'll be nice:</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=678010517-21062005></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN
class=678010517-21062005><!--StartFragment -->
<H2 class=H1><A name=REFRN30769><FONT face="Arial, Helvetica, sans-serif"
color=#330099>V$UNDOSTAT</FONT></A></H2><!--/TOC=h1--><A name=1110666></A>
<P class=BP><CODE>V$UNDOSTAT</CODE> displays a histogram of statistical data to
show how well the system is working. The available statistics include undo
space
consumption, transaction concurrency, and length of queries executed in the
instance. You can use this view to estimate the amount of undo space required
for the current workload. Oracle uses this view to tune undo usage in the
system. This view is available in both automatic undo management mode and
manual
undo management mode.</P><A name=1110667></A>
<P class=BP>Each row in the view keeps statistics collected in the instance for
a 10-minute interval. The rows are in descending order by the
<CODE>BEGIN_TIME</CODE> column value. Each row belongs to the time interval
marked by (<CODE>BEGIN_TIME</CODE>, <CODE>END_TIME</CODE>). Each column
represents the data collected for the particular statistic in that time
interval. The first row of the view contains statistics for the (partial)
current time period. The view contains a total of 1008 rows, spanning a 7 day
cycle.</P><A name=1110765></A>
<TABLE class=InformalWide dir=ltr title="" cellSpacing=0 cellPadding=3
rules=groups width="100%" summary="" border=1 frame=hsides>
<THEAD>
<TR class=Informal>
<TH class=Informal vAlign=bottom scope=col align=left><A
name=1110670></A><FONT
face="Arial, Helvetica, sans-serif"><STRONG>Column</STRONG></FONT></TH>
<TH class=Informal vAlign=bottom scope=col align=left><A
name=1110672></A><FONT
face="Arial, Helvetica, sans-serif"><STRONG>Datatype</STRONG></FONT></TH>
<TH class=Informal vAlign=bottom scope=col align=left><A
name=1110674></A><FONT
face="Arial, Helvetica, sans-serif"><STRONG>Description</STRONG></FONT><
/TH></TR></THEAD>
<TBODY>
<TR class=Informal vAlign=top align=left>
<TD class=Informal><A name=1110676></A>
<P class=TB><CODE>BEGIN_TIME</CODE></P></TD>
<TD class=Informal><A name=1110678></A>
<P class=TB><CODE>DATE</CODE></P></TD>
<TD class=Informal><A name=1110680></A>
<P class=TB>Identifies the beginning of the time interval</P></TD></TR>
<TR class=Informal vAlign=top align=left>
<TD class=Informal><A name=1110682></A>
<P class=TB><CODE>END_TIME</CODE></P></TD>
<TD class=Informal><A name=1110684></A>
<P class=TB><CODE>DATE</CODE></P></TD>
<TD class=Informal><A name=1110686></A>
<P class=TB>Identifies the end of the time interval</P></TD></TR>
<TR class=Informal vAlign=top align=left>
<TD class=Informal><A name=1110688></A>
<P class=TB><CODE>UNDOTSN</CODE></P></TD>
<TD class=Informal><A name=1110690></A>
<P class=TB><CODE>NUMBER</CODE></P></TD>
<TD class=Informal><A name=1110692></A>
<P class=TB>Represents the last active undo tablespace in the duration of
time. The tablespace ID of the active undo tablespace is returned in this
column. If more than one undo tablespace was active in that period, the
active undo tablespace that was active at the end of the period is
reported.</P></TD></TR>
<TR class=Informal vAlign=top align=left>
<TD class=Informal><A name=1110694></A>
<P class=TB><CODE>UNDOBLKS</CODE></P></TD>
<TD class=Informal><A name=1110696></A>
<P class=TB><CODE>NUMBER</CODE></P></TD>
<TD class=Informal><A name=1110698></A>
<P class=TB>Represents the total number of undo blocks consumed. You can
use this column to obtain the consumption rate of undo blocks, and
thereby
estimate the size of the undo tablespace needed to handle the workload on
your system.</P></TD></TR>
<TR class=Informal vAlign=top align=left>
<TD class=Informal><A name=1110700></A>
<P class=TB><CODE>TXNCOUNT</CODE></P></TD>
<TD class=Informal><A name=1110702></A>
<P class=TB><CODE>NUMBER</CODE></P></TD>
<TD class=Informal><A name=1110704></A>
<P class=TB>Identifies the total number of transactions executed within
the period</P></TD></TR>
<TR class=Informal vAlign=top align=left>
<TD class=Informal><A name=1110706></A>
<P class=TB><CODE>MAXQUERYLEN</CODE></P></TD>
<TD class=Informal><A name=1110708></A>
<P class=TB><CODE>NUMBER</CODE></P></TD>
<TD class=Informal><A name=1110710></A>
<P class=TB>Identifies the length of the longest query (in number of
seconds) executed in the instance during the period. You can use this
statistic to estimate the proper setting of the
<CODE>UNDO_RETENTION</CODE> initialization parameter.</P></TD></TR>
<TR class=Informal vAlign=top align=left>
<TD class=Informal><A name=1110712></A>
<P class=TB><CODE>MAXCONCURRENCY</CODE></P></TD>
<TD class=Informal><A name=1110714></A>
<P class=TB><CODE>NUMBER</CODE></P></TD>
<TD class=Informal><A name=1110716></A>
<P class=TB>Identifies the highest number of transactions executed
concurrently within the period</P></TD></TR>
<TR class=Informal vAlign=top align=left>
<TD class=Informal><A name=1110718></A>
<P class=TB><CODE>UNXPSTEALCNT</CODE></P></TD>
<TD class=Informal><A name=1110720></A>
<P class=TB><CODE>NUMBER</CODE></P></TD>
<TD class=Informal><A name=1110722></A>
<P class=TB>Number of attempts to obtain undo space by stealing unexpired
extents from other transactions</P></TD></TR>
<TR class=Informal vAlign=top align=left>
<TD class=Informal><A name=1110724></A>
<P class=TB><CODE>UNXPBLKRELCNT</CODE></P></TD>
<TD class=Informal><A name=1110726></A>
<P class=TB><CODE>NUMBER</CODE></P></TD>
<TD class=Informal><A name=1110728></A>
<P class=TB>Number of unexpired blocks removed from certain undo segments
so they can be used by other transactions</P></TD></TR>
<TR class=Informal vAlign=top align=left>
<TD class=Informal><A name=1110730></A>
<P class=TB><CODE>UNXPBLKREUCNT</CODE></P></TD>
<TD class=Informal><A name=1110732></A>
<P class=TB><CODE>NUMBER</CODE></P></TD>
<TD class=Informal><A name=1110734></A>
<P class=TB>Number of unexpired undo blocks reused by
transactions</P></TD></TR>
<TR class=Informal vAlign=top align=left>
<TD class=Informal><A name=1110736></A>
<P class=TB><CODE>EXPSTEALCNT</CODE></P></TD>
<TD class=Informal><A name=1110738></A>
<P class=TB><CODE>NUMBER</CODE></P></TD>
<TD class=Informal><A name=1110740></A>
<P class=TB>Number of attempts to steal expired undo blocks from other
undo segments</P></TD></TR>
<TR class=Informal vAlign=top align=left>
<TD class=Informal><A name=1110742></A>
<P class=TB><CODE>EXPBLKRELCNT</CODE></P></TD>
<TD class=Informal><A name=1110744></A>
<P class=TB><CODE>NUMBER</CODE></P></TD>
<TD class=Informal><A name=1110746></A>
<P class=TB>Number of expired undo blocks stolen from other undo
segments</P></TD></TR>
<TR class=Informal vAlign=top align=left>
<TD class=Informal><A name=1110748></A>
<P class=TB><CODE>EXPBLKREUCNT</CODE></P></TD>
<TD class=Informal><A name=1110750></A>
<P class=TB><CODE>NUMBER</CODE></P></TD>
<TD class=Informal><A name=1110752></A>
<P class=TB>Number of expired undo blocks reused within the same undo
segments</P></TD></TR>
<TR class=Informal vAlign=top align=left>
<TD class=Informal><A name=1110754></A>
<P class=TB><CODE>SSOLDERRCNT</CODE></P></TD>
<TD class=Informal><A name=1110756></A>
<P class=TB><CODE>NUMBER</CODE></P></TD>
<TD class=Informal><A name=1110758></A>
<P class=TB>Identifies the number of times the error
<CODE>ORA-01555 (See ORA-01555.ora-code.com)</CODE> occurred. You can use this statistic to decide
whether or not the <CODE>UNDO_RETENTION</CODE> initialization parameter
is
set properly given the size of the undo tablespace. Increasing the value
of <CODE>UNDO_RETENTION</CODE> can reduce the occurrence of this
error.</P></TD></TR>
<TR class=Informal vAlign=top align=left>
<TD class=Informal><A name=1110760></A>
<P class=TB><CODE>NOSPACEERRCNT</CODE></P></TD>
<TD class=Informal><A name=1110762></A>
<P class=TB><CODE>NUMBER</CODE></P></TD>
<TD class=Informal><A name=1110764></A>
<P class=TB>Identifies the number of times space was requested in the
undo
tablespace and there was no free space available. That is, all of the
space in the undo tablespace was in use by active transactions. The
corrective action is to add more space to the undo
tablespace.</P></TD></TR></TBODY></TABLE></SPAN></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<B>From:</B> Jeffrey Beckstrom [mailto:JBECKSTROM@(protected)] <BR><B>Sent:</B>
Tuesday, June 21, 2005 1:03 PM<BR><B>To:</B> oracle-l@(protected);
oracle-db-l@(protected); ORACLE-L@(protected); oracledba@(protected)
;
Goulet, Dick; oracle-rdbms@(protected)<BR><B>Subject:</B> RE: question
regarding automatic undo<BR><BR></DIV>
<DIV></DIV>Am I correct in that possibly database 2 was very busy and decided
it
needed more segment so dynamically created them. Sometime thereafter, not
needed so they were offlined????<BR><BR>>>> "Goulet, Dick"
<DGoulet@(protected)> 6/21/05 12:54:27 PM >>><BR>
<DIV style="FONT: 10pt Tahoma">
<DIV dir=ltr align=left><SPAN class=441555316-21062005>Yes. When you
enable automatic undo management you leave all of that to the database
engine.</SPAN></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<B>From:</B> oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] <B>On Behalf Of </B>Jeffrey
Beckstrom<BR><B>Sent:</B> Tuesday, June 21, 2005 12:41 PM<BR><B>To:</B>
oracle-l@(protected); oracle-db-l@(protected);
ORACLE-L@(protected); oracledba@(protected);
oracle-rdbms@(protected)<BR><B>Subject:</B> question regarding automatic
undo<BR><BR></DIV>
<DIV></DIV>
<DIV>Two identical databases.</DIV>
<DIV> </DIV>
<DIV>database 1 has 10 undo online and 2 offline</DIV>
<DIV>database 2 has 10 undo online and 23 offline</DIV>
<DIV> </DIV>
<DIV>Why does the second have more offline undo segments. Does Oracle
automatically create them if needed and when no longer needed offline it??</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>Jeffrey Beckstrom<BR>Database Administrator<BR>Greater Cleveland Regional
Transit Authority<BR>1240 W. 6th Street<BR>Cleveland, Ohio
44113</DIV></DIV></BODY></HTML>
|
|
|