ORACLE--預編譯與共享池--SGA基本介紹

我們暫時先不說其他的，我們先做一個簡單的實驗來證明來看出一些問題，最後通過為什麼來說明實驗的結論，并介紹原理和正常檢視方式，好了，我們先看看下面三段代碼分别執行的結果。

首先為了測試，我們需要建立一張表：

create table pre_test_table(

   c1  number,

   c2  varchar2(100)

);

好了，我們做一個插入操作的對比：

代碼段1：

begin

      for i in 1..20000 loop

           execute immediate 'insert into pre_test_table values('||i||',''test'')';

      end loop;

      commit;

end;

代碼片段2：

           execute immediate 'insert into pre_test_table values(:v1,:v2)' using i,'test';

      commit;

代碼片段3：

           insert into pre_test_table values(i,'test');

三段代碼執行效率你可以清晰的對比出來，代碼段1是最慢的，而且比後兩者慢很多倍，而代碼片段2和代碼片段3執行效率基本是一樣的，為什麼會有這樣的效果呢？看了下面的推敲，我們就清楚了，我們先把資料清理掉，共享池清理一下（下面我們再說），在做操作比較好。 

現在我需要做的sql語句操作是對表的插入pre_test_table values表的操作：

insert into pre_test_table values....

至于參數如何，我們不一定，首先查詢一下共享池内部做此操作的記錄：

select * from v$sqlarea

where sql_text like 'insert into pre_test_table values%';

發現資料太多，有多少不好說了,暫時不數了，因為對得出結論沒有意義，需要清理下共享池友善試驗。

truncate table pre_test_table;//清空表

alter system flush shared_pool;//清空緩沖區

查詢共享池:

發現沒有任何資料。。。。我們開始比較幹淨的做實驗了。。。

開始試驗：

我們将上述試驗的循環次數降低為3次

首先執行代碼段1：

      for i in 1..3 loop

查詢共享池（發現多了3條記錄）：

sql> select sql_text,hash_value,parse_calls from v$sqlarea

  2  where sql_text like 'insert into pre_test_table values%';

sql_text                                                            hash_value      parse_calls

--------------------------------------------------------------- -------------- ---------------------

insert into pre_test_table values(1,'test')        2868385396       1

insert into pre_test_table values(2,'test')        2513099158       1

insert into pre_test_table values(3,'test')        455953479         1

再執行第二段代碼：

再查詢共享池（發現隻多了一條sql）：

sql_text                                                              hash_value  parse_calls

--------------------------------------------------------------- ---------------- ------------

insert into pre_test_table values(1,'test')         2868385396    1

insert into pre_test_table values(:v1,:v2)        357326048      3

insert into pre_test_table values(2,'test')         2513099158    1

insert into pre_test_table values(3,'test')         455953479      1

再執行第三段代碼：

           insert into pre_test_table values(i,'test');

再查詢共享池（發現也隻多了一條sql）：

sql_text                                                             hash_value   parse_calls

------------------------------------------------------------- ------------------  ------------

insert into pre_test_table values(1,'test')          2868385396    1

insert into pre_test_table values(:b1 ,'test')     2239119514    3

insert into pre_test_table values(:v1,:v2)         357326048      3

insert into pre_test_table values(2,'test')          2513099158    1

insert into pre_test_table values(3,'test')          455953479      1

從這裡可能大家基本可以得出初步的結論，就是第一段代碼每條sql都會占用共享池，并征用共享池，而且用過一次後就不會在使用了，這個答案是正确的，我首先給出預編譯和拼sql對oracle的四個不同影響進行定義，下面再說明為什麼：

1、拼sql會導緻共享池的長期征用狀态

2、拼sql會導緻共享池中存在一堆的垃圾sql。

3、拼sql會硬解析（hard parse），而預編譯可以充分利用軟解析。

4、拼sql會導緻注入型錯誤。

疑問：

為什麼第二段代碼和第三段代碼是一個效果呢，這裡說明一下，在存儲過程提高效率的基本因素就是你如果按照過程化去寫sql，存儲過程預設就會按照預編譯方式去執行，因為這是oracle優化的基本原則，而即使在過程中使用execute immediate sql_str去執行，請注意，execute immediate不是sql語句，而是oracle在過程中支援的指令，即直接發送執行指令給oracle的分析器，是以這個是否進行編譯完全看你的sql是什麼樣的了，而不是oracle自己能決定的。

過程說明：

