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 poolfree空间中分配一个空闲的chunksql语句和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语句:

       如何找出不能共享cursorsql

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.

 

 


分割线
感谢打赏
江西数库信息技术有限公司
YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS