shared_pool的sql命中率

如果一個SQL語句命中,將大大降低資料庫伺服器的負載,因為一個sql的硬解析生成執行計劃是很消耗資源的。

下面列一下一個sql語句的執行過程。

1.將SQL語句經過hash演算法後得到一個值Hash_Value

2.如果該值在記憶體中存在，那麼叫命中執行軟分析

3.如果該值不存在，執行硬解析

4.進行語法分析

5.進行語意分析

6如果有檢視，將檢視的定義取出

7.進行SQL語句的自動改寫，如將子查詢改成為連線

8.優選最佳的執行計劃

9.變數的繫結

10.執行執行計劃

11.將結果返回給使用者

如果是軟分析，直接執行9以後的步驟。

 

共享池的命中率

select namespace,pins,pinhits,reloads,invalidations from v$librarycache order by namespace;

NAMESPACE             PINS    PINHITS    RELOADS INVALIDATIONS
--------------- ---------- ---------- ---------- -------------
BODY                 29530      29446          0             0
CLUSTER                429        421          0             0
INDEX                   60          3          0             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            134280     128465        226            32
TABLE/PROCEDURE      63727      59391          4             0
TRIGGER               2375       2356          0             0

已選擇11行。

 

 

 

SUM(PINHITS)/SUM(PINS)
----------------------
            .955756135

 

根據如下檢視可以檢視shared_pool建議大小

select * from v$shared_pool_advice

 

如果SQL的命中率小於90%,我們就要最佳化,最佳化的手段如下:

1.加大shared_pool_size 的大小，v$shared_pool_advice 根據這個檢視

2.編寫程式的時候使用變數傳入，而不是使用常量

3.將大的包定在記憶體中

4.修改初始化引數cursor_sharing

   a.Force是比較理想的情況時候使用，如果你的業務邏輯很清晰，應用設計的非常好，那麼可以使用FORCE，我對   一  些小的專案設定成FORCE，這樣可以減少shared_pool的開支，9i的不建議如此設定，10.2.0.3以前的版本不建議這麼設定，有很多bug；
    b.EXACE是精確匹配變數的一種解析方式，這個模式下，如果一個sql查詢的時候where條件裡寫a=1和a=2時，最佳化器會生成新的執行計劃，而不認為是一直的sql，佔用shared_pool比率很嚴重；10.2.0.3以後的版本不建議設定；
    c.SIMILAR是個折中的方案，讓最佳化器自己去判斷，是Oracle比較嚮往的方式，但是無論是基於規則的最佳化器還是基於成本的最佳化器，目前做的都不是很好，Similar的bug目前要比FORCE還要多；

 

實驗進行驗證，如下:

 

conn scott/tiger

create table t1 as select * from emp;

insert into t1 select * from t1;

/

/

commt;

update t1 set empno=1000;

commit;

update t1 set empno=2000 where rownum=1;

commit;

create index i_t1 on t1(empno);

//分析表，告訴資料庫表的大小

analyze table t1 compute statistics;

//分析列，告訴資料庫empno列的資料分佈是不均勻的,只有一行為2000,其它所有行為1000

analyze table t1 compute statistics for columns empno;

 

show parameter cursor_sharing

exact

 

SQL> set autot traceonly explain
SQL> select * from scott.t1 where empno=1000;

執行計劃
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   229K|  6943K|   320   (5)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T1   |   229K|  6943K|   320   (5)| 00:00:04 |

 

 

SQL> select * from scott.t1 where empno=2000;

執行計劃
----------------------------------------------------------
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 |
------------------------------------------------------------------------------------

 

 

實驗 SIMILAR

conn /as sysdba

alter system set cursor_sharing=SIMILAR  scope=spfile

startup force

set autotrace traceonly