中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務條款》

高效的SQL（函數索引優化VIEW一例）

發布時間：2020-08-07 18:26:33 來源：ITPUB博客 閱讀：203 作者：desert_xu 欄目：關系型數據庫

高效的SQL（函數索引優化VIEW一例）

原創                     Oracle                    作者： lovehewenyu                    時間：2016-07-12 14:57:43                     242                                                                                                                    

高效的SQL（函數索引優化VIEW一例）

業務人員反映系統執行超級慢，查看系統資源發現CPU負載已經接近100%。挑戰的CASE來了，十分激動。哈哈哈。


1.遇到性能問題 先分析系統資源 ，發現CPU負載持續100%左右。11.2.0.4 2 nodes RAC架構，每個節點CPU負載都很高
System: bmcdb1                                      Tue Jun 28 17:17:06 2016
Load averages: 21.06, 17.79, 13.17
687 processes: 417 sleeping, 270 running
Cpu states:
CPU   LOAD   USER   NICE    SYS   IDLE  BLOCK  SWAIT   INTR   SSYS
 0   20.37  95.8%   0.0%   4.2%   0.0%   0.0%   0.0%   0.0%   0.0%
 2   22.15  95.8%   0.0%   4.2%   0.0%   0.0%   0.0%   0.0%   0.0%
 4   20.79  90.3%   0.0%   9.7%   0.0%   0.0%   0.0%   0.0%   0.0%
 6   19.88  91.1%   0.0%   8.9%   0.0%   0.0%   0.0%   0.0%   0.0%
 8   20.54  97.0%   0.0%   3.0%   0.0%   0.0%   0.0%   0.0%   0.0%
10   21.11  98.0%   0.0%   2.0%   0.0%   0.0%   0.0%   0.0%   0.0%
12   19.15  99.0%   0.0%   1.0%   0.0%   0.0%   0.0%   0.0%   0.0%
14   24.51  95.7%   0.0%   4.3%   0.0%   0.0%   0.0%   0.0%   0.0%
---   ----  -----  -----  -----  -----  -----  -----  -----  -----
avg  21.06  95.4%   0.0%   4.6%   0.0%   0.0%   0.0%   0.0%   0.0%


System: bmcdb2                                      Tue Jun 28 17:17:26 2016
Load averages: 22.63, 18.72, 13.23
695 processes: 450 sleeping, 244 running, 1 zombie
Cpu states:
CPU   LOAD   USER   NICE    SYS   IDLE  BLOCK  SWAIT   INTR   SSYS
 0   21.69  98.2%   0.0%   1.8%   0.0%   0.0%   0.0%   0.0%   0.0%
 2   21.87  99.0%   0.0%   1.0%   0.0%   0.0%   0.0%   0.0%   0.0%
 4   23.55  96.2%   0.0%   3.8%   0.0%   0.0%   0.0%   0.0%   0.0%
 6   22.04  98.0%   0.0%   2.0%   0.0%   0.0%   0.0%   0.0%   0.0%
 8   21.89  94.9%   0.0%   5.1%   0.0%   0.0%   0.0%   0.0%   0.0%
10   22.55  97.8%   0.0%   2.2%   0.0%   0.0%   0.0%   0.0%   0.0%
12   24.17  96.0%   0.0%   4.0%   0.0%   0.0%   0.0%   0.0%   0.0%
14   23.27  96.4%   0.0%   3.6%   0.0%   0.0%   0.0%   0.0%   0.0%
---   ----  -----  -----  -----  -----  -----  -----  -----  -----
avg  22.63  97.0%   0.0%   3.0%   0.0%   0.0%   0.0%   0.0%   0.0%


2.分析AWR報告
節點1
Snap Id Snap Time Sessions Cursors/Session Instances
Begin Snap: 17049 28-Jun-16 15:00:15 241 6.7 2
End Snap: 17050 28-Jun-16 16:00:19 282 6.2 2
Elapsed: 60.08 (mins) 
DB Time: 1,710.37 (mins)
Top 10 Foreground Events by Total Wait Time
Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
DB CPU 24.2K 23.6 
latch: cache buffers chains 54,799 9199.7 168 9.0 Concurrency   <==latch: cache buffers chains等待嚴重
log file sync 206,339 1927.7 9 1.9 Commit
direct path read 91,627 367 4 .4 User I/O
latch free 2,307 319 138 .3 Other
latch: row cache objects 2,775 309 111 .3 Concurrency
gc current grant busy 172,410 220.6 1 .2 Cluster
gc cr multi block request 110,803 119.9 1 .1 Cluster
reliable message 140,184 102.2 1 .1 Other
gc buffer busy acquire 13,083 99.8 8 .1 Cluster


