flashback_transaction_query 查询慢的问题

今天有网友在sina weibo上问我这个问题：
我数据库由于做过大量dml语句，所以flashback_transaction_query 这个视图很大，今天想根据时间查询一个表的记录，
发现加条件之后查询特别慢，不是一般的慢，怎么办啊@君三思 @oracledatabase12c

晚上回到家，花了点时间研究了一下，这里分享出来，同时也算是回复该网友。
首先，我们来看下该试图到底是什么 ？

SQL> col object_name FOR a40 SQL> SET LINES 120 SQL> l 1* SELECT owner,object_name,object_type FROM dba_objects WHERE object_name=UPPER('flashback_transaction_query') SQL> /   OWNER OBJECT_NAME OBJECT_TYPE ---------- ------------------------------ ------------------- SYS FLASHBACK_TRANSACTION_QUERY VIEW PUBLIC FLASHBACK_TRANSACTION_QUERY SYNONYM   SQL> SELECT dbms_metadata.get_ddl('VIEW','FLASHBACK_TRANSACTION_QUERY') FROM dual;   DBMS_METADATA.GET_DDL('VIEW','FLASHBACK_TRANSACTION_QUERY') --------------------------------------------------------------------------------   CREATE OR REPLACE FORCE VIEW "SYS"."FLASHBACK_TRANSACTION_QUERY" ("XID", "STAR T_SCN", "START_TIMESTAMP", "COMMIT_SCN", "COMMIT_TIMESTAMP", "LOGON_USER", "UNDO _CHANGE#", "OPERATION", "TABLE_NAME", "TABLE_OWNER", "ROW_ID", "UNDO_SQL") AS SELECT xid, start_scn, start_timestamp, decode(commit_scn, 0, commit_scn, 281474976710655, NULL, commit_scn) commit_scn, commit_timestamp, logon_user, undo_change#, operation, TABLE_NAME, table_owner, row_id, undo_sql FROM sys.x$ktuqqry   SQL> SQL> SELECT COUNT(1) FROM FLASHBACK_TRANSACTION_QUERY;   COUNT(1) ---------- 45018   SQL> SELECT COUNT(1) FROM sys.x$ktuqqry 2 ;   COUNT(1) ---------- 45018   SQL>

该x$表的表结构如下：

SQL> desc x$ktuqqry Name Null? Type --------------------- -------- ----------------------- ADDR RAW(4) INDX NUMBER INST_ID NUMBER XID RAW(8) START_SCN NUMBER START_TIMESTAMP DATE COMMIT_SCN NUMBER COMMIT_TIMESTAMP DATE LOGON_USER VARCHAR2(30) UNDO_CHANGE# NUMBER OPERATION VARCHAR2(32) TABLE_OWNER VARCHAR2(32) TABLE_NAME VARCHAR2(256) ROW_ID VARCHAR2(19) UNDO_SQL VARCHAR2(4000)

既然是关于闪回方面的技术，那么显然也就是跟undo有关系了，我们来检查下undo信息：

