今天有网友在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.
Elapsed: 00:00:16.62
发表回复