節點2
Snap Id Snap Time Sessions Cursors/Session Instances
Begin Snap: 17049 28-Jun-16 15:00:15 244 3.1 2
End Snap: 17050 28-Jun-16 16:00:20 289 3.1 2
Elapsed: 60.08 (mins) 
DB Time: 1,813.76 (mins)
Top 10 Foreground Events by Total Wait Time
Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
DB CPU 24.1K 22.2 
latch: cache buffers chains 52,429 9193.1 175 8.4 Concurrency   <==latch: cache buffers chains等待嚴重
log file sync 206,024 1777.8 9 1.6 Commit
latch: row cache objects 2,115 382.1 181 .4 Concurrency
latch free 2,191 364.7 166 .3 Other
gc buffer busy acquire 20,663 255.9 12 .2 Cluster
gc cr multi block request 153,940 245.7 2 .2 Cluster
gc cr block 2-way 109,222 169.7 2 .2 Cluster
gc current grant busy 121,973 143.7 1 .1 Cluster
gc current block 2-way 79,675 119.5 1 .1 Cluster


3.找到問題SQL，優化SQL減少邏輯讀
latch: cache buffers chains等待嚴重CASE處理
參考：Troubleshooting 'latch: cache buffers chains' Wait Contention (文檔 ID 1342917.1)
SQL ordered by Gets =>Segments by Logical Reads
結果找出問題SQL
SQL_ID  68uj68brn2nvs
SQL TEXT select sum(a.N_RINGING) as N_RINGING, sum(a.T_RINGING) as T_RINGING, sum(a.N_INBOUND) as N_INBOUND, sum(a.T_INBOUND) as T_INBOUND, sum(a.N_TRANSFERS) as N_TRANSFERS, sum(a.N_WORK) as N_WORK, sum(a.T_WORK) as T_WORK from V_RPT_AGENT_DAY a where a.AGENT_ID='2193' and a.TIME_DAY='20160628'


4.優化問題SQL
根據AWR中的SQL_ID查詢執行計劃
select * from table(dbms_xplan.display_awr('68uj68brn2nvs'));


SQL_ID 68uj68brn2nvs
--------------------
select sum(a.N_RINGING) as N_RINGING,sum(a.T_RINGING) as T_RINGING,
sum(a.N_INBOUND) as N_INBOUND, sum(a.T_INBOUND) as
T_INBOUND,sum(a.N_TRANSFERS) as N_TRANSFERS, sum(a.N_WORK) as N_WORK,
sum(a.T_WORK) as T_WORK from V_RPT_AGENT_DAY a where a.AGENT_ID='2193'
and a.TIME_DAY='20160628'


Plan hash value: 2468449739


----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                      |       |       | 51984 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| IVRREPORTDETAIL      |     1 |    24 |     3   (0)| 00:00:01 |
|   2 |   INDEX UNIQUE SCAN         | PK_CALLID            |     1 |       |     2   (0)| 00:00:01 |
|   3 |  SORT AGGREGATE             |                      |     1 |   125 |            |          |
|   4 |   HASH JOIN                 |                      |  2846 |   347K| 51984   (2)| 00:10:24 |
|   5 |    HASH JOIN                |                      |  1906 |   191K| 46029   (2)| 00:09:13 |
|   6 |     VIEW                    | V_RPT_AGENT_DAY_TEMP |  1287 | 70785 | 40095   (1)| 00:08:02 |
|   7 |      HASH GROUP BY          |                      |  1287 |   134K| 40095   (1)| 00:08:02 |
|   8 |       HASH JOIN             |                      |  1287 |   134K| 40094   (1)| 00:08:02 |
|   9 |        TABLE ACCESS FULL    | OBJECT               |     1 |    24 |     6   (0)| 00:00:01 |
|  10 |        INDEX FAST FULL SCAN | PK_R_21_STAT_RES     |   136K|    10M| 40087   (1)| 00:08:02 |
|  11 |     VIEW                    | V_DETAIL_TEMP        | 13919 |   652K|  5934   (2)| 00:01:12 |
|  12 |      HASH GROUP BY          |                      | 13919 |   611K|  5934   (2)| 00:01:12 |
|  13 |       TABLE ACCESS FULL     | IVRREPORTDETAIL      |   553K|    23M|  5895   (2)| 00:01:11 |
|  14 |    VIEW                     | V_DETAIL_TEMP2       | 14036 |   301K|  5955   (3)| 00:01:12 |
|  15 |     HASH GROUP BY           |                      | 14036 |   246K|  5955   (3)| 00:01:12 |
|  16 |      TABLE ACCESS FULL      | IVRREPORTDETAIL      |   551K|  9685K|  5916   (2)| 00:01:11 |
----------------------------------------------------------------------------------------------------


