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

 Oracle SQL语句的运行环境分为多个不同的层次,主要包括实例级别,会话级别,语句级别,其优先级依次递增。即语句级别的执行环境具
有最高的优先权,会话级别次之,实例级别最低。反过来,实例级别的环境设置影响全局,而会话级别的则影响当前会话,语句级别的设置当然
也就只影响当前语句。由此可知,运行环境中每一个环节的参数都对最终的数据库性能或所执行的SQL语句有直接的影响。因此在对数据库优化
或调试SQL时,获得当前SQL语句运行环境显得尤为重要。为此,Oracle提供了三个重要的视图来获取不同级别的参数信息。

一、优化器性能视图名字
  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)
  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)
  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)
  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语句设置不同级别的优化器相关参数提供了便利
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页