程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> DB2數據庫 >> DB2教程 >> DB2 V9.7 語句集中器的使用

DB2 V9.7 語句集中器的使用

編輯:DB2教程

簡介

在 DB2 數據庫中存在兩種類型的 SQL 語句,一種為動態 SQL,一種為靜態 SQL 。靜態 SQL 的執行計劃是在 bind 包到數據庫時就已經確定,執行時只需要把執行計劃調出來即可;動態 SQL 的執行計劃需要每次執行時進行編譯,如果下次執行時,執行計劃已經不在包緩存中則需要重新編譯該語句。

在 OLTP 環境下,每秒鐘需要執行的 SQL 非常多,如果這些 SQL 語句都是動態語句,則都需要大量的 CPU 時間進行編譯。 DB2 判斷一個動態 SQL 語句的執行計劃是否在包緩存中時采用的是 HASH 算法,該算法根據 SQL 語句的文本進行 HASH,SQL 文本即使只有一個字母的大、小寫不同,也會造成 HASH 值不同如果 HASH 值不同,則認為是兩個不同的 SQL 語句。

對下面的兩個語句 DB2 就認為是不同的 SQL 。

select firstnme,lastname from employee where empno='000020' 
 select firstnme,lastname from employee where empno='000070'

上面兩個語句雖然在在 Where 條件處只有一個數字差異,DB2 HASH 算法也會認為這是兩個不同的 SQL 。但是 DB2 為他們生成的執行計劃都是一樣的,我們使用 db2expln 工具獲得執行計劃如下。執行計劃顯示上面兩個 SQL 語句都是使用索引 PK_EMPLOYEE 先獲取 RID,然後根據 RID 再讀取具體的數據。

                        Rows 
   RETURN 
   (  1) 
    Cost 
    I/O 
    | 
     1 
   FETCH 
   (  2) 
   7.58163 
     1 
  /----+----\ 
  1      42 
   IXSCAN  TABLE: DB2INST1 
   (  3)    EMPLOYEE 
   0.0165581    Q1 
  0 
    | 
    42 
 INDEX: DB2INST1 
  PK_EMPLOYEE 
    Q1

雖然兩個 SQL 的執行計劃是相同的,但是 DB2 為了獲取執行計劃需要對兩個 SQL 都要進行編譯,消耗了 CPU 。這種消耗在每秒鐘執行成千條 SQL 語句的 OLTP 環境下,對性能的影響是比較大的。

語句集中器的啟用

DB2V9.7 推出了語句集中器的功能,語句集中器在數據庫服務器上修改動態 SQL 語句,以使類似而不等同的 SQL 語句可以共享同一個執行計劃。如果啟動了語句集中器,上述兩個 SQL 只需要編譯一次即可。

在聯機事務處理(OLTP)系統中,可能會反復生成包含不同字面值的簡單語句。在此類工作負載中,重新編譯語句的成本會導致開銷大幅增加。語句集中器通過允許重復使用已編譯的語句(而不考慮字面值)來消除此開銷。

缺省情況下,語句集中器處於禁用狀態。如果希望對數據庫中的所有動態語句啟用語句集中器,我們需要將 stmt_conc 數據庫配置參數設置為 LITERALS 。不過 DB2 只會將前 100000 個字面值才進行替換;其余字面值保持不變,一般情況下這也能滿足我們的要求。

db2 get db cfg for sample |grep "CON" 
語句集中器 (STMT_CONC) = OFF

如果並不希望對所有 SQL 啟動語句集中器,只是希望指定連接在執行動態 SQL 時進行語句集中,則我們可以在客戶機上啟動語句集中器,需要在 db2cli.ini 配置文件中設置:

StmtConcentrator = WITHLITERALS

默認情況下連接的語句集中器是否啟動由 Server 的配置決定。如果設置 StmtConcentrator 的值為 OFF,表示連接的語句集中器關閉;如果 StmtConcentrator 為 WITHLITERALS 表示啟動語句集中器。當語句集中器啟動後,所有 Server 支持集中的語句將共享執行計劃。 db2cli.ini 中的參數 StmtConcentrator 影響的是連接的 SQL_ATTR_STMT_CONCENTRATOR 屬性,我們也可以在 ODBC、JDBC 程序中直接設置連接的這個屬性。

我們應優先考慮在客戶機級別啟用語句集中器,首先它允許在最精細的級別控制語句集中器,其次,它是在整個 DB2 產品系列中啟用語句集中器的唯一一致方式。