|   8 |       HASH JOIN             |                      |  1287 |   134K| 40094   (1)| 00:08:02 |
|   9 |        TABLE ACCESS FULL    | OBJECT               |     1 |    24 |     6   (0)| 00:00:01 |
|  10 |        INDEX FAST FULL SCAN | PK_R_21_STAT_RES     |   136K|    10M| 40087   (1)| 00:08:02 |
注意：10行消耗COST很多，13,16行TAF都值得關注。


執行一次語句收集更準確的執行計劃。
Plan hash value: 1272971961


-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                      |      1 |        |      1 |00:00:30.19 |    1424K|       |       |          |
|*  1 |  TABLE ACCESS BY INDEX ROWID| IVRREPORTDETAIL      |    556K|      1 |   1881 |00:00:06.76 |    1233K|       |       |          |
|*  2 |   INDEX UNIQUE SCAN         | PK_CALLID            |    556K|      1 |    556K|00:00:03.22 |     676K|       |       |          |
|   3 |  SORT AGGREGATE             |                      |      1 |      1 |      1 |00:00:30.19 |    1424K|       |       |          |
|*  4 |   HASH JOIN                 |                      |      1 |     21M|      1 |00:00:30.19 |    1424K|  1857K|  1857K| 5305K (0)|
|   5 |    VIEW                     | V_DETAIL_TEMP2       |      1 |    551K|  14066 |00:00:01.75 |   21454 |       |       |          |
|   6 |     HASH GROUP BY           |                      |      1 |    551K|  14066 |00:00:01.74 |   21454 |    36M|  6735K| 2658K (0)|
|*  7 |      TABLE ACCESS FULL      | IVRREPORTDETAIL      |      1 |    551K|    554K|00:00:00.61 |   21454 |       |       |          |
|*  8 |    HASH JOIN                |                      |      1 |    362K|      1 |00:00:28.43 |    1402K|  1229K|  1229K|  421K (0)|
|   9 |     VIEW                    | V_RPT_AGENT_DAY_TEMP |      1 |   6153 |      1 |00:00:13.00 |     148K|       |       |          |
|  10 |      HASH GROUP BY          |                      |      1 |   6153 |      1 |00:00:13.00 |     148K|   691K|   691K|  704K (0)|
|* 11 |       HASH JOIN             |                      |      1 |   6153 |     96 |00:00:12.88 |     148K|  1245K|  1245K|  433K (0)|
|* 12 |        TABLE ACCESS FULL    | OBJECT               |      1 |      5 |      1 |00:00:00.01 |      15 |       |       |          |
|* 13 |        INDEX FAST FULL SCAN | PK_R_21_STAT_RES     |      1 |    136K|  43104 |00:00:12.94 |     148K|       |       |          |
## E-Rows=136k與A-Rows=43104 相差4倍左右，執行計劃值得關注 ##
|  14 |     VIEW                    | V_DETAIL_TEMP        |      1 |    553K|  13950 |00:00:15.43 |    1254K|       |       |          |
|  15 |      HASH GROUP BY          |                      |      1 |    553K|  13950 |00:00:15.42 |    1254K|    59M|  4907K| 3047K (0)|
|* 16 |       TABLE ACCESS FULL     | IVRREPORTDETAIL      |      1 |    553K|    556K|00:00:00.60 |   21454 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter(("T1"."CUSTHANGUPTIME" IS NOT NULL AND "T1"."RINGTIME" IS NOT NULL))
   2 - access("T1"."CALLID"=:B1)
   4 - access("C"."TIME"="A"."TIME_DAY" AND "C"."USERID"="A"."AGENT_ID")
   7 - filter(("T"."TURNONTIME" IS NOT NULL AND "T"."CUSTHANGUPTIME" IS NULL))
   8 - access("A"."TIME_DAY"="B"."TIME" AND "A"."AGENT_ID"="B"."USERID")
  11 - access("OBJECT_ID"="O"."OBJECT_ID")
  12 - filter((SUBSTR("O"."OBJECT_NAME",1,4)='2193' AND "O"."OBJECT_TYPE_ID"=0))
  13 - filter(SUBSTR("TIME_KEY",1,8)='20160628')
  16 - filter("T"."RINGTIME" IS NOT NULL)
