关于修改数据库参数的测试

更新时间：2022-05-06 17:56:37

在性能调优的时候，会发现很多类型的问题，有些问题可能通过使用隐含参数就能够解决，不过这种变更需要特别注意，因为做隐含参数的变更无形中会影响到其它的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;

SQL> select * from table(dbms_xplan.display_cursor(null)); --可以看到启用特性之后，走了平均的统计信息。

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;

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

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来看看全表扫描

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

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;

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

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

2 - access("ID"=:FID)

20 rows selected.

通过上面的测试发现，执行计划都是取平均的统计信息，达到了预期的目标。

上一篇 : ：多行数据的批处理之bulk collect下一篇 : pl/sql中错误的异常处理

关于修改数据库参数的测试

相关阅读

推荐文章