专栏首页杨建荣的学习笔记关于修改数据库参数的测试(r3笔记第18天)

关于修改数据库参数的测试(r3笔记第18天)

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

本文分享自微信公众号 - 杨建荣的学习笔记(jianrong-notes),作者:r3笔记第18天

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2014-10-07

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 聚簇因子和执行计划的联系(r3笔记第90天)

    在平时的工作中,可能会碰到一种很奇怪的问题,本来在生产环境中有些sql语句执行没有问题,一个很普通的查询预期走了索引扫面,但是拷贝数据到其它环境之后,就发现却走...

    jeanron100
  • 落地SQL审核的迭代思路

    在近一年的工作里面,发现很多事情不是一蹴而就,一个核心思想就是迭代,简而言之,迭代的目标有两个主要的结果,一个是从0到1,另外一个是从1到99。对于很多运维系统...

    jeanron100
  • 简单分析shared pool(三) (r5笔记第94天)

    提到shared pool,都会不由得和sql语句的解析过程联系起来,因为shared pool所做的主要工作就是解析sql语句,生成执行计划,在之前的两篇中...

    jeanron100
  • 几种去重的SQL写法

    墨天轮社区的每日一题(https://www.modb.pro/test),可以说是个小而精的专栏,利用碎片时间,就可以学习知识,非常推荐。

    bisal
  • 如何修改自增列值以及相应的解决方法

    今天工作中遇到特殊的一个任务,就是将两个自增列值的进行对调变更。 SQL Server 平台修改自增列值 由于之前处理过sql server数据库的迁移工作,尝...

    逸鹏
  • 安全科普:SQLi Labs 指南 Part 1

    译者:SQL Libs一直也没看到有人写过比较完整的指南,只有作者在自己的博客上帖了一些tip和一些视频,偶然看到一篇文章在写这个,便拿过来翻一下,以作参考,原...

    FB客服
  • 星巴克也开始送外卖啦luckin coffee通过小程序占据优势

    在瑞幸咖啡的威胁下,星巴克的业绩逐渐下滑,在这个情况下星巴克与阿里巴巴达成战略合作。一直注重体验营销的品牌现在才加入外卖和电商到底会不会被5个月开525家店的咖...

    微购儿小程序
  • 云+社区杂货摊年终上新,定制礼盒发发发!

    五谷丰登好时节,瑞雪兆迎丰收年。2020年即将度过,不管今年经历了什么样的风雨,云+社区始终作为一个温暖的港湾,和技术开发者们度过风雨,一同见证彩虹。

    腾小云
  • Python装饰器原理——偷梁换柱

    本篇博客写于学完  金角大王Alex和海峰老师  于  老男孩2016年周末班S14期第4周03~08章节   装饰器  的相关内容,为对该部分视频的一个思考与...

    py3study
  • go []string slice utils

    solate

扫码关注云+社区

领取腾讯云代金券