sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle flashback_transaction_query查询较慢的解决思路

时间:2016-12-19 09:47   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心案例分析:运维DBA反映数据库由于做过大量dml语句,所以flashback_transaction_query 这个视图很大,想根据时间查询一个表的记录,发现加条件之后查询特别慢。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 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> SELECT COUNT(1) FROM FLASHBACK_TRANSACTION_QUERY;

  COUNT(1)
----------
     45018

SQL> SELECT COUNT(1) FROM sys.x$ktuqqry;

  COUNT(1)
----------
     45018


该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

-------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')

我们可以看到是进行的全表扫描。

通过查看11.2的环境,发现也是一样,如下:
SQL> SELECT * FROM v$version WHERE rownum < 2;

BANNER
--------------------------------------------------------------------------------
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.2.0 - Production

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

可以看到,该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     |
-------------------------http://www.oracleplus.net-----------------------------------------------------
|   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

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle flashback_transaction_query查询较慢的解决思路

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1412.html

Oracle研究中心

关键词:

flashback_transaction_query

Oracle闪回查询较慢的解决办法