ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 105710807812443F8798F21BC3AA2410Y select gu.inst_id, xxen_util.client_time(gu.begin_time) begin_time, xxen_util.client_time(gu.end_time) end_time, gu.undo_tablespace, gu.undoblks*gu.block_size/1000000 undo_generated, gu.undoblks*gu.block_size/1000000/xxen_util.zero_to_null((gu.end_time-gu.begin_time)*86400) undo_per_second, gu.txncount transactions, gu.maxconcurrency max_concurrent_transactions, xxen_util.time(gu.maxquerylen) longest_query, gu.maxquerylen longest_query_seconds, gu.maxqueryid longest_query_sql_id, xxen_util.time(gu.tuned_undoretention) tuned_undo_retention, gu.tuned_undoretention tuned_undo_retention_seconds, gu.undo_retention undo_retention_parameter, gu.activeblks*gu.block_size/1000000 active_undo, gu.unexpiredblks*gu.block_size/1000000 unexpired_undo, gu.expiredblks*gu.block_size/1000000 expired_undo, xxen_util.zero_to_null(gu.ssolderrcnt) ora_01555_errors, xxen_util.zero_to_null(gu.nospaceerrcnt) out_of_space_errors, xxen_util.zero_to_null(gu.unxpstealcnt) unexpired_steal_attempts, xxen_util.zero_to_null(gu.unxpblkrelcnt) unexpired_blocks_released, xxen_util.zero_to_null(gu.unxpblkreucnt) unexpired_blocks_reused, xxen_util.zero_to_null(gu.expstealcnt) expired_steal_attempts from ( select gu.*, (select gt.name from gv$tablespace gt where gu.inst_id=gt.inst_id and gu.undotsn=gt.ts#) undo_tablespace, (select dt.block_size from gv$tablespace gt, dba_tablespaces dt where gu.inst_id=gt.inst_id and gu.undotsn=gt.ts# and gt.name=dt.tablespace_name) block_size, (select to_number(gp.value) from gv$parameter gp where gu.inst_id=gp.inst_id and gp.name='undo_retention') undo_retention from gv$undostat gu where 1=1 ) gu order by gu.begin_time desc, gu.inst_id US DBA Undo Statistics Undo generation, longest running query and undo errors per 10-minute interval from gv$undostat, which keeps about four days of history. ORA-01555 snapshot too old occurs when a query needs undo that has already been overwritten. Compare the Longest Query Seconds with the Tuned Undo Retention Seconds: unexpired steal attempts and blocks show undo that was overwritten before its retention expired because the undo tablespace was too small. Enginatics 1=1 1 10 1=1 (gu.ssolderrcnt>0 or gu.nospaceerrcnt>0) LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Errors only Show only intervals with ORA-01555 or out of space errors 2 20 1=1 gu.end_time>=sysdate-:hours_back/24 Number US Hours Back Show only the intervals of the last number of hours, for example the time since the previous scheduled run 3 30 1=1 gu.inst_id=:inst_id LOV DBA Instance Id 8E2FF36EDF1279D2E0530100007F1FF2 Y select gi.inst_id value, gi.instance_name||' '||gi.host_name||' '||gi.startup_time description from gv$instance gi US Instance Id