Oracle 我怎么知道正在运行的查询正在读取UNDO

嗨，大师们，

我们当前的生产查询运行不稳定。通常，它会运行10分钟，但有时会运行几个小时。有时会抛出 “快照太旧” 错误。当错误弹出时，我们知道它正在读取undo。该系统非常古老。每天运行数千个流程。在解决性能问题时，我想知道慢速查询是否正在读取UNDO。这些信息是否有可用的动态视图？

查看会话级别的 “撤消应用记录” 系列统计信息。下面是一个查询的示例，该查询需要在删除数据之前查看数据。

SQL>
SQL> create table t as select * from dba_objects;

Table created.

SQL> create index ix on t ( object_id );

Index created.

SQL>
SQL> variable rc refcursor
SQL> exec open :rc for select /*+ index(t) */ * from t where object_id > 0;

PL/SQL procedure successfully completed.

SQL> delete from t;

82260 rows deleted.

SQL> commit;

Commit complete.

SQL> @mystat
Enter value for statname: undo records applied

NAME                                                              VALUE
------------------------------------------------------------ ----------
transaction tables consistent reads - undo records applied            0
data blocks consistent reads - undo records applied                   0
rollback changes - undo records applied                               0
IM populate undo records applied                                      0
IM repopulate undo records applied                                    0
IM scan CUs undo records applied                                      0

6 rows selected.

SQL> set feedback only
SQL> print rc

82256 rows selected.

SQL> set feedback on
SQL> @mystat
Enter value for statname: undo records applied

NAME                                                              VALUE
------------------------------------------------------------ ----------
transaction tables consistent reads - undo records applied            0
data blocks consistent reads - undo records applied               82256
rollback changes - undo records applied                               0
IM populate undo records applied                                      0
IM repopulate undo records applied                                    0
IM scan CUs undo records applied                                      0

6 rows selected.

SQL>
SQL>
SQL>

但实际上，您可能只需要撤消查询中的单个记录...如果该更改是3小时前的 (因为您的查询运行了3小时)，那么您仍然可以遇到相同的问题。

不是您找到了多少 * 撤消... 而是它的年龄。