备案控制台
探索云世界
开发者社区 数据库 文章 正文

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

2012-04-09 846
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：     Oracle SQL语句的运行环境分为多个不同的层次，主要包括实例级别，会话级别，语句级别，其优先级依次递增。即语句级别的执行环境具有最高的优先权，会话级别次之，实例级别最低。
    Oracle SQL语句的运行环境分为多个不同的层次，主要包括实例级别，会话级别，语句级别，其优先级依次递增。即语句级别的执行环境具
有最高的优先权，会话级别次之，实例级别最低。反过来，实例级别的环境设置影响全局，而会话级别的则影响当前会话，语句级别的设置当然
也就只影响当前语句。由此可知，运行环境中每一个环节的参数都对最终的数据库性能或所执行的SQL语句有直接的影响。因此在对数据库优化
或调试SQL时，获得当前SQL语句运行环境显得尤为重要。为此，Oracle提供了三个重要的视图来获取不同级别的参数信息。

一、优化器性能视图名字
 
--下面的三个性能视图分别对应到不同的级别
scott@ORCL> select table_name from dict where table_name like 'V$%OPTIMIZER_ENV';

TABLE_NAME
------------------------------
V$SYS_OPTIMIZER_ENV
V$SES_OPTIMIZER_ENV
V$SQL_OPTIMIZER_ENV

--注：上述的3个视图实际上是Oracle 10053事件中参数的一个子集
二、实例级别执行环境(视图V$SYS_OPTIMIZER_ENV) 
--提供实例级别的环境执行的信息。Oracle 优化器在为SQL语句生成执行计划时，会根据实例级别参数的值来确定，如 optimizer_mode 用于
--确定优化器的模式，optimizer_index_cost_adj用于确定全表扫描与索引扫描之间成本开销的比值等。
--实例级别的参数可以使用下面的方式来设置

alter system set parameter=value scope=memory | both |spfile;

--我们来看看当前实例级别的运行环境

scott@ORCL> select
  2  name,
  3  value,
  4  isdefault
  5  from
  6  v$sys_optimizer_env
  7  order by 3,1;

NAME                               |VALUE          |ISD
-----------------------------------|---------------|---
statistics_level                   |all            |NO     -->非缺省值在此显示为NO
active_instance_count              |1              |YES
bitmap_merge_area_size             |1048576        |YES
cpu_count                          |1              |YES
cursor_sharing                     |exact          |YES
hash_area_size                     |131072         |YES
optimizer_dynamic_sampling         |2              |YES
optimizer_features_enable          |10.2.0.1       |YES
optimizer_index_caching            |0              |YES
optimizer_index_cost_adj           |100            |YES
optimizer_mode                     |all_rows       |YES
optimizer_secure_view_merging      |true           |YES
parallel_ddl_mode                  |enabled        |YES
parallel_dml_mode                  |disabled       |YES
parallel_execution_enabled         |true           |YES
parallel_query_mode                |enabled        |YES
parallel_threads_per_cpu           |2              |YES
pga_aggregate_target               |59392 KB       |YES
query_rewrite_enabled              |true           |YES
query_rewrite_integrity            |enforced       |YES
skip_unusable_indexes              |true           |YES
sort_area_retained_size            |0              |YES
sort_area_size                     |65536          |YES
star_transformation_enabled        |false          |YES
workarea_size_policy               |auto           |YES

--从上面的查询结果可知，当前系统中仅有statistics_level参数使用了非缺省值，statistics_level参数的缺省值为typical，而此处为all。
--对于实例级别任意参数的修改其影响都是全局的，因此应当慎重。当然，如果某个参数设置的不合理，其影响也是全局的，因此对于实例级别
--参数的合理设置，可以避免成百上千枯燥的SQL语句调整，反之整个数据库性能急剧下降也可能是因为实例级别参数设置不当所致。
三、会话级别执行环境(视图V$SES_OPTIMIZER_ENV) 
--与实例级别相对应的会话级别视图是V$SES_OPTIMIZER_ENV。此时session 级别的参数值会优先于实例级别的参数值。
--我们可以通过V$SYS_OPTIMIZER_ENV与V$SES_OPTIMIZER_ENV来获得当前或某个特定session的所有session级别参数值。

scott@ORCL> show parameter cursor_shar

NAME                                |TYPE       |VALUE
------------------------------------|-----------|------------------
cursor_sharing                      |string     |EXACT
scott@ORCL> alter session set cursor_sharing=force;

Session altered.

scott@ORCL> show parameter cursor_shar

NAME                                |TYPE       |VALUE
------------------------------------|-----------|------------------
cursor_sharing                      |string     |FORCE
scott@ORCL> alter session set optimizer_mode=first_rows_100;

Session altered.

scott@ORCL> show parameter optimizer_mode

NAME                                |TYPE                            |VALUE
------------------------------------|--------------------------------|--------------------
optimizer_mode                      |string                          |FIRST_ROWS_100	

scott@ORCL> alter session set db_file_multiblock_read_count=128;

Session altered.

scott@ORCL> select username,sid,serial# from v$session where username='SCOTT';

USERNAME                      |       SID|   SERIAL#
------------------------------|----------|----------
SCOTT                         |       159|         5

scott@ORCL> select name,value,isdefault
  2  from v$ses_optimizer_env
  3  where sid=&input_sid 
  4  minus
  5  select name,value,isdefault
  6  from v$sys_optimizer_env;
Enter value for input_sid: 159
old   3: where sid=&input_sid
new   3: where sid=159

NAME                               |VALUE          |ISD
-----------------------------------|---------------|---
_db_file_optimizer_read_count      |128            |NO
cursor_sharing                     |force          |NO
optimizer_mode                     |first_rows_100 |NO
四、语句级别执行环境(视图V$SQL_OPTIMIZER_ENV) 
--影响SQL语句运行的执行环境受到实例级别以及会话级别之外，最后的，优先级最高的级别即是语句级别。因此语句级别的设置直接影响最终
--SQL语句执行计划的产生。SQL语句级别的参数设置是通过提示来完成的。	
--在下面的例子中，我们通过为同一SQL 语句使用提示来使其运行在不同的环境之中。

scott@ORCL> select /*+ no_hint */ ename,job,sal from emp where empno=7369;  -->此SQL语句会根据实例与会话级别的优化器环境
                                                                            -->的设置来生成SQL执行计划并执行
ENAME     |JOB      |       SAL                                             -->此时的no_hint不是一个有效的提示，被当成注释
----------|---------|----------
Henry     |CLERK    |      1400

scott@ORCL> select /*+ all_rows */ ename,job,sal from emp where empno=7369;  -->此时该SQL语句使用了all_rows提示
                                                                             -->注，此语句与上一语句尽管得到的相同的结果
ENAME     |JOB      |       SAL                                              -->但运行在不同的优化器环境模式之中
----------|---------|----------                                              -->两者会有不同的sql_id
Henry     |CLERK    |      1400	

scott@ORCL> show parameter optimizer_index_cost_adj                          -->查看参数设置

NAME                                |TYPE       |VALUE
------------------------------------|-----------|------------------------------
optimizer_index_cost_adj            |integer    |100

scott@ORCL> show parameter cursor_sharing                                    -->查看参数设置

NAME                                |TYPE       |VALUE
------------------------------------|-----------|------------------------------
cursor_sharing                      |string     |EXACT

scott@ORCL> select avg(sal) from emp;                                        -->聚合运算

  AVG(SAL)
----------
      2335

scott@ORCL> alter session set cursor_sharing=force;                          -->基于session级别修改参数

Session altered.

scott@ORCL> alter session set optimizer_index_cost_adj=80;                   -->基于session级别修改参数

Session altered.

scott@ORCL> select avg(sal) from emp;                                        -->再次聚合运算

  AVG(SAL)
----------
      2335

