oracle优化步骤和方法

oracle优化参考

业务是否用最优的方式来运行。
如果不是最优的方式那就对SQL进行优化。
查看数据库的执行计划

技术方向上，应多考虑性能方面的问题
积极参与到业务层面，从业务角度思考问题。

导致性能问题的可能原因
1，表没有正确的创建索引－－－错误的执行计划
2，表没有及时的分析－－－错误的执行计划
3，热块－－－数据块的争用（反向索引？）
4，锁的阻塞－－－业务设计缺陷、
5，SQL解析消耗大量CPU－－－变量绑定
6，低效的SQL－－－SQL自身的问题
7，数据库整体负载过程－－－架构设计的问题

性能问题的定位
原则 尽可能从小范围分析问题
1，SQL层
如果能从定位到SQL，就不要从会话层面分析
已经定位到了某条SQL语句有问题，就针对该语句着手。使用工具 和 执行计划来分析该语句，如使用：10053，10046（查看某条语句资源消耗情况）

2，会话层
如果能定位到会话，就不要从系统层面分析：
V$SESSION,V$SESSTAT, V$SESSIO{N}_{W}AIT,V$SQL, V$LOCK SQL_TRACE

3，系统层
如果无法定位任何性能问题，从系统层面入手
AWR（STATSPACK）， OS tools（top, iostat）

锁
没有并发就没有锁
Oracle中锁的分类：
Enqueues–队列类型的锁，通常和业务相关的 简写: enq
Latches—系统资源方面的锁，比如内存结构，SQL解析

锁的原则：
1，只有被修改时，行才会被锁定，select操作不会在数据表中加锁。
2，当一条语句修改了一条记录，只有这条记录上被锁定，在Oracle数据库中不存在锁升级。
3，当某行被修改时，它将阻塞别人对它的修改。
4，当一个事务修改一行时，将在这个行上加上行锁（TX），用于阻止其它事务对相同行的修改。
5，读永远不会阻止写。
6，读不会阻塞写，但有唯一的一个例外，就是select … for update.
7，写永远不会阻塞读
8，当一行被修改后，Oracle通过回滚段提供给数据的一致性读。

查看Oracle的锁的类型：
select type, name from v$lock_type;

TM锁和TX锁：
TM表锁：发生在insert, update, delete以及select for update操作时，目的是保证操作能够正常进行，并且阻止其它人对表执行DDL操作。
TX锁 事务锁（行锁）对于正在修改的数据，阻止其它会话进行修改。

表锁的演示：
当未提交一个事务，另外一个相同的事务操作就会被阻塞。查看表的阻塞的详细情况：使用视图：v$lockselectsid,type,id1,id2,lmode,request,blockfromv$lock where type in (“TM”,“TX”) order by 1,2;

注1：TM锁所对应的 字段名：id1 返回的就是数据表的ID号，通过这个返回值可以查出被阻塞的表名，使用数据字典：dba_objects:
select object_name, from dba_objects where object_id =
注2：TX锁所对应的 字段名：id1 与 id2 之后就是对应着的回滚段的地址。
注3：request字段表示是否请求一个锁，值为0表示没有请求，非0说明有请求。
注4：BLOCK字段表示当前是不是有阻塞。值为0没有阻塞，非0表有阻塞。

查看当前会话的SID：
select distinct sid from v$mystat;

会话等待视图：v$sessio{n}_{w}aitselectsid,eventfromv$session_wait where sid in (sid1, sid2);

insert的阻塞与update的阻塞是不一样的。

TM锁的几种模式－－－lock mode
Row Share(RS)—2
Row Exclusive Table Lock (RX) — 3
Share Table Lock—4
Share Row Exclusive Table Lock(SRX) —5
Exclusive Lock(X) — 6

RI锁－－－基于引用关系的锁定
当于具有主外键关系的表做DML操作时，锁定不单单发生在操作表上，相应的引用表上也可能加上相应的锁定。

创建一个主表：
create table p(id int primary key);
创建一个辅表：
create table c(id references p(id));

