Shulou(Shulou.com)06/01 Report--

Http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_4069.htm

DBA_HIST_UNDOSTAT view

DBA_HIST_UNDOSTAT displays a statistical histogram of the history of Oracle UNDO tablespaces, including UNDO consumption, transaction conformance, and query execution time in the instance, which contains snapshots of V$UNDOSTAT.

ColumnDatatypeNULLDescriptionBEGIN_TIMEDATENOT NULLIdentifies the beginning of the time intervalEND_TIMEDATENOT NULLIdentifies the end of the time intervalDBIDNUMBERNOT NULLDatabase ID for the snapshotINSTANCE_NUMBERNUMBERNOT NULLInstance number for the snapshotSNAP_IDNUMBERNOT NULLUnique snapshot IDUNDOTSNNUMBERNOT NULLRepresents 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.UNDOBLKSNUMBERRepresents 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.TXNCOUNTNUMBERIdentifies the total number of transactions executed within the periodMAXQUERYLENNUMBERIdentifies 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. The length of a query is measured from the cursor open time to the last fetch/execute time of the cursor. Only the length of those cursors that have been fetched/executed during the period are reflected in the view.MAXQUERYSQLIDVARCHAR2 (13) SQL identifier of the longest running SQL statement in the periodMAXCONCURRENCYNUMBERIdentifies the highest number of transactions executed concurrently within the periodUNXPSTEALCNTNUMBERNumber of attempts to obtain undo space by stealing unexpired extents from other transactionsUNXPBLKRELCNTNUMBERNumber of unexpired blocks removed from certain undo segments so they can be used by other transactionsUNXPBLKREUCNTNUMBERNumber of unexpired undo blocks reused by transactionsEXPSTEALCNTNUMBERNumber of attempts to steal expired undo blocks from other undo segmentsEXPBLKRELCNTNUMBERNumber of expired undo blocks stolen from other undo segmentsEXPBLKREUCNTNUMBERNumber of expired undo blocks reused within the same undo segmentsSSOLDERRCNTNUMBERIdentifies the number of times the error ORA-01555 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.NOSPACEERRCNTNUMBERIdentifies 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.ACTIVEBLKSNUMBERTotal number of blocks in the active extents of the undo tablespace for the instance at the sampled time in the periodUNEXPIREDBLKSNUMBERTotal number of blocks in the unexpired extents of the undo tablespace for the instance at the sampled time in the periodEXPIREDBLKSNUMBERTotal number of blocks in the expired extents of the undo tablespace for the instance at the sampled time in the periodTUNED_UNDORETENTIONNUMBERSystem tuned value indicating the period for which undo is being retained

By default, the information in V$UNDOSTAT adds one record every 10 minutes, and V$UNDOSTAT generally retains only 576 recent UNDO records (equivalent to 4 days of UNDO information, 1008 before 10g, equivalent to 7 days of information), and information beyond that time is saved in the DBA_HIST_UNDOSTAT view. This statistics is valid only in UNDO automatic management mode.

SQL > show parameter undo_managementNAME TYPE VALUE-- undo_management string AUTO

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.