Oracle体系结构

终于迎来了Oracle最重要的一章。当然，大多数研发可能都不太会关心Oracle的体系结构，因为只需要会sql语句就OK了。不过，个人觉得，简单的了解仍然是有必要的。

---

Oracle体系结构概览

数据库服务器包含两个部分：

1. oracle实例
   SGA内存区域与后台进程

2. 数据库
   控制、数据、日志文件

   
   oracle体系结构

Oracle主要体系为：三大文件，五大进程，六大池（streams pool目前很少使用）

体系结构

1. 三大文件：ctl(控制文件),log(日志文件),dbf(数据文件)
2. 五大进程：PMON,SMON,DBWR,LGWR,CKPT
3. 六大池：shared pool,buffer cache,redo log buffer,large pool,java pool,streams pool
   sga

shared pool

• shared pool作用
  用于缓存SQL语句以及SQL语句中涉及的表定义(数据列以及数据类型)
  查看共享池
  select pool,sum(bytes/1024/1024)from v$sgastat t where t.POOL is not null group by t.POOL;
  当执行一个sql语句，就会被记录，在生产平台上，很多sql语句会被很多人重复执行，所以，缓存了的sql语句，会被更快的执行
  select count(*) from dba_objects;
  select sql_text from v$sqlarea where sql_text like '%dba_objects%'; //查看缓存的sql
  alter system flush shared_pool; //刷新（清空）share pool,不建议使用
  show parameter shared_pool_size; //清空之后没有了
• shared pool内存结构
  Shared pool由三块区域组成
  Free：
  select * from v$sgastat where pool='shared pool' and name='free memory';
  Library cache:
  select * from v$sgastat where name='library cache';
  Row cache:
  select * from V$sgastat where name='row cache';
  Free空间：不同大小的空闲块挂在链(chain)上，同样链上内存块空间(chunk)大小相同；从上往下不断增大；写入后挂在library cache上，产生碎片往上层链上挂载。
  Library cache: sql语句——ASCI——二次运算——数字——chain编号
  Row cache：缓存数据字典信息
  select count(*) from x$ksmsp;
  select name,value from v$sysstat where name like 'parse%';

buffer cache

• Database buffer cache（作用：缓存数据）
  Buffer cache存储从数据文件检索出来的数据块拷贝,提升数据检索速度，降低IO压力；构造Cr块。
  小实验
  create table aa as select * from dba_objects;
  alter system flush shared_pool;
  alter system flush buffer_cache;
  set autotrace traceonly;
  select count(*) from aa;

  
  执行计划

    再次执行
    set autotrace traceonly;
    select count(*) from aa;
  

  逻辑读

    有神马区别吗？我们主要看这两个指标
    consistent gets(逻辑读 内存)
    physical reads(物理读 硬盘)
    比较两次的差异，是不是马上明白了buffer cache的作用
  

• buffer cache内存组织结构：双项链
  CBC链：以地址方式链接；将block头部信息挂载到链上。根据block地址找block是需用到CBC chain.
  LRU链：least recently used(最近最少使用)，将磁盘block调入buffer cache时用到
  LRUW链：DBWR用
  ckpt pueue:检查点队列；按块第一次被脏的时间点连接起来；

  
  chain

• Buffer cache的类型

  • default buffer cache（默认使用，你的读和写缓存全在这保存）
    普通对象的缓冲池，那些没有在keep pool也没有在recycle pool的对象将缓冲到这里。
  • keep buffer cache（要手工单独配置，默认值是0）
    对于经常访问的“小表”将其常驻内存，即放置到keep pool
  • recycle buffer cache（要手工单独配置，默认值是0）
    对于不经常访问的大segment，就可以考虑将其放置到recycle pool，以尽快将其淘汰出去（比default buffer cache区域内的数据优先被覆盖）。
    小测试
    show parameter size;
    alter system set db_keep_cache_size=10M;（为keep buffer cache区域分配空间）
    alter system set db_keep_cache_size=10M;
    create table t1(id int);
    insert into t1 values(123);
    commit;
    alter table t1 storage (buffer_pool keep);
    select table_name,cache,buffer_pool from user_TABLES where table_name='T1';
    alter system set db_recycle_cache_size=10M;
    alter table t1 storage (buffer_pool recycle);

• data buffer中 数据块 4种状态

  • pined:多个进程想写这个块,但只有1个进程能获得锁
  • clear:buffer中的数据和磁盘上的数据时一致的，这些块将优先被淘汰
  • free/unused:没有被使用过，是空白内容
  • dirty:不被某个进程使用，但buffer的内容和磁盘内容不一致

• buffer cache调优

  • 降低SQL命令对数据块的请求，如避免使用select * from 全表扫描语句
  • 增加缓冲池的大小
  • 不同访问方式使用不同的缓冲池(buffer pools)
  • 缓存常用的表到内存(keep区域)
  • 并行读或排序操作不使用cache,直接从磁盘读入到PGA及larage pool
    select name,value from v$sysstat where name in
    ('session logical reads', //所有的逻辑读的数据块的数量
    'physical reads',
    'physical reads direct',
    'physical reads direct(lob)',
    'consistent gets', //在回滚段Buffer中的数据构造一致性读数据块的总次数
    'db block gets', //在操作中提取的块数目
    'free buffer inspected', //为寻找空闲buffer之前所检查块的总数量，即跳过块的数量。如果该值接近脏数据块的数量，则表明空闲块很少，该值应尽可能小于脏块的数量
    'free buffer requested', //空闲的Buffer请求
    'dirty buffers inspected', //检测脏数据块，当值为0时，当前比较轻闲，没有什么操作
    'pinned buffers inspected'); //检测正在使用的数据块，当值为0时，当前比较轻闲，没有什么操作

