国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

優化案例：不合理的復合索引導致大表訪問低效

IT那活兒 / 2239人閱讀
優化案例：不合理的復合索引導致大表訪問低效

點擊上方“IT那活兒”公眾號，關注后了解更多內容，不管IT什么活兒，干就完了！！！

一

事件描述

前段時間一個27日的晚上，某生產庫進行割接，28日08:30至09:00，監測到第5個節點DPR事件高達130多個。
INST_ID EVENT# EVENT WAIT_CLASS COUNT(*)
 ---------- ---------- ---------------------------------------- --------------- ----------
          1        447 SQL*Net message from dblink Network 2
          2        172 log file sync                            Commit 2
          2        161 log file parallel write                 System I/O 1
          2        186 gc current request Cluster 1
          2        860 Redo Transport MISC Other 1
          3        447 SQL*Net message from dblink Network 1
          5        246 direct path read                         User I/O 131
          5        183 db file async I/O submit System I/O 13
          5        179 db file sequential read                  User I/O 10
          5        184 db file parallel read                    User I/O 8
          5        172 log file sync                            Commit 8
          5        182 db file parallel write                   System I/O 7
          5        860 Redo Transport MISC Other 1
          5        447 SQL*Net message from dblink Network 1
          5        848 SYNC Remote Write Other 1
檢查 I/O 讀寫情況，發現 30 分鐘內 I/O 讀取高達 1.4 TB，響應時間上升至 7 毫秒。
Start - End               5_R         5_W     5_T
 -------------------------- -------- ----------- -------
 07/28 06:30 - 07/28 07:00     59.29        3.90    0.45
 07/28 07:00 - 07/28 07:30      4.97        2.64    0.55
 07/28 07:30 - 07/28 08:00     64.17      107.45    0.66
 07/28 08:00 - 07/28 08:30     49.35      119.04    0.49
 07/28 08:30 - 07/28 09:00  1,410.74       61.31    7.11
二

分析過程

分析此時段數據庫所有等待事件，DPR 位置榜首，說明很可能存在掃描大表的 SQL 語句。
Top Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
 --- ---------------------------------------- ------------ --------------------- ---------------- --------- --------------
   1 direct path read 799,755 60,444.8 75.579 19.2 User I/O
   2 log file sync 16,373,135 30,794.3 1.881 9.8 Commit
   3 log file switch (checkpoint incomplete) 4,761              12,784.5        2,685.246       4.1 Configuration
   4 db file sequential read                       107,176               6,753.6           63.014       2.1 User I/O
   5 DB CPU 5,841.9                        1.9
   6 db file parallel read                          23,852               4,916.0          206.103       1.6 User I/O
   7 buffer busy waits 29,536               2,097.8           71.024       0.7 Concurrency
   8 log file switch completion 1,906               1,488.0          780.686       0.5 Configuration
   9 row cache lock                                320,632               1,111.9            3.468       0.4 Concurrency
  10 read by other session                             105                 220.0        2,095.040       0.1 User I/O
