ORACLE 体系机构简介

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:33

1、oracle 网络架构及应用环境
2、oracle 体系结构
1)oracle server :database +instance 2)database:data file 、control file 、 redolog file 3)instance(实例):access a database ---oracle memory: sga + pga 4)instance : sga +backgroud process 5)sga组成:sga 在一个instance只有一个sga,sga所有的session共享;随着instance启动而分配,instancedown ,sga被释放。
----查看OS分配给oracle的内存
[font=Consolas][oracle@work oradata]$ ipcs
[font=Consolas]------ SharedMemory Segments --------[font=Consolas]key shmid owner perms bytes nattch status [font=Consolas]0x995eb2e865537 oracle 640 379584512 18
[font=Consolas]------ SemaphoreArrays --------[font=Consolas]key semid owner perms nsems [font=Consolas]0xbe3edae098304 oracle 640 44
[font=Consolas]------ MessageQueues --------[font=Consolas]key msqid owner perms used-bytes messages [font=Consolas][oracle@workoradata]$ ipcs -m
[font=Consolas]------ SharedMemory Segments --------[font=Consolas]key shmid owner perms bytes nattch status [font=Consolas]0x995eb2e865537 oracle 640 379584512 18

[font=Consolas]---------Solaris
[font=Consolas][oracle@sun ~]$ipcs -a[font=Consolas]IPC status from as of Wednesday, May 9, 2012 10:27:20 AM CST[font=Consolas]T ID KEY MODE OWNER GROUP CREATOR CGROUP CBYTES QNUM QBYTES LSPID LRPID STIME RTIME CTIME [font=Consolas]Message Queues:[font=Consolas]T ID KEY MODE OWNER GROUP CREATOR CGROUP NATTCH SEGSZ CPID LPID ATIME DTIME CTIME [font=Consolas]Shared Memory:[font=Consolas]m 1 0xb8bd04d0 --rw-r----- oracleoinstall oracle oinstall 17 289411072 958 1086 10:26:08 10:27:13 9:43:18[font=Consolas]T ID KEY MODE OWNER GROUP CREATOR CGROUP NSEMS OTIME CTIME [font=Consolas]Semaphores:[font=Consolas]s 3 0xb694b4e4 --ra-r----- oracleoinstall oracle oinstall 154 10:27:14 9:43:20

------启动实例会自动分配内存空间
[font=Consolas]10:29:07 SQL> startup[font=Consolas]ORACLE instancestarted.[font=Consolas]Total System GlobalArea 289406976 bytes[font=Consolas]Fixed Size 1279820 bytes[font=Consolas]Variable Size 113248436 bytes[font=Consolas]DatabaseBuffers 171966464 bytes[font=Consolas]Redo Buffers 2912256 bytes[font=Consolas]Database mounted.[font=Consolas]Database opened.[font=Consolas]10:30:04 SQL> show parametersga;[font=Consolas]NAME TYPE VALUE[font=Consolas]----------------------------------------------- ------------------------------[font=Consolas]lock_sga boolean FALSE[font=Consolas]pre_page_sga boolean FALSE[font=Consolas]sga_max_size big integer 276M[font=Consolas]sga_target big integer 276M

内存自动管理:(ASSM)
sharepool、databuffer、log buffer、java pool、large pool[font=Consolas]
sga_max_size
sga_target: share pool 、data buffer、large pool、java pool
granules(内存颗粒):组成oracle内存的最小逻辑单位
sga_max_size<1g ,4m sga_max_size>1g ,16m

