程序博客网 > 淘宝 卖家客服电话

使用优化器性能视图获取SQL语句执行环境

来源：互联网 发布：淘宝 卖家客服电话 编辑：程序博客网 时间：2024/05/21 08:36
 Oracle SQL语句的运行环境分为多个不同的层次，主要包括实例级别，会话级别，语句级别，其优先级依次递增。即语句级别的执行环境具
有最高的优先权，会话级别次之，实例级别最低。反过来，实例级别的环境设置影响全局，而会话级别的则影响当前会话，语句级别的设置当然
也就只影响当前语句。由此可知，运行环境中每一个环节的参数都对最终的数据库性能或所执行的SQL语句有直接的影响。因此在对数据库优化
或调试SQL时，获得当前SQL语句运行环境显得尤为重要。为此，Oracle提供了三个重要的视图来获取不同级别的参数信息。

一、优化器性能视图名字
[sql] view plaincopyprint?
  1. --下面的三个性能视图分别对应到不同的级别  
  2. scott@ORCL> select table_name from dict where table_name like 'V$%OPTIMIZER_ENV';  
  3.   
  4. TABLE_NAME  
  5. ------------------------------  
  6. V$SYS_OPTIMIZER_ENV  
  7. V$SES_OPTIMIZER_ENV  
  8. V$SQL_OPTIMIZER_ENV  
  9.   
  10. --注：上述的3个视图实际上是Oracle 10053事件中参数的一个子集  
二、实例级别执行环境(视图V$SYS_OPTIMIZER_ENV)
[sql] view plaincopyprint?
  1. --提供实例级别的环境执行的信息。Oracle 优化器在为SQL语句生成执行计划时，会根据实例级别参数的值来确定，如 optimizer_mode 用于  
  2. --确定优化器的模式，optimizer_index_cost_adj用于确定全表扫描与索引扫描之间成本开销的比值等。  
  3. --实例级别的参数可以使用下面的方式来设置  
  4.   
  5. alter system set parameter=value scope=memory | both |spfile;  
  6.   
  7. --我们来看看当前实例级别的运行环境  
  8.   
  9. scott@ORCL> select  
  10.   2  name,  
  11.   3  value,  
  12.   4  isdefault  
  13.   5  from  
  14.   6  v$sys_optimizer_env  
  15.   7  order by 3,1;  
  16.   
  17. NAME                               |VALUE          |ISD  
  18. -----------------------------------|---------------|---  
  19. statistics_level                   |all            |NO     -->非缺省值在此显示为NO  
  20. active_instance_count              |1              |YES  
  21. bitmap_merge_area_size             |1048576        |YES  
  22. cpu_count                          |1              |YES  
  23. cursor_sharing                     |exact          |YES  
  24. hash_area_size                     |131072         |YES  
  25. optimizer_dynamic_sampling         |2              |YES  
  26. optimizer_features_enable          |10.2.0.1       |YES  
  27. optimizer_index_caching            |0              |YES  
  28. optimizer_index_cost_adj           |100            |YES  
  29. optimizer_mode                     |all_rows       |YES  
  30. optimizer_secure_view_merging      |true           |YES  
  31. parallel_ddl_mode                  |enabled        |YES  
  32. parallel_dml_mode                  |disabled       |YES  
  33. parallel_execution_enabled         |true           |YES  
  34. parallel_query_mode                |enabled        |YES  
  35. parallel_threads_per_cpu           |2              |YES  
  36. pga_aggregate_target               |59392 KB       |YES  
  37. query_rewrite_enabled              |true           |YES  
  38. query_rewrite_integrity            |enforced       |YES  
  39. skip_unusable_indexes              |true           |YES  
  40. sort_area_retained_size            |0              |YES  
  41. sort_area_size                     |65536          |YES  
  42. star_transformation_enabled        |false          |YES  
  43. workarea_size_policy               |auto           |YES  
  44.   
  45. --从上面的查询结果可知，当前系统中仅有statistics_level参数使用了非缺省值，statistics_level参数的缺省值为typical，而此处为all。  
  46. --对于实例级别任意参数的修改其影响都是全局的，因此应当慎重。当然，如果某个参数设置的不合理，其影响也是全局的，因此对于实例级别  
  47. --参数的合理设置，可以避免成百上千枯燥的SQL语句调整，反之整个数据库性能急剧下降也可能是因为实例级别参数设置不当所致。  
