在具有索引的列上使用where子句查詢要慢得多-有解無憂

以下查詢具有良好的性能

    select distinct i.TERMINALNAME, 
           to_char(i.BEGINTIME,'mm/dd/yyyy hh:mi:ss AM') BEGINTIME,
           i.ERRORTEXT, 
           i.RECORDSPROCESSED, 
           i.RECORDTYPE 
      from MYLOGGING i
inner join (select recordtype, 
                   max(BEGINTIME) as lastrundate 
              from MYLOGGING group by recordtype) im on 
      im.recordtype=i.recordtype and im.lastrundate=i.BEGINTIME
     where i.ERRORTEXT in ('Success', 'Failure') 
       and i.TERMINALNAME Not In ('REE300', 'XEE300', 'YT', 'QX', 'VC', 'DF') 
    ORDER BY i.TERMINALNAME ASC;

QUERY PLAN

Plan hash value: 3900617130
 
------------------------------------------------------------------------------------------
| Id  | Operation             | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                  |     2 |   132 |  1257   (2)| 00:00:01 |
|   1 |  SORT UNIQUE          |                  |     2 |   132 |  1256   (2)| 00:00:01 |
|*  2 |   HASH JOIN RIGHT SEMI|                  |     2 |   132 |  1255   (2)| 00:00:01 |
|   3 |    VIEW               |                  |   772 | 16984 |   630   (2)| 00:00:01 |
|   4 |     HASH GROUP BY     |                  |   772 | 16212 |   630   (2)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| MYLOGGING |   281K|  5765K|   623   (1)| 00:00:01 |
|*  6 |    TABLE ACCESS FULL  | MYLOGGING |   191K|  8222K|   625   (1)| 00:00:01 |
------------------------------------------------------------------------------------------

但是，在查詢末尾添加新的 where 子句后and i.BEGINTIME>= trunc(sysdate) - 60，如果第一次運行，它會導致查詢運行得更慢，第二次運行后它會很快。

     select distinct i.TERMINALNAME, 
               to_char(i.BEGINTIME,'mm/dd/yyyy hh:mi:ss AM') BEGINTIME, 
               i.ERRORTEXT, 
               i.RECORDSPROCESSED, 
               i.RECORDTYPE 
          from MYLOGGING i
    inner join (select recordtype, 
                       max(BEGINTIME) as lastrundate 
                  from MYLOGGING group by recordtype) im on im.recordtype=i.recordtype 
           and im.lastrundate=i.BEGINTIME
         where i.ERRORTEXT in ('Success', 'Failure') 
           and i.TERMINALNAME Not In ('REE300', 'XEE300', 'YT', 'QX', 'VC', 'DF') 
           and i.BEGINTIME>= trunc(sysdate) - 60 ORDER BY i.TERMINALNAME ASC;

查詢計劃

Plan hash value: 2346866897
 
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                      |     1 |    86 |   809   (1)| 00:00:01 |
|   1 |  SORT UNIQUE                            |                      |     1 |    86 |   809   (1)| 00:00:01 |
|*  2 |   FILTER                                |                      |       |       |            |          |
|   3 |    SORT GROUP BY                        |                      |     1 |    86 |   809   (1)| 00:00:01 |
|*  4 |     HASH JOIN                           |                      | 32572 |  2735K|   807   (1)| 00:00:01 |
|*  5 |      TABLE ACCESS BY INDEX ROWID BATCHED| MYLOGGING     |     1 |    64 |     4   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN                  | IX2_MYLOGGING |     1 |       |     3   (0)| 00:00:01 |
|   7 |      TABLE ACCESS FULL                  | MYLOGGING     |   283K|  6081K|   802   (1)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

查詢計劃顯示查詢運行速度很快，因為這不是我第一次運行它并且我假設資料已快取。我沒有特權重繪快取。

知道什么可能導致查詢在第一次運行時運行速度很慢嗎？超過2-3分鐘。目前 BEGINTIME 列上的索引將“join_index”設定為 no。會不會跟這有關系？

uj5u.com熱心網友回復：

我會先縮小 MYLOGGING 表的范圍，這樣搜索的范圍就會減少并將其放入另一個子查詢中。按日期縮小范圍。因為 FROM 發生在 WHERE 之前。

select distinct i.TERMINALNAME
, to_char(i.BEGINTIME,'mm/dd/yyyy hh:mi:ss AM') BEGINTIME
, i.ERRORTEXT
, i.RECORDSPROCESSED
, i.RECORDTYPE 
from 
(select i2.*
 from MYLOGGING i2
 where i2.BEGINTIME >= trunc(sysdate) - 60
) i
inner join (select recordtype
            , max(BEGINTIME) as lastrundate 
            from MYLOGGING 
            group by recordtype
           ) im on im.recordtype=i.recordtype 
            and im.lastrundate=i.BEGINTIME
     where i.ERRORTEXT in ('Success', 'Failure') and i.TERMINALNAME Not In ('REE300', 'XEE300', 'YT', 'QX', 'VC', 'DF')  ORDER BY i.TERMINALNAME ASC;

uj5u.com熱心網友回復：

您可以通過將自聯接轉換為決議函式來避免性能問題。如果沒有連接，要做的作業就會減少，優化器選擇錯誤計劃的方法也會減少。

select distinct
    TERMINALNAME, 
    to_char(BEGINTIME,'mm/dd/yyyy hh:mi:ss AM') BEGINTIME, 
    ERRORTEXT, 
    RECORDSPROCESSED, 
    RECORDTYPE
from
(
    select MYLOGGING.*, max(BEGINTIME) over (partition by recordtype) MAX_BEGINTIME_PER_RECORDTYPE
    from MYLOGGING
)
where BEGINTIME = MAX_BEGINTIME_PER_RECORDTYPE
  and ERRORTEXT in ('Success', 'Failure') 
  and TERMINALNAME Not In ('REE300', 'XEE300', 'YT', 'QX', 'VC', 'DF') 
  and BEGINTIME>= trunc(sysdate) - 60
ORDER BY TERMINALNAME ASC;

找出為什么原來的查詢運行緩慢可能更加困難。您將希望找到具有實際數字的實際執行計劃，而不是僅使用解釋計劃猜測。有關如何使用和顯示執行計劃的資訊，請參閱此問題。/* GATHER_PLAN_STATISTICS */DBMS_XPLAN

完整的執行計劃，尤其是“注釋”部分中的資料，可能會為您提供有關計劃為何緩慢以及為何變得更快的線索。也許是動態重新優化，Oracle 意識到第一次運行很糟糕，并調整了計劃。您可能還想嘗試使用DBMS_XPLAN.DISPLAY_AWR查看執行計劃的歷史記錄。

實際時間以及實際行數與估計行數的比較通常可以告訴您 Oracle 如何以及為什么做出錯誤的決定。例如，額外的 WHERE 子句可能導致 Oracle 顯著低估回傳的行數，這使得索引訪問和嵌套回圈操作看起來更快。但出于多種原因，嵌套回圈和索引掃描通常最適合回傳一小部分行，而散列連接和全表掃描通常最適合回傳大部分行。

收集和解釋這些資料可能需要幾個小時，尤其是在您不熟悉該程序的情況下。您可以借此機會了解有關查詢調優的更多資訊，或者如果您沒有時間，只需使用上述分析函式方法并完全避免問題。

轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/393567.html

標籤：甲骨文

上一篇：選擇每個月插入日期的最大日期

下一篇：在oracle中如何在搜索字串時消除區分大小寫