-------查看内存颗粒
[font=Consolas]20:12:30 SQL> select name,bytes/1024/1024 "Size(M)" from v$sgainfo; [font=Consolas]NAME Size(M)[font=Consolas]------------------------------------------[font=Consolas]Fixed SGA Size 1.16238022[font=Consolas]Redo Buffers 2.8359375[font=Consolas]Buffer CacheSize 80[font=Consolas]Shared PoolSize 60[font=Consolas]Large PoolSize 0[font=Consolas]Java Pool Size 24[font=Consolas]Streams PoolSize 0[font=Consolas]Granule Size 4[font=Consolas]Maximum SGASize 244[font=Consolas]Startup overhead inShared Pool 28[font=Consolas]Free SGA MemoryAvailable 76[font=Consolas]11 rows selected.
[font=Consolas]05:33:36 SYS # Tony >select *from v$sga;
[font=Consolas]NAME VALUE[font=Consolas]------------------------------[font=Consolas]Fixed Size 1274668[font=Consolas]Variable Size 515902676[font=Consolas]DatabaseBuffers 4194304[font=Consolas]Redo Buffers 2916352

6)share pool :
sql 语句的执行过程: [color=#0070C0][font=Consolas]1,parse[color=#0070C0][font=Consolas]2,bind data[color=#0070C0][font=Consolas]3,execute[color=#0070C0][font=Consolas]4,fetch
[color=#0070C0][font=Consolas]Parse[color=#0070C0]a, 检索共享池,寻找对应的执行计划,如果找到,跳到第二部分开始执行。 软解析[color=#0070C0]b,检查sql的语法[color=#0070C0]c, 检查sql对象,权限,锁情况[color=#0070C0]d, 参照统计信息,生产执行计划,存放在共享池里 硬解析
-----查询sql解析(绑定变量)[font=Consolas]11:22:07 SQL> var num number;[font=Consolas]11:22:16 SQL> exec :num :=10;[font=Consolas]PL/SQL procedure successfullycompleted.[font=Consolas]Elapsed:00:00:00.00
[font=Consolas]11:22:24 SQL>select * from emp where deptno=:num;[font=Consolas] EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO[font=Consolas]-------------------- --------- ---------- --------- ---------- ---------- ----------[font=Consolas] 7782 CLARK MANAGER 7839 09-JUN-81 2450 10[font=Consolas] 7839 KING PRESIDENT 17-NOV-81 5000 10[font=Consolas] 7934 MILLER CLERK 7782 23-JAN-82 1300 10[font=Consolas]Elapsed:00:00:00.08
[font=Consolas]11:22:35 SQL> exec :num :=20;[font=Consolas]PL/SQL proceduresuccessfully completed.[font=Consolas]Elapsed:00:00:00.00

[font=Consolas]11:22:47 SQL> select * fromemp where deptno=:num;[font=Consolas] EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO[font=Consolas]-------------------- --------- ---------- --------- ---------- ---------- ----------[font=Consolas] 7369 SMITH CLERK 7902 17-DEC-80 800 20[font=Consolas] 7566 JONES MANAGER 7839 02-APR-81 2975 20[font=Consolas] 7788 SCOTT ANALYST 7566 19-APR-87 3000 20[font=Consolas] 7876 ADAMS CLERK 7788 23-MAY-87 1100 20[font=Consolas] 7902 FORD ANALYST 7566 03-DEC-81 3000 20[font=Consolas]Elapsed:00:00:00.00

[font=Consolas]11:22:51 SQL> exec :num :=30;[font=Consolas]PL/SQL procedure successfullycompleted.[font=Consolas]Elapsed: 00:00:00.00[font=Consolas]11:22:54 SQL> select * fromemp where deptno=:num;[font=Consolas] EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO[font=Consolas]-------------------- --------- ---------- --------- ---------- ---------- ----------[font=Consolas] 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30[font=Consolas] 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30[font=Consolas] 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30[font=Consolas] 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30[font=Consolas] 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30[font=Consolas] 7900 JAMES CLERK 7698 03-DEC-81 950 30[font=Consolas]6 rows selected.[font=Consolas]Elapsed:00:00:00.00

[font=Consolas]11:22:57 SQL>conn /as sysdba[font=Consolas]Connected.

[font=Consolas]11:23:04 SQL> selectsql_text,parse_calls from v$sql[font=Consolas]11:23:37 2 where upper(sql_text) like 'SELECT * FROM EMP% ';[font=Consolas]no rows selected[font=Consolas]Elapsed:00:00:00.06

[font=Consolas]11:24:10 SQL> selectsql_text,parse_calls from v$sql[font=Consolas]11:24:19 2 where upper(sql_text) like 'SELECT * FROM EMP% WHERE DEPTNO=:NUM';[font=Consolas]SQL_TEXT PARSE_CALLS[font=Consolas]------------------------- ---------------[font=Consolas]select* from emp where deptno=:num 3[font=Consolas]Elapsed:00:00:00.01

[font=Consolas]11:24:33 SQL>col sql_text for a50[font=Consolas]11:24:50 SQL> /[font=Consolas]SQL_TEXT PARSE_CALLS[font=Consolas]-------------------------------------------------------------[font=Consolas]select * from empwhere deptno=:num 3[font=Consolas]Elapsed:00:00:00.01--select* from emp where deptno=:num ,语句被共享了三次,解析一次,执行了3次

[font=Consolas]SELECTshared_pool_size_for_estimate "SP", estd_lc_size"EL",estd_lc_memory_objects "ELM",[font=Consolas]estd_lc_time_saved"ELT", estd_lc_time_saved_factor as"ELTS",estd_lc_memory_object_hits as "ELMO" [font=Consolas]from v$shared_pool_advice;
[font=Consolas] SP EL ELM ELT ELTS ELMO[font=Consolas] -------------------- ---------- ---------- ---------- ----------[font=Consolas] 60 17 2465 514 1 35837[font=Consolas] 76 32 3921 514 1 36029[font=Consolas] 92 47 5292 514 1 36108[font=Consolas] 108 49 5718 514 1 36108[font=Consolas] 124 49 5718 514 1 36108[font=Consolas] 140 49 5718 514 1 36108[font=Consolas] 156 49 5718 514 1 36108[font=Consolas] 172 49 5718 514 1 36108[font=Consolas] 188 49 5718 514 1 36108[font=Consolas] 204 49 5718 514 1 36108[font=Consolas] 220 49 5718 514 1 36108[font=Consolas] 236 49 5718 514 1 36108[font=Consolas] 252 49 5718 514 1 36108[font=Consolas] 268 49 5718 514 1 36108[font=Consolas] 284 49 5718 514 1 36108
[font=Consolas] 15rows selected.

第一列表示Oracle所估计的shared pool的尺寸值,其他列表示在该估计的shared pool大小下所表现出来的指标值,具体含义可以参见Oracle的联机帮助。我们主要关注estd_lc_time_saved_factor列的值,当该列值为1时,表示再增加shared pool的大小对性能的提高没有意义。

7)sga_target:sga内存分配自动管理(ASMM)
sga_target=0 关闭ASMM >0 启动内存自动管理(可以对share pool、data buffer、large pool、java pool 可以实现自动管理)
[font=Consolas]sga_target<=sga_max_size ----内存自动管理 [font=Consolas]11:29:55 SQL> select * fromv$version;[font=Consolas]BANNER[font=Consolas]----------------------------------------------------------------[font=Consolas]Oracle Database 10gEnterprise Edition Release 10.2.0.2.0 - Prod[font=Consolas]PL/SQL Release10.2.0.2.0 - Production[font=Consolas]CORE 10.2.0.2.0 Production[font=Consolas]TNS for Solaris:Version 10.2.0.2.0 - Production[font=Consolas]NLSRTL Version10.2.0.2.0 - Production[font=Consolas]Elapsed:00:00:00.00

[font=Consolas]11:30:36 SQL> show parametersga[font=Consolas]NAME TYPE VALUE[font=Consolas]----------------------------------------------- ------------------------------[font=Consolas]lock_sga boolean FALSE[font=Consolas]pre_page_sga boolean FALSE[font=Consolas]sga_max_size big integer 304M[font=Consolas]sga_target big integer 276M[font=Consolas]11:31:06 SQL>alter system set sga_target =304m;[font=Consolas]System altered.[font=Consolas]Elapsed:00:00:00.17

[font=Consolas]11:35:17 SQL> show parametersga[font=Consolas]NAME TYPE VALUE[font=Consolas]----------------------------------------------- ------------------------------[font=Consolas]lock_sga boolean FALSE[font=Consolas]pre_page_sga boolean FALSE[font=Consolas]sga_max_size big integer 304M[font=Consolas]sga_target big integer 304M

[font=Consolas]11:35:19 SQL> show parametershare[font=Consolas]NAME TYPE VALUE[font=Consolas]----------------------------------------------- ------------------------------[font=Consolas]hi_shared_memory_address integer 0[font=Consolas]max_shared_servers integer[font=Consolas]shared_memory_address integer 0[font=Consolas]shared_pool_reserved_size big integer 5M[font=Consolas]shared_pool_size big integer 0[font=Consolas]shared_server_sessions integer[font=Consolas]shared_servers integer 1

[font=Consolas]11:35:45 SQL> selectname,bytes/1024/1024 from v$sgainfo;[font=Consolas]NAME BYTES/1024/1024[font=Consolas]-----------------------------------------------[font=Consolas]Fixed SGA Size 1.22071457[font=Consolas]Redo Buffers 2.77734375[font=Consolas]Buffer CacheSize 192[font=Consolas]Shared PoolSize 100[font=Consolas]Large PoolSize 4[font=Consolas]Java Pool Size 4[font=Consolas]Streams PoolSize 0[font=Consolas]Granule Size 4[font=Consolas]Maximum SGASize 304[font=Consolas]Startup overhead inShared Pool 36[font=Consolas]Free SGA MemoryAvailable 0[font=Consolas]11 rows selected.[font=Consolas]Elapsed:00:00:00.05

[font=Consolas]11:36:07 SQL> alter systemset shared_pool_size=50m;[font=Consolas]System altered.[font=Consolas]Elapsed:00:00:00.03

[font=Consolas]11:36:35 SQL> selectname,bytes/1024/1024 from v$sgainfo;[font=Consolas]NAME BYTES/1024/1024[font=Consolas]-----------------------------------------------[font=Consolas]Fixed SGA Size 1.22071457[font=Consolas]Redo Buffers 2.77734375[font=Consolas]Buffer CacheSize 192[font=Consolas]Shared PoolSize 100[font=Consolas]Large PoolSize 4[font=Consolas]Java Pool Size 4[font=Consolas]Streams PoolSize 0[font=Consolas]Granule Size 4[font=Consolas]Maximum SGASize 304[font=Consolas]Startup overhead inShared Pool 36[font=Consolas]Free SGA MemoryAvailable 0[font=Consolas]11 rows selected.[font=Consolas]Elapsed:00:00:00.00

[font=Consolas]11:36:39 SQL> show parametershare[font=Consolas]NAME TYPE VALUE[font=Consolas]----------------------------------------------- ------------------------------[font=Consolas]hi_shared_memory_address integer 0[font=Consolas]max_shared_servers integer[font=Consolas]shared_memory_address integer 0[font=Consolas]shared_pool_reserved_size big integer 5M[font=Consolas]shared_pool_size big integer 52M[font=Consolas]shared_server_sessions integer[font=Consolas]shared_servers integer 1[font=Consolas]11:36:42 SQL> ---sharepool 通过内存自动管理分配的空间为100m ,shared_pool_size 的大小是分配给share pool的最小值

8) data buffer:存放从datafile 里读出的数据块的镜像。

