Hello,
DB version 11.2.0.4
We have a Update query which running long today and we have an execution plan change caused the query to be running long
Query
UPDATE example
SET day_trd_prft_loss_am = :1,
tot_trd_cmsn_am = :2,
optn_trd_cmsn_am = :3,
WHERE acct_intrl_id = :160
AND mnth_smry_start_dt = :161
Execution PLAN history:
BEGINTIME INSTANCE_NUMBER PLANHASH EXECDELTA ROWSDELTA PARSECALLSDELTA BUFFERGETSDELTA DISKREADSDELTA IOWAITDELTA CPUTIMEDELTA ELAPSEDEXECDELTA ELAPSEDEXECTOTAL
----------- --------------- ----------- ------------ ------------ --------------- --------------- -------------- ------------------ ------------ ---------------- ----------------
02-21 04:30 2 742615603 107,915 1 .000064866 7 0 0 0 .00034395 .000395849
02-21 04:30 1 742615603 46,145 1 .000065012 7 0 0 0 .000918229 .000918229
02-22 03:30 2 742615603 6,966 1 .000717772 13 1 0 0 .002175608 .002175608
02-27 03:30 2 742615603 19,581 1 .00025535 13 0 0 0 .001211885 .001277855
03-02 04:00 2 742615603 0 0 0 0 0 0 0 0 .00093822
03-02 04:00 2 1473327666 630 1 .007936508 5,895 2 0 0 .052997125 .052997125
03-03 07:00 1 742615603 20,118 1 .000347947 11 1 0 0 .001577413 .001427644
03-03 07:00 2 742615603 0 0 0 0 0 0 0 0 .00093822
03-03 07:00 2 1473327666 1,563 1 .001919386 288,482 178 0 1 1.6364055 1.18152758
03-03 07:30 2 1473327666 3,651 1 0 287,795 0 0 1 1.47220188 1.36312441
03-03 07:30 2 742615603 0 0 0 0 0 0 0 0 .00093822
03-03 08:00 2 1473327666 3,400 1 0 306,224 1 0 2 1.53824033 1.42753312
03-03 08:00 2 742615603 0 0 0 0 0 0 0 0 .00093822
Execution Plans :
GOOD PLAN
Plan hash value: 742615603
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 1 (100)| | | |
| 1 | UPDATE | ACCT_SMRY_MNTH | | | | | | |
| 2 | PARTITION RANGE SINGLE| | 1 | 434 | 1 (0)| 00:00:01 | KEY | KEY |
| 3 | INDEX UNIQUE SCAN | PK_ACCT_SMRY_MNTH | 1 | 434 | 1 (0)| 00:00:01 | KEY | KEY |
--------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - UPD$1
3 - UPD$1 / ACCT_SMRY_MNTH@UPD$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('query_rewrite_enabled' 'false')
OPT_PARAM('_optimizer_distinct_agg_transform' 'false')
OPT_PARAM('optimizer_index_cost_adj' 30)
OPT_PARAM('optimizer_dynamic_sampling' 6)
OUTLINE_LEAF(@"UPD$1")
INDEX(@"UPD$1" "ACCT_SMRY_MNTH"@"UPD$1" ("ACCT_SMRY_MNTH"."ACCT_INTRL_ID"
"ACCT_SMRY_MNTH"."MNTH_SMRY_START_DT"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
160 - :160 (VARCHAR2(30), CSID=873): '38356324'
161 - :161 (DATE): 10/01/2014 00:00:00
BAD Plan:
Plan hash value: 1473327666
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 1 (100)| | | |
| 1 | UPDATE | ACCT_SMRY_MNTH | | | | | | |
| 2 | PARTITION RANGE SINGLE | | 1 | 2098 | 1 (0)| 00:00:01 | KEY | KEY |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| ACCT_SMRY_MNTH | 1 | 2098 | 1 (0)| 00:00:01 | KEY | KEY |
| 4 | INDEX RANGE SCAN | ACCT_SM_ST_DT_IDX | 1 | | 1 (0)| 00:00:01 | KEY | KEY |
--------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - UPD$1
3 - UPD$1 / ACCT_SMRY_MNTH@UPD$1
4 - UPD$1 / ACCT_SMRY_MNTH@UPD$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('query_rewrite_enabled' 'false')
OPT_PARAM('_optimizer_distinct_agg_transform' 'false')
OPT_PARAM('optimizer_index_cost_adj' 30)
OPT_PARAM('optimizer_dynamic_sampling' 6)
OUTLINE_LEAF(@"UPD$1")
INDEX_RS_ASC(@"UPD$1" "ACCT_SMRY_MNTH"@"UPD$1" ("ACCT_SMRY_MNTH"."MNTH_SMRY_START_DT"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
160 - :160 (VARCHAR2(30), CSID=873): '67511602'
161 - :161 (DATE): 12/01/2014 00:00:00
As you can see above oracle has changed the plan to Index range scan from Index Unique Scan.