SQL> SHOW parameter undo   NAME TYPE VALUE ------------------------------------ ----------- ------------------- _gc_undo_affinity BOOLEAN FALSE undo_management string AUTO undo_retention INTEGER 900 undo_tablespace string UNDOTBS1 SQL> SELECT owner,segment_name,SEGMENT_ID,FILE_ID,BLOCK_ID,INITIAL_EXTENT,NEXT_EXTENT,MAX_EXTENTS,STATUS FROM dba_rollback_segs;   OWNER SEGMENT_NAME SEGMENT_ID FILE_ID BLOCK_ID INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS STATUS ------ -------------- ---------- ---------- ---------- -------------- ----------- ----------- ---------------- SYS SYSTEM 0 1 9 114688 57344 32765 ONLINE PUBLIC _SYSSMU1$ 1 2 9 131072 65536 32765 ONLINE PUBLIC _SYSSMU2$ 2 2 25 131072 65536 32765 ONLINE PUBLIC _SYSSMU3$ 3 2 41 131072 65536 32765 ONLINE PUBLIC _SYSSMU4$ 4 2 57 131072 65536 32765 ONLINE PUBLIC _SYSSMU5$ 5 2 73 131072 65536 32765 ONLINE PUBLIC _SYSSMU6$ 6 2 89 131072 65536 32765 ONLINE PUBLIC _SYSSMU7$ 7 2 105 131072 65536 32765 ONLINE PUBLIC _SYSSMU8$ 8 2 121 131072 65536 32765 ONLINE PUBLIC _SYSSMU9$ 9 2 137 131072 65536 32765 ONLINE PUBLIC _SYSSMU10$ 10 2 153 131072 65536 32765 ONLINE PUBLIC RBS_001 11 2 1321 131072 65536 32765 OFFLINE   12 ROWS selected. SQL> oradebug setmypid Statement processed. SQL> ALTER system dump undo header 'SYSTEM';   System altered.   SQL> ALTER system dump undo header '_SYSSMU1$';   System altered.   SQL> ALTER system dump undo header '_SYSSMU2$';   System altered.   SQL> ALTER system dump undo header '_SYSSMU3$';   System altered.   SQL> ALTER system dump undo header '_SYSSMU4$';   System altered.   SQL> ALTER system dump undo header '_SYSSMU5$';   System altered.   SQL> ALTER system dump undo header '_SYSSMU6$';   System altered.   SQL> ALTER system dump undo header '_SYSSMU7$';   System altered.   SQL> ALTER system dump undo header '_SYSSMU8$';   System altered.   SQL> ALTER system dump undo header '_SYSSMU9$';   System altered.   SQL> ALTER system dump undo header '_SYSSMU10$';   System altered.   SQL> ALTER system dump undo header 'RBS_001';   System altered.   SQL> oradebug tracefile_name /home/ora10g/admin/roger/udump/roger_ora_15306.trc SQL>   -------trace   [ora10g@killdb udump]$ cat /home/ora10g/admin/roger/udump/roger_ora_15306.trc| grep TRN TRN CTL:: seq: 0x0059 chd: 0x001a ctl: 0x000c inc: 0x00000000 nfb: 0x0001 TRN TBL:: TRN CTL:: seq: 0x041a chd: 0x0025 ctl: 0x000d inc: 0x00000000 nfb: 0x0001 TRN TBL:: TRN CTL:: seq: 0x079e chd: 0x0005 ctl: 0x0018 inc: 0x00000000 nfb: 0x0001 TRN TBL:: TRN CTL:: seq: 0x0324 chd: 0x0015 ctl: 0x0022 inc: 0x00000000 nfb: 0x0002 TRN TBL:: TRN CTL:: seq: 0x04e0 chd: 0x001c ctl: 0x000d inc: 0x00000000 nfb: 0x0003 TRN TBL:: TRN CTL:: seq: 0x03d0 chd: 0x0028 ctl: 0x002e inc: 0x00000000 nfb: 0x0001 TRN TBL:: TRN CTL:: seq: 0x063f chd: 0x001d ctl: 0x0009 inc: 0x00000000 nfb: 0x0001 TRN TBL:: TRN CTL:: seq: 0x06c0 chd: 0x0026 ctl: 0x0008 inc: 0x00000000 nfb: 0x0002 TRN TBL:: TRN CTL:: seq: 0x0472 chd: 0x000e ctl: 0x0011 inc: 0x00000000 nfb: 0x0003 TRN TBL:: TRN CTL:: seq: 0x04f3 chd: 0x002f ctl: 0x0001 inc: 0x00000000 nfb: 0x0002 TRN TBL:: TRN CTL:: seq: 0x040e chd: 0x0024 ctl: 0x0025 inc: 0x00000000 nfb: 0x0001 TRN TBL:: TRN CTL:: seq: 0x0001 chd: 0x0001 ctl: 0x0000 inc: 0x00000000 nfb: 0x0001 TRN TBL::   SQL> SELECT 89+1050+1950+804+1248+976+1599+1728+1138+1267+1038+1 FROM dual;   89+1050+1950+804+1248+976+1599+1728+1138+1267+1038+1 ---------------------------------------------------- 12888

通过dump 回滚段头我们可以统计出来，该undo datafile目前涉及到的事务一共有12888个，涉及到的记录数肯定也就是
我们count整个表的记录数了。

