Oracle 历史SQL语句执行计划的对比与分析

本文涉及的产品
网络型负载均衡 NLB,每月750个小时 15LCU
传统型负载均衡 CLB,每月750个小时 15LCU
应用型负载均衡 ALB,每月750个小时 15LCU
简介:     基于CBO优化器的环境中,SQL执行计划的生成依赖于统计信息的真实与完整。如列的离散度,列上的直方图,索引的可用性,索引上的聚簇因子。当这些信息是真实完整的情况下,CBO优化器通常都可以制定最优的执行计划。

    基于CBO优化器的环境中,SQL执行计划的生成依赖于统计信息的真实与完整。如列的离散度,列上的直方图,索引的可用性,索引上的聚簇因子。当这些信息是真实完整的情况下,CBO优化器通常都可以制定最优的执行计划。也正因此CBO优化器也灵活,难以控制,任一信息的不真实或缺失都可能导致执行计划发生变化而产生多个版本。经常碰到的情形是之前的某个SQL语句前阵子还不是TOP SQL,而最近变成了TOP SQL。或者说之前尽管是TOP SQL但,但最近尽然成了TOP 1。对于此情形,我们可以比对SQL语句的历史执行计划进行分析是何种原因导致SQL变慢或执行计划发生变化。下面通过例子来模拟SQL执行计划变异的情形。
  
1、创建演示环境

--演示环境
scott@SYBO2SZ> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

--创建1000000万记录的表
scott@SYBO2SZ> @cr_big_tb

check total rows  for big_table
====================================
  COUNT(*)
----------
   1000000

--为表创建索引
scott@SYBO2SZ> create index i_big_tb_owner on big_table(owner);

sys@SYBO2SZ> conn / as sysdba;

sys@SYBO2SZ> select snap_id from dba_hist_snapshot order by snap_id;

   SNAP_ID
----------
        30
        31

--清除awr的历史记录,shared pool及buffer cache        
sys@SYBO2SZ> exec dbms_workload_repository.drop_snapshot_range(30,31);

sys@SYBO2SZ> alter system flush shared_pool;

sys@SYBO2SZ> alter system flush buffer_cache;

--清除dba_hist_sql_plan视图,实际上清除wrh$_sql_plan,wrh$_sqltext,wrh$_sqlstat
sys@SYBO2SZ> truncate table wrh$_sql_plan;

--清除dba_hist_sql_sqltext以及dba_hist_sqlstat视图

sys@SYBO2SZ> truncate table wrh$_sqltext;

sys@SYBO2SZ> truncate table wrh$_sqlstat;

sys@SYBO2SZ> select count(*) from dba_hist_sql_plan;

  COUNT(*)
----------
         0

sys@SYBO2SZ> select count(*) from dba_hist_sqltext;

  COUNT(*)
----------
         0

2、生成历史SQL及其执行计划

sys@SYBO2SZ> conn scott/tiger

scott@SYBO2SZ> select count(*) from big_table where owner='GOEX_ADMIN';

  COUNT(*)
----------
     43560

scott@SYBO2SZ> @my_last_sql

ADDRESS          HASH_VALUE SQL_ID        COMMAND_TYPE      PIECE SQL_TEXT
---------------- ---------- ------------- ------------ ---------- ---------------------------------------------------------
000000007B9BB7D0  243468085 4hqyjwh7861tp            3          0 select count(*) from big_table where owner='GOEX_ADMIN'

--从awr中查询sql的执行计划,由于没有生成快照,所以无其执行计划
scott@SYBO2SZ> @sql_plan_disp_awr
Enter value for input_sqlid: 4hqyjwh7861tp

no rows selected

--创建快照
scott@SYBO2SZ> exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.

--查看SQL的历史执行计划
scott@SYBO2SZ> @sql_plan_disp_awr
Enter value for input_sqlid: 4hqyjwh7861tp

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 4hqyjwh7861tp
--------------------
select count(*) from big_table where owner='GOEX_ADMIN'

Plan hash value: 334839806

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |       |       |   139 (100)|          |
|   1 |  SORT AGGREGATE   |                |     1 |    17 |            |          |
|   2 |   INDEX RANGE SCAN| I_BIG_TB_OWNER | 10073 |   167K|   139   (0)| 00:00:02 |
------------------------------------------------------------------------------------

3、生成不同的历史SQL并对比执行计划

--对表big_table进行move操作
scott@SYBO2SZ> alter table big_table move;

--检查其表上的索引,如下,索引已经失效
scott@SYBO2SZ> @idx_info
Enter value for owner: scott
Enter value for table_name: big_table

TABLE_NAME                INDEX_NAME          CL_NAM               CL_POS STATUS   IDX_TYP         DSCD
------------------------- ------------------- -------------------- ------ -------- --------------- ----
BIG_TABLE                 BIG_TABLE_PK        ID                        1 UNUSABLE NORMAL          ASC
BIG_TABLE                 I_BIG_TB_OWNER      OWNER                     1 UNUSABLE NORMAL          ASC

--再次执行与之前相同的SQL语句
scott@SYBO2SZ> select count(*) from big_table where owner='GOEX_ADMIN';

  COUNT(*)
----------
     43560

scott@SYBO2SZ> @my_last_sql

ADDRESS          HASH_VALUE SQL_ID        COMMAND_TYPE      PIECE SQL_TEXT
---------------- ---------- ------------- ------------ ---------- ----------------------------------------------------------
000000007B9BB7D0  243468085 4hqyjwh7861tp            3          0 select count(*) from big_table where owner='GOEX_ADMIN'

--创建一个新的快照,使之成为历史SQL
scott@SYBO2SZ> exec dbms_workload_repository.create_snapshot();

--查看SQL的执行计划
scott@SYBO2SZ> @sql_plan_disp_awr
Enter value for input_sqlid: 4hqyjwh7861tp

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 4hqyjwh7861tp
--------------------
select count(*) from big_table where owner='GOEX_ADMIN'

Plan hash value: 334839806

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |       |       |   139 (100)|          |
|   1 |  SORT AGGREGATE   |                |     1 |    17 |            |          |
|   2 |   INDEX RANGE SCAN| I_BIG_TB_OWNER | 10073 |   167K|   139   (0)| 00:00:02 |
------------------------------------------------------------------------------------

SQL_ID 4hqyjwh7861tp
--------------------
select count(*) from big_table where owner='GOEX_ADMIN'

Plan hash value: 599409829

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |       |       |  3221 (100)|          |
|   1 |  SORT AGGREGATE    |           |     1 |    17 |            |          |
|   2 |   TABLE ACCESS FULL| BIG_TABLE | 10073 |   167K|  3221   (1)| 00:00:39 |
--------------------------------------------------------------------------------

28 rows selected.

--从上面的查询结果可以看到,同一条历史SQL语句有不同的plan_hash_value 以及使用了不同的执行计划
--最早的一个是走索引范围扫描,一个是全表扫描

--下面直接从dba_hist_sql_plan查看sql语句的执行计划
--该视图记录了所有被awr快照捕获的所有历史sql的执行计划以及执行计划的生成时间
scott@SYBO2SZ> run sql_plan_his
  1  SELECT id,
  2           operation,
  3           options,
  4           object_name,
  5           bytes,
  6           cpu_cost,                -----> Author : Robinson
  7           io_cost,                 -----> Blog   : http://blog.csdn.net/robinson_0612
  8           timestamp
  9      FROM dba_hist_sql_plan
 10     WHERE sql_id = '&input_sql_id'
 11* ORDER BY timestamp,id
Enter value for input_sql_id: 4hqyjwh7861tp

 ID OPERATION                 OPTIONS       OBJECT_NAME            BYTES   CPU_COST    IO_COST TIMESTAMP
--- ------------------------- ------------- ----------------- ---------- ---------- ---------- -----------------
  0 SELECT STATEMENT                                                                           20130517 11:23:20
  1 SORT                      AGGREGATE                               17                       20130517 11:23:20
  2 INDEX                     RANGE SCAN    I_BIG_TB_OWNER        171241    1789880        139 20130517 11:23:20
  0 SELECT STATEMENT                                                                           20130517 11:27:16
  1 SORT                      AGGREGATE                               17                       20130517 11:27:16
  2 TABLE ACCESS              FULL          BIG_TABLE             171241  325825194       3203 20130517 11:27:16

6 rows selected.

4、修正SQL执行计划

--如前面可知,由于索引不可用导致了SQL语句执行了全表扫描。
--事实上导致全表扫描的问题很多,若使用谓词列函数,谓词列数据类型转换,使用不等于,以及谓词列参与计算等,不一一列出
--针对上面的情形,我们应当收集统计信息以及重建索引
scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true);
BEGIN dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true); END;

*
ERROR at line 1:
ORA-20000: index "SCOTT"."BIG_TABLE_PK"  or partition of such index is in unusable state
ORA-06512: at "SYS.DBMS_STATS", line 13182
ORA-06512: at "SYS.DBMS_STATS", line 13202
ORA-06512: at line 1

--上面再收集统计信息时,提示索引不可用,需要先rebulid

scott@SYBO2SZ> alter index i_big_tb_owner rebuild nologging;

scott@SYBO2SZ> alter index big_table_pk rebuild nologging;

scott@SYBO2SZ> exec dbms_stats.gather_table_stats('SCOTT','BIG_TABLE',cascade=>true);

--下面我们再次执行原SQL以及,由下可知,SQL已经使用了最优的执行计划
scott@SYBO2SZ> set autot trace exp;
scott@SYBO2SZ> select count(*) from big_table where owner='GOEX_ADMIN';

Execution Plan
----------------------------------------------------------
Plan hash value: 334839806

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |     6 |   108   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE   |                |     1 |     6 |            |          |
|*  2 |   INDEX RANGE SCAN| I_BIG_TB_OWNER | 44750 |   262K|   108   (1)| 00:00:02 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='GOEX_ADMIN')

5、后记
a、示例中创建的big_table脚本,请参考:Oracle 测试常用表BIG_TABLE
b、alter table move 方式用于实现段收缩,移动高水位,但不会释放申请的空间,以及导致索引失效
c、对于历史SQL语句,需要执行snapshot之后,才会被填充到DBA_HIST_SQL_PLAN、DBA_HIST_SQLSTAT、DBA_HIST_SNAPSHOT数据字典中
d、如果你的测试无法获得历史SQL语句及其执行计划,通常是由于awr阀值设置所致,可参考:Oracle AWR 阙值影响历史执行计划
e、历史SQL语句的执行计划也可以通过$ORACLE_HOME/rdbms/admin/awrsqrpt.sql来生成txt或html文件
f、引起同一SQL执行计划发生变化的情形很多,如统计信息的缺失,索引失效,不同级别的参数发生变化等
h、对于实例,会话,语句级别环境变化导致同一SQL执行计划发变异,也可以对此跟踪。参考:使用优化器性能视图获取SQL语句执行环境

 

更多参考

有关Oracle RAC请参考
     使用crs_setperm修改RAC资源的所有者及权限
     使用crs_profile管理RAC资源配置文件
     RAC 数据库的启动与关闭
     再说 Oracle RAC services
     Services in Oracle Database 10g
     Migrate datbase from single instance to Oracle RAC
     Oracle RAC 连接到指定实例
     Oracle RAC 负载均衡测试(结合服务器端与客户端)
     Oracle RAC 服务器端连接负载均衡(Load Balance)
     Oracle RAC 客户端连接负载均衡(Load Balance)
     ORACLE RAC 下非缺省端口监听配置(listener.ora tnsnames.ora)
     ORACLE RAC 监听配置 (listener.ora tnsnames.ora)
     配置 RAC 负载均衡与故障转移
     CRS-1006 , CRS-0215 故障一例 
     基于Linux (RHEL 5.5) 安装Oracle 10g RAC
     使用 runcluvfy 校验Oracle RAC安装环境

有关Oracle 网络配置相关基础以及概念性的问题请参考:
     配置非默认端口的动态服务注册
     配置sqlnet.ora限制IP访问Oracle
     Oracle 监听器日志配置与管理
     设置 Oracle 监听器密码(LISTENER)
     配置ORACLE 客户端连接到数据库

有关基于用户管理的备份和备份恢复的概念请参考
     Oracle 冷备份
     Oracle 热备份
     Oracle 备份恢复概念
     Oracle 实例恢复
     Oracle 基于用户管理恢复的处理
     SYSTEM 表空间管理及备份恢复
     SYSAUX表空间管理及恢复
     Oracle 基于备份控制文件的恢复(unsing backup controlfile)

有关RMAN的备份恢复与管理请参考
     RMAN 概述及其体系结构
     RMAN 配置、监控与管理
     RMAN 备份详解
     RMAN 还原与恢复
     RMAN catalog 的创建和使用
     基于catalog 创建RMAN存储脚本
     基于catalog 的RMAN 备份与恢复
     RMAN 备份路径困惑
     使用RMAN实现异机备份恢复(WIN平台)
     使用RMAN迁移文件系统数据库到ASM
     linux 下RMAN备份shell脚本
     使用RMAN迁移数据库到异机

有关ORACLE体系结构请参考
     Oracle 表空间与数据文件
     Oracle 密码文件
     Oracle 参数文件
     Oracle 联机重做日志文件(ONLINE LOG FILE)
     Oracle 控制文件(CONTROLFILE)
     Oracle 归档日志
     Oracle 回滚(ROLLBACK)和撤销(UNDO)
     Oracle 数据库实例启动关闭过程
     Oracle 10g SGA 的自动化管理
     Oracle 实例和Oracle数据库(Oracle体系结构) 

相关实践学习
SLB负载均衡实践
本场景通过使用阿里云负载均衡 SLB 以及对负载均衡 SLB 后端服务器 ECS 的权重进行修改,快速解决服务器响应速度慢的问题
负载均衡入门与产品使用指南
负载均衡(Server Load Balancer)是对多台云服务器进行流量分发的负载均衡服务,可以通过流量分发扩展应用系统对外的服务能力,通过消除单点故障提升应用系统的可用性。 本课程主要介绍负载均衡的相关技术以及阿里云负载均衡产品的使用方法。
目录
相关文章
|
1天前
|
SQL Java 数据库连接
如何在 Java 代码中使用 JSqlParser 解析复杂的 SQL 语句?
大家好,我是 V 哥。JSqlParser 是一个用于解析 SQL 语句的 Java 库,可将 SQL 解析为 Java 对象树,支持多种 SQL 类型(如 `SELECT`、`INSERT` 等)。它适用于 SQL 分析、修改、生成和验证等场景。通过 Maven 或 Gradle 安装后,可以方便地在 Java 代码中使用。
77 11
|
3月前
|
SQL 存储 数据可视化
手机短信SQL分析技巧与方法
在手机短信应用中,SQL分析扮演着至关重要的角色
|
3月前
|
SQL 监控 Oracle
Oracle SQL性能优化全面指南
在数据库管理领域,Oracle SQL性能优化是确保数据库高效运行和数据查询速度的关键
|
3月前
|
SQL 存储 Oracle
Oracle数据库SQL语句详解与应用指南
在数字化时代,数据库已成为各类企业和组织不可或缺的核心组件。Oracle数据库作为业界领先的数据库管理系统之一,广泛应用于各种业务场景。掌握Oracle数据库的SQL语句是数据库管理员、开发人员及运维人员的基本技能。本文将详细介绍Oracle数据库SQL语句的基本概念、语法、应用及最佳实践。一、Or
104 3
|
3月前
|
SQL Oracle 关系型数据库
Oracle SQL:了解执行计划和性能调优
Oracle SQL:了解执行计划和性能调优
97 1
|
4月前
|
Oracle NoSQL 关系型数据库
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
810 2
|
5月前
|
前端开发 Java JSON
Struts 2携手AngularJS与React:探索企业级后端与现代前端框架的完美融合之道
【8月更文挑战第31天】随着Web应用复杂性的提升,前端技术日新月异。AngularJS和React作为主流前端框架,凭借强大的数据绑定和组件化能力,显著提升了开发动态及交互式Web应用的效率。同时,Struts 2 以其出色的性能和丰富的功能,成为众多Java开发者构建企业级应用的首选后端框架。本文探讨了如何将 Struts 2 与 AngularJS 和 React 整合,以充分发挥前后端各自优势,构建更强大、灵活的 Web 应用。
71 0
|
5月前
|
SQL 数据采集 数据挖掘
为什么要使用 SQL 函数?详尽分析
【8月更文挑战第31天】
73 0
|
5月前
|
SQL 数据采集 算法
【电商数据分析利器】SQL实战项目大揭秘:手把手教你构建用户行为分析系统,从数据建模到精准营销的全方位指南!
【8月更文挑战第31天】随着电商行业的快速发展,用户行为分析的重要性日益凸显。本实战项目将指导你使用 SQL 构建电商平台用户行为分析系统,涵盖数据建模、采集、处理与分析等环节。文章详细介绍了数据库设计、测试数据插入及多种行为分析方法,如购买频次统计、商品销售排名、用户活跃时间段分析和留存率计算,帮助电商企业深入了解用户行为并优化业务策略。通过这些步骤,你将掌握利用 SQL 进行大数据分析的关键技术。
284 0
|
5月前
|
SQL 数据挖掘 BI
【超实用技巧】解锁SQL聚合函数的奥秘:从基础COUNT到高级多表分析,带你轻松玩转数据统计与挖掘的全过程!
【8月更文挑战第31天】SQL聚合函数是进行数据统计分析的强大工具,可轻松计算平均值、求和及查找极值等。本文通过具体示例,展示如何利用这些函数对`sales`表进行统计分析,包括使用`COUNT()`、`SUM()`、`AVG()`、`MIN()`、`MAX()`等函数,并结合`GROUP BY`和`HAVING`子句实现更复杂的数据挖掘需求。通过这些实践,你将学会如何高效地应用SQL聚合函数解决实际问题。
73 0

推荐镜像

更多