在效能調優的時候，會發現很多型別的問題，有些問題可能透過使用隱含引數就能夠解決，不過這種變更需要特別注意，因為做隱含引數的變更無形中會影響到其它的sql語句執行。如果為了個別的問題而做了變更，導致了整個系統問題，那就得不償失了，這中變更一定得經過測試，至少在測試環境中部署測試過，而且解決的問題可能是通用的，要不真是吃力不討好。
個人建議有下面的步驟：
在測試環境部署。
測試前設定baselin，修改後，在同樣的負載下對比效能情況，是否有明顯的抖動。
分析收集的資訊，進行評估，是否達到預期的目標。

現在生產環境中碰到了一個問題，發現了bind peek的問題，有些sql語句的結構都是一致的，但是傳入的list變數值不同，結果導致系統的硬解析很高。
從系統中抓到的sql語句如下。可以看到變數值略有變化，但是sql_id每次都不一樣。
7ww8mgvpgwnvb SELECT xxxxx from xxxxx where PERIOD_KEY in(:3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 ,
:20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 , :32 , :33 , :34 , :35 , :36 , :37 , :38 , :39 ,
:40 , :41 ) xxxxxxx;

115hygxgm58ss SELECT xxxxx from xxxxx where PERIOD_KEY in(:3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 ,
:20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 , :32 , :33 , :34 , :35 , :36 , :37 , :38 , :39 ) xxxxxx

cq2pswgj3p9ru SELECT xxxxx from xxxxx where PERIOD_KEY in(:3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 ,
:20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 , :32 , :33 , :34 ) xxxxxx

0rp04bthp6whf SELECT xxxxx from xxxxx where PERIOD_KEY in(:3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 ,
:20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 , :32 , :33 , :34 , :35 , :36 , :37 , :38 , :39 ,
:40 ) xxxxxx

從分析來看，有很明顯的bin peek問題，有一個隱含引數和這個相關, _optim_peek_user_binds 這個特性可以關掉。
檢視隱含引數的語句如下：
SQL> set linesize 132
SQL> column name format a30
SQL> column value format a25
select
x.ksppinm name,
y.ksppstvl value,
y.ksppstdf isdefault,
decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj
from
sys.x$ksppi x,
sys.x$ksppcv y
where
x.inst_id = userenv('Instance') and
y.inst_id = userenv('Instance') and
x.indx = y.indx and
x.ksppinm like '%_&par%'
order by
translate(x.ksppinm, ' _', ' ')
SQL> /
Enter value for par: bind_peek
old 14: x.ksppinm like '%_&par%'
new 14: x.ksppinm like '%_bind_peek%'
NAME VALUE ISDEFAULT ISMOD ISADJ
------------------------------ ------------------------- --------- ---------- -----
_px_bind_peek_sharing TRUE TRUE FALSE FALSE

關於這個特性，可以這麼理解，比如表中有100萬條資料，會按照分佈的一個均值來統一顯示執行計劃，所以對於資料分佈比較均勻，那就是一個很平衡的提高，如果資料分佈不均勻，也是去均值，這樣能夠基本平衡。
當然了這個特性相當於對於問題的處理的一個很綜合的處理，所以可以根據自己的情況來決定是否啟用。
如果是分割槽表，比如有100個分割槽，那麼對於統計資訊而言就會取表級的統計資訊而不是基於分割槽級的。

這種引數想要進行驗證，是很困難的，你得很瞭解這個引數變更帶來的邊界，怎麼去界定這個變更的範圍。可能出現什麼問題，需要得到什麼樣的預期效果。
對於這個引數的測試，可以使用一個Hint來靈活的啟停（/*+opt_param('_optim_peek_user_binds', 'false')*/）。
來簡單驗證一下。
首先新建一個表，然後一通update，使得資料刻意的不均勻。然後透過動態的變數來檢視啟停hint得到的執行計劃和統計資訊。
SQL> set linesize 200
SQL> set pages 100
SQL> create table t (id varchar2(10),text varchar2(4000)) tablespace pool_data;

Table created.

SQL> insert into t select 1,object_name from dba_objects;

13576 rows created.

SQL> commit;

Commit complete.