-->下面的查询得到同一SQL语句由于不同运行环境的sql_id,child_number等	  
scott@ORCL> select sql_id, child_number, sql_text,optimizer_env_hash_value oehv,child_address
  2  from v$sql where sql_text like '%avg(sal)%' and sql_text not like '%from v$sql%';

SQL_ID       |CHILD_NUMBER|SQL_TEXT                           |      OEHV|CHILD_AD
-------------|------------|-----------------------------------|----------|--------
5du23va3p3ad0|           0|select avg(sal) from emp           |2196913643|3015F434
5du23va3p3ad0|           1|select avg(sal) from emp           |3293423667|2CD868A0

-->获得同一SQL语句父游标与子游标执行环境不同的地方
scott@ORCL> select se0.name,se0.value as value_child_0,se1.value as value_child_1
  2  from v$sql_optimizer_env se0
  3  inner join v$sql_optimizer_env se1
  4  on se0.sql_id=se1.sql_id
  5  where se0.sql_id='&input_sql_id'
  6  and se0.child_number=0
  7  and se1.child_number=1
  8  and se0.name=se1.name
  9  and se0.value<>se1.value;
Enter value for input_sql_id: 5du23va3p3ad0
old   5: where se0.sql_id='&input_sql_id'
new   5: where se0.sql_id='5du23va3p3ad0'

NAME                                    |VALUE_CHILD_0            |VALUE_CHILD_1
----------------------------------------|-------------------------|-------------------------
cursor_sharing                          |exact                    |force
optimizer_index_cost_adj                |100                      |80

-->获得上一SQL语句运行时优化器的详细信息
-->我们看到父游标与子游标的信息全部被列出来
scott@ORCL> select address, name, value, isdefault,child_number
  2  from v$sql_optimizer_env
  3  where sql_id = '5du23va3p3ad0'
  4  order by child_number, isdefault, name;

ADDRESS |NAME                                    |VALUE                    |ISD|CHILD_NUMBER
--------|----------------------------------------|-------------------------|---|------------
30177094|sqlstat_enabled                         |true                     |NO |           0
30177094|statistics_level                        |all                      |NO |           0
30177094|active_instance_count                   |1                        |YES|           0
30177094|bitmap_merge_area_size                  |1048576                  |YES|           0
30177094|cpu_count                               |1                        |YES|           0
30177094|cursor_sharing                          |exact                    |YES|           0
30177094|hash_area_size                          |131072                   |YES|           0
30177094|optimizer_dynamic_sampling              |2                        |YES|           0
30177094|optimizer_features_enable               |10.2.0.1                 |YES|           0
30177094|optimizer_index_caching                 |0                        |YES|           0
30177094|optimizer_index_cost_adj                |100                      |YES|           0
30177094|optimizer_mode                          |all_rows                 |YES|           0
30177094|optimizer_secure_view_merging           |true                     |YES|           0
30177094|parallel_ddl_mode                       |enabled                  |YES|           0
30177094|parallel_dml_mode                       |disabled                 |YES|           0
30177094|parallel_execution_enabled              |true                     |YES|           0
30177094|parallel_query_mode                     |enabled                  |YES|           0
30177094|parallel_threads_per_cpu                |2                        |YES|           0
30177094|pga_aggregate_target                    |59392 KB                 |YES|           0
30177094|query_rewrite_enabled                   |true                     |YES|           0
30177094|query_rewrite_integrity                 |enforced                 |YES|           0
30177094|skip_unusable_indexes                   |true                     |YES|           0
30177094|sort_area_retained_size                 |0                        |YES|           0
30177094|sort_area_size                          |65536                    |YES|           0
30177094|star_transformation_enabled             |false                    |YES|           0
30177094|workarea_size_policy                    |auto                     |YES|           0
30177094|cursor_sharing                          |force                    |NO |           1
30177094|optimizer_index_cost_adj                |80                       |NO |           1
30177094|sqlstat_enabled                         |true                     |NO |           1
30177094|statistics_level                        |all                      |NO |           1
30177094|active_instance_count                   |1                        |YES|           1
30177094|bitmap_merge_area_size                  |1048576                  |YES|           1
30177094|cpu_count                               |1                        |YES|           1
30177094|hash_area_size                          |131072                   |YES|           1
30177094|optimizer_dynamic_sampling              |2                        |YES|           1
30177094|optimizer_features_enable               |10.2.0.1                 |YES|           1
30177094|optimizer_index_caching                 |0                        |YES|           1
30177094|optimizer_mode                          |all_rows                 |YES|           1
30177094|optimizer_secure_view_merging           |true                     |YES|           1
30177094|parallel_ddl_mode                       |enabled                  |YES|           1
30177094|parallel_dml_mode                       |disabled                 |YES|           1
30177094|parallel_execution_enabled              |true                     |YES|           1
30177094|parallel_query_mode                     |enabled                  |YES|           1
30177094|parallel_threads_per_cpu                |2                        |YES|           1
30177094|pga_aggregate_target                    |59392 KB                 |YES|           1
30177094|query_rewrite_enabled                   |true                     |YES|           1
30177094|query_rewrite_integrity                 |enforced                 |YES|           1
30177094|skip_unusable_indexes                   |true                     |YES|           1
30177094|sort_area_retained_size                 |0                        |YES|           1
30177094|sort_area_size                          |65536                    |YES|           1
30177094|star_transformation_enabled             |false                    |YES|           1
30177094|workarea_size_policy                    |auto                     |YES|           1