## filter部分值得關注,主要優化方法優化函數SUBSTR值列與NULL值列 ##
## 使用INDEX RANGE SCAN來代替 INDEX FAST FULL SCAN
## 函數列可以增加函數索引,NULL值列可以添加組合索引 ##
 
5.分析業務SQL
5.1 根據業務人員反應這段問題SQL是時時更新的業務類型。
5.2 語句雖然簡單，但是是多個VIEW嵌套而成，想優化還需要找到基表


bmc_etl.V_RPT_AGENT_DAY 視圖包含以下表


-bmc_etl.V_RPT_AGENT_DAY_temp   a,
---FROM bmc_etl.V_RPT_AGENT_NO_AGG
----bmc_etl.R_AGENT_TFSP_NO_AGG U,
------bmc_etl.R_21_STAT_RES  <=基表
----bmc_etl.V_O_AGENT A
------ bmc_etl.object <=基表
-bmc_etl.v_detail_temp  b,      
---from cms.ivrreportdetail t<=基表      
-bmc_etl.v_detail_temp2 c
---from cms.ivrreportdetail t


添加函數索引,并收集統計信息
  12 - filter((SUBSTR("O"."OBJECT_NAME",1,4)='2193' AND "O"."OBJECT_TYPE_ID"=0))
  13 - filter(SUBSTR("TIME_KEY",1,8)='20160628')


 create index idx_sub_R_21 on R_21_STAT_RES (SUBSTR(TIME_KEY, 1, 8));
 create index idx_sub_object on object (substr(object_name,1,4));
 exec dbms_stats.gather_table_stats(user,'R_21_STAT_RES',cascade=>true);
 exec dbms_stats.gather_table_stats(user,'OBJECT',cascade=>true);


Execution Plan
----------------------------------------------------------
Plan hash value: 3127161072


----------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                      |     1 |   142 | 12204   (2)| 00:02:27 |
|*  1 |  TABLE ACCESS BY INDEX ROWID      | IVRREPORTDETAIL      |     1 |    24 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN               | PK_CALLID            |     1 |       |     2   (0)| 00:00:01 |
|   3 |  SORT AGGREGATE                   |                      |     1 |   142 |            |          |
|*  4 |   HASH JOIN                       |                      |    14 |  1988 | 12204   (2)| 00:02:27 |
|*  5 |    HASH JOIN                      |                      |     4 |   340 |  6321   (2)| 00:01:16 |
|   6 |     VIEW                          | V_RPT_AGENT_DAY_TEMP |     1 |    54 |   401   (1)| 00:00:05 |
|   7 |      HASH GROUP BY                |                      |     1 |   125 |   401   (1)| 00:00:05 |
|   8 |       NESTED LOOPS                |                      |    37 |  4625 |   401   (1)| 00:00:05 |
|*  9 |        TABLE ACCESS BY INDEX ROWID| OBJECT               |     1 |    23 |     2   (0)| 00:00:01 |
|* 10 |         INDEX RANGE SCAN          | IDX_SUB_OBJECT       |     1 |       |     1   (0)| 00:00:01 |
|* 11 |        INDEX UNIQUE SCAN          | PK_R_21_STAT_RES     |    36 |  3672 |   399   (1)| 00:00:05 |
|* 12 |         INDEX RANGE SCAN          | IDX_SUB_R_21         | 16739 |       |   110   (0)| 00:00:02 |
|  13 |     VIEW                          | V_DETAIL_TEMP2       |  2999 | 92969 |  5920   (2)| 00:01:12 |
|  14 |      HASH GROUP BY                |                      |  2999 | 53982 |  5920   (2)| 00:01:12 |
|* 15 |       TABLE ACCESS FULL           | IVRREPORTDETAIL      |  4240 | 76320 |  5919   (2)| 00:01:12 |
|  16 |    VIEW                           | V_DETAIL_TEMP        |  3013 |   167K|  5883   (2)| 00:01:11 |
|  17 |     HASH GROUP BY                 |                      |  3013 |   132K|  5883   (2)| 00:01:11 |
|* 18 |      TABLE ACCESS FULL            | IVRREPORTDETAIL      |  4262 |   187K|  5881   (2)| 00:01:11 |
----------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter("T1"."CUSTHANGUPTIME" IS NOT NULL AND "T1"."RINGTIME" IS NOT NULL)
   2 - access("T1"."CALLID"=:B1)
   4 - access("A"."TIME_DAY"="B"."TIME" AND "A"."AGENT_ID"="B"."USERID")
   5 - access("C"."TIME"="A"."TIME_DAY" AND "C"."USERID"="A"."AGENT_ID")
   9 - filter("O"."OBJECT_TYPE_ID"=0)
  10 - access(SUBSTR("OBJECT_NAME",1,4)='2193')
  11 - access(SUBSTR("TIME_KEY",1,8)='20160628')
       filter("OBJECT_ID"="O"."OBJECT_ID")
  12 - access(SUBSTR("TIME_KEY",1,8)='20160628')
  15 - filter("T"."TURNONTIME" IS NOT NULL AND "T"."USERID"='2193' AND "T"."CUSTHANGUPTIME" IS
              NULL)
  18 - filter("T"."RINGTIME" IS NOT NULL AND "T"."USERID"='2193')

 


