【最佳化】引數SESSION_CACHED_CURSORS與解析之間的關係
一：引數SESSION_CACHED_CURSORS,就是說的是一個session可以快取多少個cursor（軟軟解析），讓後續相同的SQL語句不再開啟遊標,從而避免軟解析的過程來提高效能，軟解析同硬解析一樣,比較消耗資源。
二：open_cursors設定每個session（會話）最多能同時開啟多少個cursor（遊標），是相對於SPA中的share pool下libarary cache而言。session_cached_cursor設定每個session（會話）最多可以快取多少個關閉掉的cursor，是相對於PGA而言。
三：另外檢查SESSION_CACHED_CURSORS引數是否設定的合理，可以從兩個statistic來檢查，即select name,value from v$sysstat where name like '%cursor%';和select name,value from v$sysstat where name like '%parse%';
１．創造環境：
SYS@ORA11GR2>create table t1 as select * from all_objects;
SYS@ORA11GR2>desc t1
SYS@ORA11GR2>select count(*) from t1;
COUNT(*)
----------
84999
２．當session_cached_cursors＝50進行操作
SYS@ORA11GR2>show parameter session_cached_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 50
1）直接進行查詢操作（無繫結變數）：
SYS@ORA11GR2>select count(*) from t1 where object_id=101;
COUNT(*)
----------
1
SYS@ORA11GR2>select count(*) from t1 where object_id=102;
COUNT(*)
----------
1
SYS@ORA11GR2>select count(*) from t1 where object_id=103;
COUNT(*)
----------
1
驗證檢視解析執行情況：
SYS@ORA11GR2>select sql_text,s.parse_calls,loads,executions from v$sql s where sql_text like 'select count(*) from t1%' order by 1,2,3,4;
SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
---------------------------------------- ----------- ---------- ----------
select count(*) from t1 where object_id=101 1 1 1
select count(*) from t1 where object_id=102 1 1 1
select count(*) from t1 where
object_id=103 1 1 1
三次執行SQL都是硬解析,都是解析1次，執行一次；
2）用繫結變數執行sql查詢語句：
SYS@ORA11GR2>var v_id number;
SYS@ORA11GR2>exec :v_id:=101;
PL/SQL procedure successfully completed.
SYS@ORA11GR2>select count(*) from t1 where object_id=:v_id;
COUNT(*)
----------
1
SYS@ORA11GR2>exec :v_id:=102;
PL/SQL procedure successfully completed.
SYS@ORA11GR2>select count(*) from t1 where object_id=:v_id;
COUNT(*)
----------
1
SYS@ORA11GR2>exec :v_id:=103;
PL/SQL procedure successfully completed.
SYS@ORA11GR2>select count(*) from t1 where object_id=:v_id;
COUNT(*)
----------
1
驗證檢視解析執行情況：
SYS@ORA11GR2>select sql_text,s.parse_calls,loads,executions from v$sql s where sql_text like 'select count(*) from t1%' order by 1,2,3,4;
SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
---------------------------------------- ----------- ---------- ----------
select count(*) from t1 where object_id=101 1 1 1
select count(*) from t1 where object_id=102 1 1 1
select count(*) from t1 where object_id=103 1 1 1
select count(*) from t1 where object_id=:v_id 3 1 3
繫結變數執行的後執行三次，硬解析1此，軟解析2次，共解析3次，執行了3次，軟解析發揮了作用；
SYS@ORA11GR2>exec :v_id:=104;
PL/SQL procedure successfully completed.
SYS@ORA11GR2>select count(*) from t1
where object_id=:v_id;
COUNT(*)
----------
1
再次驗證檢視解析執行情況：
SYS@ORA11GR2>select sql_text,s.parse_calls,loads,executions from v$sql s where
sql_text like 'select count(*) from t1%' order by 1,2,3,4;
SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
----------------------------------------
----------- ---------- ----------
select count(*) from t1 where
object_id=101 1 1 1
select count(*) from t1 where
object_id=102 1 1 1
select count(*) from t1 where
object_id=103 1 1 1
select
count(*) from t1 where object_id=:v_id 4 1 4
3）繫結變數迴圈執行五次操作：
begin
for i in 1 .. 5 loop
execute immediate 'select count(*) from t1 where object_id=:1'
using i + 100;
end loop;
end;
/
SYS@ORA11GR2>begin
2 for i in 1..5 loop
3 execute immediate 'select count(*) from t1 where object_id=:1' using i+100;
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
驗證檢視解析執行情況：
SYS@ORA11GR2>select sql_text,s.parse_calls,loads,executions from v$sql s where sql_text like 'select count(*) from t1%' order by 1,2,3,4;
SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
---------------------------------------- ----------- ---------- ----------
select count(*) from t1 where object_id=101 1 1 1
select count(*) from t1 where object_id=102 1 1 1
select count(*) from t1 where object_id=103 1 1 1
select count(*) from t1 where object_id=:1 1 1 5
SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
---------------------------------------- ----------- ---------- ----------
select count(*) from t1 where object_id=:v_id 3 1 3
即總共解析了1次，而執行了五次，剩餘四次的執行也沒有經過解析就直接執行了，即軟軟解析；
軟軟解析，定義為同一sql連續執行了三次以上，則將sql的執行計劃放到server process的pga中，下次再執行同一sql時跳過解析階段直接執行。
３．當session_cached_cursors＝０時進行操作：
1)修改session_cached_cursor引數值為0；
SYS@ORA11GR2>show parameter session_cached_cursor
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 50
SYS@ORA11GR2>alter system set session_cached_cursors=0 scope=spfile;
System altered.
SYS@ORA11GR2>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ORA11GR2>startup
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 503319672 bytes
Database Buffers 322961408 bytes
Redo Buffers 2392064 bytes
Database mounted.
Database opened.
SYS@ORA11GR2>show parameter session_cached_cursor
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors integer 0
2）直接進行查詢操作（無繫結變數）：
SYS@ORA11GR2>select count(*) from t1 where object_id=101;
COUNT(*)
----------
1
SYS@ORA11GR2>select count(*) from t1 where object_id=102;
COUNT(*)
----------
1
SYS@ORA11GR2>select count(*) from t1 where object_id=103;
COUNT(*)
----------
1
驗證檢視解析執行情況：
SYS@ORA11GR2>select sql_text,s.parse_calls,loads,executions from v$sql s where sql_text like 'select count(*) from t1%' order by 1,2,3,4;
SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
---------------------------------------- ----------- ---------- ----------
select count(*) from t1 where object_id=101 1 1 1
select count(*) from t1 where object_id=102 1 1 1
select count(*) from t1 where object_id= 103 1 1 1
三次硬解析
3）用繫結變數執行sql查詢語句：
SYS@ORA11GR2>var v_id number;
SYS@ORA11GR2>exec :v_id:=101;
PL/SQL procedure successfully completed.
SYS@ORA11GR2>select count(*) from t1 where object_id=:v_id;
COUNT(*)
----------
1
SYS@ORA11GR2>exec :v_id:=102;
PL/SQL procedure successfully completed.
SYS@ORA11GR2>select count(*) from t1 where object_id=:v_id;
COUNT(*)
----------
1
SYS@ORA11GR2>exec :v_id:=103;
PL/SQL procedure successfully completed.
SYS@ORA11GR2>select count(*) from t1 where object_id=:v_id;
COUNT(*)
----------
1
驗證檢視解析執行情況：
SYS@ORA11GR2>select sql_text,s.parse_calls,loads,executions from v$sql s where sql_text like 'select count(*) from t1%' order by 1,2,3,4;
SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
---------------------------------------- ----------- ---------- ----------
select count(*) from t1 where object_id=101 1 1 1
select count(*) from t1 where object_id=102 1 1 1
select count(*) from t1 where object_id=103 1 1 1
select count(*) from t1 where object_id=：v_id 3 1 3
軟解析，共3次解析，後2次軟解析，執行3次；
4）繫結變數迴圈執行五次操作：
begin
for i in 1 .. 5 loop
execute immediate 'select count(*) from t1 where object_id=:1'
using i + 100;
end loop;
end;
/
SYS@ORA11GR2>begin
2 for i in 1..5 loop
3 execute immediate 'select count(*) from t1 where object_id=:1' using i+100;
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
驗證檢視解析執行情況：
SYS@ORA11GR2>select sql_text,s.parse_calls,loads,executions from v$sql s where sql_text like 'select count(*) from t1%' order by 1,2,3,4;
SQL_TEXT PARSE_CALLS LOADS EXECUTIONS
---------------------------------------- ----------- ---------- ----------
select count(*) from t1 where object_id=101 1 1 1
select count(*) from t1 where object_id=102 1 1 1
select count(*) from t1 where object_id=103 1 1 1
select count(*) from t1 where object_id=：1 5 1 5
select count(*) from t1 where object_id=：v_id 3 1 3
此次沒有引數軟軟解析，表明當session_cached_cursors值設為0時，不產生軟軟解析（不管同一語句執行了多少次）；
open_cursors與session_cached_cursor：
a、兩個引數之間沒有任何關係，相互也不會有任何影響。
b、兩個引數有著相同的作用：讓後續相同的sql語句不在開啟遊標，從而避免軟解析過程來提供應用程式的效率。
C、pga中的cursor（軟軟解析）管理採用LRU原則，當一個session打算parse一個sql時,它會先去pga內搜尋session cursor cache list,如果找到那麼會把這個cursor脫離list,然後當關閉的時候再把這個cursor加到MRU端.session_cached_cursor提供了快速軟分析的功能,提供了比soft parse更高的效能.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2126425/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 前端之DOM解析和渲染與CSS、JS之間的關係前端CSSJS
- Retrofit2 完全解析 探索與okhttp之間的關係HTTP
- 多版本cursor，與session_cached_cursors關係Session
- ODS與DW之間的關係
- TLS與SSL之間關係TLS
- ps 與 svmon之間關係
- 求引數遍歷疊加與結果之間強關係的探索測試思路
- 類與類之間的基本關係
- 併發使用者數與TPS之間的關係
- 引數SESSION_CACHED_CURSORSSession
- 思考 TPS 與 RT 之間的關係
- 談Ubuntu與FOSS之間的關係(轉)Ubuntu
- FAILGROUP和REDUNDANCY之間的關係關係！AI
- v$sqlarea_parse_calls與executions與session_cached_cursors關係SQLSession
- 類之間的關係
- 關於open_cursors和session_cached_cursors的引數值Session
- 成員方法與const之間的關係
- 引數 session_cached_cursors 與 open_cursors詳解Session
- 【java】類之間的關係Java
- 探索“精益”與“智慧製造”之間的關係
- dispaly、position、float之間的關係與相互作用
- ERP與精益生產之間的關係
- oracle 11G引數檔案之伺服器引數檔案(spfile)與例項啟動的關係Oracle伺服器
- Window、WindowManager、View 之間的關係View
- UML中類之間的關係
- tablespace和datafile之間的關係
- 不同層之間的物件關係物件
- 大資料技術與Hadoop之間的關係大資料Hadoop
- 特殊特性與FMEA之間的關係是什麼？
- 統計學三大相關係數之Pearson相關係數、Spearman相關係數
- 個人理解emulateJSON作用 與java後臺介面引數的關係JSONJava
- CentOS升級核心與容器執行時核心引數的關係CentOS
- Window, WindowManager和WindowManagerService之間的關係
- git、github、gitlab之間的關係GithubGitlab
- UML類圖--類之間的關係
- Activity、View、Window之間關係的分析View
- QT中類之間的關係圖QT
- .Net Framework各版本之間的關係Framework