Oracle-OCP学习笔记:Oracle体系结构1.进程结构图
对Oracle生产库来讲，服务器进程（可以简单理解是前台进程）的数量远远大于后台进程。因为一个用户进程对应了一个服务器进程。
而且后台进程一般出问题几率不大，所以学习重点也是服务器进程和PGA的关系（容易出问题）。
2.Oracle 实例管理
6大共享池，5大后台进程，3大文件
控制文件：记录了各文件存放的位置以及当前的运行状态；
数据文件：存放数据；
重做日志文件：对数据文件所有的修改记录；
补充知识点：
(1)实际生产环境中可以多个实例（一般是2个，4个，8个）对应一个数据库。
例如RAC技术，在日常情况下2个实例实现负载均衡（LB），在一个实例出故障的时候也能继续单实例运转。
(2)实际生产库环境，user=>应用服务器=>数据库服务器；
客户端输入SQL语句，SQL语句通过网络到达，数据库实例，server process接受SQL语句。
3.SQL语句执行过程剖析
(1)sql语句读取数据：
客户端输入SQL语句，SQL语句通过网络到达数据库实例，server process接收SQL语句:
1. 在oracle中，server process先拿着sql语句去shared pool中查找是否有缓存这条sql语句，如果有缓存，将直接执行缓存的sql语句，如果没有则将sql语句解析成执行计划，然后才能执行
2. 这时oracle拿着执行计划去实例当中执行，并返回结果
解析（parse）=>执行（execute）=>获取数据返回给客户端（fetch）
解析简单划分可以分为：硬解析和软解析。（实际还有软软解析）
硬解析过程中：会判断sql语法，查询的表是否存在，是否有权限，判断如何执行（挑出最优的执行计划作为执行计划，最费时间，耗费CPU，I/O资源,内存资源）；
软解析是在shared pool中library cache中找到了缓存的sql语句和执行计划，这样就不会再挑选执行计划，节约了大部分时间。
sql语句读取这块还引入了一个LI/O和 P I/O的概念：
L I/O 逻辑读（内存读）
P I/O 物理读（硬盘读）
命中率：=L/L+P
Database buffer cache：用来缓存dbf的数据块。如果用户查询的数据块没有在这里找到，会从数据文件中取数据先放在buffercache中，再返回给用户。(server process进程在操作)
有关缓存的地方都涉及一个命中率的概念，实际上，命中率低一定有问题，命中率高不一定没问题，还要关注此时系统每秒钟的物理读是多少。
#vmstat 1 10
#iostat 1 10
命中率高=10W/10W+1W 逻辑读很高，这时命中率很高，但物理读也很高,系统表现为比较慢
(2)sql语句修改数据：
Server process只是修改buffer cache中的数据，这样效率高。修改后将会产生日志 ，serverprocess写入redo log buffer中,后期LGWR将redo log buffer中的日志写入日志文件中。
注：server process不负责写，由backgroundprocess负责写（DBWn，LGWR）。这实际上是Oracle设计的一个小技巧，把用户不关心的事情交给后台进程来做，把跟用户关心的才交给 server process来做，后期优化也主要就针对server process进行优化。
Server process的反映速度会跟用户使用的体验有关系。如果server process反映比较慢，则用户体验会不好。
CKPT进程：检查点进程(checkpoint)，进程占用不高
CKPT周期性运行，将数据库当前的状态信息写到控制文件和数据文件的头部。更新数据文件和控制文件的头部。
SMON进程：系统监视器:对数据库实例进行维护，比如shared pool使用很长时间后对其中的数据碎片进行整理。负责对数据库实例内部进行清理和维护。(主内:对SGA内部进行维护)
PMON进程：进程监视器:主要周期性对server process进程进行监控，如果客户端断开连接，则会回收serverprocess进程资源以及PGA占用的资源（主外:对server process进行维护）
ARCn进程：归档日志：在日志将要被覆盖前，将日志复制到其它一个地方存起来。以后就查以查看历史的日志了。
缓冲区状态：
1. 已连接，表示同一瞬间，BUFFERCACHE 中的数据块被同时读同时写，这个状态叫PIN,由于内存速度非常快，所有叫做已连接,这个状态可防止多个会话同时对同一数据块执行写操作，此时其它会话正等待访问该块。
2. 干净：当serverprocess将数据块从数据文件读到BUFFER CACHE中时，这时这两个数据块数据一样，所有是干净的。如果SGA空间不够用了，优先使用未被使用的，如果没有未被使用的空间，将会使用干净的数据块，因为这时内存中的数据块和物理文件的数据块数据一样。
3. 空闲或未使用的：BUFFERCACHE中，还有空闲的空间未被使用。当要将其它的数据从数据文件读取到BUFFER CACHE中时，优先使用空闲的空单。
干净的和空闲或未使用的内存空间可以被重复使用。
如果干净的和空闲的空间都没有了，这时如果要从数据文件中读数据到内存中，这时将触发DBWR将脏的数据块写入到数据文件，来使脏块变干净。
4. 脏：数据文件中的数据与内存BUFFERCACHE中的数据一致，内存的数据叫脏数据。
5. shared pool、sql共享、绑定变量
shared pool的大部分问题是因为没有打oracle补丁
a、shared pool的组成
shared pool 的3块区域：free、library cache、row cache 只能配置shared pool的总的大小，不能配置row cache,library cache的大小。
Free,library cache,row cache内存结构都是以链(chain)来组织管理的。
free:空闲空间,(容易出问题)
library cache:库缓存空间作用：缓存SQL语句以及SQL语句对应的执行计划(容易出问题)
row cache:字典缓存：oracle自身的信息，包括表信息，库信息,状态信息。
1 2 3 4 | select * from v$sgastat a where a.name = 'library cache'; select * from v$sgastat a where a.pool = 'shared pool' and a.name = 'free memory'; select * from v$sgastat a where a.name = 'row cache'; 简述数据字典 |
|
解析（parse）[shared pool]=>执行（execute）=>获取数据返回给客户端（fetch） Shared pool 用于缓存sql语句以及sql语句对应的执行计划
|
|
|
|
|
b、硬解析
硬解析步骤、软解析步骤,软软解析，
硬解析过程：一个SQL语句进入oracle后，server process 会拿着sql语句到shared pool里的librarycache里找一下，这条语句是否在以前有被解析过。也就是有没有这个sql语句对应的执行计划，如果有则不需要解析，这叫软解析(soft parse)。如果在shared pool的library cache没有找到这个语句对应的执行计划，则会发生硬解析(hardparse)。
硬解析包含的步骤：(消耗资源多)
1. server process 会判断sql的语法有没有错误
2. 这个sql语句所要查找的对象是否存在
3. 执行这个sql语句的用户对表有没有执行权限(系统权限和对象权限)
4. 再生成具体的执行计划,最优的执行方案(最消耗资源)
软解析包含的步骤：（消耗资源少）
1. 要执行的sql语句所对应的对象的权限是否满足,常规判断
2. 软解析不会生成具体的执行计划.
3.
软硬解析的具体情况
SYS AS SYSDBA@ORCL>select name, value from v$sysstat where name like 'parse%'; NAME VALUE -------------------- ---------- parse time cpu 4838 #解析花费CPU的时间 parse time elapsed 10759 #解析花费的时间 parse count (total) 11003 #硬解析+软解析 parse count (hard) 1581 #硬解析 parse count (failure 3 #解析失败的时间 s) parse count (describ 0 e) |
|
讲解shared pool内存块组成结构
两个概念：chain、chunk
ora-4031错误
free空间的内存组成：
只有硬解析时，才需要在free空间中找trunk，软解析不需要
Free空间是一个一个小的内存块，使用链(chain)将一个一个的内存块连接起来。每个链上挂的内存块大小是不一样的。如果这时因为硬解析的原因，需要在shared pool的free空间中分配一个空闲的chunk给sql语句和sql语句对应的执行计划存放。放好后再将这个chunk再放到library cache中缓存起来。
硬解析时，会在free空间中产生小碎片空间。如果系统中有大量的硬解析，那么free空间中将有大量的小碎片空间。表面看起来free空间还有很多空闲空间，但实际是上因为太多的小空间，而导致空闲空间无法使用。
如果由于server process没有在free空间中找到空闲的块来使用，oracle就会报ora-4031错误。31
Ora-4031产生的原因是：
1. 大量的硬解析
2. 大量的硬解析产生了大量的小碎片空间
3. 这个突然来了一个占用空间比较大的大sql语句，就会报ora-4031错误
Oracle链(chain)的特点：
1. 将需要组织的内存块串起来
2. 可以遍历所有的内存块
Latch(锁)：
1. 用于保护链(chain)的机制
Library cache与free 对应关系：
1.server process在free中找到trunk空间后，将sql语句和执行计划放到trunk中，这个trunk存放的sql语句通过运算后(ascii)，会计算出要存放到library cache 中链的编号，通过这个编号将trunk挂到对应library cache的链上去。
2.当server process拿着同一条sql语句来library cache找是否有已存在的sql语句和执行计划时，先将sql语句进行ascii值计算，计算后将得到一个数值，这个数据会对应library cache中的一条链(chain)的值，这条sql语句通过hash后，将得到一个hash值，这个hash值将对应链上的一个位置，然后顺着这条链再去找sql语句和sql语句的执行计划。如果找到后就不会发生硬解析。如果没有找到，则会发生硬解析。
select count(*) from x$ksmsp; #查看shared pool里面chunk的总数量,shared pool里面每个chunk，都会在x$ksmsp里面的一行信息
SYS AS SYSDBA@ORCL>select count(*) fromx$ksmsp;
COUNT(*)
----------
43362
SYS AS SYSDBA@ORCL>select count(*) fromdba_indexes;
COUNT(*)
----------
4830 #新执行一个sql语句将发生硬解析
Elapsed: 00:00:01.79
SYS AS SYSDBA@ORCL>select count(*) fromx$ksmsp;
COUNT(*)
----------
43385 #发生硬解析之后，将会新增加新的trunk
Elapsed: 00:00:00.18
查看硬解析的情况：
select name, valuefrom v$sysstat where name like 'parse%';
SYS AS SYSDBA@ORCL>select name, value fromv$sysstat where name like 'parse%';
NAME VALUE
-------------------- ----------
parse time cpu 7901
parse time elapsed 14370
parse count (total) 20798
parse count (hard) 2122
parse count (failure 3
s)
parse count (describ 12
e)
select count(*) from dba_objects;
alter system flush shared_pool; #清空所有的library cache 和row cache,可以临时解析ora-4031错误,这个命令执行后会产生大量的硬解析.
SQL共享，绑定变量
要共享sql:sql语句要完全相同，只有相同的sql语句才能共享。
空格，数据不一样，回车，统统认为是不一样的sql语句。
所有需要统一书写风格
例子：
SYS AS SYSDBA@ORCL>select /*hello*/ count(*) from t1where READ_ONLY='NO';
COUNT(*)
----------
2853
Elapsed: 00:00:00.02
SYS AS SYSDBA@ORCL>select /*hello*/ count(*) from t1 where READ_ONLY='NO';
COUNT(*)
----------
2853
Elapsed: 00:00:00.02
SYS AS SYSDBA@ORCL>select /*hello*/ count(*) from t1 where READ_ONLY='YES';
COUNT(*)
----------
0
查看执行的后的情况：
SYS AS SYSDBA@ORCL>select sql_id,sql_text,executionsfrom v$sql where sql_text like '%hello%';
SQL_ID SQL_TEXT EXECUTIONS
----------------------------------------------------------------------------------------- ----------
5rkq6jp80x0h4 select/*hello*/ count(*) from t1 where READ_ONLY= 1
:"SYS_B_0"
5rkq6jp80x0h4 select/*hello*/ count(*) from t1 where READ_ONLY= 1
:"SYS_B_0"
7qhzzhjbq9gt9 select/*hello*/ count(*) from t1 whereREAD_ONLY 1
=:"SYS_B_0"
7qhzzhjbq9gt9 select/*hello*/ count(*) from t1 whereREAD_ONLY 1
=:"SYS_B_0"
SQL语句组成，动态部分、静态部分
动态部分：变量的值
静态部分由select ,insert,update等组成
cursor_sharing
SYS ASSYSDBA@ORCL>show parameter cursor;
NAME TYPE VALUE
cursor_sharing string SIMILAR
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 50
修改静态参数值：
Sql>altersystem set session_cached_cursors=150 scope=spfile
例子1：
绑定变量：
delare
v1 varchar2(10);
n1 int;
begin
n1:=1;
select salary into v1 from test where id=n1;
end;
select SQL_ID,sql_text,EXECUTIONS from v$sql where SQL_TEXT like
例子二：
使用绑定变量来共享SQL:
SYS AS SYSDBA@ORCL>createtable test(id int not null);
SYS AS SYSDBA@ORCL>declarev_sql varchar2(50);
2 begin for i in 1..1000 loop
3 v_sql := 'insert /*hello*/ intotest values (:1)';
4 execute immediate v_sql usingi;
5 end loop;
6 commit;
7 end;
8 /
查看sql语句执行情况：
SYS AS SYSDBA@ORCL>selectsql_text,sql_id,executions from v$sql where sql_text like '%hello%';
SQL_TEXT SQL_ID EXECUTIONS
----------------------------------------------------------------------------------------- ----------
insert /*hello*/ into test values(:1) 9j6auvgx0mbzh 1000
declare v_sql varchar2(50); beginfor i in 1..1000 4292v1sf1msrw 1
loop v_sql := 'insert /*hello*/ into testvalues
(:1)'; execute immediate v_sqlusing i; end loop;
commit; end
找出没有共享的SQL语句：
如何找出不能共享cursor的sql
在v$sql查找执行次数较小的sql 语句，观察这些sql语句是否是经常执行
Select sql_fulltext from v$sql where executions=1 and sql_text like “%from t%”
SYS AS SYSDBA@ORCL>select sql_fulltextfrom v$sql where executions=1 and sql_text like '%from t%'
2 ;
SQL_FULLTEXT
--------------------------------------------------------------------------------
select actionsize from trigger$ where obj#= :1
select * from test
select action# from trigger$ where obj# =:1
select count(*) from test
select externtype, externname from type$where tvoid=:1
Select sql_fulltext from v$sql where executions=1 order by sql_text;
SYS AS SYSDBA@ORCL>spool 1.lst
SYS AS SYSDBA@ORCL>Select sql_fulltextfrom v$sql where executions=1 order bysql_text;
SYS AS SYSDBA@ORCL>spool end
SQL_FULLTEXT
--------------------------------------------------------------------------------
select u.name, o.name, t.attributes from sys.type$ t, sys.user$ u, sys.obj$
o
CALLmgmt_admin_data.eval_repo_performance(:target_guid,:metric_guid,:coll_nam
e,
DECLARE job BINARY_INTEGER := :job;next_date DATE := :mydate; brokenBOOLEAN
:
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWSIGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLES
解析命中率：需要数据库跑了一段时间后，一般在99%左右
反映软解析命中的次数：librarycache
SYSAS SYSDBA@ORCL>select sum(pinhits)/sum(pins)*100 from v$librarycache;
SUM(PINHITS)/SUM(PINS)*100
--------------------------
87.4384737
反映软解析命中的次数：rowcache
SYSAS SYSDBA@ORCL>selectsum(gets),sum(getmisses),100*sum(gets-getmisses)/sum(gets) from v$rowcachewhere gets>0;
SUM(GETS) SUM(GETMISSES)100*SUM(GETS-GETMISSES)/SUM(GETS)
------------------------ ---------------------------------
87406 10465 88.0271377
如何解决4031错误：
1. alter system flush shared pool;
2. 共享sql
3. 修改系统参数:
SYSAS SYSDBA@ORCL>show parameter cursor;
NAME TYPE VALUE
-------------------------------------------------------- ------------------------------
cursor_sharing string SIMILAR
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 50
SYSAS SYSDBA@ORCL>alter system set cursor_sharing='force';
Systemaltered.
Elapsed:00:00:00.08
SYSAS SYSDBA@ORCL>show parameter cursor;
NAME TYPE VALUE
-------------------------------------------------------- ------------------------------
cursor_sharing string force
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 50
将alter system setcursor_sharing='force';后静态相同，变量不相同的部分，oracle会强制使用绑定变量 ,可以解决字面值没有绑定变量的问题。
强行缓存sql语句:
DBMS_SHARED_POOL
1>@?/rdbms/admin/dbmspool.sql #产生包
2> select * from v$db_object_cachewhere sharable_mem>10000 and (type=’PACKAGE’or type=’PACKAGE BODY’ or type=’FUNCTION’ or type=’PROCEDURE’) and kept=’NO’;
3>执行dbms_shared_pool.keep(‘对象名’) #强行缓存sql语句
4. 保留区
Select REQUEST_MISSES from v$shared_pool_reserved;
作用：只用来缓存大对象,所以如果增大保留区的大小，将大量减少4031错误的发生
上面的语句的意思是，在保留区中查找miss数据块的次数，如果在保留区都没有找到空闲的空间，那就一次会产生4031错误
这个返回值最好是0，表示没有发生4031错误：
SYS AS SYSDBA@ORCL>Select REQUEST_MISSES fromv$shared_pool_reserved;
REQUEST_MISSES
--------------
0
查看保留区的大小：
SYS AS SYSDBA@ORCL>show parameter share
NAME TYPE VALUE
-------------------------------------------------------- ------------------------------
hi_shared_memory_address integer 0
max_shared_servers integer
shared_memory_address integer 0
shared_pool_reserved_size big integer 10M #保留区的大小
shared_pool_size big integer 4M
shared_server_sessions integer
shared_servers integer 1
如果查询保留区的miss的值来为0，则要增加shared_pool_reserved_size的大小，从而减少4031错误.
5. 增加shared pool空间
Select component,current_size from v$sga_dynamic_components; #查看已分配的空间大小
Show parameter sga_target #sga总的大小
Show parameter sga_max_size #max是用来约束sga_target,是静态参数，要重启数据库才会生效
Alter system set shared_pool_size=150M scope=both; #修改参数
例子：
SYS AS SYSDBA@ORCL>show parameter shared_pool;
NAME TYPE VALUE
-------------------------------------------------------- ------------------------------
shared_pool_reserved_size big integer 10M
shared_pool_size big integer 200M
SYS AS SYSDBA@ORCL>alter system setshared_pool_size=204800;
SYS AS SYSDBA@ORCL>show parameter shared_pool;
NAME TYPE VALUE
-------------------------------------------------------- ------------------------------
shared_pool_reserved_size big integer 10M
shared_pool_size big integer 4M
配置shared_pool_size时，分配的大小要大于sga_target自动分派的大小，否则保持原大小不变；
6. 查看执行计划：
SYS AS SYSDBA@ORCL>select * fromtable(dbms_xplan.display_cursor('9j6auvgx0mbzh'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9j6auvgx0mbzh,child number 0
-------------------------------------
insert /*hello*/ into test values (:1)
-------------------------------------------------
| Id |Operation | Name | Cost |
-------------------------------------------------
| 0 | INSERTSTATEMENT | | 1 |
| 1 | LOAD TABLE CONVENTIONAL | | |
-------------------------------------------------
Note
-----
- cpu costingis off (consider enabling it)
16 rows selected.
