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内存块组成结构

只有硬解析时，才需要在 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.