Oracle数据库性能优化

索引创建原则
不建议表上的索引超过5个 能够用组合索引则尽量用组合索引 组合索引的靠左原则 对于大表，尽量利用local的分区索引
例子1——优化前
select * from lccont where contno=9025000001602988;
例子1——优化后
select * from lccont where contno=‘9025000001602988’;
例子4——优化后
SELECT r.* FROM LOPRTManager r, LCCont t WHERE r.StateFlag in ('1') and (patchflag is null or patchflag <> '1') and r.Code <> '70' AND r.PrtType = '0' and t.ContNo = r.OtherNo and r.ManageCom like '86070101%' union all SELECT r.* FROM LOPRTManager r WHERE r.StateFlag in ('1') and (patchflag is null or patchflag <> '1') and r.ManageCom like '86070101%' AND r.PrtType = '0' and r.Code = '70' and r.PrtSeq = '1019010000132388' and r.PrtSeq = '1019010000132388'
系统设计人员 程序开发人员 数据库管理员 系统管理员
有哪些限制？
数据库优化总体原则
技术
逻辑 正确 实践 第一 把握 重点 让用 户参 与
沟通
简化 业务 逻辑 持续 改进
优化一般过程
定位问题
发现问题
提出优化 方案
跟踪实际 应用
验证优化 方案
STATSPACK报告
安装Statspack的准备
——数据库调优专题培训
2011年09月
培训目标
通过本次培训你将了解
• 数据库优化基本原则 • Oracle性能诊断工具的使用 • SQL优化主要手段及原则 • 数据库设计原则及其重要性
也很重要但本次不涉及
• 第三方优化工具的使用 • 数据库参数调优 • ……
2
数据库优化总体原则
为什么要优化？ 优化的范围？ 谁来优化？
• 80/20原则
Enqueue activity for DB rollback segment stats for DB rollback segment storage for DB Undo Segment Stats for DB latch activity for DB dictionary Cache stats for DB SGA memory summary for DB init.ora parameters for DB
例子3——优化前
select b.insuredname, b.insuredidno
from llclaimpedor a, lccont b where trim(a.contno) = trim(b.contno)
and a.caseno = '9055000006018488'
例子3——优化后
例子2——优化后
Select * from v_bc_lcinsured i, LLSubReport t, LLReportRela a where i.insuredno = t.customerno and t.SubRptNo = a.subrptno and a.rptno = '9050000000766388'
STATSPACK报告
性能快照的采集
SQL>exec statspack.snap;
统计报告的生成
SQL> @?/rdbms/admin/spreport.sql;
性能快照的自动采集
SQL> @?/rdbms/admin/spauto.sql;
AWR报告
报告中关注哪些内容
Top 5 timed events
单独表空间（>90M) 设置参数timed_statistics=true • 在init<sid>.ora中设置该参数，并让其生效 • SQL>alter system set timed_statistics=true;
STATSPACK的安装
SQL> @?/rdbms/admin/spcreate.sql; 脚本将自动创建PERFSTAT用户，并提示保存 STATSPACK统计信息的表空间以及临时表空间
Result
Execution Plan
Row Source Generator
execute
执行计划
RBO和CBO
优化器决定SQL语句的执行路径，对性能至关重要。 基于规则的优化器 RBO (Rule Based Optimizer) • No change since oracle 7.3,desupported in oracle 10 • Stable, generate the execution plan according to fixed rules • Not necessary good • Do not support new oracle features(like IOT, partition, materialized view,…) 基于成本的优化器 CBO (Cost based optimizer) • Since oracle 7.3 • Support all oracle features • Better than RBO in most case, Can be bad in some case • Choose the best execution plan according to internal algorithm • Need to analyze schema to get the best execution plan
例子2——优化前
select * from v_bc_lcinsured where insuredno = (select distinct CustomerNo from LLSubReport where SubRptNo in (select SubRptNo from LLReportRela where RptNo = '9050000000766388'))
索引创建原则
提高查询语句的效率，减慢了DML语句的速度 在全表扫描和索引之间权衡 在哪些列建立索引 Where字句中引用的列 Join中引用的列 在子表的FK上建立索引 • 防止对父表操作时锁住子表 在哪些列上不要建立索引 经常有DML操作 排它性小 Select count(1),count(distinct col_name ) from table_name
Instance efficiency Top SQL
Cache size – Load profile – Instance efficiency Percentages • 数据库主要都在干什么– Top timed event Top SQL – Instance Activity statistic tablespace IO stats for – DB – • 内存命中率DB file IO stats for buffer pool statistics for – DB PGA Aggr Target Stats – for DB
Oracle操作优先级
RBO Path 1: Single Row by Rowid(等级最高) RBO Path 2: Single Row by Cluster Join RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key RBO Path 4: Single Row by Unique or Primary Key RBO Path 5: Clustered Join RBO Path 6: Hash Cluster Key RBO Path 7: Indexed Cluster Key RBO Path 8: Composite Index RBO Path 9: Single-Column Indexes RBO Path 10: Bounded Range Search on Indexed Columns RBO Path 11: Unbounded Range Search on Indexed Columns RBO Path 12: Sort Merge Join RBO Path 13: MAX or MIN of Indexed Column RBO Path 14: ORDER BY on Indexed Column RBO Path 15: Full Table Scan(等级最低) 14
SQL书写原则
同样的SQL只写一次。 使用表的别名，降低解析时间。 SELECT子句中避免使用 ‘ * ‘ 减少访问数据库的次数 用Where子句替换HAVING子句 尽量多使用COMMIT 用>=替代> 用Union all代替union 尽量用EXISTS替代IN、用NOT EXISTS替代NOT IN 用EXISTS替换DISTINCT
select b.insuredname, b.insuredidno
from llclaimpedor a, lccont b where a.contno = b.contno
and a.caseno = '9055000006018488'
例子4——优化前
SELECT r.* FROM LOPRTManager r, LCCont t WHERE r.StateFlag in ('1') and (patchflag is null or patchflag <> '1') and r.Code <> '70' union SELECT r.* FROM LOPRTManager, LCCont WHERE r.StateFlag in ('1') and (patchflag is null or patchflag <> '1') and r.Code = '70' AND r.PrtType = '0' and r.PrtSeq = '1019010000132388' and r.ManageCom like '86070101%' and r.PrtSeq = '1019010000132388‘ AND r.PrtType = '0' and t.ContNo = r.OtherNo and r.ManageCom like '86070101%'
SQL书写原则
少用in、Or关键词 避免在索引列上进行计算 在条件上多加引号，避免改变数据类型 避免在索引列上使用IS NULL和IS NOT NULL 使用复合索引的第一个列进行查询 在索引列上慎用LIKE字句 在存储过程中慎用打开超过一万行数据的游标 优先使用INT而不是Varchar2 优先使用Varchar2，而不是CHAR。 避免不必要的排序：distinct、order by
优化一般过程
定位问题
发现问题
提出优化 方案
跟踪实际 应用
验证优Baidu Nhomakorabea 方案
Oracle 如何处理 SQL语句
User

SQL Stage 1: Create a Cursor Stage 2: Parse the Statement PARSER Stage 3: Bind Any Variables Dictionary Stage 4: Run the Statement RBO CBO Stage 5: Fetch Optimizer of a Query Cost-base Rows Rule-base Optimizer Optimizer Stage 6: Close the Cursor