三、会话级别执行环境(视图V$SES_OPTIMIZER_ENV)
[sql] view plaincopyprint?
  1. --与实例级别相对应的会话级别视图是V$SES_OPTIMIZER_ENV。此时session 级别的参数值会优先于实例级别的参数值。  
  2. --我们可以通过V$SYS_OPTIMIZER_ENV与V$SES_OPTIMIZER_ENV来获得当前或某个特定session的所有session级别参数值。  
  3.   
  4. scott@ORCL> show parameter cursor_shar  
  5.   
  6. NAME                                |TYPE       |VALUE  
  7. ------------------------------------|-----------|------------------  
  8. cursor_sharing                      |string     |EXACT  
  9. scott@ORCL> alter session set cursor_sharing=force;  
  10.   
  11. Session altered.  
  12.   
  13. scott@ORCL> show parameter cursor_shar  
  14.   
  15. NAME                                |TYPE       |VALUE  
  16. ------------------------------------|-----------|------------------  
  17. cursor_sharing                      |string     |FORCE  
  18. scott@ORCL> alter session set optimizer_mode=first_rows_100;  
  19.   
  20. Session altered.  
  21.   
  22. scott@ORCL> show parameter optimizer_mode  
  23.   
  24. NAME                                |TYPE                            |VALUE  
  25. ------------------------------------|--------------------------------|--------------------  
  26. optimizer_mode                      |string                          |FIRST_ROWS_100      
  27.   
  28. scott@ORCL> alter session set db_file_multiblock_read_count=128;  
  29.   
  30. Session altered.  
  31.   
  32. scott@ORCL> select username,sid,serial# from v$session where username='SCOTT';  
  33.   
  34. USERNAME                      |       SID|   SERIAL#  
  35. ------------------------------|----------|----------  
  36. SCOTT                         |       159|         5  
  37.   
  38. scott@ORCL> select name,value,isdefault  
  39.   2  from v$ses_optimizer_env  
  40.   3  where sid=&input_sid   
  41.   4  minus  
  42.   5  select name,value,isdefault  
  43.   6  from v$sys_optimizer_env;  
  44. Enter value for input_sid: 159  
  45. old   3: where sid=&input_sid  
  46. new   3: where sid=159  
  47.   
  48. NAME                               |VALUE          |ISD  
  49. -----------------------------------|---------------|---  
  50. _db_file_optimizer_read_count      |128            |NO  
  51. cursor_sharing                     |force          |NO  
  52. optimizer_mode                     |first_rows_100 |NO  