sql從終端通過1521 tcp服務端口以字元串方式傳送至oracle後（包含java程式也是這樣，其他應用程式，如出現string sql = "select * from a where id="+id;也會出現和代碼段1一樣的結果），oracle通過hash算法對其sql轉換，并在共享池中查找是否存在同樣hash值的sql（即：sql即使是參數或者大小寫不同，也會導緻找不到一樣的hash值），如果找到了，直接執行已經編譯完的sql，并修改使用率（這個有用）；若沒有，則首先通過硬解析工具對其進行各項文法分析和性能名額分析等等，然後開始征用共享池（此為共享資源），并注冊到共享池中，标志調用次數為1，然後再執行，當在大量征用共享資源時候，并且在硬解析過程中，高并發将導緻阻塞。可以将上述第一段代碼的循環次數增加為10萬，基本機器可以弄死，呵呵。。。。

共享池的大小肯定是有限制的，是以oracle在共享池不夠用的時候，采用基于lru為核心的算法進行替換(上述的parse_calls字段可以基本看出sql被調用的次數，但是不完全依賴于它)，我們最希望的就是不要做這樣的操作，因為這樣的操作必然面臨磁盤讀取，在記憶體中擷取我們稱為命中，命中率高才能提高使用率，系統的整體性能才能得到保證。

第四點中提到的至于注入型錯誤或攻擊就是傳送特殊字元串，導緻sql執行sql的修改，為什麼，因為你的sql是拼出來的，我舉個簡單例子：

你的程式中：

string sql = "select * from a where a.name = '"+name+"'";

此name假如為查詢條件傳入，那麼此時我在查詢條件文本框中這樣輸入：

第一種輸入：

' or 1=1--

此時你的sql變成：

select * from a where a.name ='' or 1=1 --'

後面兩杠是屏蔽你後面的sql，用一個or 1=1前面的東東不論是啥就永遠成立了(注意：oracle的sql執行，優先級是and 大于 or的，是以隻要or 1=1，無論有多少個and，最後會和這個or 1=1去比對，所有的資料都會提取出來)，此時分頁或者說導出控制天數或者資料量，都控制不了，有多少資料就會出來多少資料，首先資料權限沒有了，然後開幾個浏覽器系統就能挂掉，呵呵！

另一種更加損的輸入招數是：

';drop table dual--

在我以前用sql server的jdbc時，這種方式是支援的，這樣是很損的一種辦法。其實輸入的方式有些通過url有些通過條件，不一定，而且千奇百怪，有些想都想不到，至少他可能會導緻你的sql執行不是那麼順利篡改了sql執行的原有意義。

是以oltp系統使用這樣的sql，尤其對于oracle資料庫（其餘資料庫請自己研究下），是非常惡心的，是以我們在oltp系統是封殺這樣的sql存在。

簡要共享池的使用介紹：

談到共享池我們大緻介紹一些sga的内容，oracle資料庫我們操作主要對象是執行個體，而非資料庫本身，主要原因為：性能、安全性。而執行個體大緻分：sga、pga，本文隻是由共享池介紹一下sga，細節說下其共享池部分，而pga後續讨論。

sga内部主要包含：資料緩沖區、共享池、java池、大池、stream池、重做日志緩沖區；

pga内部包含：使用者session資訊、排序資訊、hash area、堆棧。這些資訊被背景程序所控制，版本遞增的背景程序也在不斷增加，細節的資訊後續讨論。

其實今天所謂查詢共享池，也是查詢共享池内部的library cache。我們在sqlplus中最常用的指令就是：

sql> show sga;

total system global area 1.7062e+10 bytes

fixed size                      2102776 bytes

variable size                  4613736968 bytes

database buffers            1.2432e+10 bytes

redo buffers                  14671872 bytes

也可以使用一下方式查詢和上述一樣的sga資訊：

sql> select * from v$sga;

name                      value

-------------------- ----------

fixed size               2102776

variable size           4613736968

database buffers     1.2432e+10

redo buffers           14671872

分别解釋下幾個字段的意義：

total system global area：代表sga的總體大小，包含下面幾者之和，都是以byte為機關，即位元組；

fixed size：字典資訊、控制資訊、狀态資訊。

variable size：共享池（shared pool）、java池(java pool)、大池(large pool)、stream pool；

database buffers：為資料緩沖區，oltp系統要求這塊設定較大。

redo buffer：重做日志緩沖區，适當提高緩沖區，減少檔案組切換，可以提高效率。

