� 第1周 性能优化综述
� 第2周 锁
� 第3周 Latch
� 第4周 优化器和执行计划
� 第5周 Hints
� 第6周 等待事件
� 地7周 索引和分区（包括11g下新的组合分区）
� 地8周 分析及动态采样（包括11g下的extended statistics技术）
� 第9周 并行执行
� 第10周 变量邦定（包括11g下Adaptive cursor sharing技术）
� 第11周 sql_trace及10046
� 第12周 10053事件
� 第13周 性能视图和性能参数
� 第14周 性能报告分析（AWR，ASH）
� 第15周 基于Oracle RAC架构的性能优化
标明数据库的全过程，核心重点是哪？oracle于mysql的主要区别是？重点转向牢记oracle
面试中常问点：表的数据量太多如何存储最快、如何查询表数据最快，，，(分库分表分区，建索引，，，)？
sql操作：ddl、dml、
设计理论： 事务、三范式、多之间的关系、索引
底层的执行优化过程：优化器(执行计划)
oracle于mysql的主要区别是？
sql操作： 部分函数，某些关键字
设计理论：
底层的执行优化过程：引擎，，，
MySQL与oracle的区别： 某些关键字，某些函数，某些底层原理？（重点研究oracle）
MYSQL ORACLE
auto_increment sequence
ifnull nvl
limit rownum
不支持check约束 check约束
不支持开窗函数 “row_number() 加开窗函数over()”
默认自动提交事务 不自动提交事务
-------------第一课------------------------------
关系型数据库功能趋于完美
核心：事务(四大特性)
导致性能问题的可能原因
� 表没有正确的创建索引 ----错误的执行计划
� 表没有及时的分析 -------错误的执行计划
� 热块-------数据块的争用（反向索引？）
� 锁的阻塞------业务设计缺陷
� SQL解析消耗大量CPU ----变量绑定
� 低效的SQL -----SQL自身的问题
� 数据库整体负载过程----架构设计的问题
......
性能问题的定位
原则 尽可能从小范围分析问题
� SQL层
– 如果能从定位到SQL，就不要从会话层面分析
• 工具 执行计划，10053,10046....
� 会话层 --- 用户与数据库的会话
– 如果能定位到会话，就不要从系统层面分析
• V$SESSION,V$SESSTAT,V$SESSION_WAIT,V$SQL,V$LOCK，SQL_TRACE
� 系统层
– 如果无法定位任何性能问题，从系统层面入手
• AWR（STATSPACK）-- 系统报告,OS tools(TOP,IOSTAT....)
-------------------------------
不要迷恋优化器
� 不要迷信优化器，优化器永远无法知道你的业务需求
– 优化器永远无法按照你的业务需求来重写你的SQL语句。
– 优化器只能在数学（集合）逻辑上做SQL的重写。
� 高效的SQL来自于对业务的理解和对SQL执行过程的理解。
cmd下操作oracle：
oracle的一个登陆命令 sqlplus
net start oracleserviceorcl ----》 net start oracleserviceName
oracle数据库本身默认不设置自动提交 (mysql,jdbc默认提交)
select只是查询，又不是增删改，不需要commit呀
;表示行结束， /表示块结束并提交
ed：编辑命令edit的缩写，用这个命令调用记事本编辑sql，然后保存直接会回到命令行执行sql
Oracle中的优化器又叫查询优化器（Query Optimizer）。它是SQL分析和执行的优化工具，它负责生成、制定SQL的执行计划。
Oracle的优化器有两种：
RBO: Rule-Based Optimization 基于规则的优化器
CBO: Cost-Based Optimization 基于代价的优化器
查看执行计划：
EXPLAIN PLAN FOR sql语句;
或
SQL> set autotrace on
SQL> select * from dave;
或
set autotrace trace exp;
set linesize 120;
select * from t;
业务是把mytable的各条数据累加：
select t1.id, t1.value, sum(t2.value) --》 统计小于等于号左边值
from mytable t1
join mytable t2
on t2.id <= t1.id ----》 小于等于
group by t1.id, t1.value
效率更高的sql：
select id, value, sum(value) OVER(ORDER BY id) ---> 分区了则每个分区是一个窗； 没分区则输出一次是一个窗，所以可以累加
from mytable;
-------------
存储过程：
执行存储过程：
1.如果是命令窗口就用exec 存储过程名
EXEC procedure; --procedure是存储过程名
2.如果是PL/SQL窗口就用 begin 存储过程名 end;
begin
procedure;--procedure是存储过程名
end;
3.如果是程序中调用就用 call 存储过程名 ：
hibernateDao.excuteSqlUpdate("{Call proc_stuInfo()}");//存储过程proc_stuInfo
存储过程创建方法：
create or replace procedure pro_test --pro_test为存储过程名
is
temp varchar2(128); --temp为存储过程临时变量
bengin
select count(*) into temp from TEST; --将查询出来的数据放入存储过程变量中
insert into TEST values(3,'sss',25,'asd');
commit;--切记commit一下（提交）
end;
注意：在存储过程中是不能直接出现"select * from test",这种简单查询，必须将查询出来的数据放入存储过程变量中，如上所示的temp变量。
-------------第一课------------------------------
-------------第二课------------------------------
LOCK
应用于事务的四大特性中，知道有这个东西，我不是DBA，更不是数据库开发
Oracle中锁的分类
Enqueues(LOCK，大锁)---队列类型的锁，通常和业务相关的。
Latches(门栓) ---系统资源方面的锁，比如内存结构，SQL解析......
Latch V.S. enqueue：
Latch Lock
队列性 X O
时长 很短 可能很长
层面 数据库资源层 业务应用层
目的 保证资源的完整性 保证业务操作的完整性
不放入回收站，直接删除则是：drop table xx purge;
执行drop table xx ：被放在回收站(user_recyclebin)里，而不是直接删除掉。这样，回收站里的表信息就可以被恢复，或进行彻底清除
没有并发就没有锁！
体现锁，阻塞状态：
时间 操作 会话1 会话2
create table (id int primary key)；
insert into t values(1); cmd会话窗口插入一条数据，但没提交
insert into t values(1); 另一会话则处于等待状态，即阻塞
锁的原则
� 只有被修改时，行才会被锁定
� 当一条语句修改了一条记录，只有这条记录上被锁定，在Oracle数据库中不存在锁升级。
� 当某行被修改时，它将阻塞别人对它的修改。
� 当一个事务修改一行时，将在这个行上加上行锁（TX），用于阻止其它事务对相同行的修改。
� 读永远不会阻止写。
� 读不会阻塞写，但有唯一的一个例外，就是select ...for update，手动加锁
� 写永远不会阻塞读。
� 当一行被修改后，Oracle通过回滚段提供给数据的一致性读
select * from test where a=2 for update wait 3; 如果3秒内还是无法加锁则返回错误.
在一个session内可以多次进行select XX for update,然后只需要commit或rollback一次即可释放.
Oracle锁的类型: select type,name from V$lock_type;
涉及业务的锁主要是这两种：
TM锁和TX锁
� TM 表锁，发生在insert,update,delete以及select for update操作时，目的是保证操
作能够正常进行，并且阻止其它人对表执行DDL操作。
� TX锁 事务锁（行锁）对于正在修改的数据，阻止其它会话进行修改
锁定是一个开发的范畴 ----》 不是DBA，更不是我的范畴
� 通过锁定，可以达到预期的业务需求。
� 通过对业务深入的分析，可以最大程度的避免不必要锁定的发生。
-------------Latch------
系统全局域SGA主要由三部分构成:共享池(sql 解析，sql重用)、数据缓冲区(数据写入磁盘，数据读入内存)、日志缓冲区； -----》 Latch存在于SGA中
� 是数据库资源的管理机制。
� 业务层面无法感知
� 良好的系统设计，可以有效的减少Latch的争用
– 绑定变量
– 减少热块
......
显示所有的latch：
select name from v$latchname;
– 这个视图实际上是Oracle对每个latch的统计信息的一个汇总，每一条记录表示一种latch
Latch优化的思路
� Latch导致的性能问题，通常是一个系统层面的问题，所以：
– AWR报告是一个比较好的入口。
– 通过动态视图v$latch..可以分析当前系统的latch资源情况。
– 确定争用最大的Latch
– 分析可能的原因。
– 从应用层面和数据库层面考虑解决途径。
-------------第二课------------------------------
-------------第三课------------------------------
Oracle的优化器
� RBO（8i之前）---Rule based optimizer – 一套死板的规则来控制数据的访问。
� CBO（8i之后）---Cost based optimizer – 依据一套数据模型，计算数据访问和处理的成本，择最优成本为执行方案
CBO的工作模式：
� all_rows--- 以结果集的全部处理完毕为目的
– select id,count(*) from t group by id,order by id.
� first_rows（n）--- 以最快返回n行 为目的 ----》 oracle分页
SELECT OBJECT_NAME
FROM
(
SELECT ROWNUM RN, OBJECT_NAME
FROM
(
SELECT OBJECT_NAME FROM T ORDER BY OBJECT_NAME
)
WHERE ROWNUM <= 20
)
WHERE RN >= 11;
选择哪种呢，看写的sql更偏向于哪种则选哪种；
实在不知道就两种都设置，然后查看执行计划看哪种更优选择哪种
优化器模式的设置方式
� 参数设置 ---》 全局都改 optimizer_mode
� 会话设置 ---》 当前会话都改 alter session set optimizer_mode=all_rows;
� SQL设置 ----》 常用，灵活，针对单一sql
select /*+ all_rows */ count(*) from t;
--类似于hints中给优化器提示：
select /*+ full(t) */ from t where id = 10;
几个重要的概念：
selectivity： 表选择性、索引选择性： 选择重复率较低的作为索引，比如重复率为50%，就好比书只有两页，建索引就没有任何意义；
建索引的目的是在大量的数据中快速定位到该条数据
cardinality：
�在执行计划中表示每一步操作返回的记录数。 （该数据是由oracle估算出来的，并不是实际返回的数）
�CBO通过对这个值的权重计算，决定使用哪一种方式访问
数据
CBO的核心----成本的计算：
� 数据访问的成本的估算
– I/O成本的估算
• 全表扫描（多数据块）
• 索引（单数据块，多数据块）
– CPU成本的估算
� 数据处理的成本
– CPU的成本的估算
I/O成本比较容易估算，CBO成本不容控制
优化器的总结
� 是Oracle一个不断完善的数学模型。
� 每个版本的机制有较大的差异。
� I/O成本最为关键（执行计划）。
� 无法对SQL进行智能优化。
------------
执行计划有多种情况：
数据的访问(直接，索引)、 ---> 索引查询，两步： 一查找到索引，二根据索引去表中查找数据
数据的处理(group by ...)、
数据的关联处理(merge join:先排好序、hash join：大小表join)
查看执行计划：
EXPLAIN PLAN FOR sql语句;
或
SQL> set autotrace on
SQL> select * from dave;
或
set autotrace trace exp;
set linesize 120;
select * from t;
从下往上看，看到的每一步结果都是累加的！
-------------第三课------------------------------
-------------第四课------------------------------
Hints的使用范畴
� 尽量避免在开发中使用
� 辅助DBA用来做性能排查和优化
给优化器的一种提示，可以控制优化器的一种行为！
-----------
访问路径相关的Hints：
全表扫描：select /*+ full(t) */ from t where id = 10;
no_index: select /*+ no_index(t idx_t) */ from t where id = 10;
index:,,,
表关联的hints：
Nested Loop Joins(NL) : /*+ use_nl(t1,t2) */ , 常见拿一个表中每条数据去另一个表中进行查找一次
NL的场景
– 关联中有一个表比较小 ----》 有一个小表，用hash join也很高效，关键看执行器怎么选择
– 被关联表的关联字段上有索引 (写在前，指的是大表，并建索引)
– 索引的键值不应该重复率很高
Hash join的应用场景：
– 一个大表，一个小表的关联(把小表hash到内存或磁盘中) --- /*+ use_hash(t1 t2) */
– 表上没有索引 （因为使用了hash，所以没有表没有索引）
– 适合返回结果集比较大
Sort Merge join： /*+ use_merge(t1 t2) */ ---> 要先排序，耗费资源，用得不多
应用场景
� 当结果集已经排过序操作
------
创建个小表的方式：create table t1 as select * from dba where rownum<99;
一个表插入数据： insert into t select * from t1
Oracle的优化器是CBO，所以对象的统计数据对执行计划的生成至关重要！
DBMS_STATS.GATHER_TABLE_STATS 统计表,列,索引的信息 --- （默认参数下是对表进行直方图信息收集，包含该表的自身-表的行数、数据块数、行长等信息；列的分析--列值的重复数、列上的空值、数据在列上的分布情况；索引的分析-索引页块的数量、索引的深度、索引聚合因子）.
exec dbms_stats.gather_table_stats(user,'t',cascade=>true);
-------------第四课------------------------------
-------------第五课------------------------------
等待事件
等待产生性能问题(不等待是不可能的，是否在可接受的范围)
SQL*Net： 一种协议， 客户端访问oracle数据库
一个最简单的等待事件
SQL*Net message from client (数据库服务器等待客户端发信息)
造成Oracle的等待---三种情况
� 请求的资源太忙，需要等待资源释放
� 会话处于空闲状态，等待新的任务
� 会话被阻塞，需要等待阻塞解除
等待的定位方式
--sql级别: 已经知道是某条sql问题，直接拿过来再执行一次就好了
--会话级别： 获取会话的id，从 v$session_wait 中去查看 event
--系统级别：AWR报告 (v$system_event)
begin end是表示的一个语句块
begin
for i in 1..10 loop
insert into t select * from b;
end loop;
end;
/
-------------第五课------------------------------
-------------第六课------------------------------
索引的目的
� 提高数据访问的效率
建立索引的目的是加快对表中的数据访问。付出的代价：一增加数据库的存储空间来存储索引，二是对数据的修改时也要花费较多的时间(因为索引也要随之变动)
索引的底层存储原理是B+树(平衡的多叉查找树，子节点存储索引值，叶子节点间相互链接)
oracle、mysql 主要是B+树实现索引的底层结构
Oracle的索引类型
�B-tree索引 B树索引 --- 应用广泛 (这里是个泛泛的概念，准确来说是B+树)
�Bitmap索引 位图索引 ---- 为了弥补B树索引的低效场景
�TEXT index 全文索引 －－－ b-tree,bitmap无法发挥作用的场景，like '%string%'
B-tree 索引
� 高效的场景
– 索引字段有着很高的selectivity或者结果集很小的时候
select * from t where t=100 ， 数据量几万条，等于适合索引
� 低效的场景
– 索引字段有着很低的selectivity或者结果集很大的时候。
select * from t where t>100 ， 数据量几万条，大于，相当于全表扫描了，用索引不合适(扫索引几万次，扫表几万次)，还不如直接扫表几万次快一倍！
� 基本上适用于所有类型的数据库。
� 没有太明显的缺点。
位图（bitmap）索引 －－－》 位运算，计算机最快的处理方式
� 使用场景
– OLAP（On-Line Analytical Processing联机分析处理）：是数据仓库系统的主要应用，强调数据分析，并且提供直观易懂的查询结果。
– 重复率很高的键值
� 不适用的场景
– OLTP（on-line transaction processing 联机事务处理）：是传统的关系型数据库的主要应用（有主键，重复率很低，对位图存储没有意义），主要是基本的、日常的事务处理，例如银行交易。
– DML频繁操作 （位图索引会锁表锁定数据，高并发下难以忍受），在有bitmap的表中修改数据，会对所有受影响的键值关联的记录做锁定！
全文索引
� 使用场景
– b-tree,bitmap无法发挥作用的场景，
like '%string%' －－－》 模糊查询索引找不到入口
� 缺点
– 占用过大的磁盘空间
– 维护成本高
– bug多
-----------
分区
分区索引与全局索引性能相差无几
分区索引和全局索引
� 分区索引的目的在于数据的管理而非性能。
� 一个分区表上如果经常有DDL操作，将会导致全局索引无效，需要对索引重建，此时
创建分区索引更加适合。
分区索引和全局索引针对分区表来说的，每个分区表对应一个index区，这就叫分区索引
多个分区表对应一个index区，这就叫全局索引
全局索引： 表的DDL操作会导致索引无效
-------------第六课------------------------------
-------------第七课其它。。。------------------------------
并行度，就是Oracle在进行并行处理时，会启动几个并行进程来同时执行
----
绑定变量： 多条sql除了个别参数不一样，大体一样，就用一个变量来替代该参数，把多条sql变成少量的sql，节省了很多sql的解析时间(解析器会把他们当成一样的sql)
场景： 每天刷公交卡、地铁扣款。。。
变量绑定的目的：
� 减少SQL硬解析的次数。(软解析不会再解析了，直接拿上一条的解析)
� 减少系统资源开销。
� 减少latch争用。
变量绑定的应用场景：
� 适用于OLTP(关系型数据库)
– 用户并发很高
– 表中有主键
– 操作的数据少
– 执行计划基本相同
– SQL的重复率高
• select * from account where account_id=xxxx; • update account set money=xxxx where account_id=xxxx;
� 不适用于OLAP(数据仓库)
– 执行计划多变
– 用户少
– SQL解析对系统性能影响小
---
游标：存放SQL语句的执行结果,指向数据缓冲区(SGA)，可以理解为指向结果集的指针，利用游标不断的滚动，遍历结果集得到所有数据
使用游标会降低执行效率，平时尽可能不要用
---
10046事件和SQL_TRACE作用一样，都是用来追踪SQL，通过生成的trace来了解SQL的执行过程。
当一个SQL出现性能问题的时候，可以使用SQL_TRACE或者10046事件来跟踪SQL，通过生成的trace来了解SQL的执行过程；
和普通的SQL_TRACE方法相比，10046事件提供了可选的追踪级别。
10046事件可选四个级别：level 1、4、8、12：
Level 0 停用SQL跟踪，相当于SQL_TRACE=FALSE
Level 1 标准SQL跟踪，相当于SQL_TRACE=TRUE
Level 4 在level 1的基础上增加绑定变量的信息
Level 8 在level 1的基础上增加等待事件的信息
Level 12 在level 1的基础上增加绑定变量和等待事件的信息
tkprof工具只能用来处理sql_trace 和 10046事件产生的trace文件，
tkprof工具不能处理10053事件的trace文件，我们只能直接阅读原始的trace文件
10053事件是用来诊断优化器(CBO:cost-based)如何估算成本和选择执行计划的，用它产生的trace文件提供了Oracle如何选择执行计划，为什么会得到这样的执行计划信息；
-------------
性能问题的来源
CPU、内存、磁盘、IO、网络
---
ASH的采样数据是保存在内存中。分配给ASH的内存空间是有限的，当所分配空间占满后，旧的记录就会被覆盖掉；而且数据库重启后，所有的这些ASH信息都会消失。
使用AWR，每小时对v$active_session_history进行采样一次，并将信息保存到磁盘中，并且保留7天，7天后旧的记录才会被覆盖
ASH保存了系统最新的处于等待的会话记录，可以用来诊断数据库的当前状态；
而AWR中的信息最长可能有1小时的延迟，所以其采样信息并不能用于诊断数据库的当前状态，但可以用来作为一段时期内数据库性能调整的参考
ASH -- Active Session History
� ASH报表间隔时间可以精确到分钟，因而ASH可以提供比AWR更详细的关于历史会
话的信息，可以作为AWR的补充
AWR & ASH
� 对业务的了解是前提。
� 建立基线数据做比对。
� 不必纠结于某些不太相干数值，有目的性的去阅读
--------
RAC(real application clusters)“实时应用集群”技术，Oracle实现高可用性的技术
2041
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
