CTAS 优化 FLASHBACK_TRANSACTION_QUERY 查询使用

问题：

FLASHBACK_TRANSACTION_QUERY 使用 where 的查询，执行很慢近 8 分钟（ 00:07:58.76 ）、 consistent gets 近 3 百万（ 3260345 ）。每次查询竟然需要 8 分钟 ，实在不能忍受。

答案：

优化视图，不了解。最后采取了 cats 建立临时表 doudou_test 的方式解决问题。同样的查询仅仅需要 0.12 秒， consistent gets 仅仅 5632 。 从等待时间上优化 4000 倍

总结：

1、   主观矛盾解决不了，可以转换为客户矛盾进行解决！思路要灵活！优化视图会是以后研究的课题。这里暂不深研究了。

2、   ??视图查询是可以使用基表索引的

http://docs.oracle.com/cd/E11882_01/server.112/e25789/schemaob.htm#autoId11 

前后调整性能如下图

FLASHBACK_TRANSACTION_QUERY 视图

create or replace view flashback_transaction_query 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   UNDO_SQL from FLASHBACK_TRANSACTION_QUERY   where table_name='INQUIRYSENDCASE'

  2   AND TO_CHAR(COMMIT_TIMESTAMP,'YYYY/MM/DD HH24:MI:SS')>'2013/1/10 23:59:00';

no rows selected

Elapsed: 00:07:58.76

Execution Plan

----------------------------------------------------------

Plan hash value: 1115820779

------------------------------------------------------------------------------

| Id   | Operation         | Name       | Rows   | Bytes | Cost (%CPU)| Time      |

------------------------------------------------------------------------------

|    0 | SELECT STATEMENT |            |      1 |   2141 |      0    (0)| 00:00:01 |

|*   1 |   FIXED TABLE FULL| X$KTUQQRY |      1 |   2141 |      0    (0)| 00:00:01 |

------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("TABLE_NAME"='INQUIRYSENDCASE' AND

              TO_CHAR(INTERNAL_FUNCTION("COMMIT_TIMESTAMP"),'YYYY/MM/DD

              HH24:MI:SS')>'2013/1/10 23:59:00')

Statistics

----------------------------------------------------------

     607172   recursive calls

          0   db block gets

    3260345   consistent gets

       2958   physical reads

          0   redo size

        325   bytes sent via SQL*Net to client

        457   bytes received via SQL*Net from client

          1   SQL*Net roundtrips to/from client

        379   sorts (memory)

          0   sorts (disk)

          0   rows processed

DOUDOU_TEST 测试表的建立 （ doudou_test 267975 行）       

create table doudou_test as select * from FLASHBACK_TRANSACTION_QUERY

SQL> select   UNDO_SQL from doudou_test   where table_name='INQUIRYSENDCASE'

  2   AND TO_CHAR(COMMIT_TIMESTAMP,'YYYY/MM/DD HH24:MI:SS')>'2013/1/10 23:59:00';

no rows selected

Elapsed: 00:00:00.12

Execution Plan

----------------------------------------------------------

Plan hash value: 1163728840

--------------------------------------------------------------------------------

-

| Id   | Operation          | Name         | Rows   | Bytes | Cost (%CPU)| Time

|

--------------------------------------------------------------------------------

-

|    0 | SELECT STATEMENT   |              |    123 |    257K|   1519    (1)| 00:00:19

|

|*   1 |   TABLE ACCESS FULL| DOUDOU_TEST |    123 |    257K|   1519    (1)| 00:00:19

|

--------------------------------------------------------------------------------

-

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("TABLE_NAME"='INQUIRYSENDCASE' AND

              TO_CHAR(INTERNAL_FUNCTION("COMMIT_TIMESTAMP"),'YYYY/MM/DD

              HH24:MI:SS')>'2013/1/10 23:59:00')

Note

-----

   - dynamic sampling used for this statement

Statistics

----------------------------------------------------------

         88   recursive calls

          0   db block gets

         5632   consistent gets

       5547   physical reads

          0   redo size

        325   bytes sent via SQL*Net to client

        457   bytes received via SQL*Net from client

          1   SQL*Net roundtrips to/from client

          0   sorts (memory)

0          sorts (disk)

寄语：

         本文，仅是个人记录一下应用思路，整体实验可能存在遗漏，竟请各位大师指点。但是对视图 FLASHBACK_TRANSACTION_QUERY进行条件查询是不可取的，性能超低！ ( 提醒自己 )

cats.jpg