Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Rich.

Asked: September 11, 2020 - 6:04 am UTC

Last updated: September 15, 2020 - 3:28 pm UTC

Version: 12.2.0.1

Viewed 1000+ times

You Asked

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.

and Chris said...

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.

Rating

  (2 ratings)

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

Comments

Query performance

Rich, September 14, 2020 - 5:07 am UTC


Hi Chris

Thanks.

1) the given query returns 3 rows


2) optimizer_adaptive_statistics is already set to FALSE. How do i prevent this " - statistics feedback used for this statement" ?


SQL> show parameter OPTIMIZER_ADAPTIVE_STATISTICS

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_adaptive_statistics boolean FALSE
SQL>


3) Below is the SQL Monitor report for the same query pasted earlier.

Can you suggest, How do i correct the wrong cardinality etimates on the index range scan on FF_INC_PRICE_IDX1 step 10.

===================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
===================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +1 | 1 | 3 | . | | |
| 1 | SORT ORDER BY | | 1 | 46 | 1 | +1 | 1 | 3 | 2048 | | |
| 2 | NESTED LOOPS OUTER | | 1 | 45 | 1 | +1 | 1 | 3 | . | | |
| 3 | NESTED LOOPS | | 1 | 41 | 1 | +1 | 1 | 3 | . | | |
| 4 | VIEW | | 1 | 36 | 1 | +1 | 1 | 3 | . | | |
| 5 | HASH GROUP BY | | 1 | 36 | 1 | +1 | 1 | 3 | 2MB | | |
| 6 | PARTITION RANGE ITERATOR | | 1 | 35 | 1 | +1 | 1 | 31215 | . | | |
| 7 | INLIST ITERATOR | | | | 1 | +1 | 5 | 31215 | . | | |
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | FFINC_PRC | 1 | 35 | 1 | +1 | 10 | 31215 | . | 100.00 | Cpu (1) |
| 9 | SORT CLUSTER BY ROWID | | 1 | 17 | 1 | +1 | 10 | 31215 | 2048 | | |
| 10 | INDEX RANGE SCAN | FF_INC_PRICE_IDX1 | 1 | 17 | 1 | +1 | 10 | 31215 | . | | |
| 11 | PARTITION RANGE ITERATOR | | 1 | 5 | 1 | +1 | 3 | 3 | . | | |
| 12 | INLIST ITERATOR | | | | 1 | +1 | 3 | 3 | . | | |
| 13 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | FFINC_PRC | 1 | 5 | 1 | +1 | 6 | 3 | . | | |
| 14 | INDEX RANGE SCAN | FF_INC_PRICE_IDX1 | 1 | 4 | 1 | +1 | 6 | 3 | . | | |
| 15 | TABLE ACCESS BY GLOBAL INDEX ROWID | FPRC_ASSUM | 1 | 4 | 1 | +1 | 3 | 3 | . | | |
| 16 | INDEX UNIQUE SCAN | FPRC_ASSUM_PK | 1 | 3 | 1 | +1 | 3 | 3 | . | | |
===================================================================================================================================================================================

Chris Saxon
September 14, 2020 - 12:01 pm UTC

1 - which query?

2 - As the docs say:

The OPTIMIZER_ADAPTIVE_STATISTICS initialization parameter does not control all features of automatic reoptimization. Specifically, this parameter controls statistics feedback for join cardinality only in the context of automatic reoptimization. For example, setting OPTIMIZER_ADAPTIVE_STATISTICS to FALSE disables statistics feedback for join cardinality misestimates, but it does not disable statistics feedback for single-table cardinality misestimates.

I don't know of a what to disable these signle-table cardinalities.

3. You may be able to improve the estimates by:

- Using dynamic stats (dynamic_sampling hint)
- Creating SQL profiles/baselines

I'd strongly recommend rewriting the query so it only accesses the table once over trying to fix the estimates though.

Query performance

Rich, September 15, 2020 - 4:35 am UTC

Thanks!

1) This is the query returns 3 rows. The outer query is doing left outer join.. I am trying to figure to rewrite with analytical function as per your example to read TEST.FFINC_PRC table only once.

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
/


Chris Saxon
September 15, 2020 - 3:28 pm UTC

Remove the group by in the with clause.

Return all the columns (you want) from the table.

Add the over () clause to the MAXes.

Then in an outer query return the rows where the max analytic = the corresponding column in the table.

Or you may be able to ditch the MAXes and replace with row_number, sorting by these columns. Then filter the rows where this = 1 in the outer query (like the example above).
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.