CTAS优化FLASHBACK_TRANSACTION_QUERY查询使用

CTAS优化FLASHBACK_TRANSACTION_QUERY查询使用

?

问题：

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

答案：

优化视图，不了解。最后采取了ctas建立临时表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,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进行条件查询是不可取的，性能超低！(提醒自己)