Oracle体系结构
终于迎来了Oracle最重要的一章。当然，大多数研发可能都不太会关心Oracle的体系结构，因为只需要会sql语句就OK了。不过，个人觉得，简单的了解仍然是有必要的。
Oracle体系结构概览
数据库服务器包含两个部分：
- oracle实例
SGA内存区域与后台进程 -
数据库
控制、数据、日志文件
oracle体系结构
Oracle主要体系为：三大文件，五大进程，六大池（streams pool目前很少使用）
- 三大文件：ctl(控制文件),log(日志文件),dbf(数据文件)
- 五大进程：PMON,SMON,DBWR,LGWR,CKPT
- 六大池：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);
- default buffer cache（默认使用，你的读和写缓存全在这保存）
-
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执行过程
- 检查语法
- 检查语义
- hash计算
- 解析：
软解析(当一条sq1语句执行过，就会保存在library cache中，当再一次执行与此条sql相同的语句时，不经过cpu计算，直接调用share pool)
硬解析(当软解析不成功进，经过cpu计算)，生成多种执行方案，选择最佳执行方案（选择方案占用大部分资源），产生物理IO，调用磁盘数据或空间。 - 执行
select name from aa where id=1（先计算条件，再对比表，减少查找范围，触发索引就是先看where） 从右往左 - 查看执行计划：
select sql_id,sql_text from v$sql where sql_text like '% select';
select * from table(dbms_xplan.display_cursor('sql_id'));
-
ORA-4031错误
- 产生原因
大量硬解析
大量硬解析产生大量碎片，突然又来大的SQL语句。 - 解析命中率
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; - 解决方法
标准化，书写规范
使用绑定变量 - 增加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(检查点进程)
