Oracle 添加单个列后，查询的性能会大大影响

嗨，

在添加单个列时，查询的性能受到了极大的影响 (从0.0002秒到40秒)。Oracle已经改变了早期的计划，并选择了需要更多时间的计划。


更改: 在select子句中添加到查询的新列: dsig。
旧查询获取数据的时间: 0.002秒 (通过rowid和索引范围扫描使用的计划索引访问)
新查询获取数据所需的时间: 40秒 (计划使用索引的哈希连接和索引的快速填充扫描)

详细信息如下:

 
message_exchange and transaction are heavy tables.
 
MESSAGE_EXCHANGE :rows: 56407885
TRANSACTION : rows: 9509739
 
CONSTRAINT "TRANSACTION_ID_FK" FOREIGN KEY ("ID")  REFERENCES  TRANSACTION" ("ID")
PRIMARY KEY of TRANSACTION is ID.
PRIMARY KEY of MESSAGE_EXCHANGE is MQ_MSG_ID.
 
TRANSACTION table has indexes on following columns:
id, as_id, source_ep, destination_ep, creation_timestamp, source_ref_id, destination_ref_id, requestor_dn, responder_dn, lasmodify_timestamp, composite index on status and swift_error, logical_filename
MESSAGE_EXCHANGE table has indexes on following columns:
mq_msg_id and id
 
New query
 
SELECT  txn.id                   ,
        txn.as_id                ,
        txn.source_ep            ,
        txn.destination_ep       ,
        txn.creation_timestamp   ,
        txn.traffic_type         ,
        txn.source_ref_id        ,
        txn.destination_ref_id   ,
        txn.requestor_dn         ,
        txn.responder_dn         ,
        txn.service              ,
        txn.status               ,
        txn.lasmodify_timestamp,
        txn.wf_id                ,
        txn.curr_wf_stage_id     ,
        txn.source_cl_id         ,
        txn.destination_cl_id    ,
        txn.e2e_msg_id           ,
        txn.mode                 ,
        txn.ack_indicator        ,
        txn.deliv_notif          ,
        txn.deliv_responder_dn   ,
        txn.payload_size         ,
        txn.currenattempt      ,
        txn.snf_delivery_time    ,
        txn.instance_id          ,
        txn.exporstatus        ,
        txn.swiferror          ,
        txn.non_repudiation      ,
        txn.logical_filename     ,
        txn.fileinfo             ,
        txn.filedesc             ,
        txn.requestype         ,
        txn.file_digesalgo     ,
        txn.seq_id               ,
        txn.physical_filename    ,
        txn.snfinpuseq         ,
        txn.swfcomp_algo       ,
        txn.dsig
FROM    message_exchange me,
        transaction txn
WHERE   me.mq_msg_id = :1
        AND me.id  = txn.id
 
new plan
 
################################################################
 
 
| Id  | Operation     | Name     | Rows  | Bytes | Cost |
 
 
-------------------------------------------------------------------------------------------
 
 
|   0 | SELECT STATEMENT     |     | 29M | 1.4066E+10| 597636|
|   1 |  NESTED LOOPS     |     | 29M | 1.4066E+10| 597636|
|*  2 |   VIEW     | index$_join$_001      | 29M | 214M| 244037|
|*  3 |    HASH JOIN     |     |     |     |  |
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  4 |     INDEX RANGE SCAN     | IX_MQ_MSG_ID | 29M | 214M|   3404|
|   5 |     INDEX FAST FULL SCAN     | IX_ME_TXN_ID     | 29M | 214M| 4718  |
|   6 |   TABLE ACCESS BY INDEX ROWID| IS_TB_TRANSACTION     |   1 | 400 |   1   |
|*  7 |    INDEX UNIQUE SCAN     | PK_TXN_ID     |     1 |     |   1   |
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 
 
 
old query
 
 
SELECT  txn.id                   ,
        txn.as_id                ,
        txn.source_ep            ,
        txn.destination_ep       ,
        txn.creation_timestamp   ,
        txn.traffic_type         ,
        txn.source_ref_id        ,
        txn.destination_ref_id   ,
        txn.requestor_dn         ,
        txn.responder_dn         ,
        txn.service              ,
        txn.status               ,
        txn.lasmodify_timestamp,
        txn.wf_id                ,
        txn.curr_wf_stage_id     ,
        txn.source_cl_id         ,
        txn.destination_cl_id    ,
        txn.e2e_msg_id           ,
        txn.mode                 ,
        txn.ack_indicator        ,
        txn.deliv_notif          ,
        txn.deliv_responder_dn   ,
        txn.payload_size         ,
        txn.currenattempt      ,
        txn.snf_delivery_time    ,
        txn.instance_id          ,
        txn.exporstatus        ,
        txn.swiferror          ,
        txn.non_repudiation      ,
        txn.logical_filename     ,
        txn.fileinfo             ,
        txn.filedesc             ,
        txn.requestype         ,
        txn.file_digesalgo     ,
        txn.seq_id               ,
        txn.physical_filename    ,
        txn.snfinpuseq         ,
        txn.swfcomp_algo      
FROM    message_exchange me,
        transaction txn
WHERE   me.mq_msg_id = :1
        AND me.id  = txn.id
 
 
old plan
 
################################################################
 
 
| Id  | Operation     | Name     | Rows  | Bytes | Cost |
 
 
-------------------------------------------------------------------------------------------
 
 
|   0 | SELECT STATEMENT     |     | 29M | 1.4066E+10| 597636|
|   1 |  NESTED LOOPS     |     | 29M | 1.4066E+10| 597636|
|*  2 |   NESTED LOOPS |     | 29M | 214M  | 597636|
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  3 |   TABLE ACCESS BY INDEX ROWID| IS_TB_MESSAGE_EXCHANGE | 29M | 214M|   289037|
|   4 |     INDEX RANGE SCAN     | IX_ME_TXN_ID     | 29M | 214M| 2993  |
|   5 |   TABLE ACCESS BY INDEX ROWID| IS_TB_TRANSACTION     |   1 | 400 |   1   |
|*  6 |    INDEX UNIQUE SCAN     | PK_TXN_ID     |     1 |     |   1   |
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
复制

谢谢，
罗希特

是的，我想你只是运气不好。看看成本:

新计划 = 597636
旧计划 = 597636

所以你在那里进入了 “掷硬币” 的领域。您可以使用sql计划基线或提示来强制执行旧计划。

http://www.oracle.com/technetwork/issue-archive/2014/14-jul/o44asktom-2196080.html