52 rows selected.

-->Author : Robinson Cheng  
-->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语句设置不同级别的优化器相关参数提供了便利

六、更多参考

NULL 值与索引(一)

NULL 值与索引(二)

SQL tuning 步骤

高效SQL语句必杀技

父游标、子游标及共享游标

绑定变量及其优缺点

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

执行计划中各字段各模块描述

使用 EXPLAIN PLAN 获取SQL语句执行计划

启用 AUTOTRACE 功能

函数使得索引列失效

Oracle 绑定变量窥探

Oracle 自适应共享游标


文章标签：
SQL
关系型数据库
Oracle
索引
数据库
关键词：
SQL视图
SQL性能
优化求解器视图
优化求解器sql
SQL优化器
leshami
目录
相关文章
穆雄雄.
|
27天前
|
SQL 存储 数据库
sql事务、视图和索引
sql事务、视图和索引
穆雄雄.
14 0
SarPro
|
2月前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之视图
【数据库SQL server】关系数据库标准语言SQL之视图
SarPro
76 0
码农~
|
3月前
|
SQL 存储 关系型数据库
sql语句，索引，视图，存储过程
sql语句，索引，视图，存储过程
码农~
27 0
Star时光
|
4月前
|
存储 SQL 关系型数据库
PolarDB这个sql行存和列存性能差别好大 ，为什么？
PolarDB这个sql行存和列存性能差别好大 ，为什么？
Star时光
33 0
瑶大头*^_^*
|
6月前
|
SQL
SQL视图View(是一张虚拟的表)
SQL视图View(是一张虚拟的表)
瑶大头*^_^*
51 0
蛇蛇keying
|
4月前
|
SQL 数据库 数据安全/隐私保护
SQL Server 数据操控,视图和索引
SQL Server 数据操控,视图和索引
蛇蛇keying
96 0
Love-Java.
|
5月前
|
SQL 关系型数据库 MySQL
MySql数据库中的视图，索引与数据库sql脚本如何导入与导出---(详细介绍)
MySql数据库中的视图，索引与数据库sql脚本如何导入与导出---(详细介绍)
Love-Java.
243 0
高二的笔记
|
3月前
|
存储 SQL 缓存
4.2.1 SQL语句、索引、视图、存储过程
4.2.1 SQL语句、索引、视图、存储过程
高二的笔记
36 0
jerrywangsap
|
6月前
|
SQL 数据库 数据安全/隐私保护
SAP CDS view 定义的数据库视图和传统 SQL 语句定义视图的区别
SAP CDS view 定义的数据库视图和传统 SQL 语句定义视图的区别
jerrywangsap
73 0
游客nmbcre4jd7shs
|
3月前
|
SQL 存储 缓存
如何通过优化SQL查询提升数据库性能
SQL查询是数据库的核心功能之一，对于大型数据量的应用程序来说，优化SQL查询可以显著提升数据库的性能。本文将介绍如何通过优化SQL查询语句来提升数据库的性能，包括索引优化、查询语句优化以及其他一些技巧。
游客nmbcre4jd7shs
35 0