通過一下sql可以得到sga内部詳細的元件配置設定情況：

sql> select * from v$sgastat;

pool         name                            bytes

------------ -------------------------- ----------

                  fixed_sga                     2102776

                  buffer_cache               1.2432e+10

                  log_buffer                   14671872

shared pool  transaction                   8062512

shared pool  table definiti                  80336

shared pool  kgski scheduler heap 2 de         232

shared pool  kspd run-time context              16

shared pool  px subheap                      61344

shared pool  partitioning d                 455480

shared pool  message pool freequeue         757568

shared pool  qesblgf:bfm                       728

等等資料。。。。。。。自己檢視一下即可，我這由于篇幅所限，就輸出這麼多了。

--下面sql用于檢視sga中可進行手工調配參數的清單：

sql> select * from v$sga_dynamic_components;

component                                                        current_size   min_size   max_size user_specified_size oper_count last_oper_typ last_

---------------------------------------------------------------- ------------ ---------- ----------

shared pool                                                        4194304000 4194304000          0          4194304000          0 static                              

large pool                                                          134217728  134217728          0           134217728          0 static                              

java pool                                                           134217728  134217728          0           134217728          0 static                              

streams pool                                                        117440512  117440512          0           117440512          0 static                              

default buffer cache                                               1.2264e+10 1.2264e+10          0          1.2264e+10          2 shrink        manual    07-4月

keep buffer cache                                                   100663296          0          0           100663296          8 grow          manual    07-4月 -10

recycle buffer cache                                                 67108864          0          0            67108864          1 grow          manual    07-4月 -10

default 2k buffer cache                                                     0          0          0                   0          0 static                              

default 4k buffer cache                                                     0          0          0                   0          0 static                              

default 8k buffer cache                                                     0          0          0                   0          0 static                              

default 16k buffer cache                                                    0          0          0                   0          0 static                              

default 32k buffer cache                                                    0          0          0                   0          0 static                              

asm buffer cache                                                            0          0          0          1.2465e+10          0 static                  

查詢共享池大小：

sql> show parameter shared_pool_size;

name                                 type        value

------------------------------------ ----------- ------------------------------

shared_pool_size                     big integer 4000m

sql> select name,type,value

  2      from v$parameter a

  3      where a.name='shared_pool_size';

name                                       type          value

-------------------- ----------------------------- ----------------------------

shared_pool_size                       6               4194304000

這裡順便說下，很多時候大家不知道資料字典是什麼，很多時候資料字典的名字很長，而且有些後面又s，有些後面沒有s，大家記錄不下來，此時大家隻需要知道大緻是什麼就ok了，然後用下面的基于視圖的總視圖去查詢視圖的實際名稱（我們以不知道共享池的視圖是什麼）：

sql> select * from dict t

  2  where t.table_name like '%v$shared%';

table_name                                comments

------------------------------ -------------------------------------------------------------------

v$shared_server_monitor        synonym for v_$shared_server_monitor

v$shared_server                       synonym for v_$shared_server

v$shared_pool_reserved          synonym for v_$shared_pool_reserved

v$shared_pool_advice              synonym for v_$shared_pool_advice

gv$shared_server_monitor      synonym for gv_$shared_server_monitor

gv$shared_server                     synonym for gv_$shared_server

gv$shared_pool_reserved        synonym for gv_$shared_pool_reserved

gv$shared_pool_advice            synonym for gv_$shared_pool_advice

其中gv$開頭的視圖是用于叢集中的，我們一般隻關心v$開頭的資訊；可能你連表的大緻意思都不清楚，你隻大緻記得有一個字段大緻的名稱，如我們知道一個視圖内部字段的名稱有一個以scn開頭的列，那麼我們這樣也可以反向給它定位：

sql> select t.table_name, t.column_name

  2    from dict_columns t

  3   where t.column_name like 'scn%';

table_name                     column_name

------------------------------ ------------------------------

all_sumdelta                   scn

dba_audit_trail                scn

user_audit_trail               scn

dba_audit_statement            scn

user_audit_statement           scn

dba_audit_object               scn

user_audit_object              scn

dba_audit_exists               scn

dba_fga_audit_trail            scn

dba_common_audit_trail         scn

dba_capture_prepared_tables    scn

all_capture_prepared_tables    scn

dba_file_group_tables          scn

all_file_group_tables          scn

user_file_group_tables         scn

v$restore_point                scn