• buffer cache命中率
  SELECT ROUND(1 - ((physical.value - direct.value - lobs.value) / logical.value),3) *100||'%'
  "Buffer Cache Hit Ratio"
  FROM v$sysstat physical,
  v$sysstat direct,
  v$sysstat lobs,
  v$sysstat logical
  WHERE physical.name = 'physical reads'
  AND direct.name = 'physical reads direct'
  AND lobs.name = 'physical reads direct (lob)'
  AND logical.name = 'session logical reads';

• 获得buffer pool中的相关信息
  视图v$bh(基于视图x$bh)显示当前位于SGA中所有块的详细信息。
  查询buffer cache中不同对象占住块的个数(可以根据查询将不经常访问的大对象置于到recycle pool)
  SELECT o.owner, object_name, object_type, COUNT(1) buffers
  FROM SYS.x$bh, dba_objects o
  WHERE (tch = 1 OR (tch = 0 AND lru_flag < 8))
  AND obj = o.object_id
  AND o.owner NOT IN ('SYSTEM', 'SYS')
  GROUP BY o.owner, object_name, object_type
  ORDER BY buffers;
  查询获得到经常访问的对象，可以将其放到keep pool中
  SELECT o.owner, object_name, object_type, COUNT(1) buffers
  FROM SYS.x$bh, dba_objects o
  WHERE tch > 2
  AND obj = o.object_id
  AND o.owner NOT IN ('SYSTEM', 'SYS')
  GROUP BY o.owner, object_name, object_type
  ORDER BY buffers;
  获得所有不同的buffer pool 当前分配块的总个数
  SELECT NAME, block_size, SUM(buffers)
  FROM v$buffer_pool
  GROUP BY NAME, block_size
  HAVING SUM(buffers) > 0;

其它池

• redo log buffer cache
  先写内存中，速度快，它认为内存中写完，此次操作就完了，当库空闲的时候再把内存的数据写到磁盘的redo日志文件中。
  主流数据库，都是先写日志，再写数据库。日志比较重要，日志健全，数据没了，可以通过日志进行恢复。
• large pool
  存储大数据(一次提取大的数据，存储在大池比较好，存储Buffer cache就不好了，容易造成命中假像)
  large pool存放：1、RMAN的备份数据 2、并行操作
• java pool
  调用前端的java指令

sql相关

• sql执行过程

  1. 检查语法
  2. 检查语义
  3. hash计算
  4. 解析：
     软解析(当一条sq1语句执行过，就会保存在library cache中，当再一次执行与此条sql相同的语句时，不经过cpu计算，直接调用share pool)
     硬解析(当软解析不成功进，经过cpu计算)，生成多种执行方案，选择最佳执行方案（选择方案占用大部分资源），产生物理IO，调用磁盘数据或空间。
  5. 执行
     select name from aa where id=1（先计算条件，再对比表，减少查找范围，触发索引就是先看where） 从右往左
  6. 查看执行计划：
     select sql_id,sql_text from v$sql where sql_text like '% select';
     select * from table(dbms_xplan.display_cursor('sql_id'));

• ORA-4031错误

  1. 产生原因
     大量硬解析
     大量硬解析产生大量碎片，突然又来大的SQL语句。
  2. 解析命中率
     select sum(pinhits)/sum(pins) 100 from v$librarycache;
     select sum(gets),sum(getmisses),100sum(gets-getmisses)/sum(gets) FROM v$rowcache WHERE gets > 0;
  3. 解决方法
     标准化，书写规范
     使用绑定变量
  4. 增加shared pool空间

• shared pool和buffer cache大小设置及依据
  alter system set shared_pool_size=64m scope=both;（此参数可以直接修改到内存和参数文件,不需要重启）
  显示SGA的所有设置值:
  select pool,sum(bytes/1024/1024)from v$sgastat t where t.POOL is not null group by t.POOL
  union
  select name as pool,sum(bytes/1024/1024) from v$sgastat s where s.POOL is null group by s.name;

    show parameter sga; 
  

  SGA是动态的并且最大值由SGA_MAX_SIZE参数控制(sga中划分的所有池，相加总和不可以超过sga_target。如果想把SGA中划分的各个池调大，由于SGA自动管理，SGA变大，这些划分的各个池也会变大）
  SGA大小受2个参数影响：sga_target、sga_max_size
  在oracle运行过程当中修改SGA_TARGET值不能大于SGA_MAX_SIZE，sga_target和sga_max_size大小一致最好，就不会出问题了
  建议值
  select shared_pool_size_for_estimate "SP",
  estd_lc_size "EL",
  estd_lc_memory_objects "ELM",
  estd_lc_time_saved "ELT",
  estd_lc_time_saved_factor "ELTS",
  estd_lc_memory_object_hits as "ELMO"
  FROM v$shared_pool_advice;

PGA进程

• DBWR(数据写进程)
• LGWR(日志写进程)
• SMON(系统监控进程)
• PMON(进程监控进程)
• CKPT(检查点进程)