[font=Consolas]Oracle 从磁盘读取数据是以块为单位,将读出的块放入到databuffer 中
[font=Consolas]db_block_size 指定了oracle 的标准块的大小,默认10g 为8k
[font=Consolas]db_cache_size 指定default cache大小,LRU 默认数据块放到default cache(可以通过内存的自动管理调整)
[font=Consolas]db_keep_cache_size 保留存放经常使用小表和索引块等(数据块一直存放到这块buffer里,直到重启instance,大小需要手工指定,不能通过内存自动分配)
[font=Consolas]db_recycle_cache_size 回收存放偶尔做全表扫描的表的数据块(数据块使用完后,自动被清理出内存,大小需要手工指定,不能通过内存自动分配)

[font=Consolas]10:38:32 SQL> alter systemset db_recycle_cache_size =12m; [font=Consolas]System altered.

[font=Consolas]10:38:35 SQL> connscott/tiger [font=Consolas]Connected.
[font=Consolas]10:38:39 SQL> alter table empstorage ( buffer_pool recycle); [font=Consolas]Table altered.

[font=Consolas]23:56:43 SQL> create indexemp1_ename_id on emp1(ename) storage (buffer_pool keep); [font=Consolas]Index created.

[font=Consolas]23:56:56 SQL>desc user_segments; [font=Consolas] Name Null? Type[font=Consolas]------------------------------------------------------------------------- ------------------------ [font=Consolas] SEGMENT_NAME VARCHAR2(81)[font=Consolas] PARTITION_NAME VARCHAR2(30)[font=Consolas] SEGMENT_TYPE VARCHAR2(18)[font=Consolas] TABLESPACE_NAME VARCHAR2(30)[font=Consolas] BYTES NUMBER[font=Consolas] BLOCKS NUMBER[font=Consolas] EXTENTS NUMBER[font=Consolas] INITIAL_EXTENT NUMBER[font=Consolas] NEXT_EXTENT NUMBER[font=Consolas] MIN_EXTENTS NUMBER[font=Consolas] MAX_EXTENTS NUMBER[font=Consolas] PCT_INCREASE NUMBER[font=Consolas] FREELISTS NUMBER[font=Consolas] FREELIST_GROUPS NUMBER[font=Consolas] BUFFER_POOL VARCHAR2(7)