從多個維度對 SQL 執行情況進行分析，發現有兩條 SQL 語句 Total% 項均為最高。
Top Elapsed Time (s) Executions Elapsed Time per Exec (ms) Total% SQL_ID Schema Module
 --- ---------------- ------------ -------------------------- ------- ------------- ------ --------------------------
   1       122,861.29           20              6,143,064.500   38.94 dwyq6uy86w4gw AD MrOfeJ@mafjnh-10.lacolavon
   2       122,580.44           22              5,571,838.182   38.85 1gxak4kjy0tj6 AD MrOfeJ@mafjnh-10.lacolavon
   3         1,949.11        6,674                    292.045    0.62 9kuw4fh61x8sj AD MrOfeJ@mafjnh-15.lacolavon
   4         1,907.77            1              1,907,770.000    0.60 dmyv32kv2v407 AD MrOfeJ@mafjnh-10.lacolavon
   5         1,784.75            1              1,784,750.000    0.57 55a3n59n7duy0 AD MrOfeJ@mafjnh-10.lacolavon
 Top CPU Time (s) Executions CPU per Exec (ms) Total% Elapsed Time (s) SQL_ID Schema Module
 --- ------------- ------------ ----------------- ------- ---------------- ------------- ------ ----------------------------
   1        324.08           22         14,731.11    5.55       122,580.44 1gxak4kjy0tj6 AD MrOfeJ@mafjnh-10.lacolavon
   2        314.10           20         15,704.77    5.38       122,861.29 dwyq6uy86w4gw AD MrOfeJ@mafjnh-10.lacolavon
   3         76.83      320,633              0.24    1.32         1,352.41 bsy2r0brwxdba AD emarfs-167924-1@hnjfapp55-3
   4         43.22           31          1,394.24    0.74            42.78 gx4drnh6540u5 JF SQL*Plus
   5         33.33    1,046,074              0.03    0.57           508.34 9vaj2dfcrc44v AD emarfs-175368-13@hnjfapp17-7
 Top User I/O Time (s) Executions UIO per Exec (ms) %Total Elapsed Time (s) SQL_ID Schema Module
 --- ----------------- ------------ ----------------- ------- ---------------- ------------- ------ --------------------------
   1          31546.74           22    1,433,942.7927   43.46       122,580.44 1gxak4kjy0tj6 AD MrOfeJ@mafjnh-10.lacolavon
   2          29609.35           20    1,480,467.5470   40.79       122,861.29 dwyq6uy86w4gw AD MrOfeJ@mafjnh-10.lacolavon
   3           1939.27        6,674          290.5711    2.67         1,949.11 9kuw4fh61x8sj AD MrOfeJ@mafjnh-15.lacolavon
   4           1194.62        6,937          172.2093    1.65         1,231.32 d5zu2b9cz25y8 AD MrOfeJ@mafjnh-15.lacolavon
   5           1032.22        3,548          290.9299    1.42         1,065.47 c9tkyagwbvj7d AD MrOfeJ@mafjnh-15.lacolavon
 Top Buffer Gets Executions Gets per Exec %Total Elapsed Time (s) SQL_ID Schema Module
 --- ----------- ---------- --------------- ------- ---------------- ------------- ------ -----------------------------
   1 104,089,687         22    4,731,349.41   25.86       122,580.44 1gxak4kjy0tj6 AD MrOfeJ@mafjnh-10.lacolavon
   2  98,475,484         20    4,923,774.20   24.46       122,861.29 dwyq6uy86w4gw AD MrOfeJ@mafjnh-10.lacolavon
   3   6,555,975         31      211,483.06    1.63            42.78 gx4drnh6540u5 JF SQL*Plus
   4   2,800,644  1,046,074            2.68    0.70           508.34 9vaj2dfcrc44v AD emarfs-175368-13@hnjfapp17-7
   5   2,599,250  1,010,897            2.57    0.65           350.97 4wjx4ubwbumz5 AD emarfs-175388-5@hnjfapp33-13
 Top Physical Reads Executions Reads per Exec %Total Elapsed Time (s) SQL_ID Schema Module
 --- -------------- ------------ --------------- ------- ---------------- ------------- ------ --------------------------
   1    103,598,396           22    4,709,018.00   52.23       122,580.44  1gxak4kjy0tj6 AD MrOfeJ@mafjnh-10.lacolavon
   2     98,039,336           20    4,901,966.80   49.43       122,861.29 dwyq6uy86w4gw AD MrOfeJ@mafjnh-10.lacolavon
   3      1,341,265            1    1,341,265.00    0.68         1,784.75  55a3n59n7duy0 AD MrOfeJ@mafjnh-10.lacolavon
   4      1,341,265            1    1,341,265.00    0.68         1,907.77 dmyv32kv2v407 AD MrOfeJ@mafjnh-10.lacolavon
   5         29,556        6,674            4.43    0.01         1,949.11  9kuw4fh61x8sj AD MrOfeJ@mafjnh-15.lacolavon
-- dwyq6uy86w4gw
update CD.OC_DORP_173 set PROD_EXPIRE_DATE = :1  where PRODUCT_ID=:2

-- 1gxak4kjy0tj6
select t.* from CD.OC_DORP_173 t where t.PRODUCT_ID=:1
可以看到，兩條 SQL 均訪問表 CD.OC_DORP_173，且使用了 PRODUCT_ID 做為篩選條件，查看執行計劃，發現兩條 SQL 都使用了全表掃描訪問。
SQL_ID dwyq6uy86w4gw, child number 0
-------------------------------------
update CD.OC_DORP_173 set PROD_EXPIRE_DATE = :1  where PRODUCT_ID=:2

Plan hash value: 2749196760
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |    TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT |             | |       | 817K(100)|          | |      | |
| 1 |  UPDATE | OC_DORP_173 |       | |            | |        | |            |
|   2 | PX COORDINATOR |             | |       | |          | |      | |
| 3 |    PX SEND QC (RANDOM)| :TQ10000 |     1 | 18 |   817K (1)| 00:00:32 |  Q1,00 | P->S | QC (RAND) |
|   4 | PX BLOCK ITERATOR |             | 1 |    18 | 817K (1)| 00:00:32 | Q1,00 | PCWC | |
|* 5 |      TABLE ACCESS FULL| OC_DORP_173 |     1 | 18 |   817K (1)| 00:00:32 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  5 - access(:Z>=:Z AND :Z<=:Z)
      filter("PRODUCT_ID"=:2)
