ContentsPage目录01发现性能瓶颈定位问题02了解性能优化掌握基础03利用工具实践解决问题04SQL优化
小结规范要求05现存问题汇总解决方案概述?尽量简单，模块化?易读、易维护?节省资源?内存?CPU?扫描
的数据块要少?少排序?不造成死锁优化的思想：优化就是通过各种手段降低SQL语句所需要消耗的逻辑读、物理读、CPU时间、热点
争用….目标调优领域：1.应用程序级调优：SQL语句调优管理变化调优2.实例级调优内存数据
结构实例配置3.操作系统交互I/OSWAPParametersSQL语句优化的过程：?定位有
问题的语句?检查执行计划?检查执行过程中优化器的统计信息?分析相关表的记录数、索引情况?改写SQL语句、使用HINT、调整
索引、表分析?有些SQL语句不具备优化的可能，需要优化处理方式（一）发现性能瓶颈，定位问题1、硬件、系统CPU：核心内存：
IO、参数设置存储：IO，读取，条带化，存储网络….网络：LinuxOS系统参数设计2、Oracle参数Buffer大
小会话、进程数表空间、日志文件、块大小、回滚（前映像）3、业务需求、业务信息描述准备、清晰业务流程思路、准确改善、促进业
务，改进管理4、设计结构设计与编码：规范、标准、统一、通用序设计的分离：技术选型、低耦合高内聚5、SQL绑定变量索引技术
分区并行….目标：性能优化及促进管理提升与改善（一）发现性能瓶颈，定位问题迁移困难：多、不统一、不规范执行效率低下调整困难
、优化难：牵一发动全身体系结构整体Oracle体系结构服务器进程文件系统的结构层次内存区域的管理后台进程体系结构内存：命中率（热块
)CPU：运行时间IO：物理读写Linux与Windows区别体系结构内存融合（cachefusion）来保证各节点数据访问的一
致性体系结构Oracle文件系统结构数据库由一个或多个表空间组成；表空间由一个或多个数据文件组成，表空间中包含段；段(表、索引等)
由一个或多个盘区组成。段存在于表空间中，但是在表空间中可以有许多数据文件中的数据；盘区是在磁盘上连续的块的组。一个盘区在一个表空间
中，且在表空间中单一的文件中；块是数据库中最小的分配单元，也是数据库使用的最小I/O单元。体系结构Oracle文件系统结构-表空间
System表空间Sysaux表空间UNDO表空间临时表空间普通的用户表空间为了提高查询及DML操作速度，进行表空间规划：不同用户
不同表空间表分区使用不同的表空间索引使用单独表空间体系结构Oracle文件系统结构-数据块包含块类型的有关信息、在磁盘上的地址及块
上发生的活动事务和过去事务的相关信息。包含把行存储在这个块上的表的有关信息。包含块中行的描述信息。它是一个指针数组，指向块中数据部
分的行还未包含数据的空间。已包含的数据。ContentsPage目录01发现性能瓶颈定位问题02了解性能优化掌握基础03
利用工具实践解决问题04SQL优化小结规范要求05现存问题汇总解决方案（二）了解性能优化，掌握基础Or
acle数据库处理引擎程序块（PL块）都会被最终转换成：SELECTDELETEUPDATEINSERT的基本标准语句，Or
acle的SQL引擎进行标准语句处理。处理过程中，Oracle查询优化程序确定最高效的执行计划，处理任何SQL语句过程中最重
要的一个步骤：评估表达式和条件使用对象和系统统计信息（行数、块数..）确定如何访问数据（全表、索引、分区）确定如何联接表
（连接方式）确定哪条访问路径最有效….所以我们主要针对基本语句的优化（二）了解性能优化，掌握基础优化器RBO优化器（淘汰不用）C
BO优化器CBO是基于成本的优化器，它根据可用的访问路径、对象的统计信息、嵌入的Hints来选择一个成本最低的执行计划。CBO主要
包含以下组件：查询转换器（QueryTransformer）评估器（Estimator）计划生成器（PlanGenerator
）统计信息：表行数据、块数、索引级别等字典信息存储在Library、Cache中（二）了解性能优化，掌握基础统计信息优化程序依靠各
种统计信息来确定SQL语句的最佳执行计划，一些统计信息是在不同时间收集的。对象统计信息字典统计信息固定对象的统计信息
操作系统统计信息….动态采样（二）了解性能优化，掌握基础锁与阻塞作用：保证数据的一致必、完整性，避免脏读…TM锁：表锁TR锁：
行级锁（事务锁）排他锁（rowexclusive）：行级别，释放前，其他事物不能修改被锁的资源共享锁（share）：段级别，释放
之前，对象上可以继续加其他类型的锁共享锁（rowshare）：行级别主外键设置可能会RI锁：参照一致性检查锁，影响到主子表的DM
L操作会话阻塞：等待其它会话提交，导致会话排队，引起阻塞大量阻塞会话会影响系统运行（二）了解性能优化，掌握基础绑定变量与隐式转换S
QL语句绑定的优缺点SQL引擎隐式转换引起的效率问题SELECTFROMV_HY_JC_JMXXselectfrom
v_hy_jc_jmxxSELECT…..FROMV_HY_JC_JMXX.F74=10SELECT…..FRO
MV_HY_JC_JMXX.F74=''10‘ContentsPage目录01发现性能瓶颈定位问题02了解性能优化
掌握基础03利用工具实践解决问题04SQL优化小结规范要求05现存问题汇总解决方案（三）利用工具，
实践解决问题执行计划解释计划？oracle的执行计划：如何一步一步执行sql，比如说先查数据再排序，或者先作子查询再作主查询什么的
。oracle通过内部算法计算以后选择的一个消耗比较少的执行路径，就好像计算最优路径一样。确定当前的执行计划确定索引的效果确定
访问路径确认使用索引确认可使用的执行计划（三）利用工具，实践解决问题执行计划的工具和手段EXPLAINPLANV$SQL
_PLANSQLPlusAUTOTAWR（三）利用工具，实践解决问题执行计划实例（三）利用工具，实践解决问题EM可视化执行计
划（三）利用工具，实践解决问题SQLPLUS执行计划跟踪分析（三）利用工具，实践解决问题SQLPLUS执行计划跟踪分析（三）利用工
具，实践解决问题PLSQL执行计划跟踪分析（三）利用工具，实践解决问题读懂执行计划指标从执行计划找问题（三）利用工具，实践解决问题
跟踪方法（三）利用工具，实践解决问题读懂执行计划指标了解关键性能关键指标（术语）：Rowid的概念??RecursiveS
ql概念??Predicate(谓词)??DRivingTable(驱动表)??ProbedTable(被探查表)??
组合索引(concatenatedindex)oracle访问数据的存取方法??1）全表扫描（FullTableScan
s，FTS）??2）通过ROWID的表存取（TableAccessbyROWID或rowidlookup）??3）
索引扫描（IndexScan或indexlookup）（三）利用工具，实践解决问题关联连接对于每种联接，优化程序（按顺序）确定
：表的联接顺序；应用于每个联接的最佳联接方法；每个行源的访问路径嵌套循环联接排序合并联接散列联接SORTUNIQU
ESORTAGGREGATESORTGROUPBYSORTJOINSORTORDERBY（三）利用工具，实
践解决问题索引技术索引：可提高SQL性能。（单一、复合索引）（B，位图,函数，反向）用在：联接列频繁出现在WHERE
子句中的列对下列项使用组合索引：频繁一起使用在WHERE子句中的列对较大的索引请考虑使用压缩的键索引对下列项使用位图
索引：相异值很少，且行数很多的列只读表或只读为主的表具有多个谓词的WHERE子句?（1）索引唯一扫描（inde
xuniquescan）（2）索引范围扫描（indexrangescan）?????在非唯一索引上都使用索引范
围扫描。使用indexrangscan的3种情况：??（a）在唯一索引列上使用了range操作符（><<>>=
<=between）??（b）在组合索引上，只使用部分列进行查询，导致查询出多行??（c）对非唯一索引列上进行的
任何查询。（3）索引全扫描（indexfullscan）（4）索引快速扫描（indexfastfullscan）过多
索引会影响DML操作的速度（三）利用工具，实践解决问题索引技术索引高度＼ROWID＼索引值对索引的监控对索引的管理（三）利用工具，
实践解决问题索引技术（三）利用工具，实践解决问题其它：使用tkprof\AWR生成优化程序跟踪。。。。如果一个系统的执行效
率比较低，一个比较好的方法是通过跟踪用户的会话并且使用Tkprof工具使用排序功能格式化输出，从而找出有问题的SQL语句。TKPR
OF要为在跟踪文件中找到的每个SQL语句提供一个执行计划：CALL：每次SQL语句的处理都分成三个部分Parse：这步将SQL语句
转换成执行计划，包括检查是否有正确的授权和所需要用到的表、列以及其他引用到的对象是否存在。Execute：这步是真正的由Oracl
e来执行语句。对于insert、update、delete操作，这步会修改数据，对于select操作，这步就只是确定选择的记录。F
etch：返回查询语句中所获得的记录，这步只有select语句会被执行。COUNT:这个语句被parse、execute、fetc
h的次数。CPU：这个语句对于所有的parse、execute、fetch所消耗的cpu的时间，以秒为单位。ELAPSED：这个语
句所有消耗在parse、execute、fetch的总的时间。DISK：从磁盘上的数据文件中物理读取的块的数量。QUERY：在一致
性读模式下，所有parse、execute、fetch所获得的buffer的数量。一致性模式的buffer是用于给一个长时间运行的
事务提供一个一致性读的快照，缓存实际上在头部存储了状态。CURRENT：在current模式下所获得的buffer的数量。一般在c
urrent模式下执行insert、update、delete操作都会获取buffer。在current模式下如果在高速缓存区发现
有新的缓存足够给当前的事务使用，则这些buffer都会被读入了缓存区中。ROWS:所有SQL语句返回的记录数目，但是不包括子查询
中返回的记录数目。对于select语句，返回记录是在fetch这步，对于insert、update、delete操作，返回记录则是
在execute这步。分区技术分区表的分类?Rangepartitioning（范围分区）?Hashpartitioning（
哈希分区）?Listpartitioning（列表分区）?Compositerange-hashpartitioning（范
围-哈希组合分区）?Compositerange-listpartitioning（范围-列表组合分区）11G新增：…分区技术
分区技术的效益和目标?性能–Select和DML操作只访问指定分区–并行DML操作–Partition-wise
Join?可管理性：数据删除，数据备份–历史数据清除–提高备份性能–指定分区的数据维护操作?可用性–将故障
局限在分区中–缩短恢复时间分区技术CREATETABLEsales(acct_noNUMBER(5),per
sonVARCHAR2(30),sales_amountNUMBER(8),week_noNUMBER(
2))PARTITIONBYRANGE(week_no)(PARTITIONP1VALUESLESSTH
AN(4)TABLESPACEdata0,PARTITIONP2VALUESLESSTHAN(8)TABLE
SPACEdata1,...…PARTITIONP13VALUESLESSTHAN(53)TABLESPACE
data12);?分区表索引的分类：–LocalPrefixedindex–LocalNon-prefiexedin
dex–GlobalPrefixedindex–NonPartitionIndex?Global索引的分区不同与表
分区?Local索引的分区与表分区相同?分区表上的非分区索引等同于Global索引分区技术createindexcus
t_idx1oncustomers(customer_name)globalpartitionbyrange(cus
tomer_name)(partitioncust_p01valueslessthan(‘H’)tablespace
cust_index01,partitioncust_p02valueslessthan(‘N’)tablespa
cecust_index02,partitioncust_p03valueslessthan(‘T’)tables
pacecust_index03,partitioncust_p04valueslessthan(MAXVALUE)
tablespacecust_index04)createindexcust_idx2oncustomers(cu
stomer_no)local(partitioncust_idx_p01tablespacecust_index01
,partitioncust_idx_p02tablespacecust_index02,partitioncust_i
dx_p03tablespacecust_index03,partitioncust_idx_p04tablespace
cust_index04,partitioncust_idx_p05tablespacecust_index05,pa
rtitioncust_idx_p06tablespacecust_index06,partitioncust_idx_
p07tablespacecust_index07,partitioncust_idx_p08tablespacecu
st_index08)createindexcust_idx3oncustomers(customer_type)l
ocal;并行技术问题：磁盘I/O速度不够快，很慢，?特别是随机的读取并行处理概述?大批量数据处理，考虑并行处理?并行处
理的先决条件-大批量数据处理-多CPU（SMP），CPU利用率不高-足够的内存-I/O负载不高-并发用户少CPU串行执行
CPU并发执行insert/+appendparallel(d,12)/intoSUM_ACCT_DCC_SA
ACN_PdnologgingSELECT/+parallel(a,12)parallel(b,12)paral
lel(c,12)/‘20050930’,a.sa_acct_no,nvl(b.sa_curr_cod,‘01’)，
nvl(b.sa_curr_iden,''0''),并行技术执行计划区别并行技术当Oracle数据库启动的时候，实例会根据初始化参数
PARALLEL_MIN_SERVERS=n的值来预先分配n个并行服务进程，当一条SQL被CBO判断为需要并行执行时发出SQL的
会话进程变成并行协助进程，它按照并行执行度的值来分配进程服务器进程并行技术并行技术注意：并行与进程数?http://blog.ch
inaunix.net/uid-24612962-id-3799001.htmlOraclehttp://blog.chinaun
ix.net/uid-24612962-id-3799001.html表的http://blog.chinaunix.net/ui
d-24612962-id-3799001.html并行度：selecttable_name,degreefromuser_
tables;altertabletparallel(degree1);------直接指定表的并行度altertabl
etparallel;----------设置表的并行度为defaultSELECT/+PARALLEL(4)/MA
X(sal)AVG(comm)?FROMemp,deptWHEREemp.deptno=dept.deptnoG
ROUPBY1HintContentsPage目录01发现性能瓶颈定位问题02了解性能优化掌握基础03利用工具
实践解决问题04SQL优化小结规范要求05现存问题汇总解决方案常见语句调优级汇总绑定变量In,NOT
in改写成EXISTSWHERE子句中的连接顺序用TRUNCATE替代DELETE批量处理通过内部函数提高SQL效率避免使用
（）<>避免使用至少要包含组合索引的第一列。如果索引是建立在多个列上,只有在它的第一个列(leadingcolumn)被whe
re子句引用时,优化器才会选择使用该索引Orderby语句尽量多使用COMMIT事务是消耗资源的，大事务还容易引起死锁常见语句调
优级汇总低效:SELECTDISTINCTDEPT_NO,DEPT_NAMEFROMDEPTD,EMPEWHERE
D.DEPT_NO=E.DEPT_NO高效:SELECTDEPT_NO,DEPT_NAMEFROMDEPTD
WHEREEXISTS(SELECT‘X’FROMEMPEWHEREE.DEPT_NO=D.DEPT_NO)
避免使用前置通配符,SELECTUSER_NO,USER_NAME,ADDRESSFROMUSER_FILESWHEREU
SER_NOLIKE''%109204421''SQL设计及命名规范本规范：为系统设计、系统开发提供参考，主要读者是系统及数据库设
计人员、系统开发人员命名约定：Table：功能模块+汉语拼音缩写Column：汉语拼音缩写StoreProcedureFUNCT
ION:Trigger：INS_、UPD_、DEL_View：_VDATABASELINK编码+名称+级数+
是否明细+使用状态?编码增长方式：各级2位码（01-99），还是4位码SQL设计及命名规范设计、架构规范要求摘要数据库设计
规范要求：范式要求，约束要求（主外），命名，长度，注释，（码表设计，步长），（版本控制，变更控制）…数据库与程序分离原则：低耦合高
内聚，开发工具＼选型码表设计原则：若存在，就不应该再设计了若存在的码表是某一模块下的代码，将进行抽象调整到上一级代码表，各模块可以
共用；并在最后的调整中统一使用此公用代码保证数据库同内容码表：唯一、共用、规范设计的评审及落地：开发与数据组功能结合用户需求后续工
作注意：数据标准及规范的引用＼通用性清理或转移（迁移中间、过期备份表）211BF…ContentsPage目录01发现性能瓶颈定位问题02了解性能优化掌握基础03利用工具实践解决问题04SQL优化小结规范要求05现存问题汇总解决方案（五）现存问题汇总梳理清楚关系不能全以单表冗余关系以业务角度进行关联问题描述行政区划住房关系码表设计（五）设计问题解决方案1.方案目的解决系统在数据设计方面现存问题，使设计更加准确和完善2.解决方案：3.方案组成人员：4.实施时间（五）SQL性能优化方案1.方案目的：提高SQL的执行效率2.方案内容改写调整SQL3.方案组成人员优化需求提供：研发性能优化实施：数据优化小组4.实施时间附：SQL优化需求表若在使用过程中出现的需要优化的SQL问题，请以如下表格形式提交数据组序号需求（问题）描述系统功能结点所选用参数提交时间备注（其它信息）附：备份恢复运维方案方案一：库备份、还原恢复：Rman；Impdb、Empdb单机备份：oracledataguard、Goldengate方案二：备份、还原恢复：Rman；Impdb、Empdb第二套RAC（2节点）备份：oracledataguard、Goldengate交流完毕谢谢聆听 |
|