[font=Consolas]23:57:43 SQL> selectsegment_name,BUFFER_POOL from user_segments [font=Consolas]23:57:59 2 where segment_name='EMP1'; [font=Consolas]SEGMENT_NAME BUFFER_[font=Consolas]----------------------------------------------------------------------------------------[font=Consolas]EMP1 DEFAULT

[font=Consolas]23:58:08 SQL> selectsegment_name,BUFFER_POOL from user_segments [font=Consolas]23:58:16 2 where segment_name='EMP1_ENAME_ID'; [font=Consolas]SEGMENT_NAME BUFFER_[font=Consolas]----------------------------------------------------------------------------------------[font=Consolas]EMP1_ENAME_ID KEEP

db_cache_advice 建议data_buffer大小的调整[font=Consolas]select name,SIZE_FOR_ESTIMATE,SIZE_FACTOR,BUFFERS_FOR_ESTIMATE,ESTD_PHYSICAL_READ_FACTOR,ESTD_PHYSICAL_READS [font=Consolas]from v$db_cache_advice [font=Consolas]where ESTD_PHYSICAL_READ_FACTORis not null;
[font=Consolas]NAME SIZE_FOR_ESTIMATE SIZE_FACTORBUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS[font=Consolas]------------------------------------- ----------- -------------------- --------------------------------------------[font=Consolas]DEFAULT 16 .0952 1996 1.1498 6952[font=Consolas]DEFAULT 32 .1905 3992 1.0202 6168[font=Consolas]DEFAULT 48 .2857 5988 1 6046[font=Consolas]DEFAULT 64 .381 7984 1 6046[font=Consolas]DEFAULT 80 .4762 9980 1 6046[font=Consolas]DEFAULT 96 .5714 11976 1 6046[font=Consolas]DEFAULT 112 .6667 13972 1 6046[font=Consolas]DEFAULT 128 .7619 15968 1 6046[font=Consolas]DEFAULT 144 .8571 17964 1 6046[font=Consolas]DEFAULT 160 .9524 19960 1 6046[font=Consolas]DEFAULT 168 1 20958 1 6046[font=Consolas]DEFAULT 176 1.0476 21956 1 6046[font=Consolas]DEFAULT 192 1.1429 23952 1 6046[font=Consolas]DEFAULT 208 1.2381 25948 1 6046[font=Consolas]DEFAULT 224 1.3333 27944 1 6046[font=Consolas]DEFAULT 240 1.4286 29940 1 6046[font=Consolas]DEFAULT 256 1.5238 31936 1 6046
[font=Consolas]NAME SIZE_FOR_ESTIMATE SIZE_FACTORBUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS[font=Consolas]------------------------------------- ----------- -------------------- --------------------------------------------[font=Consolas]DEFAULT 272 1.619 33932 1 6046[font=Consolas]DEFAULT 288 1.7143 35928 1 6046[font=Consolas]DEFAULT 304 1.8095 37924 1 6046[font=Consolas]DEFAULT 320 1.9048 39920 .8198 4957[font=Consolas]21 rows selected.