v$recovery_status              scn_needed

v$logmnr_contents              scn

v$xml_audit_trail              scn

gv$restore_point               scn

這裡回到正題：清空共享池(oltp系統運作時不要去操作，這個過程很影響整體運作)：

sql>alter system flush shared_pool;

如果要查詢某過程或包的源碼，可以看一下系統的資源包：

sql>select * from user_source t where t.name = '過程或包的名字' order by line;

這些源碼資訊在首次是不會裝入記憶體的，因為共享池的大小有限，調用時再裝入記憶體，而且也不會逃脫lru的命運，若一些寫的很爛的sql，就有可能把它替換出去，這個時候我們想做到的是啟動時直接裝入記憶體并不會被替換，oracle給我們一個keep方法，但是并非預設的，也就是安裝oracle後并不是預設就提供的這個包，如果你用具有dba權限的人進去，不能使用dbms_shared_pool這個包（報：這個包不存在），說明還沒有建立，此時需要做一下操作，才能建立：

1、首先定位oracle_home的位置，我們沒有直接定位oracle_home的方式，除非是你自己安裝的，如果不知道，用下面一個辦法：

select * from v$parameter p1

where p1.name = 'spfile';

2、若沒有該目錄，使用create spfile from pfile;執行一下重新開機ok就有了，得到該目錄後，假如得到如下：

d:/oracle10/product/10.2.0/db_1/database/spfileorcl102.ora

那麼oracle_home上相推兩層得到：

d:/oracle10/product/10.2.0/db_1/

那麼要得到那個包的建立腳本就在：

d:/oracle10/product/10.2.0/db_1/rdbms/admin/dbmspool.sql

此時需要到安裝資料庫的機器上去執行，如果你本地有腳本當然也可以執行，但是注意：這個執行必須是在sqlplus中，pl/sql中執行該腳本不好用。

3、執行方式：進入到安裝該資料庫的sqlplus下用sys使用者登入，該包需要建立在sys使用者下。

sql> @d:/oracle10/product/10.2.0/db_1/rdbms/admin/dbmspool.sql

程式包已建立。

授權成功。

視圖已建立。

程式包體已建立。

4、對于系統的大過程，可能第一次裝載比較緩慢，而且如果使用頻率較高，可以将其脫離lru算法，并直接裝入記憶體，如果可以的話，做oracle啟動時觸發器，如果不行，就手動執行一下代碼：

   sys.dbms_shared_pool.keep('存儲過程或包的名字');

若想将某過程從記憶體中去除掉：

   sys.dbms_shared_pool.unkeep('存儲過程或包的名字');

5、此時檢視緩沖池中是否裝載改對象：

select name,owner,type

from v$db_object_cache where kept = 'yes'

and name ='過程或包的名字';//這個地方也可以用sql片段來like

檢視共享池中執行的一些sql標頭：

select * from v$sqlarea;

通不過上述的sql得到hash_value或者addredss或者sql_id都可以通過以下視圖得到對應執行sql的全部内容（當sql較長的時候，v$sqlarea隻儲存前面一部分，全部内容在該視圖中）：

select * from v$sqltext_with_newlines;

得到sql的執行計劃:

select * from v$sql_plan;

得到對共享池設定的建議值，oracle根據實際運作情況，推薦值：

select * from v$shared_pool_advance;

還有些不是很常用的：

得到sql綁定變量資訊：

select * from v$sql_bind_capture;

sql占用共享池記憶體：

select * from v$sql_shared_memory;

sql消耗調用的統計資訊：

select * from v$sqlstats;

這裡隻是由預編譯->共享池->sga的過程，對于sga的核心隻是闡述了共享池的部分，下次說明sga的另一大塊，data buffer，資料緩沖區，該區域在oltp系統中非常重要。

最後補充話題，本來這個想在後面說的，因為涉及一些其他内容，不過既然說到，就提一下吧，我們在oltp要求使用綁定參數方式執行sql如：

用應用程式的sql應當是：

string sql = “select * from a where id=?”;

而不是：

string sql = “select * from a where id=”+id;

那麼這樣的情況我們該怎麼辦呢？當要查詢多個id，使用in的情況，或者同時修改多條記錄的操作，我們無疑想出最正常的三種辦法(我們先介紹正常方法，再介紹解決問題的方法)：

方法1（拼串，放棄預編譯）：

string sql = “select * from a where id in(”+keys+”)”;

