flashback_transaction_query 查询慢的问题

发布时间：2020-12-15 07:04:54 所属栏目：百科来源：网络整理

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

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

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

SQL> col object_name FOR a40
SQL> SET LINES 120
SQL> l
  1* SELECT owner,object_nameFROM 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
?
SQLSELECT dbms_metadata.get_ddl'VIEW''FLASHBACK_TRANSACTION_QUERY') FROM dual;
?
DBMS_METADATA.GET_DDL)
--------------------------------------------------------------------------------
?
  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_timestamp(commit_scn0281474976710655NULL)
          commit_scn
          logon_user operationTABLE_NAME
          row_idFROM sys.x$ktuqqry
?
SQL> 
SQLSELECT COUNT(FROM FLASHBACK_TRANSACTION_QUERY;
?
  ----------
     45018
?
SQL.x$ktuqqry
  2  ;
?
  >

该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信息：

SHOW parameter undo ? NAME TYPE VALUE ------------------------------------ ----------- ------------------- _gc_undo_affinity BOOLEAN FALSE undo_management string AUTO undo_retention INTEGER 900 undo_tablespace string UNDOTBS1 SQLSEGMENT_IDBLOCK_IDNEXT_EXTENTSTATUS 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$ 2 131072 65536 32765 ONLINE PUBLIC _SYSSMU2$ 2 25 32765 ONLINE PUBLIC _SYSSMU3$ 3 41 32765 ONLINE PUBLIC _SYSSMU4$ 4 57 32765 ONLINE PUBLIC _SYSSMU5$ 5 73 32765 ONLINE PUBLIC _SYSSMU6$ 6 89 32765 ONLINE PUBLIC _SYSSMU7$ 7 2 105 32765 ONLINE PUBLIC _SYSSMU8$ 8 121 32765 ONLINE PUBLIC _SYSSMU9$ 9 137 32765 ONLINE PUBLIC _SYSSMU10$ 10 153 32765 ONLINE PUBLIC RBS_001 11 2 1321 32765 OFFLINE ? 12 ROWS selected. SQL> oradebug setmypid Statement processedALTER system dump undo header 'SYSTEM'; ? System altered. ? SQL'_SYSSMU1$'; ? System altered'_SYSSMU2$'; ? System altered'_SYSSMU3$'; ? System altered'_SYSSMU4$'; ? System altered'_SYSSMU5$'; ? System altered'_SYSSMU6$'; ? System altered'_SYSSMU7$'; ? System altered'_SYSSMU8$'; ? System altered'_SYSSMU9$'; ? System altered'_SYSSMU10$'; ? System altered'RBS_001'; ? System altered> oradebug tracefile_name /home/ora10g/admin/roger/udump/roger_ora_15306.trc SQL> ? -------trace ? [ora10g@killdb udump]$ cat .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:: ? SQLSELECT 89+105019508041248976159917281138126710381 FROM dual; ? 1 ---------------------------------------------------- 12888

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

MAX(xidFROM x$ktuqqry; ? (XID---------------- 0B00610003000000 ? SQL160 SQLSET pagesize 100 SQLSET autot traceonly EXP SQLSELECT 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 | 00:00:01 | |* | FIXED TABLE FULL| X$KTUQQRY ------------------------------------------------------------------------------ ? Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- ? - FILTER(RAWTOHEX'0B00610003000000') ? SQL> ? 我们可以看到是进行的全表扫描。 ? 通过查看11.2的环境，发现也是一样，如下： SQLFROM v$version WHERE rownum < 2; ? BANNER -------------------------------------------------------------------------------- Oracle DATABASE 11g Enterprise Edition Release 11.2.0.0 - Production ? SQL200 SQL10 SQL---------------- 1400210042060000 ? SQL'1400210042060000'; ? Execution Plan '1400210042060000'DESC X$KTUQQRY Name NULL? TYPE ------------------------ -------- ------------------------------ ADDR RAW4) INDX NUMBER INST_ID NUMBER XID RAW8) START_SCN NUMBER START_TIMESTAMP DATE COMMIT_SCN NUMBER COMMIT_TIMESTAMP DATE LOGON_USER VARCHAR230) UNDO_CHANGE# NUMBER OPERATION VARCHAR232) TABLE_OWNER VARCHAR2) TABLE_NAME VARCHAR2256) ROW_ID VARCHAR219) UNDO_SQL VARCHAR24000SET autot off SQLFROM X$KTUQQRY; ? 41425 ? SQL 可以看到，该x$试图内容较大，在新版本中中这个问题仍然存在，如果你的数据库比较繁忙，那么这个试图的记录数可能是几十万甚至上百万，
那样的话，你查询就会感觉非常的慢。但是oracle这里并不允许去创建相关的index，oracle本身也没有这样设计，不知道为什么。

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

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

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

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

SET timing ON SQLFLUSH BUFFER_CACHE; ? System altered. ? Elapsed: 00:00:00.02 SQLFLUSH SHARED_POOL; ? System altered00.01 SQLSET autot traceonly SQLFROM FLASHBACK_TRANSACTION_QUERY '1400210042060000'; ? no ROWS selected ? Elapsed: 00:00:04.66 ? Execution Plan | 28 2464 7 100) ? ? 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 FROM client 1 *Net roundtrips TOFROM client 338 sorts (memory) 0 sorts (disk0 ROWS processed ? SQLCREATE materialized VIEW flash_query_test AS FROM FLASHBACK_TRANSACTION_QUERY; ? Materialized VIEW created02.33 SQLINDEX xid_idx ON flash_query_test); ? INDEX created00.37 SQL> analyze INDEX xid_idx compute statistics; ? INDEX analyzed00.42 SQLSET autot traceonly SQLFROM flash_query_test 00.23 ? Execution Plan 962280044 ? ----------------------------------------------------------------------------------------- | Operation | Name SELECT STATEMENT | 4 9036 99 2| MAT_VIEW ACCESS | FLASH_QUERY_TEST ----------------------------------------------------------------------------------------- ? Predicate Information ) ? Note ----- - dynamic sampling used FOR this statement (level---------------------------------------------------------- 9 recursive calls 0 db block gets 428 consistent gets 357 physical reads ROWS processed

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

PL/SQL procedure successfully completed.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!