Statistics
----------------------------------------------------------
         50  recursive calls
          0  db block gets
     114148  consistent gets
          0  physical reads
          0  redo size
        969  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
## 函數索引部分已經從filter轉成access。COST也從5W降到1W多。現在優化NULL值列部分，使用組合索引。
## 此業務SQL經使用組合索引測試，效果不明顯。


優化后一周后，CPU負載情況如下
System: bmcdb1                                      Mon Jul  4 14:49:38 2016
Load averages: 0.47, 0.47, 0.51
532 processes: 440 sleeping, 92 running
Cpu states:
CPU   LOAD   USER   NICE    SYS   IDLE  BLOCK  SWAIT   INTR   SSYS
 0    0.47  22.2%   0.0%   2.2%  75.6%   0.0%   0.0%   0.0%   0.0%
 2    0.46  28.5%   0.0%   0.8%  70.7%   0.0%   0.0%   0.0%   0.0%
 4    0.49  25.7%   0.0%   2.0%  72.3%   0.0%   0.0%   0.0%   0.0%
 6    0.47  41.0%   0.0%   3.4%  55.6%   0.0%   0.0%   0.0%   0.0%
 8    0.47  22.2%   0.0%   0.6%  77.2%   0.0%   0.0%   0.0%   0.0%
10    0.46  19.4%   0.0%   2.6%  78.0%   0.0%   0.0%   0.0%   0.0%
12    0.43  34.7%   0.0%   1.8%  63.6%   0.0%   0.0%   0.0%   0.0%
14    0.50  24.2%   0.0%   1.6%  74.3%   0.0%   0.0%   0.0%   0.0%
---   ----  -----  -----  -----  -----  -----  -----  -----  -----
avg   0.47  27.3%   0.0%   2.0%  70.8%   0.0%   0.0%   0.0%   0.0%


總結：
 1.復雜的業務類型如果想使用VIEW，請將核心表數據減少成"最優"效數據,也就是無關的數據都砍掉，無需關聯。并考慮數據的累積，以天/月/年為基準使用有效數據。


向AI問一下細節

免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。

AI
助
手
临沭县| 济宁市| 大理市| 阜康市| 许昌县| 临城县| 藁城市| 丹巴县| 宁远县| 辽中县| 南澳县| 望都县| 宿州市| 阜平县| 阜阳市| 镶黄旗| 永嘉县| 鄢陵县| 会理县| 北川| 霸州市| 罗源县| 同仁县| 铜鼓县| 清新县| 湘潭市| 大方县| 开阳县| 翁源县| 德安县| 阜新| 固原市| 合川市| 滨州市| 临江市| 科技| 康乐县| 乐昌市| 德州市| 塔城市| 南城县|