Oracle 查询性能问题

嗨，

您好!


1.第一次，在同一会话中执行查询，它以良好的计划快速完成。

2.第二次，在同一会话中执行相同的查询，立即挂起并选择不同的计划。


a) 你能建议一下计划突然改变的原因是什么吗？

两次运行之间的基础表分区中没有数据更改。

b) 这个查询可以进一步调整吗？

PRCING_DATE是两个表中的分区键列。

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.
复制

差异原因的线索是第二个方案底部的这条线:

   - statistics feedback used for this statement
复制

这意味着优化器发现它的估计第一次出错，并采取措施进行调整。

这很可能是因为步骤6-10在第一个计划估计1行，但返回超过6,000!

请注意，在第二个计划中，第6-8行具有正确的估计值，第8-9行具有多个估计值。

问题是，它已经将这些新的估计值通过以上的方式提交给了该集团-does返回一行。这导致优化器在步骤3使用哈希连接，而不是嵌套循环。

这意味着它不能再在步骤13应用此过滤器:

13 - filter("P"."PRICING_DATE"="B"."PRICING_DATE")
复制

因此，无需快速读取一行，而是需要20分钟才能读取FFINC_PRC中的所有分区。

确保OPTIMIZER_ADAPTIVE_STATISTICS为FALSE将减少这种情况的变化。

在某些情况下，当禁用此功能时，统计信息反馈仍然会发生，但是这样做可能足以阻止您看到的完整的重新估计。

Can this query be tuned any further ?

我看到查询读取FFINC_PRC两次。在我看来，子查询是查找最近的行，然后您将其重新加入以获取详细信息。

您应该能够读取表一次，使用分析函数查找最大值/获取行号，例如:

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;
复制

或获取每个列的最大值，并过滤到外部查询中与这些列匹配的行。