四、语句级别执行环境(视图V$SQL_OPTIMIZER_ENV)
[sql] view plaincopyprint?
  1. --影响SQL语句运行的执行环境受到实例级别以及会话级别之外，最后的，优先级最高的级别即是语句级别。因此语句级别的设置直接影响最终  
  2. --SQL语句执行计划的产生。SQL语句级别的参数设置是通过提示来完成的。     
  3. --在下面的例子中，我们通过为同一SQL 语句使用提示来使其运行在不同的环境之中。  
  4.   
  5. scott@ORCL> select /*+ no_hint */ ename,job,sal from emp where empno=7369;  -->此SQL语句会根据实例与会话级别的优化器环境  
  6.                                                                             -->的设置来生成SQL执行计划并执行  
  7. ENAME     |JOB      |       SAL                                             -->此时的no_hint不是一个有效的提示，被当成注释  
  8. ----------|---------|----------  
  9. Henry     |CLERK    |      1400  
  10.   
  11. scott@ORCL> select /*+ all_rows */ ename,job,sal from emp where empno=7369;  -->此时该SQL语句使用了all_rows提示  
  12.                                                                              -->注，此语句与上一语句尽管得到的相同的结果  
  13. ENAME     |JOB      |       SAL                                              -->但运行在不同的优化器环境模式之中  
  14. ----------|---------|----------                                              -->两者会有不同的sql_id  
  15. Henry     |CLERK    |      1400   
  16.   
  17. scott@ORCL> show parameter optimizer_index_cost_adj                          -->查看参数设置  
  18.   
  19. NAME                                |TYPE       |VALUE  
  20. ------------------------------------|-----------|------------------------------  
  21. optimizer_index_cost_adj            |integer    |100  
  22.   
  23. scott@ORCL> show parameter cursor_sharing                                    -->查看参数设置  
  24.   
  25. NAME                                |TYPE       |VALUE  
  26. ------------------------------------|-----------|------------------------------  
  27. cursor_sharing                      |string     |EXACT  
  28.   
  29. scott@ORCL> select avg(sal) from emp;                                        -->聚合运算  
  30.   
  31.   AVG(SAL)  
  32. ----------  
  33.       2335  
  34.   
  35. scott@ORCL> alter session set cursor_sharing=force;                          -->基于session级别修改参数  
  36.   
  37. Session altered.  
  38.   
  39. scott@ORCL> alter session set optimizer_index_cost_adj=80;                   -->基于session级别修改参数  
  40.   
  41. Session altered.  
  42.   
  43. scott@ORCL> select avg(sal) from emp;                                        -->再次聚合运算  
  44.   
  45.   AVG(SAL)  
  46. ----------  
  47.       2335  
  48.   
  49. -->下面的查询得到同一SQL语句由于不同运行环境的sql_id,child_number等       
  50. scott@ORCL> select sql_id, child_number, sql_text,optimizer_env_hash_value oehv,child_address  
  51.   2  from v$sql where sql_text like '%avg(sal)%' and sql_text not like '%from v$sql%';  
  52.   
  53. SQL_ID       |CHILD_NUMBER|SQL_TEXT                           |      OEHV|CHILD_AD  
  54. -------------|------------|-----------------------------------|----------|--------  
  55. 5du23va3p3ad0|           0|select avg(sal) from emp           |2196913643|3015F434  
  56. 5du23va3p3ad0|           1|select avg(sal) from emp           |3293423667|2CD868A0  
  57.   
  58. -->获得同一SQL语句父游标与子游标执行环境不同的地方  
  59. scott@ORCL> select se0.name,se0.value as value_child_0,se1.value as value_child_1  
  60.   2  from v$sql_optimizer_env se0  
  61.   3  inner join v$sql_optimizer_env se1  
  62.   4  on se0.sql_id=se1.sql_id  
  63.   5  where se0.sql_id='&input_sql_id'  
  64.   6  and se0.child_number=0  
  65.   7  and se1.child_number=1  
  66.   8  and se0.name=se1.name  
  67.   9  and se0.value<>se1.value;  
  68. Enter value for input_sql_id: 5du23va3p3ad0  
  69. old   5: where se0.sql_id='&input_sql_id'  
  70. new   5: where se0.sql_id='5du23va3p3ad0'  
  71.   
  72. NAME                                    |VALUE_CHILD_0            |VALUE_CHILD_1  
  73. ----------------------------------------|-------------------------|-------------------------  
  74. cursor_sharing                          |exact                    |force  
  75. optimizer_index_cost_adj                |100                      |80  
  76.   
  77. -->获得上一SQL语句运行时优化器的详细信息  
  78. -->我们看到父游标与子游标的信息全部被列出来  
  79. scott@ORCL> select address, name, value, isdefault,child_number  
  80.   2  from v$sql_optimizer_env  
  81.   3  where sql_id = '5du23va3p3ad0'  
  82.   4  order by child_number, isdefault, name;  
  83.   
  84. ADDRESS |NAME                                    |VALUE                    |ISD|CHILD_NUMBER  
  85. --------|----------------------------------------|-------------------------|---|------------  
  86. 30177094|sqlstat_enabled                         |true                     |NO |           0  
  87. 30177094|statistics_level                        |all                      |NO |           0  
  88. 30177094|active_instance_count                   |1                        |YES|           0  
  89. 30177094|bitmap_merge_area_size                  |1048576                  |YES|           0  
  90. 30177094|cpu_count                               |1                        |YES|           0  
  91. 30177094|cursor_sharing                          |exact                    |YES|           0  
  92. 30177094|hash_area_size                          |131072                   |YES|           0  
  93. 30177094|optimizer_dynamic_sampling              |2                        |YES|           0  
  94. 30177094|optimizer_features_enable               |10.2.0.1                 |YES|           0  
  95. 30177094|optimizer_index_caching                 |0                        |YES|           0  
  96. 30177094|optimizer_index_cost_adj                |100                      |YES|           0  
  97. 30177094|optimizer_mode                          |all_rows                 |YES|           0  
  98. 30177094|optimizer_secure_view_merging           |true                     |YES|           0  
  99. 30177094|parallel_ddl_mode                       |enabled                  |YES|           0  
  100. 30177094|parallel_dml_mode                       |disabled                 |YES|           0  
  101. 30177094|parallel_execution_enabled              |true                     |YES|           0  
  102. 30177094|parallel_query_mode                     |enabled                  |YES|           0  
  103. 30177094|parallel_threads_per_cpu                |2                        |YES|           0  
  104. 30177094|pga_aggregate_target                    |59392 KB                 |YES|           0  
  105. 30177094|query_rewrite_enabled                   |true                     |YES|           0  
  106. 30177094|query_rewrite_integrity                 |enforced                 |YES|           0  
  107. 30177094|skip_unusable_indexes                   |true                     |YES|           0  
  108. 30177094|sort_area_retained_size                 |0                        |YES|           0  
  109. 30177094|sort_area_size                          |65536                    |YES|           0  
  110. 30177094|star_transformation_enabled             |false                    |YES|           0  
  111. 30177094|workarea_size_policy                    |auto                     |YES|           0  
  112. 30177094|cursor_sharing                          |force                    |NO |           1  
  113. 30177094|optimizer_index_cost_adj                |80                       |NO |           1  
  114. 30177094|sqlstat_enabled                         |true                     |NO |           1  
  115. 30177094|statistics_level                        |all                      |NO |           1  
  116. 30177094|active_instance_count                   |1                        |YES|           1  
  117. 30177094|bitmap_merge_area_size                  |1048576                  |YES|           1  
  118. 30177094|cpu_count                               |1                        |YES|           1  
  119. 30177094|hash_area_size                          |131072                   |YES|           1  
  120. 30177094|optimizer_dynamic_sampling              |2                        |YES|           1  
  121. 30177094|optimizer_features_enable               |10.2.0.1                 |YES|           1  
  122. 30177094|optimizer_index_caching                 |0                        |YES|           1  
  123. 30177094|optimizer_mode                          |all_rows                 |YES|           1  
  124. 30177094|optimizer_secure_view_merging           |true                     |YES|           1  
  125. 30177094|parallel_ddl_mode                       |enabled                  |YES|           1  
  126. 30177094|parallel_dml_mode                       |disabled                 |YES|           1  
  127. 30177094|parallel_execution_enabled              |true                     |YES|           1  
  128. 30177094|parallel_query_mode                     |enabled                  |YES|           1  
  129. 30177094|parallel_threads_per_cpu                |2                        |YES|           1  
  130. 30177094|pga_aggregate_target                    |59392 KB                 |YES|           1  
  131. 30177094|query_rewrite_enabled                   |true                     |YES|           1  
  132. 30177094|query_rewrite_integrity                 |enforced                 |YES|           1  
  133. 30177094|skip_unusable_indexes                   |true                     |YES|           1  
  134. 30177094|sort_area_retained_size                 |0                        |YES|           1  
  135. 30177094|sort_area_size                          |65536                    |YES|           1  
  136. 30177094|star_transformation_enabled             |false                    |YES|           1  
  137. 30177094|workarea_size_policy                    |auto                     |YES|           1  
  138.   
  139. 52 rows selected.  
  140.   
  141. -->Author : Robinson Cheng    
  142. -->Blog :   http://blog.csdn.net/robinson_0612   
