Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
发新帖

6

积分

1

好友

7

主题
1#
发表于 2013-12-13 11:07:26 | 查看: 4978| 回复: 3
本帖最后由 dla001 于 2013-12-13 11:10 编辑

insert ,update 绑定变量非常多的情况下。会产生很多的version_count。
同事问我,是否能只用一个version_count。因为一个执行非常频繁的SQL绑定变量有150多个。
查过很多资料,产生的原因是变量分级导致的。在做试验时,发现个有意思的事。
这个是解决insert,update绑定version_count多的方法吗?
环境如下:
环境:1
DB:11.2.0.3.7 x64
OS:OEL5.8 x64
修改过的隐藏参数
NAME                                               VALUE                     ISDEFAULT                   ISMOD                          ISADJ
-------------------------------------------------- ------------------------- --------------------------- ------------------------------ ---------------
_disable_cursor_sharing                            FALSE                     TRUE                        FALSE                          FALSE
_optimizer_adaptive_cursor_sharing                 FALSE                     FALSE                       FALSE                          FALSE
_optimizer_extended_cursor_sharing                 NONE                      FALSE                       FALSE                          FALSE
_optimizer_extended_cursor_sharing_rel             NONE                      FALSE                       FALSE                          FALSE

其它参数未改变

环境:2
DB:11.2.0.3.7 x64
OS:CentOS5.7 x64
没有修改隐藏参数

实验如下:
  1. create table from_small_to_big( c1 varchar2(2001),c2 varchar2(2001),c3 varchar2(2001));
  2. create table from_big_to_small( c1 varchar2(2001),c2 varchar2(2001),c3 varchar2(2001));


  3. declare
  4. x varchar2(2001);
  5. y varchar2(2001);
  6. z varchar2(2001);
  7. begin
  8. x := rpad('A',32,'A');
  9. y := rpad('A',32,'A');
  10. z := rpad('A',32,'A');
  11. execute immediate 'insert into from_small_to_big values( :x, :y, :z)' using x, y, z;

  12. x := rpad('A',33,'A');  
  13. y := rpad('A',32,'A');
  14. z := rpad('A',32,'A');
  15. execute immediate 'insert into from_small_to_big values( :x, :y, :z)' using x, y, z;

  16. x := rpad('A',33,'A');
  17. y := rpad('A',33,'A');
  18. z := rpad('A',32,'A');
  19. execute immediate 'insert into from_small_to_big values( :x, :y, :z)' using x, y, z;

  20. x := rpad('A',32,'A');
  21. y := rpad('A',33,'A');
  22. z := rpad('A',32,'A');
  23. execute immediate 'insert into from_small_to_big values( :x, :y, :z)' using x, y, z;

  24. x := rpad('A',33,'A');
  25. y := rpad('A',33,'A');
  26. z := rpad('A',33,'A');
  27. execute immediate 'insert into from_small_to_big values( :x, :y, :z)' using x, y, z;

  28. x := rpad('A',2001,'A');
  29. y := rpad('A',2001,'A');
  30. z := rpad('A',2001,'A');
  31. execute immediate 'insert into from_small_to_big values( :x, :y, :z)' using x, y, z;

  32. commit;
  33. end;
  34. /


  35. declare
  36. x varchar2(2001);
  37. y varchar2(2001);
  38. z varchar2(2001);
  39. begin
  40. x := rpad('A',2001,'A');
  41. y := rpad('A',2001,'A');
  42. z := rpad('A',2001,'A');
  43. execute immediate 'insert into from_big_to_small values( :x, :y, :z)' using x, y, z;

  44. x := rpad('A',33,'A');
  45. y := rpad('A',33,'A');
  46. z := rpad('A',33,'A');
  47. execute immediate 'insert into from_big_to_small values( :x, :y, :z)' using x, y, z;

  48. x := rpad('A',32,'A');
  49. y := rpad('A',33,'A');
  50. z := rpad('A',32,'A');
  51. execute immediate 'insert into from_big_to_small values( :x, :y, :z)' using x, y, z;

  52. x := rpad('A',33,'A');
  53. y := rpad('A',33,'A');
  54. z := rpad('A',32,'A');
  55. execute immediate 'insert into from_big_to_small values( :x, :y, :z)' using x, y, z;

  56. x := rpad('A',33,'A');  
  57. y := rpad('A',32,'A');
  58. z := rpad('A',32,'A');
  59. execute immediate 'insert into from_big_to_small values( :x, :y, :z)' using x, y, z;

  60. x := rpad('A',32,'A');
  61. y := rpad('A',32,'A');
  62. z := rpad('A',32,'A');
  63. execute immediate 'insert into from_big_to_small values( :x, :y, :z)' using x, y, z;

  64. commit;
  65. end;
  66. /


  67. set linesize 200
  68. col sql_text  for a100
  69. select sql_id,sql_text,version_count,executions from v$sqlarea where sql_text like 'insert into from_big_to_small%' or sql_text like 'insert into from_small_to_big%';

  70. SQL_ID                                  SQL_TEXT                                                                                             VERSION_COUNT EXECUTIONS
  71. --------------------------------------- ---------------------------------------------------------------------------------------------------- ------------- ----------
  72. gba4nq26jsfuw                           insert into from_small_to_big values( :x, :y, :z)                                                                5          6
  73. 5210n56v52bsz                           insert into from_big_to_small values( :x, :y, :z)                                                                1          6