9) log buffer: 存放redo entries ,用于recover “先记后写”
当做DML或DDL 操作,修改数据块时,会生成日志条目,保存到log buffer,然后及时的写入到redo log file;当脏块(在内存中被修改的块)丢失时,可以通过redo 日志重做,恢复数据.
在设置日志缓冲区时,可以参考下面这个建议的公式来计算:1.5×(平均每个事务所产生的重做记录大小×每秒提交的事务数量)。首先先找到总事务量是多少.
[font=Consolas]select a.value as trancount [font=Consolas]from v$sysstat a,v$statname b[font=Consolas]where a.statistic# =b.statistic# and b.name = 'user commits';
然后,找到系统总共的运行时间:[font=Consolas]select trunc(sysdate -startup_time)*24*60*60 as[font=Consolas]seconds from v$instance;
第三,找到产生的所有重做记录大小:[font=Consolas]select value as redoblocks fromv$sysstat where name =[font=Consolas]'redo blocks written';
最后,我们可以分别计算公式中的值:平均每个事务所产生的重做记录大小= redoblocks/trancount;每秒提交的事务数量=trancount/seconds。这样,最后所建议的日志缓冲区的大小可以写为:1.5×(redoblocks/trancount)×(trancount/seconds)。

10) large pool: 做批处理、备份恢复、用shareserver模式