SQL> SELECT MAX(xid) FROM x$ktuqqry;   MAX(XID) ---------------- 0B00610003000000   SQL> SET LINES 160 SQL> SET pagesize 100 SQL> SET autot traceonly EXP SQL> SELECT * FROM x$ktuqqry WHERE xid='0B00610003000000';   Execution Plan ---------------------------------------------------------- Plan hash VALUE: 1115820779   ------------------------------------------------------------------------------ | Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 2289 | 0 (0)| 00:00:01 | |* 1 | FIXED TABLE FULL| X$KTUQQRY | 1 | 2289 | 0 (0)| 00:00:01 | ------------------------------------------------------------------------------   Predicate Information (IDENTIFIED BY operation id): ---------------------------------------------------   1 - FILTER(RAWTOHEX("XID")='0B00610003000000')   SQL>   我们可以看到是进行的全表扫描。   通过查看11.2的环境，发现也是一样，如下： SQL> SELECT * FROM v$version WHERE rownum < 2;   BANNER -------------------------------------------------------------------------------- Oracle DATABASE 11g Enterprise Edition Release 11.2.0.2.0 - Production   SQL> SQL> SET LINES 200 SQL> SET pagesize 10 SQL> SET pagesize 100 SQL> SELECT MAX(xid) FROM x$ktuqqry;   MAX(XID) ---------------- 1400210042060000   SQL> SET autot traceonly EXP SQL> SELECT * FROM x$ktuqqry WHERE xid='1400210042060000';   Execution Plan ---------------------------------------------------------- Plan hash VALUE: 1115820779   ------------------------------------------------------------------------------ | Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 2289 | 0 (0)| 00:00:01 | |* 1 | FIXED TABLE FULL| X$KTUQQRY | 1 | 2289 | 0 (0)| 00:00:01 | ------------------------------------------------------------------------------   Predicate Information (IDENTIFIED BY operation id): ---------------------------------------------------   1 - FILTER(RAWTOHEX("XID")='1400210042060000')   SQL> DESC X$KTUQQRY Name NULL? TYPE ------------------------ -------- ------------------------------ ADDR RAW(4) INDX NUMBER INST_ID NUMBER XID RAW(8) START_SCN NUMBER START_TIMESTAMP DATE COMMIT_SCN NUMBER COMMIT_TIMESTAMP DATE LOGON_USER VARCHAR2(30) UNDO_CHANGE# NUMBER OPERATION VARCHAR2(32) TABLE_OWNER VARCHAR2(32) TABLE_NAME VARCHAR2(256) ROW_ID VARCHAR2(19) UNDO_SQL VARCHAR2(4000)   SQL> SET autot off SQL> SELECT COUNT(1) FROM X$KTUQQRY;   COUNT(1) ---------- 41425   SQL> SQL>

可以看到，该x$试图内容较大，在新版本中中这个问题仍然存在，如果你的数据库比较繁忙，那么这个试图的记录数可能是几十万甚至上百万，
那样的话，你查询就会感觉非常的慢。但是oracle这里并不允许去创建相关的index，oracle本身也没有这样设计，不知道为什么。

通过前面的测试，我们可以看到调整undo_retentions可以适当的降低记录数，不过影响不大。既然我们知道该试图的记录都来源于undo datafile。
那么我们可以通过切换undo tablespace 来降低记录数。不过随着时间的推移，这个x$的记录仍然会越来越大，这个无法避免。如下：

SQL> CREATE undo tablespace undotbs2 datafile '/home/ora10g/oradata/roger/undotbs2_01.dbf' SIZE 20m;   Tablespace created.   SQL> ALTER system SET undo_tablespace=undotbs2;   System altered.   SQL> ALTER system SET undo_retention=900;   System altered.   SQL> SELECT COUNT(1) FROM sys.x$ktuqqry;   COUNT(1) ---------- 42268   SQL> DROP tablespace undotbs1 including contents AND datafiles;   Tablespace dropped.   SQL> SELECT COUNT(1) FROM sys.x$ktuqqry;   COUNT(1) ---------- 26   SQL> l 1* SELECT COUNT(1) FROM sys.x$ktuqqry SQL> /   COUNT(1) ---------- 33     通过收集x$表的统计信息，可以发现如下信息：   SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','X$KTUQQRY');   PL/SQL PROCEDURE successfully completed. SQL> SELECT COUNT(1) FROM X$KTUQQRY;   COUNT(1) ---------- 43651   SQL> SELECT owner,TABLE_NAME,COLUMN_NAME,NUM_DISTINCT,DENSITY,LAST_ANALYZED FROM dba_tab_col_statistics WHERE TABLE_NAME='X$KTUQQRY';   OWNER TABLE_NAME COLUMN_NAME NUM_DISTINCT DENSITY LAST_ANAL ------- --------------- -------------------- ------------ ---------- --------- SYS X$KTUQQRY ADDR 1 1 20-NOV-12 SYS X$KTUQQRY INDX 43421 .00002303 20-NOV-12 SYS X$KTUQQRY INST_ID 1 1 20-NOV-12 SYS X$KTUQQRY XID 1574 .001582278 20-NOV-12 SYS X$KTUQQRY START_SCN 1504 .000664894 20-NOV-12 SYS X$KTUQQRY START_TIMESTAMP 187 .005347594 20-NOV-12 SYS X$KTUQQRY COMMIT_SCN 1574 .000635324 20-NOV-12 SYS X$KTUQQRY COMMIT_TIMESTAMP 199 .005025126 20-NOV-12 SYS X$KTUQQRY LOGON_USER 1 1 20-NOV-12 SYS X$KTUQQRY UNDO_CHANGE# 11901 .000084027 20-NOV-12 SYS X$KTUQQRY OPERATION 2 .5 20-NOV-12 SYS X$KTUQQRY TABLE_OWNER 0 0 20-NOV-12 SYS X$KTUQQRY TABLE_NAME 126 .007936508 20-NOV-12 SYS X$KTUQQRY ROW_ID 0 0 20-NOV-12 SYS X$KTUQQRY UNDO_SQL 0 0 20-NOV-12   15 ROWS selected.

