SQL優化提升效能 真實體驗屌絲變土豪

        在工作中我接觸到的程式設計師，很多完全沒有SQL優化這個概念，在他們看來，只要這條語句能用就可以，不管快慢如何。而且就算他覺得慢，大部分人都會說是資料庫的原因，而不認為是自己寫的SQL太爛，所以完全忽略SQL語句本身的效能問題。下面來看看一個簡單的SQL效能提升，給你飛一般的感覺！

環境說明：oracle 11g R2 64位 memory_max_target =3200m sga_max_size=3G   --- 機子很爛，不過效果會更明顯，嘿嘿

set timing on --顯示時間
create table t (x int); ---建表t
alter system flush shared_pool;  ---清空共享池

這是一般程式設計師或者資料庫開發人員寫的，一個簡單的儲存過程，實現向t表超如100w條記錄。
create or replace procedure p1
as 
begin
    for i in 1..1000000
    loop
          execute immediate
          'insert into t values ('||i||')';
commit;
    end loop;
end;
/
1、第一次，效能最差的時候
這裡先執行一遍，是為了將儲存過程建立起來。下次直接呼叫它。
重新建立t表。
SQL> create table t (x int);
表已建立。
SQL> alter system flush shared_pool;
系統已更改。
SQL> exec p1;
PL/SQL 過程已成功完成。
已用時間:  00: 07: 15.56
7分25秒，也就是平均每秒插入2293行，哎呀！算算不錯喲！

由於上面的過程p1，因為沒有繫結變數，於是每個語句必須得硬解析1次，執行1次，硬解析了100w次，
或許有人就會想：什麼是硬解析？既然有硬解析，應該也有軟解析吧？
檢視共享池中的執行情況:
select s.sql_text,ssql_id,s.parse_calls,s.executions
from v$sql s
where sql_text like '%insert into t values%';
由於記錄很多就不列出來，免得佔地兒。

2、第一次提升：
接下來看看繫結變數後的情況：
當然為了公平。這裡還是先刪除t表，病清空共享池：
SQL> drop table t purge;
SQL> create table t (x int);
SQL> alter system flush shared_pool;
先建立儲存過程p2
create or replace procedure p2
as 
begin
    for i in 1..1000000
    loop
          execute immediate
          'insert into t values(:x)' using i;
commit;
    end loop;
end;
/
重複上面的步驟，後再次執行過程p2
結果：
SQL> exec p2;
PL/SQL 過程已成功完成。

已用時間:  00: 02: 11.41