热门文章

最新文章

  • 1
    阿里云数据库内核 Apache Doris 兼容 Presto、Trino、ClickHouse、Hive 等近十种 SQL 方言，助力业务平滑迁移
  • 2
    一文搞懂SQL优化——如何高效添加数据
  • 3
    Seata常见问题之服务端 error日志没有输出,客户端执行sql报错如何解决
  • 4
    【软件设计师备考 专题 】数据库语言（SQL）
  • 5
    TiDB支持的SQL语法概述
  • 6
    【SQL server】玩转SQL server数据库：第三章 关系数据库标准语言SQL（二）数据查询
  • 7
    SQL常见面试题总结2
  • 8
    MyBatis中的9种常用动态sql标签精妙用法
  • 9
    【SQL server】玩转SQL server数据库：第二章 关系数据库
  • 10
    【MySQL技术之旅】（7）总结和盘点优化方案系列之常用SQL的优化
  • 1
    阿里达摩院MindOpt优化求解器-月刊（2024年2月）
    93
  • 2
    阿里达摩院MindOpt优化求解器-月刊（2024年1月）
    87
  • 3
    如何通过阿里达摩院MindOpt获得MILP多个解
    95
  • 4
    阿里达摩院MindOpt求解器V1.1新增C#接口
    136
  • 5
    「达摩院MindOpt」优化形状切割问题（MILP）
    78
  • 6
    「达摩院MindOpt」优化FlowShop流水线作业排班问题
    51
  • 7
    MindOpt APL建模语言自定小义函数的重要性和示例
    36
  • 8
    阿里达摩院MindOpt优化求解器-月刊（2023年12月）
    74
  • 9
    MindOpt 云上建模求解平台：多求解器协同优化
    97
  • 10
    PyTorch基础之优化器模块、训练和测试模块讲解（附源码）
    67

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server在电子商务中的应用与实践
  • GeoMesa on Spark SQL
  • 阿里技术专家一挃：MongoDB 优化器 & 执行器介绍

    • 相关实验场景

    更多
  • PolarDB NL2SQL：自然语言驱动的数据智能
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • 使用SQL语句实现数据插入、修改和删除操作
  • 使用SQL语句实现数据查询操作
  • 使用SQL语句管理索引
    • 下一篇
    阿里云oss简介和使用流程
    为什么选择阿里云什么是云计算全球基础设施技术领先稳定可靠安全合规分析师报告
    产品和定价全部产品免费试用产品动态产品定价价格计算器云上成本管理
    解决方案技术解决方案
    文档与社区文档开发者社区天池大赛培训与认证
    权益中心免费试用高校计划企业扶持计划推荐返现计划
    支持与服务基础服务企业增值服务迁云服务官网公告健康看板信任中心
    关注阿里云
    关注阿里云公众号或下载阿里云APP，关注云资讯，随时随地运维管控云服务
    阿里云APP阿里云微信
    联系我们：4008013260
    法律声明Cookies政策廉正举报安全举报联系我们加入我们
    阿里巴巴集团淘宝网天猫全球速卖通阿里巴巴国际交易市场1688阿里妈妈飞猪阿里云计算AliOS万网高德UC友盟优酷钉钉支付宝达摩院淘宝海外阿里云盘饿了么
    © 2009-2024 Aliyun.com 版权所有 增值电信业务经营许可证： 浙B2-20080101 域名注册服务机构许可： 浙D3-20210002 京D3-20220015
    浙公网安备 33010602009975号浙B2-20080101-4