Hi,
Greetings!
1. First time, Executing the query within the same session, it completes quick with good plan.
2. Second time, Executing the same query within the same session, immediately, it hangs and picks up different plan.
a) Could you suggest what could be the reason for this sudden change in plan.
There is no data changes in the underlying table partition between both the runs.
b) Can this query be tuned any further ?
PRCING_DATE is the partition key column in both the tables.
SQL> WITH LAST_TICKS_BY_DAY AS (
SELECT /*+ gather_plan_statistics */ MAX(LAST_PRICE_TIME) AS LAST_PRICE_TIME, MAX(PRICING_DATE) AS PRICING_DATE
FROM TEST.FFINC_PRC A
WHERE A.INSTRUMENT_ID = 9730862
AND A.PRICING_DATE BETWEEN TO_DATE('2020-07-28', 'YYYY-MM-DD') AND TO_DATE('2020-07-29', 'YYYY-MM-DD')
AND LAST_PRICE_TIME >= TO_TIMESTAMP('28-JUL-20 11.59.59.000000000 PM','DD-MON-YY HH.MI.SS.FF PM')
AND LAST_PRICE_TIME <= TO_TIMESTAMP('29-JUL-20 11.59.59.000000000 PM','DD-MON-YY HH.MI.SS.FF PM')
AND A.PRICE_TYPE_ID in(5 , 6 )
GROUP BY TRUNC(LAST_PRICE_TIME)
)
SELECT /*+ gather_plan_statistics */ p.INSTRUMENT_ID, p.SOURCE, p.BID_PRICE, p.LAST_PRICE_TIME, p.ASK_PRICE,
p.MID_PRICE, p.BID_SPREAD, p.ASK_SPREAD, p.MID_SPREAD, p.BID_YIELD, p.ASK_YIELD, p.MID_YIELD,
p.ASK_SPREAD_CURVE, p.T_SPREAD, p.MID_T_ 4 SPREAD, p.ASK_T_SPREAD, p.TEST_CONDITION_CODE,
p.CURVE_YIELD, p.PRICE_TYPE, p.BID_SPREAD_CURVE, p.MID_SPREAD_CURVE, p.PRICE_TYPE_ID,
p.BID_DISCOUNT_RATE, p.ASK_DISCOUNT_RATE, p.EOD_LATE_BID_PRICE_DELTA, p.SPREAD,
a.PRICING_SPREAD_TYPE, a.BENCHMARK_NAME, a.TBA_BACK_BENCH, a.DIMINIMIS_ELIGIBLE_FLAG,
a.HALF_DEMINIMIS_ELIGIBLE_FLAG, a.BENCHMARK_TBA, a.PAYUP
FROM TEST.FFINC_PRC p
LEFT JOIN TEST.FPRC_ASSUM a
ON p.ASSUMP_ID = a.ASSUMP_ID
JOIN LAST_TICKS_BY_DAY B
ON P.LAST_PRICE_TIME = B.LAST_PRICE_TIME
WHERE p.INSTRUMENT_ID = 9730862
AND p.PRICING_DATE = B.PRICING_DATE
AND p.PRICE_TYPE_ID in(5, 6)
ORDER BY A.LAST_PRICE_TIME ASC
/
Elapsed: 00:00:00.09
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------
SQL_ID 4suz6vatc44ts, child number 0
-------------------------------------
WITH LAST_TICKS_BY_DAY AS ( SELECT /*+ gather_plan_statistics */
MAX(LAST_PRICE_TIME) AS LAST_PRICE_TIME, MAX(PRICING_DATE) AS
PRICING_DATE FROM TEST.FFINC_PRC A WHERE
A.INSTRUMENT_ID = 9730862 AND A.PRICING_DATE BETWEEN
TO_DATE('2020-07-28', 'YYYY-MM-DD') AND TO_DATE('2020-07-29',
'YYYY-MM-DD') AND LAST_PRICE_TIME >= TO_TIMESTAMP('28-JUL-20
11.59.59.000000000 PM','DD-MON-YY HH.MI.SS.FF PM') AND
LAST_PRICE_TIME <= TO_TIMESTAMP('29-JUL-20 11.59.59.000000000
PM','DD-MON-YY HH.MI.SS.FF PM') AND A.PRICE_TYPE_ID in(5 , 6 )
GROUP BY TRUNC(LAST_PRICE_TIME) ) SELECT /*+ gather_plan_statistics */
p.INSTRUMENT_ID, p.SOURCE, p.BID_PRICE, p.LAST_PRICE_TIME, p.ASK_PRICE,
p.MID_PRICE, p.BID_SPREAD, p.ASK_SPREAD, p.MID_SPREAD,
p.BID_YIELD, p.ASK_YIELD, p.MID_YIELD, p.ASK_SPREAD_CURVE,
p.T_SPREAD, p.MID_T_SPREAD, p.ASK_T_SPREAD, p.TEST_CONDITION_CODE,
p.CURVE_YIELD, p.PRICE_TYPE, p.BID_SPREAD_CURVE, p.MID_SPREAD_CURVE,
p.PRICE_TYPE_ID, p.BID
Plan hash value: 86494900
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.06 | 11512 | | | |
| 1 | SORT ORDER BY | | 1 | 1 | 1 |00:00:00.06 | 11512 | 2048 | 2048 | 2048 (0)|
| 2 | NESTED LOOPS OUTER | | 1 | 1 | 1 |00:00:00.06 | 11512 | | | |
| 3 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.06 | 11506 | | | |
| 4 | VIEW | | 1 | 1 | 1 |00:00:00.06 | 11497 | | | |
| 5 | HASH GROUP BY | | 1 | 1 | 1 |00:00:00.06 | 11497 | 2456K| 2456K| 1291K (0)|
| 6 | PARTITION RANGE ITERATOR | | 1 | 1 | 6272 |00:00:00.05 | 11497 | | | |
| 7 | INLIST ITERATOR | | 2 | | 6272 |00:00:00.05 | 11497 | | | |
|* 8 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| FFINC_PRC | 4 | 1 | 6272 |00:00:00.05 | 11497 | | | |
| 9 | SORT CLUSTER BY ROWID | | 4 | 1 | 6272 |00:00:00.01 | 50 | 4096 | 4096 | 2048 (0)|
|* 10 | INDEX RANGE SCAN | FF_INC_PRICE_IDX1 | 4 | 1 | 6272 |00:00:00.01 | 50 | 1025K| 1025K| |
| 11 | PARTITION RANGE ITERATOR | | 1 | 1 | 1 |00:00:00.01 | 9 | | | |
| 12 | INLIST ITERATOR | | 1 | | 1 |00:00:00.01 | 9 | | | |
|* 13 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | FFINC_PRC | 2 | 1 | 1 |00:00:00.01 | 9 | | | |
|* 14 | INDEX RANGE SCAN | FF_INC_PRICE_IDX1 | 2 | 1 | 1 |00:00:00.01 | 8 | 1025K| 1025K| |
| 15 | TABLE ACCESS BY GLOBAL INDEX ROWID | FPRC_ASSUM | 1 | 1 | 1 |00:00:00.01 | 6 | | | |
|* 16 | INDEX UNIQUE SCAN | FPRC_ASSUM_PK | 1 | 1 | 1 |00:00:00.01 | 5 | 1025K| 1025K| |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - filter("A"."PRICING_DATE"<=TO_DATE(' 2020-07-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
10 - access((("A"."PRICE_TYPE_ID"=5 OR "A"."PRICE_TYPE_ID"=6)) AND "A"."INSTRUMENT_ID"=9730862 AND "LAST_PRICE_TIME">=TO_TIMESTAMP('28-JUL-20
11.59.59.000000000 PM','DD-MON-YY HH.MI.SS.FF AM') AND "LAST_PRICE_TIME"<=TO_TIMESTAMP('29-JUL-20 11.59.59.000000000 PM','DD-MON-YY HH.MI.SS.FF AM'))
13 - filter("P"."PRICING_DATE"="B"."PRICING_DATE")
14 - access((("P"."PRICE_TYPE_ID"=5 OR "P"."PRICE_TYPE_ID"=6)) AND "P"."INSTRUMENT_ID"=9730862 AND "P"."LAST_PRICE_TIME"="B"."LAST_PRICE_TIME")
16 - access("P"."ASSUMP_ID"="A"."ASSUMP_ID")
53 rows selected.
Elapsed: 00:00:00.02
SQL> WITH LAST_TICKS_BY_DAY AS (
SELECT /*+ gather_plan_statistics */ MAX(LAST_PRICE_TIME) AS LAST_PRICE_TIME, MAX(PRICING_DATE) AS PRICING_DATE
FROM TEST.FFINC_PRC A
WHERE A.INSTRUMENT_ID = 9730862
AND A.PRICING_DATE BETWEEN TO_DATE('2020-07-28', 'YYYY-MM-DD') AND TO_DATE('2020-07-29', 'YYYY-MM-DD')
AND LAST_PRICE_TIME >= TO_TIMESTAMP('28-JUL-20 11.59.59.000000000 PM','DD-MON-YY HH.MI.SS.FF PM')
AND LAST_PRICE_TIME <= TO_TIMESTAMP('29-JUL-20 11.59.59.000000000 PM','DD-MON-YY HH.MI.SS.FF PM')
AND A.PRICE_TYPE_ID in(5 , 6 )
GROUP BY TRUNC(LAST_PRICE_TIME)
)
SELECT /*+ gather_plan_statistics */ p.INSTRUMENT_ID, p.SOURCE, p.BID_PRICE, p.LAST_PRICE_TIME, p.ASK_PRICE,
p.MID_PRICE, p.BID_SPREAD, p.A 2 SK_SPREAD, p.MID_SPREAD, p.BID_YIELD, p.ASK_YIELD, p.MID_YIELD,
p.ASK_SPREAD_CURVE, p.T_SPREAD, p.MID_T_SPREAD, p.ASK_T_SPREAD, p.TEST_CONDITION_CODE,
p.CURVE_YIELD, p.PRICE_TYPE, p.BID_SPREAD_CURVE, p.MID_SPREAD_CURVE, p.PRICE_TYPE_ID,
p.BI 3 D_DISCOUNT_RATE, p.ASK_DISCOUNT_RATE, p.EOD_LATE_BID_PRICE_DELTA, p.SPREAD,
a.PRICING_SPREAD_TYPE, a.BENCHMARK_NAME, a.TBA_BACK_BENCH, a.DIMINIMIS_ELIGIBLE_FLAG,
a.HALF_DEMINIMIS_ELIGIBLE_FLAG, a.BENCHMARK_TBA, a.PAYUP
4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 FROM TEST.FFINC_PRC p
LEFT JOIN TEST.FPRC_ASSUM a
ON p.ASSUMP_ID = a.ASSUMP_ID
JOIN LAST_TICKS_BY_DAY B
ON P.LAST_PRICE_TIME = B.LAST_PRICE_TIME
WHERE p.INSTRUMENT_ID = 9730862
AND p.PRICING_DATE = B.PRICING_DATE
AND p.PRICE_TYPE_ID in(5, 6)
ORDER BY A.LAST_PRICE_TIME ASC
/
19 20 21 22 23 24 25 26 27
^CWITH LAST_TICKS_BY_DAY AS (
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
Elapsed: 00:18:42.80
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------
SQL_ID 4suz6vatc44ts, child number 1
-------------------------------------
WITH LAST_TICKS_BY_DAY AS ( SELECT /*+ gather_plan_statistics */
MAX(LAST_PRICE_TIME) AS LAST_PRICE_TIME, MAX(PRICING_DATE) AS
PRICING_DATE FROM TEST.FFINC_PRC A WHERE
A.INSTRUMENT_ID = 9730862 AND A.PRICING_DATE BETWEEN
TO_DATE('2020-07-28', 'YYYY-MM-DD') AND TO_DATE('2020-07-29',
'YYYY-MM-DD') AND LAST_PRICE_TIME >= TO_TIMESTAMP('28-JUL-20
11.59.59.000000000 PM','DD-MON-YY HH.MI.SS.FF PM') AND
LAST_PRICE_TIME <= TO_TIMESTAMP('29-JUL-20 11.59.59.000000000
PM','DD-MON-YY HH.MI.SS.FF PM') AND A.PRICE_TYPE_ID in(5 , 6 )
GROUP BY TRUNC(LAST_PRICE_TIME) ) SELECT /*+ gather_plan_statistics */
p.INSTRUMENT_ID, p.SOURCE, p.BID_PRICE, p.LAST_PRICE_TIME, p.ASK_PRICE,
p.MID_PRICE, p.BID_SPREAD, p.ASK_SPREAD, p.MID_SPREAD,
p.BID_YIELD, p.ASK_YIELD, p.MID_YIELD, p.ASK_SPREAD_CURVE,
p.T_SPREAD, p.MID_T_SPREAD, p.ASK_T_SPREAD, p.TEST_CONDITION_CODE,
p.CURVE_YIELD, p.PRICE_TYPE, p.BID_SPREAD_CURVE, p.MID_SPREAD_CURVE,
p.PRICE_TYPE_ID, p.BID
Plan hash value: 2213069938
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 0 | 0 | | | |
| 1 | SORT ORDER BY | | 1 | 1 | 0 |00:00:00.01 | 0 | 0 | 73728 | 73728 | |
| 2 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 3 | HASH JOIN | | 1 | 1 | 0 |00:00:00.01 | 0 | 0 | 2991K| 2991K| 1694K (0)|
| 4 | VIEW | | 1 | 6271 | 1 |00:00:00.05 | 11352 | 0 | | | |
| 5 | HASH GROUP BY | | 1 | 6271 | 1 |00:00:00.05 | 11352 | 0 | 2456K| 2456K| 1288K (0)|
| 6 | PARTITION RANGE ITERATOR | | 1 | 6272 | 6272 |00:00:00.04 | 11352 | 0 | | | |
| 7 | INLIST ITERATOR | | 2 | | 6272 |00:00:00.04 | 11352 | 0 | | | |
|* 8 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| FFINC_PRC | 4 | 6272 | 6272 |00:00:00.04 | 11352 | 0 | | | |
| 9 | SORT CLUSTER BY ROWID | | 4 | 34 | 6272 |00:00:00.01 | 50 | 0 | 96256 | 96256 |43008 (0)|
|* 10 | INDEX RANGE SCAN | FF_INC_PRICE_IDX1 | 4 | 34 | 6272 |00:00:00.01 | 50 | 0 | 1025K| 1025K| |
| 11 | PARTITION RANGE ALL | | 1 | 13931 | 2529K|00:20:16.16 | 1302K| 1038K| | | |
| 12 | INLIST ITERATOR | | 889 | | 2529K|00:20:42.40 | 1302K| 1038K| | | |
| 13 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | FFINC_PRC | 1777 | 13931 | 2529K|00:20:44.74 | 1302K| 1038K| | | |
|* 14 | INDEX RANGE SCAN | FF_INC_PRICE_IDX1 | 1497 | 13931 | 2529K|00:00:09.91 | 28025 | 19080 | 1025K| 1025K| |
| 15 | TABLE ACCESS BY GLOBAL INDEX ROWID | FPRC_ASSUM | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 16 | INDEX UNIQUE SCAN | FPRC_ASSUM_PK | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | 1025K| 1025K| |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("P"."PRICING_DATE"="B"."PRICING_DATE" AND "P"."LAST_PRICE_TIME"="B"."LAST_PRICE_TIME")
8 - filter("A"."PRICING_DATE"<=TO_DATE(' 2020-07-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
10 - access((("A"."PRICE_TYPE_ID"=5 OR "A"."PRICE_TYPE_ID"=6)) AND "A"."INSTRUMENT_ID"=9730862 AND "LAST_PRICE_TIME">=TO_TIMESTAMP('28-JUL-20
11.59.59.000000000 PM','DD-MON-YY HH.MI.SS.FF AM') AND "LAST_PRICE_TIME"<=TO_TIMESTAMP('29-JUL-20 11.59.59.000000000 PM','DD-MON-YY HH.MI.SS.FF AM'))
14 - access((("P"."PRICE_TYPE_ID"=5 OR "P"."PRICE_TYPE_ID"=6)) AND "P"."INSTRUMENT_ID"=9730862)
16 - access("P"."ASSUMP_ID"="A"."ASSUMP_ID")
Note
-----
- statistics feedback used for this statement
57 rows selected.
The clue to the reason for the difference is this line at the bottom of the second plan:
- statistics feedback used for this statement
This means that the optimizer has spotted that it got its estimates wrong the first time and taken steps to adjust them.
This is likely because steps 6 - 10 in the first plan estimate 1 row, yet return over 6,000!
Notice that in the second plan lines 6-8 have the correct estimate and 8-9 estimate more than one row.
The problem is it's carried these new estimates up to the group by above - which
does return one row. This is leading the optimizer to use a hash join at step 3 instead of nested loops.
This means it can no longer apply this filter at step 13:
13 - filter("P"."PRICING_DATE"="B"."PRICING_DATE")
So instead of reading one row fast, it's taking 20+ minutes to read all the partitions in FFINC_PRC.
Ensuring that OPTIMIZER_ADAPTIVE_STATISTICS is FALSE will reduce the change of this happening.
Statistics feedback will still happen in some cases when this is disabled, but doing this may be enough to stop the complete re-estimate you're seeing.
Can this query be tuned any further ?I see the query reads FFINC_PRC twice. It looks to me the subquery is to find the most recent row, then you join back to it to get the details.
You should be able to read the table just once, using analytic functions to find the max values/get the row number, for example:
with rws as (
select f.*,
row_number () over (
order by pricing_date desc, last_price_time desc
) rn
from ffinc_prc f
where ...
)
select * from rws
where rn = 1;
Or getting the max value for each of these columns and filtering to the rows matching these in the outer query.