複盤eygle在甲骨文大會上演講中的樣本,看看什麼是大師的由點及面,複盤eygle

來源:互聯網
上載者:User

複盤eygle在甲骨文大會上演講中的樣本,看看什麼是大師的由點及面,複盤eygle

蓋總(eygle)在剛結束的甲骨文大會的演講中,通過一個簡單的UPDATE語句,為我們展示了什麼叫由點及面的最佳化,什麼叫由點及面的知識覆蓋度,不在於這個案具體如何操作,更應關注或更值得我們借鑒的是這種學習態度和方法思路,大師是如何煉成的?我想這個案例可以帶給我們一些啟迪。

 

下面就複盤一下這個案例的整個過程,註:著作權歸蓋總(eygle)所有~

 

問題描述:

問題的標題是:“並行更新成為系統瓶頸”

SQL:

UPDATE /*+ parallel(a, 8) */ tbl_a aSET name = (SELECT name FROM tbl_b WHERE id = a.id),        class = (SELECT class FROM tbl_b WHERE id = a.id)WHERE a.id IN (SELECT /*+ parallel(b, 8) */ id FROM tbl_b b);

現象是這條SQL執行時間非常長,從介紹看是有2.5分鐘。

 

最佳化過程:

1. 為了以下可以更清楚地說明問題,對這個SQL做了簡化處理,我們需要最佳化的是這條SQL:

UPDATE tbl_a aSET name = (SELECT name FROM tbl_b WHERE id = a.id),        class = (SELECT class FROM tbl_b WHERE id = a.id)WHERE a.id IN (SELECT id FROM tbl_b b);

我們建立兩張類比表:

SQL> create table tbl_a(          id number,          name varchar2(5),          class varchar2(5));Table created.SQL> create table tbl_b(          id number,          name varchar2(5),          class varchar2(5));Table created.SQL> create sequence seq_a cache 1000;Sequence created.SQL> create sequence seq_b cache 1000;Sequence created.

插入一些隨機資料:

begin  for i in 1 .. 100000 loop    insert into tbl_a values (seq_a.nextval, dbms_random.string('U', 5), dbms_random.string('U', 5));  end loop;  commit;end;/PL/SQL procedure successfully completed.SQL> select count(*) from tbl_a;  COUNT(*)------------     100000begin  for i in 1 .. 10000 loop    insert into tbl_b values (seq_b.nextval, dbms_random.string('U', 5), dbms_random.string('U', 5));  end loop;  commit;end;/PL/SQL procedure successfully completed.SQL> select count(*) from tbl_b;  COUNT(*)------------      10000


2. 執行原SQL語句

SQL> set timing onSQL> UPDATE tbl_a a          SET name = (SELECT name FROM tbl_b WHERE id = a.id),                 class = (SELECT class FROM tbl_b WHERE id = a.id)          WHERE a.id IN (SELECT id FROM tbl_b b);10000 rows updated.Elapsed: 00:00:07.42

需要7秒多的時間(雖然和樣本中2.5分鐘有差距,但僅為了說明最佳化的問題,時間上的差距可以忽略)。

3. 第一次最佳化

我們從這個SQL中可以看到,更新TBL_A表的ID列,但TBL_B表的SELECT有三次,即三次的全表掃描,那麼要是能減少TBL_B表檢索的次數,執行時間肯定可以減少。

SQL> UPDATE tbl_a a          SET (name, class) = (SELECT name, class FROM tbl_b WHERE id = a.id)          WHERE a.id IN (SELECT id FROM tbl_b b);10000 rows updated.Elapsed: 00:00:04.04

這樣的調整是符合SQL文法的,執行時間變為了4秒多,效果顯著。

 

4. 第二次最佳化

雖然執行時間減少了接近一半,但SQL中還是對TBL_B執行了兩次掃描,是否還可以減少一次?

SQL> UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class                      FROM tbl_a a, tbl_b b                      WHERE a.id = b.id)          SET name = b_name, class = b_class;SET name = b_name, class = b_class    *ERROR at line 4:ORA-01779: cannot modify a column which maps to a non key-preserved tableElapsed: 00:00:00.01

這樣就做到了只掃描一次TBL_B表,直接對子查詢更新,但此時報了一個錯誤,ORA-01779,

這就引出了non key-preserved table的概念。非索引值儲存表,楊長老的部落格(http://blog.itpub.net/4227/viewspace-195889/)中提到過這個錯誤:

“造成這個錯誤的原因是更新的列不是事實表的列,而是維度資料表的列。換句話說,如果兩張表關聯,其中一張表的關聯列是主鍵,那麼另一張表就是事實表,也就是說另一張表中的列就是可更新的;除非另一張表的關聯列也是主鍵,否則這張表就是不可更新的,如果更新語句涉及到了這張表,就會出現ORA-1799錯誤。如果是兩張表主鍵關聯,那麼無論更新那個表的欄位都可以。

其實這個限制的真正原因是Oracle要確保串連後更新的內容可以寫到一張表中,而這就要求串連方式必須是1對N或者1對1的串連。這樣才能確保串連後的結果集數量和事實表一致。從而使得Oracle對串連後子查詢的更新可以順利的更新到事實表中。”

a.id=b.id,我們是用TBL_B的id列作為條件更新,需要確保這列只會對應到TBL_B表的一行記錄,可以為表TBL_B的id列設定主鍵、唯一索引或唯一約束,三種操作,這裡選擇設定唯一約束:

SQL> alter table tbl_b add constraint uq_b_id unique(id);Table altered.

再次執行:

SQL> UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class                      FROM tbl_a a, tbl_b b                      WHERE a.id = b.id)          SET name = b_name, class = b_class;10000 rows updated.Elapsed: 00:00:00.12

