You Asked

Hi,

On addition of a single column, performance of a query has drastically impacted (40 secs from 0.0002 secs). Oracle has changed earlier plan and picked plan that takes more time.

Change : new column added to a query in select clause : dsig.
Time taken by old query to fetch data: 0.002 secs (plan used index access by rowid and index range scan)
Time taken by new query to fetch data: 40 secs (plan used HASH join of indexes and fast fill scan of index)

details below:

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

Thanks,
Rohit

and Connor said...

Yeah I think you just got unlucky. Look at the costs:

new plan = 597636
old plan = 597636

so you're into "flip a coin" territory there. You can use a sql plan baseline or a hint to force the old plan into place.

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

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Primary Key lookup should match 1 row

Gary, September 14, 2017 - 6:11 am UTC

The query is doing an equality match from a bind variable to the primary key column of message_exchange, then joining to the transaction parent table.

It should return one row, not the 29M in the rows estimate, and should be near instantaneous. The range scan on the primary key index is a red flag. I suspect a data type mismatch, where the database column is character but with values that 'look' numeric and the bind value is numeric.

A DBMS_XPLAN including the filter predicates should show the data type conversion.

September 19, 2017 - 2:11 am UTC

nicely spotted.

One correction and a few questions

Rohit, September 14, 2017 - 10:39 am UTC

Many Thanks Connor and Gary!!!

Appreciate your quick help.

There is a correction in my earlier post. - apologies for that.

The primary key of message_exchange is a different column : msg_id

The column MQ_MSG_ID has non-unique values.

Can you help us with the following sub-queries to understand the query behaviour further :

1) The index ON MQ_MSG_ID is not unique however even if there are repeated values in MQ_MSG_ID the count of any such repeated value cannot be so high. If the plan is mentioning 29M rows, can we assume that the plan is created for particular repeated value which has max. no. rows. Is our assumption correct?
a. Please note that as per functional behaviour we are expecting only 1 row to be returned.

2) Even if the plan is changed why the query execution time is exponentially higher? To get some 30+ columns from TRANSACTION table, the row in TRANSACTION table is already getting accessed and it is just one more column to be fetched. (dsig)

3) If the statistics are incorrect is there any correct method of collating Table & Index statistics? Currently the statistics are gathered using following method.

SQL> begin
  2     dbms_stats.gather_schema_stats(
  3        ownname          => 'SWNET1',
  4        estimate_percent => dbms_stats.auto_sample_size,
  5        method_opt       => 'for all columns size auto',
  6        cascade          => true,
  7        degree           => 7,
  8        options          => 'GATHER AUTO'
  9     );
10  end;

11 /

4) When the old plan is getting used the data is retrieved in fraction of seconds on the same DB hence how to ensure that old plan is retained even if query is changed?

5) Can you suggest a hint that we can use in our query for this particular situation?

6) How can we force Oracle to use the earlier query plan?

September 19, 2017 - 2:12 am UTC

As I said before, see

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

It lets you extract the "full" plan for the good running (old) query, and lock it into place for the new query.

And it does so WITHOUT needing to change the SQL.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Primary Key lookup should match 1 row

One correction and a few questions

More to Explore

Performance

On addition of a single column, performance of query drastically impacted

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Primary Key lookup should match 1 row

One correction and a few questions

More to Explore

Performance