Oracle效能最佳化 之 庫快取
Oracle效能最佳化 之 庫快取
http://blog.chinaunix.net/uid-26762723-id-3247698.html
一、庫快取中的資訊
1．什麼是執行計劃
2．為什麼要共享執行計劃
執行計劃的生成要耗費許多CPU時間，而且最佳化器會將生成的執行計劃存放到共享池中。如果你執行很多相同的語句，但沒有共享執行計劃，最佳化器每次都要搜尋共享池、查詢有沒有可以共享的執行計劃，沒有查詢後它自己生成，再把生成的執行計劃存入共享池。就是說相同的語句如果你沒有共享計劃，不但消耗了更多的CPU生成執行計劃，而且每次還要搜尋共享池、儲存新生成的執行計劃，並且，管理共享池中的執行計劃還會有一些額外的負擔。這些工作都會拖慢SQL語句的執行速度。Oracle並不會因為你沒有共享執行計劃而取消有關執行計劃的一些管理性工作。打個比方，你有一輛腳踏車，你本來可以騎著它加快速度，但你不但不騎它，反而扛著它，結果是速度大大減慢。Oracle明明提供了一塊記憶體叫做庫快取，希望你可以在其中共享執行計劃，就算你不共享，庫快取還是要存在的，這個時候，你就相當於扛著腳踏車在走了。你沒有把庫快取的優勢發揮出來，你卻承受了庫快取的管理負擔。因此，共享執行計劃是最最佳化使用共享池的最重要一點。
二、庫快取的調優
庫快取的調優最重要一點就是確做使用者可以共享執行計劃。這應該從程式設計師和DBA兩個角度去作。作為程式設計師，應該學會使用繫結變數，這可以使本來相似的語句變得一模一樣，從而使它們可以共享執行計劃。下面，讓我們先來了解一下什麼樣的情況下，執行計劃才能被共享。
1．共享執行計劃
要共享執行計劃，語句的文字必須一模一樣，比如，如下的語句就不能共享執行計劃：
語句1：Select * from tab1 where id=1;
語句2：select * from tab1 where id=1;
為什麼語句1和語句2不能共享執行計劃呢？第一個語句的第一個字母是大寫，而第二個語句第一個字母是小寫。不但大小寫，就算多了一個空格，也不能共享執行計劃。
假設上面兩條語句的文字完全一樣的，但語句1在使用者USER1下發出，語句2在使用者USER2中發出。並且這兩個使用者下都有自己的TAB1表，那麼這兩個語句也不能共享計劃。
如果兩條語句要想共享計劃，兩條語句的文字不但要完全相同，語句執行時的環境也必須完成相同才行。這裡所說的“環境”，指的是一些初始化引數的值。並不是指不同的使用者。當然，如果兩個使用者分別操作不同的表，在表名相同的情況下，是不會共享執行計劃的。如果兩個使用者發出文字相同的語句，操作的又是同一個表，那麼，是可以共享執行計劃的。那麼，不同的使用者，如何用同樣的名字操作同一表呢？接著上面的例子，假設USER1中有一個TAB1表，而USER2中沒有，USER1的查詢語句形式如下：
select * from tab1 where id=1;
USER2想要查詢USER1中的表，形式如下：
select * from user1.tab1 where id=1;
這兩條語句也不能共享執行計劃，因為語句文字有很大的不同。USER2的語句多了一個“USER1.”。對於這樣的情況，我們可以使用公用同義詞使USER2訪問TAB1表時，不必在表名前加“USER1.”來解決。看下面的例子：
（舉一個這樣的例子）
也就是說，只要語句文字一模一樣，執行語句時的環境一模一樣，兩條語句就可以共享執行計劃。
2．繫結變數
再看下面一種情況。假設有一個大型網站，每天要有大量的使用者登入，使用者資訊儲存在一個User_info表中，每個使用者登入時，都要輸入使用者ID和密碼，資料庫根據使用者ID在User_info中進行查詢，取出使用者密碼和其他的一些使用者基本資訊，等等，後面的工作我就不說了，就是每個使用者在登入時，資料庫都要根據使用者ID進行一次查詢，假設又有兩個使用者登入了，一個使用者的ID是1另一個使用者ID是2。這兩個使用者登入時的查詢語句如下：
使用者1的查詢語句：select * from user_info where id=1;
使用者2的查詢語句：select * from user_info where id=2;
這兩條語句是不會共享執行計劃的。我們可以實驗一下：
Step1：在139號會話中釋出查詢語句：
SQL> select * from tab1 where id=1;
ID NAME
---------- ----------
1 ICOL$
1 I_OBJ#
此語句是首次查詢TAB1表，這將引出大量的遞迴呼叫，這些遞迴呼叫將會進行多次硬解析。以後我們在釋出以TAB1的查詢時，將不會有遞迴呼叫。這一步，是會下面的實驗做準備，下面開始實驗。
Step2：在另一會話中查詢會話139的解析情況：
SQL> select name,value from v$sesstat a ,v$statname b where a.statistic#=b.statistic# and a.sid=139 and b.name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 386
parse count (hard) 60
parse count (failures) 0
Step3：在139會話發出查詢語句：
SQL> select * from tab1 where id=2;
ID NAME
---------- ----------
2 I_USER1
2 PROXY_ROLE
Step4：在另一會話中再次查詢會話139的解析情況：
SQL> select name,value from v$sesstat a ,v$statname b where a.statistic#=b.statistic# and a.sid=139 and b.name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 387
parse count (hard) 61
parse count (failures) 0
Step5：在139會話再次發出相似的查詢語句：
SQL> select * from tab1 where id=3;
ID NAME
---------- ----------
3 CON$
3 I_IND1
Step6：再次查詢139會話中的解析情況：
SQL> select name,value from v$sesstat a ,v$statname b where a.statistic#=b.statistic# and a.sid=139 and b.name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 388
parse count (hard) 62
parse count (failures) 0
這個實驗的結果我們已經看到了，這兩條語句不同共享執行計劃。試想如果每天有大量的使用者登入，每個使用者在登入時，都無法共享相似語句的執行計劃，這將白白耗費多少CPU時間啊。這個時間就是繫結變數派上用場的時候了。下面我們先來看個使用繫結變數的例子：
Step1：在139會話中定義繫結變數User_id，並將它的值賦為4。
SQL> var user_id number;
SQL> exec :user_id:=4;
PL/SQL 過程已成功完成。
Step2：在139會話中使用繫結變數進行查詢：
SQL> select * from tab1 where id=:user_id;
ID NAME
---------- ----------
4 UNDO$
4 I_CDEF2
Step3：觀察139會話的解析次數：
SQL> select name,value from v$sesstat a ,v$statname b where a.statistic#=b.statistic# and a.sid=139 and b.name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 395
parse count (hard) 69
parse count (failures) 0
Step4：將繫結變數User_id的值變為5，再次執行查詢：
SQL> exec :user_id:=5;
PL/SQL 過程已成功完成。
注意，此語句也要解析，因此，我們在這裡還要檢視一下解析次數：
Step5：檢視139會話的解析次數：
SQL> select name,value from v$sesstat a ,v$statname b where a.statistic#=b.statistic# and a.sid=139 and b.name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 396
parse count (hard) 70
parse count (failures) 0
Step6：在139會話中，用繫結變數再次做一次查詢：
SQL> select * from tab1 where id=:user_id;
ID NAME
---------- ----------
5 C_COBJ#
5 I_PROXY_RO
查詢的結果，是ID為5的行。這裡，我們使用了同樣的語句分別查詢出了ID為4的行和ID為5的行。
Step7：檢視139會話的解析次數：
SQL> select name,value from v$sesstat a ,v$statname b where a.statistic#=b.statistic# and a.sid=139 and b.name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 397
parse count (hard) 70
parse count (failures) 0
可以看到最後一次執行查詢後，硬解析沒有增加。這就是繫結變數的作用。
在這裡只能使用繫結變數，不能使用普通變數。繫結變數是Oracle的一種特殊變數，對它賦值的過程，是在Oracle最佳化器解析過語句之後，解析語句的任務就是確定語句的執行計劃。當使用繫結變數後，最佳化器在解析語句時，解析的是“select * from tab1 where id=:user_id”，而不是“select * from tab1 where id=5;”。最佳化器將按照“select * from tab1 where id=:user_id”確定語句的執行計劃，而不是“select * from tab1 where id=5”。在解析過語句已經確定了語句的執行計劃後，最佳化器將執行計劃交給伺服器程式去執行時，再用實際的值替換繫結變數。如果你使用普通的變數，Oracle最佳化器將在解析前用變數的值替換變數，這樣在解析語句、生成執行計劃時，最佳化器看到的語句就是“select * from tab1 where id=4”或“select * from tab1 where id=5”等等，因為它們的文字不同，最佳化器將選擇重新為它們生成執行計劃。而不是取出已經存入庫快取的執行計劃直接執行。
http://blog.chinaunix.net/uid-26762723-id-3247698.html
一、庫快取中的資訊
1．什麼是執行計劃
2．為什麼要共享執行計劃
執行計劃的生成要耗費許多CPU時間，而且最佳化器會將生成的執行計劃存放到共享池中。如果你執行很多相同的語句，但沒有共享執行計劃，最佳化器每次都要搜尋共享池、查詢有沒有可以共享的執行計劃，沒有查詢後它自己生成，再把生成的執行計劃存入共享池。就是說相同的語句如果你沒有共享計劃，不但消耗了更多的CPU生成執行計劃，而且每次還要搜尋共享池、儲存新生成的執行計劃，並且，管理共享池中的執行計劃還會有一些額外的負擔。這些工作都會拖慢SQL語句的執行速度。Oracle並不會因為你沒有共享執行計劃而取消有關執行計劃的一些管理性工作。打個比方，你有一輛腳踏車，你本來可以騎著它加快速度，但你不但不騎它，反而扛著它，結果是速度大大減慢。Oracle明明提供了一塊記憶體叫做庫快取，希望你可以在其中共享執行計劃，就算你不共享，庫快取還是要存在的，這個時候，你就相當於扛著腳踏車在走了。你沒有把庫快取的優勢發揮出來，你卻承受了庫快取的管理負擔。因此，共享執行計劃是最最佳化使用共享池的最重要一點。
二、庫快取的調優
庫快取的調優最重要一點就是確做使用者可以共享執行計劃。這應該從程式設計師和DBA兩個角度去作。作為程式設計師，應該學會使用繫結變數，這可以使本來相似的語句變得一模一樣，從而使它們可以共享執行計劃。下面，讓我們先來了解一下什麼樣的情況下，執行計劃才能被共享。
1．共享執行計劃
要共享執行計劃，語句的文字必須一模一樣，比如，如下的語句就不能共享執行計劃：
語句1：Select * from tab1 where id=1;
語句2：select * from tab1 where id=1;
為什麼語句1和語句2不能共享執行計劃呢？第一個語句的第一個字母是大寫，而第二個語句第一個字母是小寫。不但大小寫，就算多了一個空格，也不能共享執行計劃。
假設上面兩條語句的文字完全一樣的，但語句1在使用者USER1下發出，語句2在使用者USER2中發出。並且這兩個使用者下都有自己的TAB1表，那麼這兩個語句也不能共享計劃。
如果兩條語句要想共享計劃，兩條語句的文字不但要完全相同，語句執行時的環境也必須完成相同才行。這裡所說的“環境”，指的是一些初始化引數的值。並不是指不同的使用者。當然，如果兩個使用者分別操作不同的表，在表名相同的情況下，是不會共享執行計劃的。如果兩個使用者發出文字相同的語句，操作的又是同一個表，那麼，是可以共享執行計劃的。那麼，不同的使用者，如何用同樣的名字操作同一表呢？接著上面的例子，假設USER1中有一個TAB1表，而USER2中沒有，USER1的查詢語句形式如下：
select * from tab1 where id=1;
USER2想要查詢USER1中的表，形式如下：
select * from user1.tab1 where id=1;
這兩條語句也不能共享執行計劃，因為語句文字有很大的不同。USER2的語句多了一個“USER1.”。對於這樣的情況，我們可以使用公用同義詞使USER2訪問TAB1表時，不必在表名前加“USER1.”來解決。看下面的例子：
（舉一個這樣的例子）
也就是說，只要語句文字一模一樣，執行語句時的環境一模一樣，兩條語句就可以共享執行計劃。
2．繫結變數
再看下面一種情況。假設有一個大型網站，每天要有大量的使用者登入，使用者資訊儲存在一個User_info表中，每個使用者登入時，都要輸入使用者ID和密碼，資料庫根據使用者ID在User_info中進行查詢，取出使用者密碼和其他的一些使用者基本資訊，等等，後面的工作我就不說了，就是每個使用者在登入時，資料庫都要根據使用者ID進行一次查詢，假設又有兩個使用者登入了，一個使用者的ID是1另一個使用者ID是2。這兩個使用者登入時的查詢語句如下：
使用者1的查詢語句：select * from user_info where id=1;
使用者2的查詢語句：select * from user_info where id=2;
這兩條語句是不會共享執行計劃的。我們可以實驗一下：
Step1：在139號會話中釋出查詢語句：
SQL> select * from tab1 where id=1;
ID NAME
---------- ----------
1 ICOL$
1 I_OBJ#
此語句是首次查詢TAB1表，這將引出大量的遞迴呼叫，這些遞迴呼叫將會進行多次硬解析。以後我們在釋出以TAB1的查詢時，將不會有遞迴呼叫。這一步，是會下面的實驗做準備，下面開始實驗。
Step2：在另一會話中查詢會話139的解析情況：
SQL> select name,value from v$sesstat a ,v$statname b where a.statistic#=b.statistic# and a.sid=139 and b.name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 386
parse count (hard) 60
parse count (failures) 0
Step3：在139會話發出查詢語句：
SQL> select * from tab1 where id=2;
ID NAME
---------- ----------
2 I_USER1
2 PROXY_ROLE
Step4：在另一會話中再次查詢會話139的解析情況：
SQL> select name,value from v$sesstat a ,v$statname b where a.statistic#=b.statistic# and a.sid=139 and b.name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 387
parse count (hard) 61
parse count (failures) 0
Step5：在139會話再次發出相似的查詢語句：
SQL> select * from tab1 where id=3;
ID NAME
---------- ----------
3 CON$
3 I_IND1
Step6：再次查詢139會話中的解析情況：
SQL> select name,value from v$sesstat a ,v$statname b where a.statistic#=b.statistic# and a.sid=139 and b.name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 388
parse count (hard) 62
parse count (failures) 0
這個實驗的結果我們已經看到了，這兩條語句不同共享執行計劃。試想如果每天有大量的使用者登入，每個使用者在登入時，都無法共享相似語句的執行計劃，這將白白耗費多少CPU時間啊。這個時間就是繫結變數派上用場的時候了。下面我們先來看個使用繫結變數的例子：
Step1：在139會話中定義繫結變數User_id，並將它的值賦為4。
SQL> var user_id number;
SQL> exec :user_id:=4;
PL/SQL 過程已成功完成。
Step2：在139會話中使用繫結變數進行查詢：
SQL> select * from tab1 where id=:user_id;
ID NAME
---------- ----------
4 UNDO$
4 I_CDEF2
Step3：觀察139會話的解析次數：
SQL> select name,value from v$sesstat a ,v$statname b where a.statistic#=b.statistic# and a.sid=139 and b.name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 395
parse count (hard) 69
parse count (failures) 0
Step4：將繫結變數User_id的值變為5，再次執行查詢：
SQL> exec :user_id:=5;
PL/SQL 過程已成功完成。
注意，此語句也要解析，因此，我們在這裡還要檢視一下解析次數：
Step5：檢視139會話的解析次數：
SQL> select name,value from v$sesstat a ,v$statname b where a.statistic#=b.statistic# and a.sid=139 and b.name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 396
parse count (hard) 70
parse count (failures) 0
Step6：在139會話中，用繫結變數再次做一次查詢：
SQL> select * from tab1 where id=:user_id;
ID NAME
---------- ----------
5 C_COBJ#
5 I_PROXY_RO
查詢的結果，是ID為5的行。這裡，我們使用了同樣的語句分別查詢出了ID為4的行和ID為5的行。
Step7：檢視139會話的解析次數：
SQL> select name,value from v$sesstat a ,v$statname b where a.statistic#=b.statistic# and a.sid=139 and b.name like '%parse%';
NAME VALUE
---------------------------------------------------------------- ----------
parse count (total) 397
parse count (hard) 70
parse count (failures) 0
可以看到最後一次執行查詢後，硬解析沒有增加。這就是繫結變數的作用。
在這裡只能使用繫結變數，不能使用普通變數。繫結變數是Oracle的一種特殊變數，對它賦值的過程，是在Oracle最佳化器解析過語句之後，解析語句的任務就是確定語句的執行計劃。當使用繫結變數後，最佳化器在解析語句時，解析的是“select * from tab1 where id=:user_id”，而不是“select * from tab1 where id=5;”。最佳化器將按照“select * from tab1 where id=:user_id”確定語句的執行計劃，而不是“select * from tab1 where id=5”。在解析過語句已經確定了語句的執行計劃後，最佳化器將執行計劃交給伺服器程式去執行時，再用實際的值替換繫結變數。如果你使用普通的變數，Oracle最佳化器將在解析前用變數的值替換變數，這樣在解析語句、生成執行計劃時，最佳化器看到的語句就是“select * from tab1 where id=4”或“select * from tab1 where id=5”等等，因為它們的文字不同，最佳化器將選擇重新為它們生成執行計劃。而不是取出已經存入庫快取的執行計劃直接執行。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2137779/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle資料庫高效能秘密之資料快取記憶體Oracle資料庫快取記憶體
- Oracle資料庫高效能祕密之資料快取記憶體Oracle資料庫快取記憶體
- 【效能最佳化】ORACLE資料庫效能最佳化概述Oracle資料庫
- Oracle效能最佳化調整--調整緩衝區快取記憶體Oracle快取記憶體
- Oracle效能最佳化之SQL最佳化(轉)OracleSQL
- Oracle效能最佳化 之 共享池Oracle
- MySQL 資料庫效能優化之快取引數優化MySql資料庫優化快取
- 多庫取數的效能最佳化方案
- [譯] 高效能 Java 快取庫 — CaffeineJava快取
- Oracle效能最佳化之應用最佳化（轉）Oracle
- 前端效能優化之快取技術前端優化快取
- 前端效能優化之HTTP快取策略前端優化HTTP快取
- Oracle12c中效能最佳化增強新特性之資料庫智慧快閃記憶體Oracle資料庫記憶體
- MySQL資料庫效能優化之快取引數優化（轉）MySql資料庫優化快取
- Oracle效能最佳化之虛擬索引Oracle索引
- Oracle效能最佳化之LockContention（轉）Oracle
- Oracle效能最佳化之Rollback(undo)Segment最佳化（轉）Oracle
- 前端效能優化 之 瀏覽器快取前端優化瀏覽器快取
- B站基於快取最佳化 PRESTO 叢集查詢效能快取REST
- 非功能性約束之效能（1）-效能銀彈：快取快取
- MySQL 效能優化之快取引數優化MySql優化快取
- Oracle資料庫效能最佳化技術(轉)Oracle資料庫
- 快取工廠之Redis快取快取Redis
- oracle 效能最佳化Oracle
- Laravel Passport OAuth 資料庫查詢改快取最佳化LaravelPassportOAuth資料庫快取
- Oracle效能最佳化之提升block的效率(轉)OracleBloC
- 前端持久化快取最佳化前端持久化快取
- 快取穿透導致資料庫效能不穩定快取穿透資料庫
- Caffeine 快取庫快取
- Oracle資料庫效能最佳化技術(轉載)Oracle資料庫
- oracle cache快取Oracle快取
- 前端效能最佳化——採用高效的快取策略提供靜態資源前端快取
- Oracle效能最佳化之診斷latch競爭（轉）Oracle
- Oracle SQL效能最佳化OracleSQL
- Oracle sql 效能最佳化OracleSQL
- [譯]高效能快取庫Caffeine介紹及實踐快取
- Oracle效能最佳化:資料庫配置和IO問題Oracle資料庫
- 快取淘汰、快取穿透、快取擊穿、快取雪崩、資料庫快取雙寫一致性快取穿透資料庫