在性能调优的时候，会发现很多类型的问题，有些问题可能通过使用隐含参数就能够解决，不过这种变更需要特别注意，因为做隐含参数的变更无形中会影响到其它的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
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.
通过上面的测试发现，执行计划都是取平均的统计信息，达到了预期的目标。