付：該方法放棄預編譯，但是也是正常方法中的無奈之舉。

方法2(将參數個數動态化去預編譯)

stringbuffer sql = new stringbuffer(256);

sql.append(“seelct * from a where id in(”);

for(….) {

   sql.append(“?”).append(“,”);

}

sql.deletecharat(sql.length()-1);

付：該方法比上一種稍微好一點，oltp下一般情況下，我們正常方法中最少要這樣去完成，大家可以把ibatis的執行sql日志拿出來看下即可發現，ibatis對于動态參數個數也是這樣去完成的，對于并發度不算高的代碼段我們可以這樣使用，如果并發度高的代碼段，這樣使用我們也不會考慮。

方法3（循環提取。循環修改）

    ptmt.setint(1,id[i]);

    ptmt.executequery(“select * from a where id=?”);

付:這在執行過程中往往是最“不應該使用”的辦法了;如果執行插入操作，我們會适當考慮攜帶批處理這樣去完成也是可以的，不過對于update和select這類操作我們不該這樣使用的。

在這裡上述三種辦法，隻有第二種方法在oltp中并發量不大的情況下可以使用，若并發量較大，且參數個數的動态性比較大，也應該考慮使用其他方法去實作，因為大家通過上述試驗和反向查詢後發現，問号個數的變化也會産生不同的sql，共享池中仍然會造成很多的垃圾，隻是相對第一種方法機率降低了很多，而第三種方法基本是我們不考慮的。

我們說一下如果對于這樣的情況，批量執行過程中，我們該如何轉換，利用oracle的數組進行轉換，為此我們先在oracle内部提供一個函數，和數組類型，前序工作:

步驟1：

建立資料類型（表格類型，也類似數組）：

create or replace type my_table_type is table of varchar2(8000);

步驟2：

建立轉換函數（在網上很多地方可以找到類似代碼，我這隻是一個參考）：

create or replace function split(src_str in varchar2,

split_str varchar2) return my_table_type is

  v_table_str my_table_type := my_table_type();

  v_temp_str  varchar2(8000) := src_str;

  v_split_str varchar2(20) := split_str;

  i           number := 1;

  j           number := 1;

  if v_split_str is null then

      v_split_str := ',';--我們預設用逗号分隔  

  end if;

  if src_str is null or src_str = v_split_str then

       return v_table_str;

  v_temp_str := ltrim(v_temp_str, v_split_str);

  loop

        i := instr(v_temp_str, v_split_str, j);

        exit when i = 0 or j > length(v_temp_str);

        v_table_str.extend;

        v_table_str(v_table_str.count) := substr(v_temp_str, j, i - j);

        j := i + length(v_split_str);

  end loop;

  if j < length(v_temp_str) then

        v_table_str(v_table_str.count) := substr(v_temp_str, j, length(v_temp_str) - j + 1);

  return v_table_str;

end split;

步驟3：（測試可用性,這裡假如資料都是按照逗号分隔的）

較高版本支援這樣的寫法：

sql> select * from table(split('123,321',','));

column_value

--------------------------------------------------------------------------------

123

321

較低版本可以這樣寫：

sql> select * from table(cast(split('123,321,456', ',') as my_table_type));

456

步驟4（程式應用）：

string sql = “select * from a where id in(select * from table(split(?,',')))”;//當然對于低版本的資料庫，相應修改即可。

但是oracle有些時候會很傻的去使用hash join，因為他們他不知道你裡面傳回多少資料，而我們通過轉換回來的id往往資料量很少，最多就是幾十行上百行，若目标表為一個大表，使用hash join的确是一件很浪費的事情，此時我們不願意這樣去做，因為很浪費cpu和臨時表空間（這其實是後面要說的），我們一般需要強制指定查詢的方式來控制他走嵌套循環，讓大表根據小表去走索引，使用oracle的hint來強制告訴它應該由小表引導大表執行，來保證sql執行計劃的穩定性：

string sql = “select /*+ordered use_nl(a2,a1)*/a2.* from a a1,(select column_value from table(split(?,',')) a2 where a1.id = a2.column_value”;

此時可能會問，這樣轉一次會不會很慢，是的，這不難會想想一個拆開字元串的過程，我們必然會消耗一點，不過要想到一次執行就是拆開一個字元串而已，而且我們傳入的字元串也不會太長，這個解析過程對于oracle來說還是沒啥問題的，不必擔心這個，而更加重要的提高了共享池的應用。