最新ORACLE SQL性能优化(全).ppt
合集下载
2024版年度Oracle的性能优化培训课件
优化培训课件•Oracle性能优化概述•Oracle数据库性能评估•Oracle数据库结构优化•Oracle数据库参数调整目•Oracle SQL语句优化•Oracle性能监控与调优实践录01CATALOGUE Oracle性能优化概述性能优化目标与意义目标提高系统响应速度、吞吐量，降低资源消耗，确保系统稳定、高效运行。
意义对于企业级应用，性能优化能够显著提升用户体验，降低运营成本，增强系统可扩展性和可维护性。
并发与锁优化优化事务处理、锁机制，提高系统并发处理能力。
调整内存参数配置，提高系统缓存命中率，减少磁盘I/O 。
存储优化优化数据存储结构、表空间管理，提高I/O 性能。
SQL 查询优化针对慢查询、低效查询进行优化，提高查询速度。
索引优化合理创建、调整索引，提高数据检索效率。
性能优化常见场景数据库结构优化合理设计表结构、分区表、索引等，提高数据访问效率。
流程性能监控与分析、瓶颈识别、优化方案制定、实施与优化效果评估。
SQL 层面优化使用EXPLAIN PLAN 分析SQL 执行计划，调整SQL 语句结构，使用绑定变量等。
参数调整与优化根据系统负载情况调整数据库参数，如SGA 大小、PGA 大小、连接池配置等。
硬件与系统优化升级硬件设备、优化操作系统配置、调整磁盘阵列等，提高整个系统的性能。
性能优化流程与方法02CATALOGUE Oracle数据库性能评估包括用户响应时间和系统响应时间，是衡量数据库性能的重要指标。
响应时间指单位时间内数据库能够处理的事务数量，反映了数据库的处理能力。
吞吐量包括CPU 利用率、内存利用率、磁盘I/O 等，是衡量数据库资源使用效率的重要指标。
资源利用率SQL 语句的执行效率直接影响数据库的整体性能，因此需要对SQL 语句的执行计划、执行时间等进行评估。
SQL 执行效率性能评估指标体系性能监控工具与方法Oracle自带工具Oracle提供了多种性能监控工具，如AWR、ASH、SQL Trace等，可以方便地监控数据库的性能状况。
Oracle性能优化PPT课件
STATSPACK统计信息的表空间以及临时表空间
-
7
AWR报告
• Oracle 10g 版本 推出的新特性 • Automatic Workload Repository • 负责收集、处理并维护性能统计信息 • 检查和分析性能问题 • 对比StackPack报告：实时+自动
-
8
AWR报告
• 生成标准统计报表
举例三:优化后
select b.insuredname, b.insuredidno from llclaimpedor a, lccont b where a.contno = b.contno
and a.caseno = '9055000006018488'
-
31
举例四:优化前
SELECT r.* FROM LOPRTManager r, LCCont t WHERE r.StateFlag in ('1') and (patchflag is null or patchflag <> '1') AND r.PrtType = '0' and t.ContNo = r.OtherNo
• Bitmap 位图索引
– 多应用于数据仓库 – 统计类需求适用 – Update代价较高
-
13
索引创建原则
• 提高查询语句的效率，减慢了DML语句的速度 • 在全表扫描和索引之间权衡
– 如果全表扫描可以接受，那么慢一些的索引访问也可以接受
• 在哪些列建立索引
– Where字句中引用的列 – Join中引用的列 – 在子表的FK上建立索引，防止对父表操作时锁住子表
-
29
-
7
AWR报告
• Oracle 10g 版本 推出的新特性 • Automatic Workload Repository • 负责收集、处理并维护性能统计信息 • 检查和分析性能问题 • 对比StackPack报告：实时+自动
-
8
AWR报告
• 生成标准统计报表
举例三:优化后
select b.insuredname, b.insuredidno from llclaimpedor a, lccont b where a.contno = b.contno
and a.caseno = '9055000006018488'
-
31
举例四:优化前
SELECT r.* FROM LOPRTManager r, LCCont t WHERE r.StateFlag in ('1') and (patchflag is null or patchflag <> '1') AND r.PrtType = '0' and t.ContNo = r.OtherNo
• Bitmap 位图索引
– 多应用于数据仓库 – 统计类需求适用 – Update代价较高
-
13
索引创建原则
• 提高查询语句的效率，减慢了DML语句的速度 • 在全表扫描和索引之间权衡
– 如果全表扫描可以接受，那么慢一些的索引访问也可以接受
• 在哪些列建立索引
– Where字句中引用的列 – Join中引用的列 – 在子表的FK上建立索引，防止对父表操作时锁住子表
-
29
OracleSQL性能优化
AND EXISTS (SELECT ‘X’ FROM DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)
2003-09
21
用NOT EXISTS替代NOT IN (1)
➢ 在子查询中，NOT IN子句将执行一个内部的排序和 合并，对子查询中的表执行一个全表遍历，因此是非 常低效的。
WHERE DEPT_CAT=’A’）
高效：
SELECT …. FROM EMP E
WHERE NOT EXISTS （SELECT ‘X’ FROM DEPT D
WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A’）
2003-09
23
用表连接替换EXISTS
2003-09
4
选择最有效率的表名顺序（1）
➢ ORACLE的解析器按照从右到左的顺序处理FROM子句中的 表名，因此FROM子句中写在最后的表(基础表 driving table) 将被最先处理
➢ 当ORACLE处理多个表时，会运用排序及合并的方式连接它 们。首先，扫描第一个表(FROM子句中最后的那个表)并对记 录进行派序，然后扫描第二个表(FROM子句中最后第二个表)， 最后将所有从第二个表中检索出的记录与第一个表中合适记 录进行合并.
SELECT … FROM EMP E
WHERE SAL > 50000 AND JOB = ‘MANAGER’ AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR = E.EMPNO)
(高效,执行时间10.6秒)MP E
WHERE 25 < (SELECT COUNT(*) FROM EMP
WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)
2003-09
21
用NOT EXISTS替代NOT IN (1)
➢ 在子查询中，NOT IN子句将执行一个内部的排序和 合并，对子查询中的表执行一个全表遍历，因此是非 常低效的。
WHERE DEPT_CAT=’A’）
高效：
SELECT …. FROM EMP E
WHERE NOT EXISTS （SELECT ‘X’ FROM DEPT D
WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A’）
2003-09
23
用表连接替换EXISTS
2003-09
4
选择最有效率的表名顺序（1）
➢ ORACLE的解析器按照从右到左的顺序处理FROM子句中的 表名，因此FROM子句中写在最后的表(基础表 driving table) 将被最先处理
➢ 当ORACLE处理多个表时，会运用排序及合并的方式连接它 们。首先，扫描第一个表(FROM子句中最后的那个表)并对记 录进行派序，然后扫描第二个表(FROM子句中最后第二个表)， 最后将所有从第二个表中检索出的记录与第一个表中合适记 录进行合并.
SELECT … FROM EMP E
WHERE SAL > 50000 AND JOB = ‘MANAGER’ AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR = E.EMPNO)
(高效,执行时间10.6秒)MP E
WHERE 25 < (SELECT COUNT(*) FROM EMP
ORACLE性能优化之SQL调整精品PPT课件
3
Oracle构架-总体结构
• 实例(Instance) 包括内存域和后台进程，数据库启动时，首先启动实例，系统自动分配系统
全局区，并启动Oracle的多个后台进程。 • 系统全局区（System Glogal Area,SGA)
是一组为系统分配的共享的内存结构，可包含一个数据库实例的数据或控制 信息。 • 数据块缓存区（Data Buffer Cache)
数据字典有访问表的至少一行的统计数据，则优化器使用的基于代价 和最佳吞吐量方法。如果访问表没有统计数据，则优化使用基于规则 的路径。默认为基于规则的路径 • All_rows 对整个SQL语句，优化器使用基于代价的路径。使用最小 资源返回整个行。 • FIRST_ROWS 对整个SQL语句，优化器使用基于代价的路径。使用 最小资源返回第一行。 • FIRST_ROWS_N(N是1、10、100或者1000）优化器使用基于代价 的路径。以最有效的方法检索行。 • RULE 对整个SQL语句，优化器使用基于规则的路径。
• 程序全局区（Program Global Area,PGA) 服务器进程使用的内存区域，包括特定服务器进程的数据和控制信息，例如 使用的操作系统资源等 。
Oracle构架-总体结构
• 数据写入进程（Database Writer,DBWR) 将缓冲区内的数据写入数据文件，是负责数据缓冲区管理的一个后台进程
Oracle优化器-CBO
• 1、条件 需要收集统计分析数据，包括记录数、块数、空值数、
值的唯一数、不同值的分布情况等； • 2、相关指标
Selective ：结果集占全表的比例； Histograms：不同列值记录数的分布； Cardinality：结果记录数； Cost：执行成本； • 维护CBO的有效性 用ANALYZE 语句; 使用DBMS_STATS 包;
Oracle 数据库SQL调优PPT课件
培训中心版权所有，未经许可不得向外传播，违者必究
规则：合理有效的使用索引提高查询效率
在多数情况下，通过索引提高查询效率是非常有效的办法 索引一定要建的合理，并且被正确的使用
选择合适的索引列规则：
➢ 选择在where子句中常用的查询列做索引字段 ➢ 选择常用来关联表的字段做索引字段 ➢ 对普通的B-TREE索引,应该选择具有选择性高（high selectivity）的字段做索
当对索引列上使用计算时，应把计算操作右移。 若需要在索引列上应用函数，考虑创建基于函数的索引。
培训中心版权所有，未经许可不得向外传播，违者必究
规则：避免在索引列上使用计算或在非基于函数的索引列上 使用函数 常见的未正确使用索引的例子 :
在下面的例子里, ‘!=' 将不使用索引. 记住, 索引只能告诉 你什么存在于表中, 而不能告诉你什么不存在于表中. 不使用索引: SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT !=0; 使用索引: SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT >0;
培训中心版权所有，未经许可不得向外传播，违者必究
规则：避免在索引列上使用计算或在非基于函数的索引列上 使用函数
如果一定要对使用函数的列启用索引, ORACLE新的功能: 基于函数的索引(Function-Based Index) 也许是一个较好 的方案. CREATE INDEX EMP_I ON EMP (UPPER(ename)); /*建 立基于函数的索引*/ SELECT * FROM emp WHERE UPPER(ename) = ‘BLACKSNAIL’; /*将使用索引*/
规则：合理有效的使用索引提高查询效率
在多数情况下，通过索引提高查询效率是非常有效的办法 索引一定要建的合理，并且被正确的使用
选择合适的索引列规则：
➢ 选择在where子句中常用的查询列做索引字段 ➢ 选择常用来关联表的字段做索引字段 ➢ 对普通的B-TREE索引,应该选择具有选择性高（high selectivity）的字段做索
当对索引列上使用计算时，应把计算操作右移。 若需要在索引列上应用函数，考虑创建基于函数的索引。
培训中心版权所有，未经许可不得向外传播，违者必究
规则：避免在索引列上使用计算或在非基于函数的索引列上 使用函数 常见的未正确使用索引的例子 :
在下面的例子里, ‘!=' 将不使用索引. 记住, 索引只能告诉 你什么存在于表中, 而不能告诉你什么不存在于表中. 不使用索引: SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT !=0; 使用索引: SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT >0;
培训中心版权所有，未经许可不得向外传播，违者必究
规则：避免在索引列上使用计算或在非基于函数的索引列上 使用函数
如果一定要对使用函数的列启用索引, ORACLE新的功能: 基于函数的索引(Function-Based Index) 也许是一个较好 的方案. CREATE INDEX EMP_I ON EMP (UPPER(ename)); /*建 立基于函数的索引*/ SELECT * FROM emp WHERE UPPER(ename) = ‘BLACKSNAIL’; /*将使用索引*/
Oracle性能调整与优化71页PPT
Oracle数据库性能 调整和优化
Oracle技术专题讲座
SUPPORT SERVICES
内容提要
1.oracle 性能调整概述 2.磁盘I/O的调整 3.oracle 内存分配与调整 4.SQL优化概述 5.Statspack概述
SUPPORT SERVICES
1.oracle 性能调整概述
SUPPORT SERVICES
２.5 管理回滚段
回滚段：用来保存数据变化前映像而提供一致读和保障 事务完整性的一段磁盘存储区域． 旧数据
回滚段
新数
表
据
UPDATE
SUPPORT SERVICES
２.5 管理回滚段
回滚段作用
回退事务
事务恢复
回滚段
读一致性
控制文件
数据文件
重做日志
SUPPORT SERVICES
SUPPORT SERVICES
2.4 使用本地管理表空间(LMT)自动段空间管理 （ASSM）
create tablespace demo datafile '/ora01/oem/demo01.dbf ' size 5m EXTENT MANAGEMENT LOCAL -- Turn on LMT SEGMENT SPACE MANAGEMENT AUTO -- Turn on ASSM;
OWNER ALEX
TABLE_NAME DEPT
PARTITION_COUNT 3
SUPPORT SERVICES
2.4 使用分区表避免磁盘争用
Select segment_name, partition_name, segment_type, tablespace_name
Oracle技术专题讲座
SUPPORT SERVICES
内容提要
1.oracle 性能调整概述 2.磁盘I/O的调整 3.oracle 内存分配与调整 4.SQL优化概述 5.Statspack概述
SUPPORT SERVICES
1.oracle 性能调整概述
SUPPORT SERVICES
２.5 管理回滚段
回滚段：用来保存数据变化前映像而提供一致读和保障 事务完整性的一段磁盘存储区域． 旧数据
回滚段
新数
表
据
UPDATE
SUPPORT SERVICES
２.5 管理回滚段
回滚段作用
回退事务
事务恢复
回滚段
读一致性
控制文件
数据文件
重做日志
SUPPORT SERVICES
SUPPORT SERVICES
2.4 使用本地管理表空间(LMT)自动段空间管理 （ASSM）
create tablespace demo datafile '/ora01/oem/demo01.dbf ' size 5m EXTENT MANAGEMENT LOCAL -- Turn on LMT SEGMENT SPACE MANAGEMENT AUTO -- Turn on ASSM;
OWNER ALEX
TABLE_NAME DEPT
PARTITION_COUNT 3
SUPPORT SERVICES
2.4 使用分区表避免磁盘争用
Select segment_name, partition_name, segment_type, tablespace_name
ORACLE SQL性能优化(全)
SQL语句的处理过程
绑定(BIND)： 1. 在语句中查找绑定变量 2. ቤተ መጻሕፍቲ ባይዱ值(或重新赋值)
SQL语句的处理过程
执行(EXECUTE)： 1. 应用执行计划 2. 执行必要的I/O和排序操作
提取(FETCH)： 1. 从查询结果中返回记录 2. 必要时进行排序 3. 使用ARRAY FETCH机制
共享游标：好处
1. 减少解析 2. 动态内存调整 3. 提高内存使用率
书写可共享的SQL
绑定变量和共享游标
ORACLE 优化器模式 概述
Oracle的优化器共有3种模式：RULE (基于规则)、COST (基于成本)、CHOOSE(基于选择)。
设置缺省的优化器的方法，是在启动参数文件中针对 OPTIMIZER_ MODE参数的各种声明进行选择，如RULE、COST、 CHOOSE、ALL_ ROWS、FIRST_ ROWS。当然也可以在SQL语 句级别或是会话级别对其进行覆盖。
SQL Tunning 的重点
SQL: insert, update, delete, select; 主要关注的是select 关注的是：如何用最小的硬件资源消
耗、最少的响应时间定位数据位置
SQL优化的一般性原则
目标： 减少服务器资源消耗（主要是磁盘IO）；
设计方面： 尽量依赖oracle的优化器，并为其提供条件； 合适的索引，索引的双重效应，列的选择性；
绑定变量解决重编译问题
未使用绑定变量的语句
sprintf(sqlstr, "insert into scott.test1 (num1, num2) values (%d,%d)",n_var1, n_var2);
Oracle的性能优化PPT课件
看Oracle数据库的冲突情况，如果没有冲突的话，latch free查询出来没有结 果。如果冲突太大的话，数据库管理员可以降低spin_count参数值，来消除 高的CPU使用率。
第29页/共72页
4．CPU的优化调整方法 一些优化CPU使用和配置的具体方法有：
（1）取消屏幕保护。 （2）把系统配置为应用服务器。 （3）监视系统中消耗中断的硬件。 （4）保持最小的安全审计记录。 （5）在专用服务器上运行Oracle。 （6）禁止非必须的服务。
Oracle的性能 优化
第1页/共72页
第九章 Oracle的性能优 化
本章学习目标 本章将介绍优化和调整Oracle数据库 系统的一些相关命令和化概述 9.2 SQL语句的优化
9.3 Oracle运行环境的优化 9.4 并发事件处理 9.5 数据完整性 9.7 常见问题处理
第13页/共72页
语句排序优化 排序发生的情况如下： SQL中包含group by 子句 SQL 中包含order by 子句 SQL 中包含 distinct 子句 SQL 中包含 minus 或 union操作
第14页/共72页
3．选择联合查询的联合次序 联合查询中如涉及到多个表的字段关联及查询，其SQL查询语句联合次
第5页/共72页
9.1.2 不同类型系统的优化
1．在线事务处理信息系统（OLTP） 这种类型的信息系统一般需要有大量的Insert、Update操作。OLTP系统需要保
证数据库的并发性、可靠性和最终用户的速度，这类系统使用的Oracle数据库需要 主要考虑下述因素或参数： （1）数据库回滚段是否足够？ （2）是否需要建立Oracle数据库索引、聚集、散列？ （3）系统全局区（SGA）大小是否足够？ （4）SQL语句是否高效？
第29页/共72页
4．CPU的优化调整方法 一些优化CPU使用和配置的具体方法有：
（1）取消屏幕保护。 （2）把系统配置为应用服务器。 （3）监视系统中消耗中断的硬件。 （4）保持最小的安全审计记录。 （5）在专用服务器上运行Oracle。 （6）禁止非必须的服务。
Oracle的性能 优化
第1页/共72页
第九章 Oracle的性能优 化
本章学习目标 本章将介绍优化和调整Oracle数据库 系统的一些相关命令和化概述 9.2 SQL语句的优化
9.3 Oracle运行环境的优化 9.4 并发事件处理 9.5 数据完整性 9.7 常见问题处理
第13页/共72页
语句排序优化 排序发生的情况如下： SQL中包含group by 子句 SQL 中包含order by 子句 SQL 中包含 distinct 子句 SQL 中包含 minus 或 union操作
第14页/共72页
3．选择联合查询的联合次序 联合查询中如涉及到多个表的字段关联及查询，其SQL查询语句联合次
第5页/共72页
9.1.2 不同类型系统的优化
1．在线事务处理信息系统（OLTP） 这种类型的信息系统一般需要有大量的Insert、Update操作。OLTP系统需要保
证数据库的并发性、可靠性和最终用户的速度，这类系统使用的Oracle数据库需要 主要考虑下述因素或参数： （1）数据库回滚段是否足够？ （2）是否需要建立Oracle数据库索引、聚集、散列？ （3）系统全局区（SGA）大小是否足够？ （4）SQL语句是否高效？