CTAS优化FLASHBACK_TRANSACTION_QUERY查询使用

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26442936/viewspace-752511/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26442936/viewspace-752511/