我们可以看到，我们使用闪回查询常用的几个字段的选择性可以说都很低，这或许就是为什么oracle不给相应的字段添加index的原因之一。
另外一种原因我猜测可能是：通常来讲，一个事务可能涉及到很多记录数，这样就比如导致xid的选择性很低，那么再去创建index 意义
也就不大了。其实我在想，如果有index的话，或许会走index fast full scan,起码也要比全表扫描要快的多。

那么是不是就没有办法了呢？ 突然我想到可以利用物化视图俩满足该网友的需求，如下：

SQL> SET timing ON SQL> ALTER system FLUSH BUFFER_CACHE;   System altered.   Elapsed: 00:00:00.02 SQL> ALTER system FLUSH SHARED_POOL;   System altered.   Elapsed: 00:00:00.01 SQL> SET autot traceonly SQL> SELECT * FROM FLASHBACK_TRANSACTION_QUERY WHERE xid='1400210042060000';   no ROWS selected   Elapsed: 00:00:04.66   Execution Plan ---------------------------------------------------------- Plan hash VALUE: 1115820779   ------------------------------------------------------------------------------ | Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 28 | 2464 | 7 (100)| 00:00:01 | |* 1 | FIXED TABLE FULL| X$KTUQQRY | 28 | 2464 | 7 (100)| 00:00:01 | ------------------------------------------------------------------------------   Predicate Information (IDENTIFIED BY operation id): ---------------------------------------------------   1 - FILTER(RAWTOHEX("XID")='1400210042060000')     Statistics ---------------------------------------------------------- 7458 recursive calls 20 db block gets 82646 consistent gets 1885 physical reads 0 redo SIZE 993 bytes sent via SQL*Net TO client 408 bytes received via SQL*Net FROM client 1 SQL*Net roundtrips TO/FROM client 338 sorts (memory) 0 sorts (disk) 0 ROWS processed   SQL> CREATE materialized VIEW flash_query_test AS SELECT * FROM FLASHBACK_TRANSACTION_QUERY;   Materialized VIEW created.   Elapsed: 00:00:02.33 SQL> CREATE INDEX xid_idx ON flash_query_test(xid);   INDEX created.   Elapsed: 00:00:00.37 SQL> analyze INDEX xid_idx compute statistics;   INDEX analyzed.   Elapsed: 00:00:00.42 SQL> SET autot traceonly SQL> SELECT * FROM flash_query_test WHERE xid='1400210042060000';   no ROWS selected   Elapsed: 00:00:00.23   Execution Plan ---------------------------------------------------------- Plan hash VALUE: 962280044   ----------------------------------------------------------------------------------------- | Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 9036 | 99 (2)| 00:00:01 | |* 1 | MAT_VIEW ACCESS FULL| FLASH_QUERY_TEST | 4 | 9036 | 99 (2)| 00:00:01 | -----------------------------------------------------------------------------------------   Predicate Information (IDENTIFIED BY operation id): ---------------------------------------------------   1 - FILTER(RAWTOHEX("XID")='1400210042060000')   Note ----- - dynamic sampling used FOR this statement (level=2)     Statistics ---------------------------------------------------------- 9 recursive calls 0 db block gets 428 consistent gets 357 physical reads 0 redo SIZE 993 bytes sent via SQL*Net TO client 408 bytes received via SQL*Net FROM client 1 SQL*Net roundtrips TO/FROM client 0 sorts (memory) 0 sorts (disk) 0 ROWS processed

显然这样要快的多了。基本上解决了该网友的问题。 不过这里还是有个小问题，就是物化视图同步的问题，因为这里基表实际上
是一个试图，所以也就没法取创建物化视图日志，对x$又不允许创建物化视图。不过，大不了我们在使用闪回查询之前，手工刷新同步
一下该物化试图即可，如下：
SQL> exec dbms_mview.refresh(‘FLASH_QUERY_TEST’,'Complete’);

PL/SQL procedure successfully completed.