執行時間一下僅為0.12秒。

上面如果TBL_A的ID列設定為主鍵,則為1對1的串連,如果僅是TBL_B的ID列為唯一約束,則為1對N的串連。

 

總結:

通過兩次最佳化,執行時間從7秒降到了0.12秒,雖然這裡的樣本資料未必和實際情況一致,但成比例的縮放足以說明這個問題,從這個案例可以看出,最佳化的本質就是少做事,原始SQL執行三次全表掃描,那目標就是減少全表掃描的次數,第一次最佳化的操作可能相對容易想到,但第二次最佳化的操作,就需要知道可以有這種文法,而且出現了ORA-01799的錯誤,還需要知道這種錯誤的根本原因是什麼,才能有可行的解決方案。

 

問題還沒完,以上說明了SQL語句的最佳化,下面就是針對這條SQL展開的知識。

假設上面的TBL_A和TBL_B表是屬於使用者bisal的,此時建立一個使用者phibisal,並授予最簡單的許可權:

SQL> create user phibisal identified by phibisal;User created.SQL> grant create session to phibisal;Grant succeeded.

bisal使用者建立這兩張表的public同義字:

SQL> create public synonym tbl_a for bisal.tbl_a;Synonym created.SQL> create public synonym tbl_b for bisal.tbl_b;Synonym created.

然後授予phibisal使用者對TBL_A表的讀和更新許可權:

SQL> grant select, update on tbl_a to phibisal;Grant succeeded.

此時phibisal登入後執行:

sqlplus phibisal/phibisalSQL> UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class                      FROM tbl_a a, tbl_b b                      WHERE a.id = b.id)          SET name = b_name, class = b_class;                FROM tbl_a a, tbl_b b                                    *ERROR at line 2:ORA-00942: table or view does not exist

會提示TBL_B不存在,因為使用者沒有該表的任何許可權,(註:此處和eygle的樣本中反饋不同,他提示的是ORA-01031: insufficient privileges)
如果授予phibisal對TBL_B表的讀許可權,

SQL> grant select on tbl_b to phibisal;Grant succeeded.

此時可以完成更新:

sqlplus phibisal/phibisalSQL> UPDATE tbl_a a          SET (name, class) = (SELECT name, class FROM tbl_b WHERE id = a.id)          WHERE a.id IN (SELECT id FROM tbl_b b);10000 rows updated.

但用如下SQL會提示許可權錯誤:

UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class                      FROM tbl_a a, tbl_b b                      WHERE a.id = b.id)          SET name = b_name, class = b_class;                FROM tbl_a a, tbl_b b                                    *ERROR at line 2:ORA-01031: insufficient privileges

即這種子查詢更新會因沒有TBL_B表的UPDATE許可權報錯。
但如果使用如下with文法,則可以正常執行:

SQL> UPDATE(WITH tmp AS (              SELECT b.name b_name, b.class b_class, a.name, a.class              FROM tbl_a a, tbl_b b              WHERE a.id = b.id)             )SET name = b_name, class = b_class;10000 rows updated.

做得更徹底一些:

SQL> revoke update on tbl_a from phibisal;Revoke succeeded.

撤消了phibisal使用者對TBL_A的更新許可權,按理說,phibisal使用者不應該能再更新TBL_A表了。
使用上面兩個調整後的SQL,確實如此:

sqlplus phibisal/phibisalSQL> UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class                      FROM tbl_a a, tbl_b b                      WHERE a.id = b.id)          SET name = b_name, class = b_class;                FROM tbl_a a, tbl_b b                                    *ERROR at line 2:ORA-01031: insufficient privileges     SQL> UPDATE tbl_a a          SET (name, class) = (SELECT name, class FROM tbl_b WHERE id = a.id)          WHERE a.id IN (SELECT id FROM tbl_b b);UPDATE tbl_a a       *ERROR at line 1:ORA-01031: insufficient privileges

但是,奇怪的是如下SQL可以執行:

SQL> UPDATE(WITH tmp AS (              SELECT b.name b_name, b.class b_class, a.name, a.class              FROM tbl_a a, tbl_b b              WHERE a.id = b.id)              SELECT * FROM tmp             )SET name = b_name, class = b_class;10000 rows updated.

這就從原理規則上,違背了許可權控制,看下版本:

SQL> select banner from v$version where rownum=1;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

這就是2014年7月提出的一個bug,在11.2.0.3、11.2.0.4、12.1等版本中都存在的一個問題,需要修正這個bug,相當於使用with文法,可以繞過使用者權限,對沒有許可權的表進行DML操作。

 

總結:

精髓不在於這個bug,而是在於從一條簡單的UPDATE語句,可以派生出如此豐富的知識,可謂舉一反三,受益匪淺。一方面需要我們能夠從原理上理解每一個概念,另一方面也要培養自己舉一反三,知識點由點及面的想法,做到真正的觸類旁通,這樣才能逐漸向大師靠攏,向大師學習。

著作權聲明:本文為博主原創文章,未經博主允許不得轉載。

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.