<<让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