SQL_ID 1gxak4kjy0tj6, child number 0
-------------------------------------
select t.* from CD.OC_DORP_173 t where t.PRODUCT_ID=:1

Plan hash value: 3713006496
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |       | | 32424 (100)| |        | |            |
|   1 | PX COORDINATOR |             | |       | |          | |      | |
| 2 |   PX SEND QC (RANDOM)| :TQ10000 |     1 | 119 | 32424   (1)| 00:00:02 |  Q1,00 | P->S | QC (RAND) |
|   3 | PX BLOCK ITERATOR |             | 1 |   119 | 32424 (1)| 00:00:02 | Q1,00 | PCWC | |
|* 4 |     TABLE ACCESS FULL| OC_DORP_173 |     1 | 119 | 32424   (1)| 00:00:02 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  4 - access(:Z>=:Z AND :Z<=:Z)
      filter("T"."PRODUCT_ID"=:1)
檢查表物理結構，發現該表創建有復合索引，包含 PRODUCT_ID 列，不過并非前導列，因此以上兩條 SQL 語句并未使用該索引，直接對表進行了全掃描。
TABLE_NAME COLUMN_NAME COLUMN_POSITION INDEX_NAME UNIQUENES STATUS
------------ ----------- --------------- ------------------- --------- ------
OC_DORP_173 OBJECT_ID 1 PK_OC_DORP_173 UNIQUE VALID
OC_DORP_173 PRODUCT_ID 2 PK_OC_DORP_173 UNIQUE VALID
OC_DORP_173 VALID_DATE 3 PK_OC_DORP_173 UNIQUE VALID
OC_DORP_173 SO_NBR 4 PK_OC_DORP_173 UNIQUE VALID
為什么創建索引時 PRODUCT_ID 列沒有被做為前導列呢？是選擇性不好嗎？我們檢查下列的數據分布情況。
COLUMN_NAME DATA_TYPE NUM_DISTINCT DENSITY
--------------------- ---------- ------------ ----------
PRODUCT_ID NUMBER 266993664 3.7454E-09
可以看到，PRODUCT_ID 列的選擇性是最好的，進一步檢查表數據，統計顯示約 2.7 億條數據，占用空間約 36 GB，但并未使用任何分區技術，因此每執行一次都會訪問約 36GB 的數據，這也是 30 分鐘內僅執行 42 次，I/O 讀取卻高達 1.4TB 的根本原因。
COUNT(1)
-------------
271,868,287

SEGMENT_NAME MB
-------------- ----------
OC_DORP_173 36,844.00

TABLE_NAME PARTITIONED
--------------- ------------
OC_DORP_173 NO
三

分析結論及建議

未合理使用復合索引，未合理利用分區技術，導致對大表的訪問過于低效。

優化建議：

  • 創建合理的復合索引，確保常用篩選列為前導列。
  • 對于超過 2G 或千萬條數據的大表，使用分區技術改造。




本文作者：任 崇(上海新炬王翦團隊）

本文來源：“IT那活兒”公眾號

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/129145.html

相關文章

  • 開源|性能優化利器：數據庫審核平臺Themis的選型與實踐

    摘要：正是存在問題，促使我們考慮引入數據庫審核平臺。的確，與很多互聯網公司相比，數據庫數十套的估摸并不是太大但與互聯網類公司不同，類似宜信這類金融類公司對數據庫的依賴性更大，大量的應用是重數據庫類的，且其使用復雜程度也遠比互聯網類的復雜。 作者：韓鋒 出處：DBAplus社群分享 Themis開源地址：https://github.com/CreditEaseDBA 拓展閱讀：宜信開源|數...

    wenhai.he 評論0 收藏0

  • 一文看懂 MySQL 高性能優化技巧實踐

    摘要：實際應用中，查詢的返回結果會有條記錄。修改保存文件后，重啟服務。通常優化至少到級別，最好能優化到指出使用哪個索引在該表找到行記錄。如果該值為，說明沒有使用索引，可以建立索引提高性能顯示實際使用的索引。 ...

    simon_chen 評論0 收藏0

發表評論

0條評論

IT那活兒

男|高級講師

TA的文章

閱讀更多
最新活動
閱讀需要支付1元查看
<