11) java pool:java 代码的解析

12)pga:随着serverprocess分配给每一个session,随着server process终止,而被释放,独立非共享
存放用户游标、变量、控制信息数据排序、存放hash值
workarea_size_policy =auto ;实现pga 的自动管理[font=Consolas]pga_aggregate_target >0 [font=Consolas]hash_area_size[font=Consolas]sort_area_size

13)process: user process、 server process 、background process
userprocess:客户端请求,不能直接和oracle server交互,必须通过server process 访问oracle server
serverprocess:影子进程或代理进程,代理user process 访问oracle server 分为专用模式和共享模式,一般为专用模式

[font=Consolas]12:22:14 SQL>select username,server from v$session where username is not null;[font=Consolas]USERNAME SERVER[font=Consolas]---------------------------------------[font=Consolas]SCOTT DEDICATED[font=Consolas]SYS DEDICATED

backgroudprocess : 后台进程用于instance 和 database 之间的交互
查看 ps 、v$process 、v$bgprocess
----------查看后台进程
[font=Consolas][oracle@oracle ~]$ ps -ef |grepora_|grep -v grep[font=Consolas]oracle 7618 1 0 08:33 ? 00:00:01 ora_pmon_lx02[font=Consolas]oracle 7620 1 0 08:33 ? 00:00:00 ora_psp0_lx02[font=Consolas]oracle 7622 1 0 08:33 ? 00:00:00 ora_mman_lx02[font=Consolas]oracle 7624 1 0 08:33 ? 00:00:01 ora_dbw0_lx02[font=Consolas]oracle 7626 1 0 08:33 ? 00:00:01 ora_lgwr_lx02[font=Consolas]oracle 7628 1 0 08:33 ? 00:00:05 ora_ckpt_lx02[font=Consolas]oracle 7630 1 0 08:33 ? 00:00:02 ora_smon_lx02[font=Consolas]oracle 7632 1 0 08:33 ? 00:00:00 ora_reco_lx02[font=Consolas]oracle 7634 1 0 08:33 ? 00:00:04 ora_mmon_lx02[font=Consolas]oracle 7636 1 0 08:33 ? 00:00:03 ora_mmnl_lx02[font=Consolas]oracle 7640 1 0 08:34 ? 00:00:00 ora_arc0_lx02[font=Consolas]oracle 7642 1 0 08:34 ? 00:00:00 ora_arc1_lx02[font=Consolas]oracle 7646 1 0 08:34 ? 00:00:00 ora_qmnc_lx02[font=Consolas]oracle 7652 1 0 08:34 ? 00:00:00 ora_q000_lx02[font=Consolas]oracle 7654 1 0 08:34 ? 00:00:00 ora_q001_lx02