在对主表进行insert操作的时候，从表也会被加表级锁。

在对主表进行update，delete操作时，不会对从表加表级锁定。

在对从表进行DML操作时，会对主表也会加表级锁。

要在外键上创建一个索引，以提高数据处理效率。

死锁
两个会话互相持有对方资源，遇到死锁时，Oracle会自行对其进行处理。

Latch的目的：
1，保证资源的串行访问：
保护SGA的资源方向
保护内存的分配
2，保证执行的串行化：
保护关键资源的串行执行
防止内存结构损坏

Latch不是队列性的。是数据库的资源层。
Lock是发生的数据库的业务层。

Latch在SGA中。
资源的请求和分配：
共享池
sql解析，sql重用
数据缓冲池
数据访问，数据写入磁盘，数据读入内存
修改数据块
数据段扩展

查看数据库中的Latch,使用的视图：v$latchname

Latch持有的时间非常短

Latch的获取：
1，wait 方式－－如期无法获取请求的Latch，则：
spin: 当一个会话无法获得需要的latch时，会继续使用CPU（CPU空转），达到一个间隔后，再次尝试申请latch，直到达到最大的重试次数。
sleep: 当一个会话无法获得需要的latch时，会等一段时间（sleep）,达到一个间隔后，再次尝试申请latch，如此反复，直到达到最大的重试次数。
2，No wait方式－－如果沅法获取请求的latch,则：
不会发生sleep或者spin
转而去获取其它可用的latch

shared pool里的latch争用－－绑定变量

用于标识trace文件标识：
alter session set tracefile_identifier=bind;
对执行的SQL语句做trace记录
alter session set sql_trace = true;

Buffer cache的机制
视图：v$bh
用于查看文件头。
NXT_HAST, PRV_HASH

Latch相关视图—V$LATCH
这个视图实际上是oracle对每个latch的统计信息的一个汇总，每一条记录表示一种latch.

select name, gets, misses, sleeps, immediate_gets, immediate_misses from v$latch where name like ‘cache%’;

注：NAME:latch名称
GETS：以Willing to wait请求模式latch的请求成功数
MISSES: 初次尝试请求不成功次数
SLEEPS：成功获取前sleeping次数
IMMEDIATE_GETS: 以Immediate模式latch请求数
IMMEDIATE_MISSES:以IMMEDIATE模式请求失败数

V$LATCHHOLDER
该视图用于显示当前的latch holders
PID 标明当前的哪个进程占用着latch
SID 标明当前哪个会话使用着latch
LADDR 标明latch地址
NAME 用于指定被占用的LATCH名
GETS 标明当前的LATCH被获取的次数

该视图包含了当前latch持有有的信息
通过视图中的PID和SID信息，关联视图V$SESSION,V$SESSION_WAIT，可以定位相应持有资源的会话信息。

V$LATC{H}_{C}HILDREN存储子latch信息的视图，在SGA中胡些资源使用多个latch保护，比如librarycache,这些多个latch保护同一个资源，成为子latchV$LATCH_CHILDREN和V$LATCH一样。

LATCH优化的思路：
LATCH导致的性能问题，通常是一个系统层面的问题，所以：
1，AWR报告是一个比较好的入口
2，通过动态视图V$LATCH，可以分析当前系统的LATCH资源情况
3，确定争用最大的LATCH
4，分析可能的原因
5，从应用层面和数据库层面考虑解决途径。

主要是使用绑定变量，减少热块，在数据库设计时决定这一切。

执行计划和优化器
执行计划
SQL语句访问和处理数据的方式
数据的访问：
1，直接访问
并行访问
多数据块
2，通过索引访问
index unique scan
index range scan
index full scan
index fast full scan
index skip scan

数据处理：
order by, group by, count, avg, sum

数据的关联处理
nested loop join, Merge join, hash join

如何产生执行计划：
set autotrace trace exp;
set linesize 120;

Oracle的优化器
CBO–Cost based optimizer
依据一套数据模型，计算数据访问和处理的成本，择最优成本为执行方案。

不同的