記錄一次沒有收集直方圖優化器選擇全表掃描導致CPU耗盡

来源:https://www.cnblogs.com/sky2088/archive/2018/04/10/8779593.html
-Advertisement-
Play Games

場景：資料庫升級第二天，操作系統CPU使用率接近100%。 查看ash報告： 再看TOP SQL 具體SQL： select count(1) as chipinCount, sum(bets) as sumBets from t_chipin_temp where status in (0) an ...


 

場景：資料庫升級第二天，操作系統CPU使用率接近100%。


查看ash報告：

 

再看TOP SQL

 

 

 

具體SQL：

select count(1) as chipinCount, sum(bets) as sumBets from t_chipin_temp where status in (0) and lottype='gp'


看到這個sql的執行計划走全表掃描。再查看相關列上是否有索引，結果是有索引的。那麼問題來了，既然有索引，而且升級之後對該表格還進行過基本的統計信息收集，那麼為什麼優化器沒有走索引。

 

 

 

收集該sql的10053事件

SQL> select child_number from v$sql where sql_id='57tdrj6a4ync4';

CHILD_NUMBER
  ------------
        0

SQL> execute DBMS_SQLDIAG.DUMP_TRACE(p_sql_id=>'57tdrj6a4ync4', p_child_number=>0, p_component=>'Optimizer', p_file_id=>'SQL_TRACE_10053');

PL/SQL procedure successfully completed.

SQL> SELECT value FROM v$diag_info WHERE name='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl11g/ORCL/trace/ORCL_ora_32070_SQL_TRACE_10053.trc

 

 

 vi  /u01/app/oracle/diag/rdbms/orcl11g/ORCL/trace/ORCL_ora_32070_SQL_TRACE_10053.trc