[font=Consolas]09:19:27 SQL> selecta.pid,a.spid,b.name,b.description from v$process a,v$bgprocess b[font=Consolas]09:19:52 2 where a.addr=b.paddr and b.paddr<>'00';[font=Consolas] PID SPID NAME DESCRIPTION[font=Consolas]---------------------- ----------------------------------------------------------------------------------------------------[font=Consolas] 2 7618 PMON process cleanup[font=Consolas] 3 7620 PSP0 process spawner 0[font=Consolas] 4 7622 MMAN Memory Manager[font=Consolas] 5 7624 DBW0 db writer process 0[font=Consolas] 6 7626 LGWR Redo etc.[font=Consolas] 7 7628 CKPT checkpoint[font=Consolas] 8 7630 SMON System Monitor Process[font=Consolas] 9 7632 RECO distributed recovery[font=Consolas] 10 7634 MMON Manageability Monitor Process[font=Consolas] 11 7636 MMNL Manageability Monitor Process 2[font=Consolas] 13 7640 ARC0 Archival Process 0[font=Consolas] 14 7642 ARC1 Archival Process 1[font=Consolas] 16 7646 QMNC AQ Coordinator[font=Consolas]13 rows selected.

scn: system change number,相当于oracle的内部时钟,用来记录事务发生先后循序。
checkpoint: 当检查点事件发生时,所对应的scn,用于作为redo logrecover 的起点和保证数据库一致性。
dbwr:1、从数据文件读数据块到data buffer、写脏块 2、释放data buffer空间 3、写入datafile 时是无序的,写入速度较慢 触发条件: 1、生成检查点时,dbwr写脏块,并释放db cache 空间(如正常关库,手工生成检查点、日志切换等) 2、当db cache 没有空间的时候 3、表空间脱机、设置只读模式、热备份 4、删除或truncate table

lgwr:写日志条目到redologfile (必须在dbwr写脏块之前写入日志),按顺序写日志条目到redo logfile。触发条件: 1、commit transaction 2、buffer 空间1/3 满 3、日志条目达到1M的时候 4、每过3s 写一次 5、在dbwr 写脏块之前会写日志建议: 日志文件和数据库文件分开存放在不同磁盘上,日志文件存放到磁盘I/O 最快的磁盘上(比如用raid 1+0)

smon :监控instance的启动,当实例非正常关闭时,再次启动时做instance recover(实例的恢复);如果正常关闭,启动instance,不需做任何操作。
正常关闭实例:触发检查点事件,instance recovery 1)写脏块
2)写redolog 3)未提交事务回滚 4)在控制文件、数据文件头部、redo log 记录检查点
未正常关闭实例:不生成检查点,打开实例时:
smon: 1) roll forward :利用redo 把日志已经写入redofile ,而未写入datafile的丢失的脏块进行重做(redo) 2) open 3) roll back :通过undo segment 将未提交的事务进行回滚
pmon :processmonitor(监控session)
1)如果session 非正常结束,pmon 会清理session所占用的pga、在table 上的lock ,回滚未完成的事务等 2)oracle 里面的‘清洁工’

