flashback_transaction_query 查询慢的问题
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
今天有网友在sina weibo上问我这个问题：
我数据库由于做过大量dml语句，所以flashback_transaction_query 这个视图很大，今天想根据时间查询一个表的记录，
发现加条件之后查询特别慢，不是一般的慢，怎么办啊@君三思 @oracledatabase12c
晚上回到家，花了点时间研究了一下，这里分享出来，同时也算是回复该网友。
首先，我们来看下该试图到底是什么 ？
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
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$表的表结构如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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信息：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 |
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整个表的记录数了。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 |
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$的记录仍然会越来越大，这个无法避免。如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
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,起码也要比全表扫描要快的多。
那么是不是就没有办法了呢？ 突然我想到可以利用物化视图俩满足该网友的需求，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 |
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
4 Responses to “flashback_transaction_query 查询慢的问题”
roger 又有大作了啊
牛牛帅帅狂威
complete刷新物化视图，不是和执行一次查询效率差不多，或者更慢了么–还要往表里写数据。
[…] 前几天某大师提醒我说了，我以前一篇文档flashback_transaction_query 查询慢的问题 有点问题。 现在回想一下，确实不太对，虽然以前的方法也是一种处理方法然而有点偏离方向了。 首先我们来看一下试图的定义： […]
Leave a Reply
You must be logged in to post a comment.