Access path analysis for T_CHIPIN_TEMP
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T_CHIPIN_TEMP[T_CHIPIN_TEMP]
Column (#8): STATUS(
AvgLen: 4 NDV: 4 Nulls: 0 Density: 0.250000 Min: -2 Max: 4
Column (#44): lottype(
AvgLen: 3 NDV: 2 Nulls: 0 Density: 0.500000
ColGroup (#4, Index) TX_CHIPIN_TMP_TIMCODE2
Col#: 5 11 31 33 CorStregth: -1.00
ColGroup (#2, Index) IDX_T_CHIPIN_TEMP
Col#: 13 34 44 CorStregth: -1.00
ColGroup (#3, Index) TX_CHIPIN_TMP_TIME2
Col#: 8 20 44 CorStregth: -1.00
ColGroup (#5, Index) TX_CHIPIN_TMP_AWARD2
Col#: 15 35 36 CorStregth: -1.00
ColGroup (#6, Index) IX_CHIPIN_TEMP_STALOC2
Col#: 8 44 CorStregth: 2.67
ColGroup (#1, Index) IDX_S_P
Col#: 8 31 CorStregth: -1.00
ColGroup Usage:: PredCnt: 2 Matches Full: #6 Partial: Sel: 0.3420
Table: T_CHIPIN_TEMP Alias: T_CHIPIN_TEMP
Card: Original: 5671105.00 Rounded: 1939622 Computed: 1939621.71 Non Adjusted: 1939621.71
           原始行數                    近似值                       精確值                            非修正值

Access Path: TableScan
Cost: 62160.58 Resp: 62160.58 Degree: 0
Cost_io: 61966.00 Cost_cpu: 7711574370
Resp_io: 61966.00 Resp_cpu: 7711574370
kkofmx: index filter:"T_CHIPIN_TEMP"."lottype"=:B1

kkofmx: index filter:"T_CHIPIN_TEMP"."STATUS"=:B1

kkofmx: index filter:"T_CHIPIN_TEMP"."lottype"=:B1

 

Access Path: index (RangeScan)
Index: IDX_S_P
resc_io: 918156.00 resc_cpu: 8328403927
ix_sel: 0.250000 ix_sel_with_filters: 0.250000
Cost: 918366.14 Resp: 918366.14 Degree: 1

Access Path: index (skip-scan)
SS scan sel: 0.500000 SS filter sel: 0.500000 ANDV (#skips): 564.000000
SS io: 14684.000000 vs. table scan io: 61966.000000
Skip Scan chosen
Access Path: index (SkipScan)
Index: IDX_T_CHIPIN_TEMP
resc_io: 226050.00 resc_cpu: 5276879027
ix_sel: 0.500000 ix_sel_with_filters: 0.500000
Cost: 226183.15 Resp: 226183.15 Degree: 1
ColGroup Usage:: PredCnt: 2 Matches Full: #6 Partial: Sel: 0.3420
ColGroup Usage:: PredCnt: 2 Matches Full: #6 Partial: Sel: 0.3420

Access Path: index (AllEqRange)
Index: IX_CHIPIN_TEMP_STALOC2
resc_io: 128169.00 resc_cpu: 3259690463
ix_sel: 0.350930 ix_sel_with_filters: 0.350930
Cost: 128251.25 Resp: 128251.25 Degree: 1
ColGroup Usage:: PredCnt: 2 Matches Full: #6 Partial: Sel: 0.3420
Access Path: index (skip-scan)
SS scan sel: 0.344312 SS filter sel: 0.344312 ANDV (#skips): 1579520.000000
SS io: 20452.000000 vs. table scan io: 61966.000000
Skip Scan chosen
Access Path: index (SkipScan)
Index: TX_CHIPIN_TMP_TIME2
resc_io: 1157882.00 resc_cpu: 10592729810
ix_sel: 0.344312 ix_sel_with_filters: 0.344312
Cost: 1158149.27 Resp: 1158149.27 Degree: 1
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
******** Begin index join costing ********
****** trying bitmap/domain indexes ******
ColGroup Usage:: PredCnt: 2 Matches Full: #6 Partial: Sel: 0.3420
ColGroup Usage:: PredCnt: 2 Matches Full: #6 Partial: Sel: 0.3420
ColGroup Usage:: PredCnt: 2 Matches Full: #6 Partial: Sel: 0.3420
Access Path: index (AllEqRange)
Index: IX_CHIPIN_TEMP_STALOC2
resc_io: 4610.00 resc_cpu: 420754238
ix_sel: 0.350930 ix_sel_with_filters: 0.350930
Cost: 4620.62 Resp: 4620.62 Degree: 0
Bitmap nodes:
Used IX_CHIPIN_TEMP_STALOC2
Cost = 4627.223361, sel = 0.342018
****** finished trying bitmap/domain indexes ******
******** End index join costing ********
Best:: AccessPath: TableScan
Cost: 62160.58 Degree: 1 Resp: 62160.58 Card: 1939621.71 Bytes: 0

***************************************

 

 

10053中看到因為沒有直方圖存在，所以這裡的Column (#8) Density = 0.25 ,Column (#44) Density = 0.5 是從 1/ NDV 算得的,統計信息顯示的status in (0)  數據行占總行數的1/4， lottype='gp' 數據行占總行數的1/2, 所以優化器選擇做全表掃描.

 

SQL> select count(1) as chipinCount, sum(bets) as sumBets from t_chipin_temp where status in (0) and lottype='gp' ;

CHIPINCOUNT SUMBETS
-----------         ---------
   3                   20


以上符合status in (0) and lottype='gp'的只有3行，且status, lottype列上建有複合索引，索引是IX_CHIPIN_TEMP_STALOC2，同時也使用了dbms_stats包收集表和索引上的統計信息，照理說CBO應該選擇 INDEX RANGE SCAN，而避免全表掃描，但實時上優化器opitimizer仍然全表掃描。

 

經過一番查閱，發現其原因是在收集統計信息時並沒有收集到直方圖，只要收集了直方圖，那麼優化器就會瞭解到status in (0) and lottype='gp' 條件僅有少量的行，優化器會走索引。

 

於是重新收集統計信息加上直方圖：


execute dbms_stats.gather_table_stats(ownname => 'USER',tabname => 'T_CHIPIN_TEMP' ,method_opt => 'FOR ALL COLUMNS SIZE 2' ,cascade => true);

alter system flush shared_pool;

 

執行完以上操作後，系統CPU很快降了下來。

再看執行計劃，便是 INDEX RANGE SCAN 了。

 

 

 

 

再看收集直方圖之後10053的內容：


Access path analysis for T_CHIPIN_TEMP
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T_CHIPIN_TEMP[T_CHIPIN_TEMP]
Column (#8):
NewDensity:0.000180, OldDensity:0.000000 BktCnt:5558, PopBktCnt:5558, PopValCnt:3, NDV:3
Column (#8): STATUS(
AvgLen: 4 NDV: 3 Nulls: 0 Density: 0.000180 Min: -2 Max: 4
Histogram: Freq #Bkts: 3 UncompBkts: 5558 EndPtVals: 3
Column (#44):
NewDensity:0.000090, OldDensity:0.000000 BktCnt:5559, PopBktCnt:5558, PopValCnt:1, NDV:2
Column (#44): lottype(
AvgLen: 3 NDV: 2 Nulls: 0 Density: 0.000090
Histogram: Freq #Bkts: 2 UncompBkts: 5559 EndPtVals: 2
ColGroup (#5, Index) TX_CHIPIN_TMP_TIMCODE2
Col#: 5 11 31 33 CorStregth: -1.00
ColGroup (#6, Index) TX_CHIPIN_TMP_AWARD2
Col#: 15 35 36 CorStregth: -1.00
ColGroup (#4, Index) TX_CHIPIN_TMP_TIME2
Col#: 8 20 44 CorStregth: -1.00
ColGroup (#3, Index) IDX_T_CHIPIN_TEMP
Col#: 13 34 44 CorStregth: -1.00
ColGroup (#1, Index) IX_CHIPIN_TEMP_STALOC2
Col#: 8 44 CorStregth: 2.00
ColGroup (#2, Index) IDX_S_P
Col#: 8 31 CorStregth: -1.00
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
Table: T_CHIPIN_TEMP Alias: T_CHIPIN_TEMP
Card: Original: 5682253.000000 Rounded: 1 Computed: 0.18 Non Adjusted: 0.18
Access Path: TableScan
Cost: 62159.09 Resp: 62159.09 Degree: 0
Cost_io: 61966.00 Cost_cpu: 7652553546
Resp_io: 61966.00 Resp_cpu: 7652553546
kkofmx: index filter:"T_CHIPIN_TEMP"."lottype"='gp'

kkofmx: index filter:"T_CHIPIN_TEMP"."STATUS"=1

kkofmx: index filter:"T_CHIPIN_TEMP"."lottype"='gp'

Access Path: index (RangeScan)
Index: IDX_S_P
resc_io: 667.00 resc_cpu: 6041476
ix_sel: 0.000180 ix_sel_with_filters: 0.000180
Cost: 667.15 Resp: 667.15 Degree: 1

Access Path: index (skip-scan)
SS scan sel: 0.000180 SS filter sel: 0.000180 ANDV (#skips): 564.000000
SS io: 14379.000000 vs. table scan io: 61966.000000
Skip Scan chosen
Access Path: index (SkipScan)
Index: IDX_T_CHIPIN_TEMP
resc_io: 14450.00 resc_cpu: 104195010
ix_sel: 0.000180 ix_sel_with_filters: 0.000180
Cost: 14452.63 Resp: 14452.63 Degree: 1
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
Access Path: index (AllEqRange)
Index: IX_CHIPIN_TEMP_STALOC2
resc_io: 4.00 resc_cpu: 29696
ix_sel: 0.000000 ix_sel_with_filters: 0.000000
Cost: 4.00 Resp: 4.00 Degree: 1
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
Access Path: index (skip-scan)
SS scan sel: 0.000000 SS filter sel: 0.000000 ANDV (#skips): 1582976.000000
SS io: 20631.000000 vs. table scan io: 61966.000000
Skip Scan chosen
Access Path: index (SkipScan)
Index: TX_CHIPIN_TMP_TIME2
resc_io: 20634.00 resc_cpu: 146945003
ix_sel: 0.000000 ix_sel_with_filters: 0.000000
Cost: 20637.71 Resp: 20637.71 Degree: 1
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
******** Begin index join costing ********
****** trying bitmap/domain indexes ******
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
ColGroup Usage:: PredCnt: 2 Matches Full: Partial:
Access Path: index (AllEqRange)
Index: IX_CHIPIN_TEMP_STALOC2
resc_io: 3.00 resc_cpu: 21564
ix_sel: 0.000000 ix_sel_with_filters: 0.000000
Cost: 3.00 Resp: 3.00 Degree: 0
Bitmap nodes:
Used IX_CHIPIN_TEMP_STALOC2
Cost = 3.000545, sel = 0.000000
****** finished trying bitmap/domain indexes ******
******** End index join costing ********
Best:: AccessPath: IndexRange
Index: IX_CHIPIN_TEMP_STALOC2
Cost: 4.00 Degree: 1 Resp: 4.00 Card: 0.18 Bytes: 0


從10053可以看出手動指定收集直方圖後CBO優化器才能做出正確的選擇

 

註：dbms_stats的自動決定直方圖的收集與否及收集的桶數受到col_usage$表影響，如果某張表的列存在於col_usage$中，oracle就認為該列存在收集直方圖的必要。

　　Histograms are not gathered on columns for which there is no predicate information captured in the  col_usage$ view. Col_usage$ is populated with the column information only when queries are executed with columns referred in predicate information.


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Supervisor安裝、配置、開啟啟動
    1、安裝Python包管理工具（easy_install） wget --no-check-certificate https://bootstrap.pypa.io/ez_setup.py -O - | sudo python 2、安裝supervisor easy_install supervi ...
  • mysql學習之完整的select語句
    本文內容： 完整語法 去重選項 欄位別名 數據源 where group by having order by limit 首發日期：2018-04-11 完整語法： select 去重選項 欄位列表 [as 欄位別名] from 數據源 [where子句] [group by 子句] [havin... ...
  • Oracle的基本語法，存儲函數及觸發器
    1.PL/SQL PL/SQL是Oracle對 ql語言的過程化擴展，指在 SQL 命令語言中增加了過程處理語句，使SQL語言具有過程處理能力。把 SQL 語言的數據操縱能 力與過程語言的數據處理能力結合起來，使得 PLSQL 面向過程但比過程語言簡單、高效、靈活和實用。 基本語法結構: [decl ...
  • MongoDB系列四（索引）.
    一、索引簡介 再來老生常談一番，什麼是索引呢？資料庫索引與書籍的索引類似。有了索引就不需要翻整本書，資料庫可以直接在索引中查找，在索引中找到條目以後，就可以直接跳轉到目標文檔的位置，這能使查找速度提高幾個數量級。 然而，使用索引是有代價的：對於添加的每一個索引，每次寫操作（插入、更新、刪除）都將耗費 ...
  • oracle安裝配置
    yum groupinstall "X Window System" 啟動 vncserver 添加用戶 groupadd oinstall groupadd dba useradd -g oinstall -G dba oracle passwd oracle 修改內核 /etc/sysctl.c ...
  • MySQL 5.7 跟蹤優化器
    Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.7.12 MySQL Community Server (GPL)Copyright (c) ...
  • [HDFS Manual] CH8 HDFS Snapshots
    HDFS Snapshots HDFS Snapshots. 1 1. 概述... 1 1.1 Snapshottable目錄... 1 1.2 快照路徑... 1 2. 帶快照的更新... 1 3. 快照操作... 1 3.1 管理操作... 1 3.2 用戶操作... 1 1. 概述 HDFS快 ...
  • greenplum集群某台機器磁碟占用100%處理方式
    一、問題描述 使用gpfdist往集群中導入大量數據， 一段時間後連接退出，集群無法連接 二、問題定位 使用如下命令查看： 連接到相應機器，查看磁碟狀況： 說明此時磁碟占用滿了。 三、問題解決 處理這種情況有如下幾種方式： 3.1 擴容磁碟 由於本人是將數據目錄直接放在根目錄下，可以通過添加一塊新磁 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類