ckpt:生成检查点 1)当检查点时间发生时,通知dbwr 写脏块 2)并且在数据文件头部和 控制文件中 记下一个统一的检查点,保持数据库的一致性

arcn :归档模式下,日志切换时,备份历史日志。

14) logicstructure:database、tablespace、segment、extent、data block
1、一个database 由多个tablespace 组成
[font=Consolas]15:12:06 SQL>select tablespace_name,STATUS,contents from dba_tablespaces;
[font=Consolas]TABLESPACE_NAME STATUS CONTENTS[font=Consolas]--------------------------------------- ---------[font=Consolas]SYSTEM ONLINE PERMANENT[font=Consolas]UNDOTBS1 ONLINE UNDO[font=Consolas]SYSAUX ONLINE PERMANENT[font=Consolas]TEMP ONLINE TEMPORARY[font=Consolas]USERS ONLINE PERMANENT[font=Consolas]EXAMPLE ONLINE PERMANENT[font=Consolas]6 rows selected.

2、一个tablespace 最少有一个或多个datafile组成
[font=Consolas]15:13:55 SQL>select file_id,file_name,tablespace_name from dba_data_files;
[font=Consolas] FILE_ID FILE_NAME TABLESPACE_NAME[font=Consolas]------------------------------------------------------------------------------------------[font=Consolas] 4/u01/app/oracle/oradata/prod/users01.dbf USERS[font=Consolas] 3/u01/app/oracle/oradata/prod/sysaux01.dbf SYSAUX[font=Consolas] 2/u01/app/oracle/oradata/prod/undotbs01.dbf UNDOTBS1[font=Consolas] 1/u01/app/oracle/oradata/prod/system01.dbf SYSTEM[font=Consolas] 5/u01/app/oracle/oradata/prod/example01.dbf EXAMPLE[font=Consolas]Elapsed:00:00:00.10[font=Consolas]15:14:10 SQL>select file_id,file_name,tablespace_name from dba_temp_files;[font=Consolas] FILE_ID FILE_NAME TABLESPACE_NAME[font=Consolas]------------------------------------------------------------------------------------------[font=Consolas] 1/u01/app/oracle/oradata/prod/temp01.dbf TEMP[font=Consolas]Elapsed:00:00:00.05

3、在tablespace 建立object ,会建立同名的segment,并且分配存储空间,以extent 为单位,最少分配一个extent,extent 由连续的db blocks 组成,db block 是oracle最小的存储单元

[font=Consolas]15:17:00 SQL>create table dept1 tablespace users as select * from dept;[font=Consolas]Table created.[font=Consolas]Elapsed:00:00:00.68
[font=Consolas]15:17:22 SQL>col segment_name for a20
[font=Consolas]15:17:42 SQL>select segment_name,segment_type,tablespace_name ,bytes/1024 ,extents,blocksfrom user_segments[font=Consolas]15:18:15 2 where segment_name ='DEPT1';
[font=Consolas]SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES/1024 EXTENTS BLOCKS[font=Consolas]-------------------------------------- ------------------------------ ---------- --------------------[font=Consolas]DEPT1 TABLE USERS 64 1 8[font=Consolas]Elapsed:00:00:00.23[font=Consolas]15:19:28 SQL>select SEGMENT_NAME,TABLESPACE_NAME,EXTENT_ID ,BYTES/1024,BLOCKS fromuser_extents[font=Consolas]15:19:57 2 where segment_name='DEPT1';[font=Consolas]SEGMENT_NAME TABLESPACE_NAME EXTENT_ID BYTES/1024 BLOCKS[font=Consolas]-------------------------------------------------- ---------- ---------- ----------[font=Consolas]DEPT1 USERS 0 64 8[font=Consolas]Elapsed:00:00:00.30

本文标签:
本文标题:ORACLE 体系机构简介
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】