复制代码
可以发现,一个version count为 5,另一个version count为1。

得出的结论是:
    当第一个执行的SQL的变量的实际长度超过2000时,后面的SQL无论实际变量长度为多少,都不会产生新的version count 。实际会用4000字节来处理变量。
    当变量长度由短变长时,会产生新的version count。 (参考变量分级 32 128 2000)
    这也是为什么有的insert语句变量非常多,产生的version count也非常多的原因。
   


     那么,我们是否可以这样做,手动插入一条SQL,将所有的变量都给最大值,这样后面的SQL就不会再产生新的version count ?

     测试如下:
  1. create table less_version_count( c1 varchar2(2001),c2 varchar2(2001),c3 varchar2(2001));


  2. declare
  3. x varchar2(2001);
  4. y varchar2(2001);
  5. z varchar2(2001);
  6. begin
  7. x := rpad('A',32,'A');
  8. y := rpad('A',32,'A');
  9. z := rpad('A',32,'A');
  10. execute immediate 'insert into less_version_count values( :x, :y, :z)' using x, y, z;

  11. --- 在这里,我们插入全为2001长度的变量,之后无论变量实际长度怎么变,都不应该产生新的version count
  12. x := rpad('A',2001,'A');  
  13. y := rpad('A',2001,'A');
  14. z := rpad('A',2001,'A');
  15. execute immediate 'insert into less_version_count values( :x, :y, :z)' using x, y, z;

  16. x := rpad('A',33,'A');
  17. y := rpad('A',33,'A');
  18. z := rpad('A',32,'A');
  19. execute immediate 'insert into less_version_count values( :x, :y, :z)' using x, y, z;

  20. x := rpad('A',32,'A');
  21. y := rpad('A',33,'A');
  22. z := rpad('A',32,'A');
  23. execute immediate 'insert into less_version_count values( :x, :y, :z)' using x, y, z;

  24. x := rpad('A',33,'A');
  25. y := rpad('A',33,'A');
  26. z := rpad('A',33,'A');
  27. execute immediate 'insert into less_version_count values( :x, :y, :z)' using x, y, z;

  28. x := rpad('A',129,'A');
  29. y := rpad('A',129,'A');
  30. z := rpad('A',2000,'A');
  31. execute immediate 'insert into less_version_count values( :x, :y, :z)' using x, y, z;

  32. commit;
  33. end;
  34. /
  35. SQL_ID                                  SQL_TEXT                                                                                             VERSION_COUNT EXECUTIONS
  36. --------------------------------------- ---------------------------------------------------------------------------------------------------- ------------- ----------
  37. gba4nq26jsfuw                           insert into from_small_to_big values( :x, :y, :z)                                                                5          6
  38. 5j0ccy2ytwhk6                           insert into less_version_count values( :x, :y, :z)                                                                2          6
  39. 5210n56v52bsz                           insert into from_big_to_small values( :x, :y, :z)                                                                1          6
复制代码
结论:如果遇到insert,update version_count 非常多的SQL,可以手动插入数据,来解决version_count很多的情况?
是否还有没考虑全的地方?
update 没测试,只是猜测的。
2#
发表于 2013-12-14 16:18:33
如果遇到insert,update version_count 非常多的SQL,可以手动插入数据,来解决version_count很多的情况?

==>

version_count高是有原因的, 原因多种多样, 解决 version_count高 那么就要找到原因

Script:诊断解析等待和高version count
http://www.askmaclean.com/archiv ... -version-count.html

回复 只看该作者 道具 举报

3#
发表于 2013-12-14 17:27:33
使用你的脚本,查到的原因只有下面这两个。
这个是按主键更新的SQL。
  1. sql_id: gw3afcnq4vp7n

  2. ------------------------

  3. 附件是这个SQL的详细的报告。


  4. - BIND_MISMATCH                  count: 2499

  5. - BIND_LENGTH_UPGRADEABLE        count: 1353
复制代码
BIND_LENGTH_UPGRADEABLE 很好理解。
那个BIND_MISMATCH很不容易理解,看过MOS上给的例子:
variable a varchar2(100);
select count(*) from emp where ename = :a ->> 1 PARENT, 1 CHILD
variable a varchar2(400);
select count(*) from emp where ename = :a ->> 1 PARENT, 2 CHILDREN

   (The bind 'a' has now changed in definition)
这种情况在我们系统中可以排除了。定义的变量都是按字段长度定义的。还有什么会导致这个问题? null值?

version_rpt.txt

11.06 KB, 下载次数: 1138

回复 只看该作者 道具 举报

4#
发表于 2013-12-15 19:18:05
11gR2游标共享新特性带来的一些问题以及_cursor_features_enabled、_cursor_obsolete_threshold和106001 event
http://www.askmaclean.com/archiv ... d-106001-event.html

回复 只看该作者 道具 举报

您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-12-21 13:02 , Processed in 0.051204 second(s), 23 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569