<<让oracle跑的更快读书笔记1>>
<<让oracle跑的更快读书笔记1>>
1 锁和阻塞
1) 当一个表有主键唯一时,一个会话在更新后,如果没COMMIT,另外一个会话也出更新
,则会发生死锁
2)查看:
select sid,type,id1,id2,lmode,request,block from v$lock where sid in
(xxx,yyy) order by sid;
其中block列=1表示这个会话正在阻塞另外一个会话；request中的值表示当前会话正
在等待另外一个lmode=XX
的锁
通过SID，可以查询出锁定的用户信息
select machine from v$session where sid in (xx,yyy0;
3)当一个表有若干分区或段的时候，每个都会加上TM表锁，比如一个表某个段加了TM
锁，则不能在其上进行DDL操作，但可以在其他分区段上进行。
4）可以通过select ...for update里阻挡另外的会话对其进行修改
5）主从表的关联查询情况，从表上的外键应该创建索引
2 LATCH和等待
当一个数据块正在被一个会话从磁盘中读到内存中时，如果另外一个会话也要读取
，则必须有机制来处理，
可以把latch理解为轻量级的锁，不会造成阻塞只会导致等待。
1）共享池中的latch争用
如果有大量的SQL被反复分析，就会造成大的latch争用
select * from v$latchname where name like 'library cache%'
2) 访问频率很高的数据块被称为热块，很多用户一起访问时，导致latch争用
当一个会话需要访问一个内存块，先要去象链表一样的结构去搜索这个数据快是否
在内存中；
当一个会话需要访问一个数据块，而这个数据块正在被另外一个用户从磁盘读取到内存中
或者正在被会话修改时，当前的会话需要等待，就产生buffer busy waits等待
A 表数据块
对于一些小表，如果频繁修改，则变成热块，可以考虑将表数据分布在更多的数据
块上，减少
数据块被多数会话同时访问的频率
alter table minimize records_per_block;
但会降低数据读取的性能
B 索引数据块
在RAC中，如果表的主键用序列递增，则多用户在RAC的不同实例中插入时，
会出现相同索引数据块在不同实例的内存中被调用，形成争用，可以使用反向索引；但会
降低性能，在范围查询时要走全表扫描
C 索引根数据块
热块可能发生在索引的根数据块中，可以把索引创建为分区索引时，索引的
根数据块会分布到更多的数据块中。
D 段头数据块
ASSM，让oracle自动管理"free lists",所谓的free lists,是每个数据段的段头
有几个free list列表，存放哪些数据块可以用，当数据块的数据达到一个比例
(pct_free)时，数据块就会从free list中删除，这个数据块不能再插入数据。
3 优化器
10G中，使用CBO（基于代价的），不用RBO（基于规则的）
当没做表分析时，会动态采样分析统计的信息，只发生在第一次，后续的不再使用
动态分析，而使用第一次硬分析时生成的计划。
CBO两种运行模式
1）first_rows(n)
2)all_rows
first_rows(n):优先返回结果集中的前n条记录以最快速度反馈回来
all_rows:意味将用最快的速度将SQL执行完，将结果集全部返回。
先创建plan_table 表
SQL>@%oracle_home%/rdbms/admin/utlxplan.sql
授权
SQL>@%oracle_home%/sqlplus/admin/plustrce.sql
这样普通用户不能用
所以要授权普通用户对 plan_table 表
SQL>conn / as sysdba
SQL>create public synonym plan_table for plan_table;
SQL>grant all on plan_table to public;
开启查询计划
SQL>set autotrace on --显示执行计划和统计信息
SQL>set autotrace on explain --只显示执行计划
SQL>set autotrace on statistics --只显示统计信息
SQL>set autotrace traceonly --同autotrace on，但不显示查询结果
SQL>set autotrace off --关闭autotrace
select /*+ dynamic_sampling(t 0) */ * from t where ...
/*+ dynamic_sampling(t 0) */ 禁止表用动态采样查询
要注意的是，在对数据UPDATE或更新后，如果不对表进行分析，表中的分析
数据CBO的，还是旧的信息，要对表进行更新。
在用explain plan for ...SQL语句后
如果发现predicate information中：
filter:类别的，表示谓词的条件值不会影响数据访问路径，只起到筛选作用；
而
access:表示谓词的条件值会影响访问路径，比如是用索引还是全表扫描。
记得如果表分析过，但数据变了，而没重新分析，则会导致错误的执行计划；
4 HINT
如果在表中指定了别名，则hint中也要使用别名，否则CBO会忽略
select /*+ full(a) */ count(*) from t a;
系统级别设置优化器：
alter session(system) set optimizer_mode=all_rows;
代码中设定：
select /*+ first_rows(20) */ * from xxxxx;
select /*+ all_rows */ * from t where id<20;
1) full hint:指定全表扫描
select /*+ full(t) */ * from t;
2)index hint,对指定的表进行扫描
select /*+ index(t t_ind)*/ * from ......;
3）不使用索引
select /*+ no_index(t t_ind)*/ * from ....
4)index_desc hint:指定索引降序
5）使用位图索引
index_combine(t t_bm)
6) index_ss_hint:当在一个联合索引中的第一列的重复率很低时，可以使用index
skip scan,效率比全表扫描高；但如果很多重复，则要用全表扫描好
7）select /*+ leading(t1,t) */,指定在多表关联时，用哪个表作为驱动表。
8）select /*+ DRIVING_SITE(departments) */ * from
employees,departments@rsite where employees.xxx=departmes.yyy
如果远端机器的departmes查询结果很大，或者本地的employess查询结果很小，
并且两者关联的结果集很小，那么可以考虑把本地的数据发送到远端，在远端执行后，再
返回给本地，速度快点
5 分析及动态采样
dbms_stats对表的分析三个层次：
1）表自身的分析，包括表中的行数，数据块行数，行长等信息
2）列的分析 3）索引的分析
dbms_stats：性能数据收集，设置，性能数据的删除，性能数据的备份和恢复
3）exec dbms_stats.gather_table_name(user,'t');
user是个变量，用来返回当前用户的信息。
4）一些重要的参数：
Estimate_percent:用多大百分比的数据来分析
FOR ALL COLUMNS:对所有的列分析
degree:使用并行度分析
granularity:分析的粒度，
例子：
exec dbms_stats.gather_table_stats(user,'t',granularity=>'GLOBAL');
如果在表分区中，新增或修改的分区，只对分区分析，没对表分析，CBO执行计划也是
错的；
5）exec dbms_stats.set_table_stats(user,'t',numrows=>10000);
//将表的记录数修改为10000条，这样CBO可以看到。
6）动态采样：有11个级别，OLTP下不应该采用动态采样，而OLAP中，可以将动态采样
的级别
设置为3或者4
6 并行和执行
1） oracle创建并行执行协调进程，将要处理的数据集分块给不同的协调进程去
处理，最后处理完后，再将每个小的集合和并为大的集合，返回给用户。
在OLAP中，并行常用，OLTP中因为多用索引，所以很少用并行。
2）当启动的时候，实例化参数
parallel_min_servers=n个进程
有时，会启动2*N个进程进行服务，比如order by时，一部分N个进程扫描，N个排序
3）在10G中，px deq credit:send blkd等待事件已不在列为空闲等待事件
其意思为：当并行服务进程向并行协调进程发 送消息时，同一时间
只有一个并行服务进程可以向上层进程发送消息，如果有其他进程也要发送，则只能等待
在那里，默认为2秒。如果太多的并行服务进程，则会有等待出现。
4）并行查询
select /*+ parallel(t 4) */........
5) 创建表或查询时，都可以用并行查询，比如
create index t_ind on t(id) parallel 4
6)并行对DML的操作中，只有操作对象为分区表时，才启用操作
7）insert /*+ parallel(t 4) */ into t select /*+ parallel(t1 4) */ from t1;
parallel_min_percent:
为一个百分数，比如设置为50，当一个SQL需要申请20个并行服务进程时，如果当前
不满足，则额外申请20*50%=10个并行服务进程
9）并行度除了通过hint指定外，可以通过
alter table t parallel 4去设置
10）直接加载可以跟并行一起执行：
insert /*+ append parallel(t,2) */ into t select * from t1;
11)直接加载使用sql loader，可以绕开解析和解析到数据缓冲区
sqlldr userid=test/aaaa control=t.ctl direct=true log=log.txt
还可以直接加载时使用并行
sqlldr userid=test/aaaa control=t.ctl direct=true parallel=true
log=log.txt
如果sql loader的并行直接加载，而且表上有索引，将导致加载失败，
但可以通过设置选项 skip_index_maintenance=true来完成加载，
索引会变成unusable,需要手工rebuild
1 锁和阻塞
1) 当一个表有主键唯一时,一个会话在更新后,如果没COMMIT,另外一个会话也出更新
,则会发生死锁
2)查看:
select sid,type,id1,id2,lmode,request,block from v$lock where sid in
(xxx,yyy) order by sid;
其中block列=1表示这个会话正在阻塞另外一个会话；request中的值表示当前会话正
在等待另外一个lmode=XX
的锁
通过SID，可以查询出锁定的用户信息
select machine from v$session where sid in (xx,yyy0;
3)当一个表有若干分区或段的时候，每个都会加上TM表锁，比如一个表某个段加了TM
锁，则不能在其上进行DDL操作，但可以在其他分区段上进行。
4）可以通过select ...for update里阻挡另外的会话对其进行修改
5）主从表的关联查询情况，从表上的外键应该创建索引
2 LATCH和等待
当一个数据块正在被一个会话从磁盘中读到内存中时，如果另外一个会话也要读取
，则必须有机制来处理，
可以把latch理解为轻量级的锁，不会造成阻塞只会导致等待。
1）共享池中的latch争用
如果有大量的SQL被反复分析，就会造成大的latch争用
select * from v$latchname where name like 'library cache%'
2) 访问频率很高的数据块被称为热块，很多用户一起访问时，导致latch争用
当一个会话需要访问一个内存块，先要去象链表一样的结构去搜索这个数据快是否
在内存中；
当一个会话需要访问一个数据块，而这个数据块正在被另外一个用户从磁盘读取到内存中
或者正在被会话修改时，当前的会话需要等待，就产生buffer busy waits等待
A 表数据块
对于一些小表，如果频繁修改，则变成热块，可以考虑将表数据分布在更多的数据
块上，减少
数据块被多数会话同时访问的频率
alter table minimize records_per_block;
但会降低数据读取的性能
B 索引数据块
在RAC中，如果表的主键用序列递增，则多用户在RAC的不同实例中插入时，
会出现相同索引数据块在不同实例的内存中被调用，形成争用，可以使用反向索引；但会
降低性能，在范围查询时要走全表扫描
C 索引根数据块
热块可能发生在索引的根数据块中，可以把索引创建为分区索引时，索引的
根数据块会分布到更多的数据块中。
D 段头数据块
ASSM，让oracle自动管理"free lists",所谓的free lists,是每个数据段的段头
有几个free list列表，存放哪些数据块可以用，当数据块的数据达到一个比例
(pct_free)时，数据块就会从free list中删除，这个数据块不能再插入数据。
3 优化器
10G中，使用CBO（基于代价的），不用RBO（基于规则的）
当没做表分析时，会动态采样分析统计的信息，只发生在第一次，后续的不再使用
动态分析，而使用第一次硬分析时生成的计划。
CBO两种运行模式
1）first_rows(n)
2)all_rows
first_rows(n):优先返回结果集中的前n条记录以最快速度反馈回来
all_rows:意味将用最快的速度将SQL执行完，将结果集全部返回。
先创建plan_table 表
SQL>@%oracle_home%/rdbms/admin/utlxplan.sql
授权
SQL>@%oracle_home%/sqlplus/admin/plustrce.sql
这样普通用户不能用
所以要授权普通用户对 plan_table 表
SQL>conn / as sysdba
SQL>create public synonym plan_table for plan_table;
SQL>grant all on plan_table to public;
开启查询计划
SQL>set autotrace on --显示执行计划和统计信息
SQL>set autotrace on explain --只显示执行计划
SQL>set autotrace on statistics --只显示统计信息
SQL>set autotrace traceonly --同autotrace on，但不显示查询结果
SQL>set autotrace off --关闭autotrace
select /*+ dynamic_sampling(t 0) */ * from t where ...
/*+ dynamic_sampling(t 0) */ 禁止表用动态采样查询
要注意的是，在对数据UPDATE或更新后，如果不对表进行分析，表中的分析
数据CBO的，还是旧的信息，要对表进行更新。
在用explain plan for ...SQL语句后
如果发现predicate information中：
filter:类别的，表示谓词的条件值不会影响数据访问路径，只起到筛选作用；
而
access:表示谓词的条件值会影响访问路径，比如是用索引还是全表扫描。
记得如果表分析过，但数据变了，而没重新分析，则会导致错误的执行计划；
4 HINT
如果在表中指定了别名，则hint中也要使用别名，否则CBO会忽略
select /*+ full(a) */ count(*) from t a;
系统级别设置优化器：
alter session(system) set optimizer_mode=all_rows;
代码中设定：
select /*+ first_rows(20) */ * from xxxxx;
select /*+ all_rows */ * from t where id<20;
1) full hint:指定全表扫描
select /*+ full(t) */ * from t;
2)index hint,对指定的表进行扫描
select /*+ index(t t_ind)*/ * from ......;
3）不使用索引
select /*+ no_index(t t_ind)*/ * from ....
4)index_desc hint:指定索引降序
5）使用位图索引
index_combine(t t_bm)
6) index_ss_hint:当在一个联合索引中的第一列的重复率很低时，可以使用index
skip scan,效率比全表扫描高；但如果很多重复，则要用全表扫描好
7）select /*+ leading(t1,t) */,指定在多表关联时，用哪个表作为驱动表。
8）select /*+ DRIVING_SITE(departments) */ * from
employees,departments@rsite where employees.xxx=departmes.yyy
如果远端机器的departmes查询结果很大，或者本地的employess查询结果很小，
并且两者关联的结果集很小，那么可以考虑把本地的数据发送到远端，在远端执行后，再
返回给本地，速度快点
5 分析及动态采样
dbms_stats对表的分析三个层次：
1）表自身的分析，包括表中的行数，数据块行数，行长等信息
2）列的分析 3）索引的分析
dbms_stats：性能数据收集，设置，性能数据的删除，性能数据的备份和恢复
3）exec dbms_stats.gather_table_name(user,'t');
user是个变量，用来返回当前用户的信息。
4）一些重要的参数：
Estimate_percent:用多大百分比的数据来分析
FOR ALL COLUMNS:对所有的列分析
degree:使用并行度分析
granularity:分析的粒度，
例子：
exec dbms_stats.gather_table_stats(user,'t',granularity=>'GLOBAL');
如果在表分区中，新增或修改的分区，只对分区分析，没对表分析，CBO执行计划也是
错的；
5）exec dbms_stats.set_table_stats(user,'t',numrows=>10000);
//将表的记录数修改为10000条，这样CBO可以看到。
6）动态采样：有11个级别，OLTP下不应该采用动态采样，而OLAP中，可以将动态采样
的级别
设置为3或者4
6 并行和执行
1） oracle创建并行执行协调进程，将要处理的数据集分块给不同的协调进程去
处理，最后处理完后，再将每个小的集合和并为大的集合，返回给用户。
在OLAP中，并行常用，OLTP中因为多用索引，所以很少用并行。
2）当启动的时候，实例化参数
parallel_min_servers=n个进程
有时，会启动2*N个进程进行服务，比如order by时，一部分N个进程扫描，N个排序
3）在10G中，px deq credit:send blkd等待事件已不在列为空闲等待事件
其意思为：当并行服务进程向并行协调进程发 送消息时，同一时间
只有一个并行服务进程可以向上层进程发送消息，如果有其他进程也要发送，则只能等待
在那里，默认为2秒。如果太多的并行服务进程，则会有等待出现。
4）并行查询
select /*+ parallel(t 4) */........
5) 创建表或查询时，都可以用并行查询，比如
create index t_ind on t(id) parallel 4
6)并行对DML的操作中，只有操作对象为分区表时，才启用操作
7）insert /*+ parallel(t 4) */ into t select /*+ parallel(t1 4) */ from t1;
为一个百分数，比如设置为50，当一个SQL需要申请20个并行服务进程时，如果当前
不满足，则额外申请20*50%=10个并行服务进程
9）并行度除了通过hint指定外，可以通过
alter table t parallel 4去设置
10）直接加载可以跟并行一起执行：
insert /*+ append parallel(t,2) */ into t select * from t1;
11)直接加载使用sql loader，可以绕开解析和解析到数据缓冲区
sqlldr userid=test/aaaa control=t.ctl direct=true log=log.txt
还可以直接加载时使用并行
sqlldr userid=test/aaaa control=t.ctl direct=true parallel=true
log=log.txt
如果sql loader的并行直接加载，而且表上有索引，将导致加载失败，
但可以通过设置选项 skip_index_maintenance=true来完成加载，
索引会变成unusable,需要手工rebuild