【實驗】shared_pool的sql命中率--cursor_sharing引數研究
1.提高命中率是為了減少硬編譯（hard parse），增加軟編譯（soft parse），從而提高sql語句的解析效率
2.sql語句執行過程
1).使用hash演算法得到sql語句的hash_value值
2).如果hash_value值在記憶體中，叫做命中執行軟解析
3).如果hash_value值不存在，執行硬解析
4).語法解析，檢視是否有錯誤
5).語意解析，檢視許可權是否符合
6).若有檢視，取出檢視的定義
7).進行sql語句的自動改寫，如將子查詢改寫為連線
8).選擇最優的執行計劃
9).變數繫結
10).執行執行計劃
11).返回結果給使用者
因為軟解析是從此11步驟中第9步開始的，因此軟解析比硬解析節約大量的系統開銷，應該儘量降低硬解析的次數。
3.共享池的命中率
sec@ora10g> select namespace, pins, pinhits, reloads, invalidations from v$librarycache order by namespace;
NAMESPACE PINS PINHITS RELOADS INVALIDATIONS
--------------- ---------- ---------- ---------- -------------
BODY 4380 4307 14 0
CLUSTER 942 928 3 0
INDEX 2378 1665 25 0
JAVA DATA 0 0 0 0
JAVA RESOURCE 0 0 0 0
JAVA SOURCE 0 0 0 0
OBJECT 0 0 0 0
PIPE 0 0 0 0
SQL AREA 132073 121593 370 74
TABLE/PROCEDURE 29526 24022 971 0
TRIGGER 935 926 2 0
11 rows selected.
4.例項啟動以來的命中率
sec@ora10g> select sum(pinhits)/sum(pins) from v$librarycache;
SUM(PINHITS)/SUM(PINS)
----------------------
.901459105
5.如果sql的命中率小於90%就需要對其進行最佳化，最佳化方法
1).加大shared_pool_size的大小，過猶不及，太大會增加資料的額外管理負擔
2).書寫程式是儘量使用變數不要過多的使用常量
3).將大的包pin在記憶體中
4).修改cursor_sharing初始化引數
6.實驗，驗證cursor_sharing引數三個不同選項（exact, similar, force）的差別
1).構造一個列值分佈不均勻的大表，empno列只有一行等於2000其他都為1000
sys@ora10g> conn sec/sec
Connected.
sec@ora10g>
sec@ora10g> create table t1 as select * from emp;
Table created.
sec@ora10g> insert into t1 select * from t1;
14 rows created.
sec@ora10g> /
sec@ora10g> /
57344 rows created.
sec@ora10g> commit;
Commit complete.
sec@ora10g> select count(*) from t1;
COUNT(*)
----------
114688
sec@ora10g> update t1 set empno=1000;
114688 rows updated.
sec@ora10g> commit;
Commit complete.
sec@ora10g> update t1 set empno=2000 where rownum=1;
1 row updated.
2).建立索引
sec@ora10g> create index i_t1 on t1(empno);
Index created.
3).對錶進行分析，告知資料庫表的大小
sec@ora10g> analyze table t1 compute statistics;
Table analyzed.
4).對列進行分析，資料庫可以識別出來表中資料是分佈不均勻的
sec@ora10g> analyze table t1 compute statistics for columns empno;
Table analyzed.
5).exact精確匹配（系統預設的模式）
sec@ora10g> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------ -------------------- --------
cursor_sharing string EXACT
sec@ora10g> set autotrace traceonly explain;
sec@ora10g> select * from t1 where empno=1000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 114K| 3471K| 168 (2)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T1 | 114K| 3471K| 168 (2)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=1000)
sec@ora10g> select * from t1 where empno=2000;
Execution Plan
----------------------------------------------------------
Plan hash value: 4068921349
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 31 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T1 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=2000)
6).similar近似匹配
sec@ora10g> conn / as sysdba
Connected.
sys@ora10g> alter system set cursor_sharing=similar scope=spfile;
System altered.
sys@ora10g> startup force;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1266392 bytes
Variable Size 113249576 bytes
Database Buffers 50331648 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
sys@ora10g> show parameter cursor_sharing
NAME TYPE VALUE
-------------------------------- -------------------- ---------
cursor_sharing string SIMILAR
sec@ora10g> set autot traceonly explain
sec@ora10g> select * from t1 where empno=1000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 114K| 3471K| 168 (2)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T1 | 114K| 3471K| 168 (2)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=1000)
sec@ora10g> select * from t1 where empno=2000;
Execution Plan
----------------------------------------------------------
Plan hash value: 4068921349
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 31 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T1 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=2000)
7).force，強制匹配
sec@ora10g> conn / as sysdba
Connected.
sys@ora10g> alter system set cursor_sharing=force scope=spfile;
System altered.
sys@ora10g> startup force;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1266392 bytes
Variable Size 113249576 bytes
Database Buffers 50331648 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
sys@ora10g> show parameter cursor_sharing
NAME TYPE VALUE
-------------------------------- -------------------- --------
cursor_sharing string FORCE
sys@ora10g> conn sec/sec
Connected.
sec@ora10g> set autotrace traceonly explain;
sec@ora10g> select * from t1 where empno=1000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 114K| 3471K| 168 (2)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T1 | 114K| 3471K| 168 (2)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=1000)
sec@ora10g> select * from t1 where empno=2000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 696 | 167 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T1 | 8 | 696 | 167 (1)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=2000)
Note
-----
- dynamic sampling used for this statement
sec@ora10g> select * from t1 where empno=2000;
Execution Plan
----------------------------------------------------------
Plan hash value: 4068921349
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 31 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T1 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=2000)
7.小結：
1).EXACT精確匹配，原語句不做處理，降低了sql的命中率，但可以保證執行計劃是準確的，此種模式為系統預設的模式；
2).SIMILAR近似匹配，將where條件都用變數來處理，單可以區分列值的資料敏感性，一種折中的方案，但是oracle在處理該類引數的sql語句時會有一定的問題，慎用；
3).FORCE強制匹配，將where條件都用變數來處理，提高了SQL的命中率，但不能區分列值的資料敏感性，執行計劃有時是正確的，但是有時會出現錯誤；
4).建議儘可能的保持系統預設的EXACT精確匹配模式，如需調整，建議在測試環境做好充足的驗證。
secooler
09.03.06
-- The End --
2.sql語句執行過程
1).使用hash演算法得到sql語句的hash_value值
2).如果hash_value值在記憶體中，叫做命中執行軟解析
3).如果hash_value值不存在，執行硬解析
4).語法解析，檢視是否有錯誤
5).語意解析，檢視許可權是否符合
6).若有檢視，取出檢視的定義
7).進行sql語句的自動改寫，如將子查詢改寫為連線
8).選擇最優的執行計劃
9).變數繫結
10).執行執行計劃
11).返回結果給使用者
因為軟解析是從此11步驟中第9步開始的，因此軟解析比硬解析節約大量的系統開銷，應該儘量降低硬解析的次數。
3.共享池的命中率
sec@ora10g> select namespace, pins, pinhits, reloads, invalidations from v$librarycache order by namespace;
NAMESPACE PINS PINHITS RELOADS INVALIDATIONS
--------------- ---------- ---------- ---------- -------------
BODY 4380 4307 14 0
CLUSTER 942 928 3 0
INDEX 2378 1665 25 0
JAVA DATA 0 0 0 0
JAVA RESOURCE 0 0 0 0
JAVA SOURCE 0 0 0 0
OBJECT 0 0 0 0
PIPE 0 0 0 0
SQL AREA 132073 121593 370 74
TABLE/PROCEDURE 29526 24022 971 0
TRIGGER 935 926 2 0
11 rows selected.
4.例項啟動以來的命中率
sec@ora10g> select sum(pinhits)/sum(pins) from v$librarycache;
SUM(PINHITS)/SUM(PINS)
----------------------
.901459105
5.如果sql的命中率小於90%就需要對其進行最佳化，最佳化方法
1).加大shared_pool_size的大小，過猶不及，太大會增加資料的額外管理負擔
2).書寫程式是儘量使用變數不要過多的使用常量
3).將大的包pin在記憶體中
4).修改cursor_sharing初始化引數
6.實驗，驗證cursor_sharing引數三個不同選項（exact, similar, force）的差別
1).構造一個列值分佈不均勻的大表，empno列只有一行等於2000其他都為1000
sys@ora10g> conn sec/sec
Connected.
sec@ora10g>
sec@ora10g> create table t1 as select * from emp;
Table created.
sec@ora10g> insert into t1 select * from t1;
14 rows created.
sec@ora10g> /
sec@ora10g> /
57344 rows created.
sec@ora10g> commit;
Commit complete.
sec@ora10g> select count(*) from t1;
COUNT(*)
----------
114688
sec@ora10g> update t1 set empno=1000;
114688 rows updated.
sec@ora10g> commit;
Commit complete.
sec@ora10g> update t1 set empno=2000 where rownum=1;
1 row updated.
2).建立索引
sec@ora10g> create index i_t1 on t1(empno);
Index created.
3).對錶進行分析，告知資料庫表的大小
sec@ora10g> analyze table t1 compute statistics;
Table analyzed.
4).對列進行分析，資料庫可以識別出來表中資料是分佈不均勻的
sec@ora10g> analyze table t1 compute statistics for columns empno;
Table analyzed.
5).exact精確匹配（系統預設的模式）
sec@ora10g> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------ -------------------- --------
cursor_sharing string EXACT
sec@ora10g> set autotrace traceonly explain;
sec@ora10g> select * from t1 where empno=1000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 114K| 3471K| 168 (2)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T1 | 114K| 3471K| 168 (2)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=1000)
sec@ora10g> select * from t1 where empno=2000;
Execution Plan
----------------------------------------------------------
Plan hash value: 4068921349
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 31 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T1 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=2000)
6).similar近似匹配
sec@ora10g> conn / as sysdba
Connected.
sys@ora10g> alter system set cursor_sharing=similar scope=spfile;
System altered.
sys@ora10g> startup force;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1266392 bytes
Variable Size 113249576 bytes
Database Buffers 50331648 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
sys@ora10g> show parameter cursor_sharing
NAME TYPE VALUE
-------------------------------- -------------------- ---------
cursor_sharing string SIMILAR
sec@ora10g> set autot traceonly explain
sec@ora10g> select * from t1 where empno=1000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 114K| 3471K| 168 (2)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T1 | 114K| 3471K| 168 (2)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=1000)
sec@ora10g> select * from t1 where empno=2000;
Execution Plan
----------------------------------------------------------
Plan hash value: 4068921349
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 31 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T1 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=2000)
7).force，強制匹配
sec@ora10g> conn / as sysdba
Connected.
sys@ora10g> alter system set cursor_sharing=force scope=spfile;
System altered.
sys@ora10g> startup force;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1266392 bytes
Variable Size 113249576 bytes
Database Buffers 50331648 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
sys@ora10g> show parameter cursor_sharing
NAME TYPE VALUE
-------------------------------- -------------------- --------
cursor_sharing string FORCE
sys@ora10g> conn sec/sec
Connected.
sec@ora10g> set autotrace traceonly explain;
sec@ora10g> select * from t1 where empno=1000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 114K| 3471K| 168 (2)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T1 | 114K| 3471K| 168 (2)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=1000)
sec@ora10g> select * from t1 where empno=2000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 696 | 167 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T1 | 8 | 696 | 167 (1)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=2000)
Note
-----
- dynamic sampling used for this statement
sec@ora10g> select * from t1 where empno=2000;
Execution Plan
----------------------------------------------------------
Plan hash value: 4068921349
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 31 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T1 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=2000)
7.小結：
1).EXACT精確匹配，原語句不做處理，降低了sql的命中率，但可以保證執行計劃是準確的，此種模式為系統預設的模式；
2).SIMILAR近似匹配，將where條件都用變數來處理，單可以區分列值的資料敏感性，一種折中的方案，但是oracle在處理該類引數的sql語句時會有一定的問題，慎用；
3).FORCE強制匹配，將where條件都用變數來處理，提高了SQL的命中率，但不能區分列值的資料敏感性，執行計劃有時是正確的，但是有時會出現錯誤；
4).建議儘可能的保持系統預設的EXACT精確匹配模式，如需調整，建議在測試環境做好充足的驗證。
secooler
09.03.06
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-562987/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- shared_pool的sql命中率SQL
- Oracle 的 cursor_sharing引數Oracle
- 【實驗】sql語句在shared_pool中的查詢（程式 繫結變數）SQL變數
- oracle引數-cursor_sharingOracle
- ORACLE中Cursor_sharing引數詳解Oracle
- 小心設定cursor_sharing=force引數
- Oracle一些引數的理解 cursor_sharingOracle
- 有關引數cursor_sharing=similar的測試MILA
- 補充：小心設定cursor_sharing=force引數
- mysql的sync_binlog引數實驗MySql
- SpringMVC實現引數校驗SpringMVC
- Oracle 檢查命中率的SQLOracleSQL
- oracle實驗記錄 （cursor_sharing(2)SIMILAR）OracleMILA
- Spring Boot實現通用的介面引數校驗Spring Boot
- pl/sql中的引數模式SQL模式
- 修改cursor_sharing引數引發的ORA-00600: internal error code, arguments: [qctcte1], [0], []..Error
- oracle實驗記錄 （cursor_sharing(1)exact&force）Oracle
- 實現Nest中引數的聯合型別校驗型別
- mysql的innodb_flush_log_at_trx_commit引數實驗MySqlMIT
- oracle實驗記錄 （storage儲存引數(1)）Oracle
- oracle實驗記錄 （storage儲存引數(2)）Oracle
- Sql Server 的引數化查詢SQLServer
- SQLBulkOperations及陣列作引數的SQLSQL陣列
- 每個月的sql引數配置SQL
- SQL Server 的max degree of parallelism引數SQLServerParallel
- SQL 掃描引數（SARG）SQL
- Laravel 引數驗證的疑與惑Laravel
- oracle實驗記錄 (快取記憶體命中率與成本)Oracle快取記憶體
- 檢視Oracle隱藏引數的SQLOracleSQL
- SPSS實現多個獨立的樣本非引數檢驗SPSS
- JavaScript形式引數和實際引數JavaScript
- 介面測試-引數校驗
- Spring Boot 引數校驗Spring Boot
- SQL*Plus Set引數詳解SQL
- mysql Sql引數用?而不用@MySql
- SQL SERVER 引數化選項SQLServer
- 隱藏引數查詢sqlSQL
- SQL Server效能分析引數 (轉)SQLServer