SQL> update t set id=2 where id=1 and rownum<2;

1 row updated.

SQL> commit;

Commit complete.

SQL> select id,count(*) from t group by id; --可以看到資料的分佈是極不均勻的。

ID           COUNT(*)
---------- ----------
1               13575
2                   1

SQL> create index t_idx on t(id); --建立索引
Index created.

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for all indexed columns',cascade=>true); --收集統計資訊

PL/SQL procedure successfully completed.

SQL>
SQL> var fid varchar2;
SQL> exec :fid := '2';

PL/SQL procedure successfully completed.

SQL> alter system flush shared_pool; --重新整理shared pool

System altered.

SQL> alter system flush buffer_cache; --重新整理buffer_cache

System altered.

SQL> select * from t where id=:fid;

ID
----------
TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2
SYS_LOB0000000920C00008$$

SQL> select * from table(dbms_xplan.display_cursor(null)); --檢視執行計劃，使用了索引掃描

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fprdgayw5y5vq, child number 0
-------------------------------------
select * from t where id=:fid

Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id | Operation                   | Name | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |       |       |     2 (100)|          |
|   1 | TABLE ACCESS BY INDEX ROWID| T     |     1 |    21 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=:FID)

19 rows selected.

SQL> --alter system flush shared_pool;
SQL> --alter system flush buffer_cache;
SQL> --set autot trace exp stat --這個地方做標識，是因為個人的反覆測試發現使用set autot trace 得到的執行計劃不一樣
SQL> --select * from t where id=:fid;
SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.

SQL> alter session set "_optim_peek_user_binds"=false; --修改了隱含引數。

Session altered.

SQL> select * from t where id=:fid;

ID
----------
TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2
SYS_LOB0000000920C00008$$

SQL> select * from table(dbms_xplan.display_cursor(null)); --可以看到啟用特性之後，走了平均的統計資訊。

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fprdgayw5y5vq, child number 0
-------------------------------------
select * from t where id=:fid

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |       |    16 (100)|          |
|* 1 | TABLE ACCESS FULL| T    | 6788 |   139K|    16   (7)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID"=:FID)

18 rows selected.
--設定完後，為了突出變化，我們把隱含引數的設定去除，設定為原來的值。
SQL> alter session set "_optim_peek_user_binds"=true;

Session altered.
SQL> select * from t where id=:fid;

ID
----------
TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2
SYS_LOB0000000920C00008$$

SQL> select * from table(dbms_xplan.display_cursor(null)); --可以看到執行計劃又開始走索引掃描了。

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fprdgayw5y5vq, child number 1
-------------------------------------
select * from t where id=:fid

Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id | Operation                   | Name | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |       |       |     2 (100)|          |
|   1 | TABLE ACCESS BY INDEX ROWID| T     |     1 |    21 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=:FID)

19 rows selected.
SQL> select /*+opt_param('_optim_peek_user_binds', 'false')*/ id,text from t where id=:fid; --我們加入Hint來看看全表掃描

ID
----------
TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2
SYS_LOB0000000920C00008$$

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dpqhz5gjt26hh, child number 0
-------------------------------------
select /*+opt_param('_optim_peek_user_binds', 'false')*/ id,text from t
where id=:fid

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |       |    16 (100)|          |
|* 1 | TABLE ACCESS FULL| T    | 6788 |   139K|    16   (7)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID"=:FID)

19 rows selected.

SQL> select /*+opt_param('_optim_peek_user_binds', 'true')*/ id,text from t where id=:fid;

ID
----------
TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2
SYS_LOB0000000920C00008$$

SQL> select * from table(dbms_xplan.display_cursor(null)); --檢視去除bind peek之後的執行計劃，執行計劃又開始走索引

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID g80r4gd8sck2v, child number 0
-------------------------------------
select /*+opt_param('_optim_peek_user_binds', 'true')*/ id,text from t
where id=:fid

Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id | Operation                   | Name | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |       |       |     2 (100)|          |
|   1 | TABLE ACCESS BY INDEX ROWID| T     |     1 |    21 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=:FID)

20 rows selected.

透過上面的測試發現，執行計劃都是取平均的統計資訊，達到了預期的目標。

關於修改資料庫引數的測試

相關文章