語句集中過程導致修改動態語句,那麼原始語句和修改後的語句都將顯示在說明輸出中。如果語句集中器已修改原始語句文本,那麼事件監視器邏輯監視元素以及 MON_GET_ACTIVITY_DETAILS 表函數的輸出都將顯示原始語句。其他監視器界面將僅顯示修改後的語句文本。

我們修改數據配置參數 STMT_CONC 對所有連接啟動語句集中器。

db2 update db cfg for sample using STMT_CONC LITERALS

然後我們分別執行:

select firstnme,lastname from employee where empno='000020' 
 select firstnme,lastname from employee where empno='000070'

我們使用下面語句獲取 SQL 語句的編譯、執行情況:

db2 get snapshot for dynamic sql on sample 
 
執行數 = 0 
編譯數 = 0 
最差預編譯時間(毫秒) = 0 
最佳預編譯時間(毫秒) = 0 
 ---------------------------------- 省略 ------------------------------ 
語句文本 = select firstnme,lastname from employee where empno='000020' 
 
執行數 = 0 
編譯數 = 0 
最差預編譯時間(毫秒) = 0 
最佳預編譯時間(毫秒) = 0 
 ---------------------------------- 省略 ------------------------------ 
語句文本 = select firstnme,lastname from employee where empno='000070' 
 
執行數 = 2 
編譯數 = 1 
最差預編譯時間(毫秒) = 218 
最佳預編譯時間(毫秒) = 218 
 ---------------------------------- 省略 ------------------------------ 
語句文本 = select firstnme,lastname from employee where empno=:L0

我們看到兩個原始的 SQL 語句編譯次數、執行次數、編譯時間均為 0,同時有個用” :L0 ”參數標識的語句編譯次數為 1,執行次數為 2,編譯時間為 218 毫秒。

語句集中器限制

由於語句集中過程將更改語句文本,因此會對執行計劃的選擇產生影響。如果程序包高速緩存中的類似語句具有大量類似的執行,那麼應該使用語句集中器。如果一個語句中的不同字面值導致執行計劃顯著不同,那麼不應對該語句啟用語句集中器。

下面我們看語句集中器的對性能產生影響的例子。

我們將創建一個表包含 10 萬行, Col1 數據順序增長,Col2 中值為 5 的行數 90001,Col2 的其他行在 10000 之內均勻分布,同時在 Col2 上存在一個索引。我們將考查當 col2 數據分布的不均衡時,語句集中器是否啟動對執行計劃的影響。

測試表創建腳本:

drop table test; 
 CREATE TABLE test 
 ( 
 col1 int, 
 col2 int, 
 padding char(50) 
 ); 
 
 create index idx_test_col2 on test(col2); 
 
 INSERT INTO test (col1, col2,padding) 
 WITH TEMP (COUNTER, col1, col2,padding) AS 
 ( 
 VALUES (0, 0,MOD(INT(RAND() * 10000), 10000),'A') 
 UNION ALL 
 SELECT 
 (COUNTER + 1),(COUNTER + 1),MOD(INT(RAND() * 10000), 10000),'A' 
 FROM 
 TEMP 
 WHERE 
 (COUNTER + 1) < 10000 
 ) 
 SELECT 
 col1, col2,padding 
 FROM 
 TEMP; 
  
 INSERT INTO test (col1, col2,padding) 
 WITH TEMP (COUNTER, col1, col2,padding) AS 
 ( 
 VALUES (10000, 10000,5,'A') 
 UNION ALL 
 SELECT 
 (COUNTER + 1),(COUNTER + 1),5,'A' 
 FROM 
 TEMP 
 WHERE 
 (COUNTER + 1) < 100000 
 ) 
 SELECT 
 col1, col2,padding 
 FROM 
 TEMP; 
    
 runstats on table db2inst1.test with DISTRIBUTION ON all COLUMNS and indexes all ;

STMT_CONC 關閉時的執行計劃

我們執行一下命令確認 STMT_CONC 關閉:

db2 update db cfg for sample using STMT_CONC off

我們准備比較下面兩個 SQL 的執行計劃。

Select * from test where col2=1; 
 Select * from test where col2=5;