五、总结
1、通过三个重要的优化器性能视图v$sys_optimizer_env,v$ses_optimizer_env,v$sql_optimizer_env可以获得SQL语句的优化器相关的信息
2、三个性能视图获得的数据实际上Oracle 10053 事件的一部分，即也可以通过Oracle 10053 事件进行剖析来得到相同的结果
3、为调试SQL语句设置不同级别的优化器相关参数提供了便利
淘宝 卖家客服电话 淘宝 卖家客服电话
原创粉丝点击
热门IT博客
双十一淘宝图书打折吗双十一淘宝图书打折吗
网络盒子与机顶盒网络盒子与机顶盒
英文小说翻译软件
stp指定端口选举过程
mac更新系统
java中string的split
软件评测师
php正则表达式中w
破解加密狗软件
python下载安装32位
js获取项目相对路径
截屏软件
淘宝上怎么找人帮代卖
数据采集器破解版
midi音乐制作软件 mac
2016网络电视剧排行榜
淘宝网店需要授权吗
俄罗斯评论中国网络
node v6.5.0 x64.msi
谁说菜鸟不懂数据分析
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 上川岛自驾游 上川岛一日游攻略 上川岛好玩还是下川岛好玩 上川岛和下川岛哪个好玩 上川岛150元门票包括哪些费用 台山上川岛旅游攻略 上下川岛自助游攻略 下川岛旅游 下川岛旅游攻略 上州屋渔具店 上州屋钓具网 上巳节 福蓉科技上市后目标价多少 九价疫苗国内上市时间 上师大 上师大教务处 上师大图书馆 上师大附中 上师大附二外国语学校 上师大心理咨询师培训 上师大在职研究生 上师大心理学考研 首师大 闽南师大 首师大附中 师大地址 师大 李蓝师大附中 师大专业 内师大首页 师大学院 内师大 师大附中 师大首页 直属师范大学 好的师范大学 省师范大学 部属师范大学 免费师范大学 师范大学吧 上帝英语怎么说

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里