看見沒，2分9秒。足足快了三倍多，平均每秒7751條記錄,快了吧！
這是因為繫結變數後hash成為一個唯一的hash值，解析一次，執行了100w次。語句執行硬解析1次，之後的999999次都是軟解析。(軟解析出來了吧，至於什麼是硬解析、什麼是軟解析不是這裡的重點，就不做說明）
檢視共享池中的執行情況:
SQL> select s.SQL_TEXT,s.sql_id,s.parse_calls,s.executions
  2  from v$sql s
  3  where sql_text like '%insert into t values%';
SQL_TEXT                                 SQL_ID        PARSE_CALLS EXECUTIONS
---------------------------------------- ------------- ----------- ----------
insert into t values(:x)                 ff53v38uhtzbz           1    1000000

就一條記錄。

來學習一點知識：
      execute immediate是一種動態的SQL寫法，常用於於表名欄位名是變數，入參的情況，由於表名都不知道，所以當然不能直接寫SQL語句了，所以要靠動態SQL語句根據傳入表名引數，來拼成一條SQL語句，由  execute immediate呼叫執行，。但是當知道表名的時候就是多此一舉了。一般來說，靜態sql會知道使用繫結變數。動態SQL的特點是執行過程中再解析，而靜態SQL的特點是編譯的過程解析好了，這樣又節省了時間。
3、開始我們的第二次提升：
create or replace procedure p3
as 
begin
    for i in 1..1000000
    loop
         insert into t values(i);
commit;
    end loop;
end;
/
SQL> exec p3;
PL/SQL 過程已成功完成。
已用時間:  00: 02: 08.79
稍微快了一點點，由於是機器和資料量的問題，但是還有提升了。

再來學習一點知識：

       commit 觸發LGWR 將REDO BUFFER 寫到redo buffer中，並且將回滾段的活動事務標記為不活動，同時讓回滾段中記錄對應前映象記錄的所在位置標記為可以重寫，（commit 不是寫資料的動作，將資料從databuffer刷出磁碟是有CKPT決定的，）所以commit做的事情開銷並不大，單詞提交可能需要0.001秒即可完成，不管多大批量操作後的提交，針對commit而言，也是做這三件事情，所花費的總時間不可能超過1秒鐘加入1條記錄執行完後commit的提交時間需要0.8秒，但是100w條記錄，就遠遠不止0.8秒。

4、來體驗一下3次飛躍：
create or replace procedure p4
as 
begin
    for i in 1..1000000
    loop
         insert into t values(i);
    end loop;
commit;
end;
/
SQL> exec p4;
PL/SQL 過程已成功完成。
已用時間:  00: 01: 14.01

噢耶！又是一個檔次的提升。

     把過程變成了SQL一條一條插入變成了一個集合的概念，變成了一個整批的寫進DATA ,這就相當於一瓢一瓢的向池子裡面注水，變成了一桶一桶的注水。是不是就更快些呢!

5、第四次提升

好了，不廢話，直接進入第4次巨大的飛躍：

insert into t select rownum from dual connect by level<=1000000;
SQL> insert into t select rownum from dual connect by level<=1000000;
已建立1000000行。
已用時間:  00: 00: 01.40

天啦！不到2秒鐘...沒看錯吧！哈哈，神奇吧。

學習一下：
    insert into t select ...的方式是將資料先寫到data buffer 中，然後再刷到磁碟。
而create table t的方式確實跳過了資料快取區，直接寫進磁碟中，這種方式稱之為直接路徑讀寫方式。原本是先寫到記憶體，再寫到磁碟，改為直接寫到磁碟，少了一個步驟。
6、第五次質變
create table t as select rownum x from dual connect by level <=1000000;
SQL> create table t as select rownum x from dual connect by level <=1000000;
表已建立。
已用時間:  00: 00: 00.97
神速啊！，一秒鐘都不要。
讓效果更明顯一些，插入300w。
SQL> create table t as select rownum x from dual connect by level <=3000000;
表已建立。
已用時間:  00: 00: 02.50

直接路徑讀寫方式的缺點在於由於資料不經過資料快取區，所以在資料快取區中一定讀不到這些資料，因此一定會有物理讀。但是實際很多情況下，尤其是海量資料需要遷移插入的時候，快速插入才是真正的第一目的。該表一般記錄巨大，date buffer 甚至存放不下其十分之一，百分之一，那麼這寫共享的資料意義也就不大了。這是，我們一般會選擇直接路徑讀寫方式來完成海量資料的插入。

7、其他的方式加快效能提升
例如：
nologging方式，

並行方式，等等

由於本人的本本配置較差，這裡開2個並行度。
create table t nologging parallel 16
as select rownum x from dual connect by level<=3000000;

SQL> create table t nologging parallel 2
  2  as select rownum x from dual connect by level<=3000000;
表已建立。
已用時間:  00: 00: 02.02

還是有一定的提升！如果是更大的資料量，效率會更明顯。如果把SQL執行在強勁的伺服器上，16CPU，32CPU，64CPU，128CPU，會有不同程度的提升。

       但是並且最大的特點就是佔用了大多數的CPU資源，如果是一個併發環境，有很多應用在跑著，就可能開過多的並且而影響其他的應用，導致別的應用資源不足，所以在做這些類似的操作前，想清楚喲！

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28389881/viewspace-1356592/，如需轉載，請註明出處，否則將追究法律責任。