首先我們看到 Col2=1 的執行計劃如下,我們看到 DB2 首先對 IDX_TEST_COL2 進行掃描,然後根據 RID 去表中讀取數據,整個語句的成本是 15.1589,其中 IO 成本是 2,返回的結果行數估計為 1.56909 。

db2 set current explain mode explain 
 db2 select * from test where col2=1 
 db2exfmt -d sample -w -1 -n % -s % -# 0 – t 
 
 Total Cost: 15.1589 
 Query Degree: 1 
 
   Rows 
  RETURN 
  (  1) 
   Cost 
   I/O 
   | 
  1.56909 
  FETCH 
  (  2) 
  15.1589 
    2 
  /---+----\ 
   1.56909   100000 
   IXSCAN  TABLE: DB2INST1 
   (  3)    TEST 
   7.59362    Q1 
    1 
    | 
   100000 
 INDEX: DB2INST1 
 IDX_TEST_COL2 
    Q1

首先我們看到 Col2=5 的執行計劃如下,我們看到 DB2 不再對 IDX_TEST_COL2 進行掃描,而是直接進行表掃描,整個語句的成本是 953.761,其中 IO 成本是 878,返回的結果行數估計為 90001 。

db2 set current explain mode explain 
 db2 select * from test where col2=5 
 db2exfmt -d sample -w -1 -n % -s % -# 0 – t 
 
 Total Cost: 953.761 
 Query Degree: 1 
 
    Rows 
    RETURN 
    (  1) 
    Cost 
     I/O 
     | 
    90001 
    TBSCAN 
    (  2) 
    953.761 
     878 
     | 
    100000 
  TABLE: DB2INST1 
    TEST 
     Q1

DB2 使用上面兩個執行計劃獲取數據時,SQL 花費的時間分別時

STMT_CONC 開啟時的執行計劃

我們執行一下命令確認 STMT_CONC 打開:

db2 update db cfg for sample using STMT_CONC LITERALS

首先我們看到 Col2=1 和 Col2=5 時執行計劃相同,這符合我們對 STMT_CONC 設置為 LITERALS 後的預期。執行計劃顯示 DB2 首先對 IDX_TEST_COL2 進行掃描,然後根據 RID 去表中讀取數據,整個語句的成本是 20.461,其中 IO 成本是 2.69924,返回的結果行數估計為 15.748 。

這個執行計劃與上面的執行計劃比較後,我們發現在 Col2=1 時結果集大小被高了,而 col2=5 時結果集大小有被嚴重低估了,而且由於成本、結果集的誤估計導致執行計劃也發生了變化,變成了先掃描 IDX_TEST_COL2 進行掃描,然後根據 RID 去表中讀取數據。

db2 set current explain mode explain 
 db2 
   "select * from test where col2=5" 
 db2exfmt -d sample -w -1 -n % -s % -# 0 – t 
 
 Total Cost: 20.461 
 Query Degree: 1 
 
                       Rows 
   RETURN 
   (  1) 
   Cost 
    I/O 
    | 
   15.748 
   FETCH 
   (  2) 
   20.461 
   2.69924 
  /---+----\ 
   15.748    100000 
   IXSCAN  TABLE: DB2INST1 
   (  3)    TEST 
   7.60197    Q1 
  1 
    | 
   100000 
 INDEX: DB2INST1 
  IDX_TEST_COL2 
    Q1

執行時間比較

我們使用下面腳本測試在 STMT_CONC 打開或者關閉的情況下,查詢時間的變化:

db2 "connect to sample" 
 db2 "values current timestamp" 
 db2 "select * from test where col2=$i" >/dev/null 
 db2 "values current timestamp"

其中 i 取值分別為 1 和 5,執行腳本後獲得以下結果。

  STMT_CONC=off STMT_CONC=LITERALS Col2=1 0.607 秒 0.732 秒 Col2=5 17.73 秒 21.65 秒

測試結果表明在 col2=5 時由於啟動了連接集中器導致執行計劃選擇錯誤,執行時間增加了約 3 秒鐘

結束語

DB2 通過配置參數 STMT_CONC 決定是否在數據庫級別上啟動語句集中器。如果我們希望精確控制語句集中器的使用,可以在客戶端上進行配置。語句集中器減少了動態 SQL 語句的編譯次數,比較適用於有大量小 SQL 執行的 OLTP 環境,對 OLAP 環境或者一個語句中的不同字面值導致執行計劃顯著不同,那麼需要慎重考慮語句集中器的啟用。

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved