有客户遇到SQL性能不稳定,突然变差导致系统性能出现严重问题的情况。对于大型的系统来说,SQL性能不稳定,有时突然变差,这是常常遇到的问题。这也是一些DBA的挑战。
对于使用Oracle数据库的应用系统,有时会出现运行得好好的SQL,性能突然变差。特别是对于OLTP类型系统执行频繁的核心SQL,如果出现性能问题,通常会影响整个数据库的性能,进而影响整个系统的正常运行。对于个别的SQL,比如较少使用的查询报表之类的SQL,如果出现问题,通常只影响少部分功能模块,而不会影响整个系统。
那么应该怎么样保持SQL性能的稳定性?
SQL的性能变差,通常是在SQL语句重新进行了解析,解析时使用了错误的执行计划出现的。下列情况是SQL会重新解析的原因:
- 1. SQL语句没有使用绑定变量,这样SQL每次执行都要解析。
- 2. SQL长时间没有执行,被刷出SHARED POOL,再次执行时需要重新解析。
- 3. 在SQL引用的对象(表、视图等)上执行了DDL操作,甚至是结构发生了变化,比如建了一个索引。
- 4. 对SQL引用的对象进行了权限更改。
- 5. 重新分析(收集统计信息)了SQL引用的表和索引,或者表和索引统计信息被删除。
- 6. 修改了与性能相关的部分参数。
- 7. 刷新了共享池。
- 8. 当然重启数据库也会使所有SQL全部重新解析。
SQL重新解析后,跟以前相比,性能突然变差,通常是下列原因:
- 1. 表和索引的优化统计信息被删除,或者重新收集后统计信息不准确。重新收集统计信息通常是由于收集策略(方法)不正确引起。比如对分区表使用analyze命令而不是用dbms_stats包、收集统计信息时采样比例过小等等。Oracle优化器严重依赖于统计信息,如果统计信息有问题,则很容易导致SQL不能使用正确的执行计划。
- 2. SQL绑定变量窥探(bind peeking),同时绑定变量对应的列上有直方图;或者绑定变量的值变化范围过大、分区数据分布极不均匀:
- 1) 绑定变量的列上有直方图:
假如表orders存储所有的订单,state列有3种不同的值:0表示未处理,1表示处理成功完成,2表示处理失败。State列上有一个索引,表中绝大部分数据的state列为1,0和2占少数。有下面的SQL:
select*fromorderswherestate=:b1
select * from orders where state=:b1
这里:b1是变量,在大多数情况下这个值为0,则应该使用索引,但是如果SQL被重新解析,而第一次执行时应用传给变量b1值为1,则不会使用索引,采用全表扫描的方式来访问表。对于绑定变量的SQL,只在第一次执行时才会进行绑定变量窥探,并以此确定执行计划,该SQL后续执行时全部按这个执行计划。这样在后续执行时,b1变量传入的值为0的时候,仍然是第一次执行时产生的执行计划,即使用的是全表扫描,这样会导致性能很差。
- 2) 绑定变量的值变化范围过大:
同样假如orders表有一列created_date表示一笔订单的下单时间,orders表里面存储了最近1年的数据,有如下的SQL:
Select*fromorderswherecreated_date>=:b1;
Select * from orders where created_date >=:b1;
假如大多数情况下,应用传入的b1变量值为最近几天内的日期值,那么SQL使用的是created_date列上的索引,而如果b1变量值为5个月之前的一个值,那么就会使用全表扫描。与上面描述的直方图引起的问题一样,如果SQL第1次执行时传入的变量值引起的是全表扫描,那么将该SQL后续执行时都使用了全表扫描,从而影响了性能。
- 3) 分区数据量不均匀:
对于范围和列表分区,可能存在各个分区之间数据量极不均匀的情况下。比如分区表orders按地区area进行了分区,P1分区只有几千行,而P2分区有200万行数据。
-
select*fromorderswherearea=:b1andproduct_id=:b2
select * from orders where area=:b1 and product_id =:b2
这条SQL由于有area条件,因此会使用分区排除。如果第1 次执行时应用传给b1变量的值正好落在P1分区上,很可能导致SQL采用全表扫描访问,如前面所描述的,导致SQL后续执行时全部使用了全表扫描。
- 3. 其他原因,比如表做了类似于MOVE操作之后,索引不可用,对索引进行了更改。当然这种情况是属于维护不当引起的问题,不在本文讨论的范围。
综上所述,SQL语句性能突然变差,主要是因为绑定变量和统计信息的原因。注意这里只讨论了突然变差的情况,而对于由于数据量和业务量的增加性能逐步变差的情况不讨论。
为保持SQL性能或者说是执行计划的稳定性,需要从以下几个方面着手:
除此之外,可以调整一些参数避免潜在的问题,比如将”_btree_bitmap_plans”参数设置为FALSE(这个参数请参考互联网上的文章或Oracle文档)。
而在实际工作中,通过使用定制的统计信息收集策略,以及在部分系统上使用OUTLINE,系统基本上不会出现已有的SQL性能突然变差的情况。当然也有维护人员操作不当引起的SQL性能突然变差,比如建了某个索引而没有收集统计信息,导致SQL使用了新建的索引,而该索引并不适合于那条SQL;维护人员意外删除了表个索引的统计信息。
分享到:
相关推荐
oracle数据库客户端 instantclient-basic-windows.x64-19.19.0.0.0dbru
cx_Oracle-5.1-11g.win32-py2.5.msi,用于连接oracle数据库
【完整课程列表】 oracle_系统表.pdf 超详细Oracle数据库开发教程 共115页.pdf 数据库开发 Oracle数据库 SQL开发教程 第01章 Oracle...数据库开发 Oracle数据库 SQL开发教程 第17章 用户、权限和角色(共28页).pdf
instantclient是连接orcal数据库的一个轻量级客户端工具,对于想用plsql但又不想安装oracle client的可以用这个替代;压缩包内附安装及配置说明;
1.1 下载以rpm后缀名的包,以11.2.0.4.0 版本为例,其中基础包basic、sql*plus、devel包建议默认下载,其他包视情况而定: oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm --基础包,为了运行OCI、OCCI、...
DBeaver 是一个通用的数据库管理工具和 SQL 客户端,支持 MySQL, PostgreSQL, Oracle, DB2, MSSQL, Sybase, Mimer, HSQLDB, Derby, 以及其他兼容 JDBC 的数据库。DBeaver 提供一个图形界面用来查看数据库结构、执行...
instantclient-basic-windows.x64-11.2.0.4.0.zip,Oracle1164位官方客户端,可用于本地电脑未安装数据库的情况下,PLSQL连接Oracle数据库,注意,PLSQL也需要是64位版本的,客户端版本为11.2.0.4.0
Oracle的oci包,最新版instantclient-basic-windows.x64-11.2.0.4.0,支持11g。
Oracle数据库SQL性能优化学习可以用到的。
2-1-大型数据库值Oracle 11g SQL Plus 共46页.ppt 2-2-Oracle数据库知识 PLSQL介绍 共69页.ppt 3-1-Oracle数据库知识 PLSQL控制结构 共25页.ppt 4-1-异常处理 共87页.ppt 4-2-过程与函数 共62页.ppt 4-3-在PLSql中...
oracle即时客户端 instantclient-basic-linux.x64-11.2.0.4.0.zip
Oracle驱动包 ojdbc6-11.2.0.4.0-atlassian-hosted.jar
Instant Client Package - Basic: All files required to run OCI, OCCI, and JDBC-OCI applications instantclient-basic-windows.x64-11.2.0.4.0.zip (54,956,947 bytes) Instant Client 程序包 — Basic(win64...
64位centos按装下按装64位oracle时,centos系统缺少的rpm包,附件内存放着以下rpm按装包: libaio-0.3.105-2.x86_64.rpm libaio-devel-0.3.105-2.i386.rpm libaio-devel-0.3.105-2.x86_64.rpm libaio-devel-0.3.106-...
Oracle可视化工具sqldeveloper-23.1.0.097.1607-x64
1.1 下载以rpm后缀名的包,以11.2.0.4.0 版本为例,其中基础包basic、sql*plus、devel包建议默认下载,其他包视情况而定: oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm --基础包,为了运行OCI、OCCI、...
compat-libcap1-1.10-7.el7.x86_64 compat-gcc-32-3.2.3-72.el7....oracle-database-preinstall-19c-1.0-1.el7.x86_64 perl-File-Slurp-9999.19-6.el7.noarch python3-3.6.8-17.el7.x86_64 rlwrap-0.45.2-2.el7.x86_64
【完整Oracle SQL开发教程课件如下】 数据库开发 Oracle数据库 SQL开发教程 第0章 开篇概述(共14页).pdf 数据库开发 Oracle数据库 SQL...数据库开发 Oracle数据库 SQL开发教程 第17章 用户、权限和角色(共28页).pdf
【完整Oracle SQL开发教程课件如下】 数据库开发 Oracle数据库 SQL开发教程 第0章 开篇概述(共14页).pdf 数据库开发 Oracle数据库 SQL...数据库开发 Oracle数据库 SQL开发教程 第17章 用户、权限和角色(共28页).pdf
Oracle SQL Developer是一个免费的,并完全支持图形数据库开发工具。使用SQL Developer,您可以浏览数据库对象,运行SQL语句和SQL脚本,编辑和调试PL / SQL语句。您还可以运行任何报告的数量,以及创建和保存您自己...