ORACLE内存结构包括两个部分:
系统全局区 (SGA): 在实例启动的时候分配, 是Oracle实例的基础组件
程序全局区 (PGA): 当服务器进程产生时分配
系统全局区包括以下几个部分:
共享池
数据缓冲区
重做日志缓冲区 ,其他 (例如 statistical data)
和两个额外的内存结构:
大池
Java池
流池
<系统全局区>
动态的
由参数SGA_MAX_SIZE决定大小(在32位系统中,SGA有1.7G的限制)
<共享池(由库缓存和数据字典缓存组成)>
用于存储:
最近执行的SQL语句
最近使用的数据定义
由两个与性能相关的部分组成:
库缓存
数据字典缓存
其中各自的大小由共享池内部指定
由参数SHARED_POOL_SIZE决定大小
show parameter shared_pool_size
alter system set shared_pool_size=100m;
查看共享池的使用情况
SQL> select pool,sum(bytes) from v$sgastat group by pool;
SQL> select pool,name,bytes from v$sgastat where name='free memory';
查看共享池的自由空间
select pool,name,bytes/1024/1024 from v$sgastat where name='free memory';
刷新共享池(商用环境不建议执行此命令、除非有指示)
SQL> alter system flush shared_pool;
共享池过小可能会造成实例无法启动
共享池究竟设多大达到最优呢,可借助共享池的内存顾问要查看,前提是要开启统计信息
SQL> show parameter statistics
NAME TYPE VALUE
----------------------------------------------------
statistics_level string TYPICAL 此处为typical或all时,共享池顾问才能使用。
timed_os_statistics integer 0
timed_statistics boolean TRUE
SQL> desc v$shared_pool_advice;
Name
--------------------------------
SHARED_POOL_SIZE_FOR_ESTIMATE 估计的共享池大小,以MB为单位
SHARED_POOL_SIZE_FACTOR 估计的共享池大小与当前共享池的比值
ESTD_LC_SIZE 估计的共享池中用于库缓存的大小,以MB为单位
ESTD_LC_MEMORY_OBJECTS 估计的共享池中库缓存中的对象数
ESTD_LC_TIME_SAVED 可以节省的解析时间
ESTD_LC_TIME_SAVED_FACTOR 可以节省的解析时间与当前共享池所需花费的解析时间的比值
ESTD_LC_LOAD_TIME 解析对象所需花费的时间
ESTD_LC_LOAD_TIME_FACTOR ESTD_LC_LOAD_TIME与当前大小共享池解析对象花费时间的比值
ESTD_LC_MEMORY_OBJECT_HITS 估计的共享池大小时,共享池中对象的命中次数
SQL> 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 AS "ELTS",
ESTD_LC_MEMORY_OBJECT_HITS AS "ELMO"
FROM V$SHARED_POOL_ADVICE;
SP EL ELM ELT ELTS ELMO
-----------------------------------------------------
64 19 2172 162 .9818 21093
80 33 3714 165 1 21703
96 37 4543 165 1 21708
112 37 4543 165 1 21708
128 37 4543 165 1 21708
144 37 4543 165 1 21708
160 37 4543 165 1 21708
从这里可以看到共享池设为80MB达到最优(此处的ELTS为1),再大的话也是浪费,小的
话影响性能。
<库缓存>
存储最近使用的SQL和PL/SQL语句的信息
共享最常用的语句
管理上遵循LRU规则(最近最少使用)
包括两个部分:
共享SQL区
共享PL/SQL区
大小由Shared Pool的大小决定,不能直接设置库缓存的大小。
要想共享SQL或PL/SQL语句:第一,库缓存要足够大,因为只有这样,要共享的SQL或PL/SQL
才不会很快地淘汰出内存;第二,SQL或PL/SQL语句要是能共享的通用代码,因为ORACLE是
通过比较SQL或PL/SQL语句的正文来决定两个语句是否相同的,只有当两个语句的正文完全相
同时,ORACLE才重用已存在的编译后的代码和执行计划。如,以下两个SQL是不相同的
SELECT * FROM emp WHERE sal >=1500;和
SELECT * FROM emp WHERE sal >=1501;是不相同的
查看共享池的一些缓存信息:
select * from v$db_object_cache;
查看库缓存的命中率:
select sum(pinhits)/sum(pins) from v$librarycache;
SUM(PINHITS)/SUM(PINS)
----------------------
.838034828
确定库高速缓存的效率:
SQL> SELECT namespace,pins,pinhits,reloads FROM v$librarycache ORDER BY namespace;
NAMESPACE PINS PINHITS RELOADS
--------------- ---------- ---------- ----------
BODY 2195 2117 0
CLUSTER 285 277 0
INDEX 57 0 0
--如果RELOADS比较大的话,表示许多SQL语句在老化退出后又被重新装入库池。
-----------------
绑定变量的使用:
SQL> select count(*) from v$sql;
COUNT(*)
----------
1262
SQL> select sql_text from v$sql where sql_text like 'SELECT * FROM SCOTT%';
SQL_TEXT
--------------------------------------------
SQL> SELECT * FROM SCOTT.EMP WHERE EMPNO=7788;
SQL> SELECT * FROM SCOTT.EMP WHERE EMPNO=7839;
SQL> select count(*) from v$sql;
COUNT(*)
----------
1264
SQL> select sql_text from v$sql where sql_text like 'SELECT * FROM SCOTT%';
SQL_TEXT
--------------------------------------------
SELECT * FROM SCOTT.EMP WHERE EMPNO=7788
SELECT * FROM SCOTT.EMP WHERE EMPNO=7839
SQL> VARIABLE bindvar NUMBER;
SQL> BEGIN
2 :bindvar:=7369;
3 END;
4 /
SQL> SELECT * FROM scott.emp WHERE empno=:bindvar;
------------------
将对象钉在共享池中:
SQL> EXECUTE SYS.DBMS_SHARED_POOL.KEEP(object_name,object_type);
<数据字典缓存>
存储在数据库中数据文件,表,索引,列,用户和其它的数据对象的定义和权限信息
大小由Shared Pool的大小决定,不能单独指定
注:即使把共享池设得足够大且所使用的SQL或PL/SQL语句是能共享的,ORACLE也并不能一
定使用内存(共享池)中的代码。例如当有用户修改了某个对象的定义后,所有使用这个对象
的内存(共享池)中的代码全部被ORACLE设置为无效,因此在使用时必须重新编译。
<数据缓冲区>
存储从数据文件中获得的数据块的镜像
当获取和更新数据的时候能够大幅度的提高性能
管理上遵循LRU规则
9I之前使用 DB_BLOCK_SIZE和 DB_BLOCK_BUFFERS两个参数来设置数据库高速缓冲区大小
DB_BLOCK_SIZE的值是在创建数据库时设置定的,如果要改变该参数的值一般需要重建数据库
其中:
DB_BLOCK_SIZE ORACLE数据块(内存缓冲区)的大小,ORACLE中内存和外 存的数据块大小是相同的
DB_BLOCK_BUFFERS 内存缓冲区的个数,数据缓冲区大小为这两个参数的积。
显示参数DB_BLOCK_SIZE其块的大小
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ----------
db_block_size integer 8192
select * from v$sgainfo;
NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 1220580 No
Redo Buffers 7168000 No
Buffer Cache Size 348127232 Yes
Shared Pool Size 197132288 Yes
Large Pool Size 4194304 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 4194304 Yes
Granule Size 4194304 No
Maximum SGA Size 566231040 No
Startup overhead in Shared Pool 41943040 No
Free SGA Memory Available 0
ORACLE 9i 及以后,引入另一个参数 DB_CACHE_SIZE 这个动态参数,可以数据库运行时
动态修改该参数。
包括以下独立的子缓存:
DB_CACHE_SIZE
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
能够动态的调整大小
alter system set db_cache_size=100m;
alter system set db_keep_cache_size=12m;
alter system set db_recycle_cace_size=16m;
select component,current_size from v$sga_dynamic_components;
刷新数据缓冲区:
SQL> alter system flush buffer_cache;
查看db_cache的命中率:
SQL> select name,value from v$sysstat where name in
('db block gets from cache','consistent gets from cache',
'physical reads cache');
NAME VALUE
---------------------------------------------------------------- ----------
db block gets from cache 3868
consistent gets from cache 73179
physical reads cache 7061
db_cache命中率=1-(physical reads cache/(db block gets from cache+
consistent gets from cache))
也可以:
SQL> SELECT name,physical_reads,db_block_gets,consistent_gets,
1-(physical_reads/(db_block_gets+consistent_gets)) "HitRation"
FROM v$buffer_pool_statistics;
NAME PHYSICAL_READS DB_BLOCK_GETS CONSISTENT_GETS HitRation
-------------------- -------------- ------------- --------------- ----------
DEFAULT 8993 23945 234148 .965155971
注意:
keep是存取你要保留时间长的 比如经常访问的索引和小表
recycle是FTS多的大表就是你不希望保留时间长的 ,要及时回收再利用的
keep也是使用LRU,但由于缺省情况下,数据块是被缓存到普通的cache中,
因此被指定换存到keep区的数据块就不太可能因为执行其他一些操作被其他数据块交换出,
即使较长时间没使用了。
而设置recycle是因为有时会有一些大的又少使用的表的操作,
如果不设置单独的缓存区,那么缺省的缓存区中的数据块就被这些偶尔使用的数据换出。
设置recycle后(通常recycle都是比较小的),将大的又较少使用的数据放到recycle中,
可以减少缺省缓存区中频繁使用的数据块的换出换入的操作。
可以这样理解,keep区中的数据是最最频繁使用的,或者属于那些需要最快相应时间操作
的数据。
recycle是偶尔使用的数据的缓存区。其他数据都被缓存在缺省区。
将数据放到 DB_KEEP_CACHE
CREATE table test2 (n number) storage(buffer_pool keep)
alter table scott.dept storage(buffer_pool keep)
select segment_name,buffer_pool from dba_segments where buffer_pool='KEEP';
从DB_KEEP_CACHE 移去:
alter table scott.dept storage(buffer_pool default)
内存缓冲区顾问 (V$DB_CACHE_ADVICE)
ORACLE 9i开始提供的一个工具,用来帮助获得调整数据库调整缓冲区的统计信息。内存缓冲
区顾问一共有3种状态。
SQL> show parameter db_cache_advice;
NAME TYPE VALUE
------------------------------------ ----------- -------------
db_cache_advice string ON
(1)ON 工具打开,为该工具分配内存并进行统计信息的收集,要有一定的内存和CPU开销。
(2)READY 工具打开,为该工具分配内存但不进行统计信息的收集,因此没有CPU开销。
(3)OFF 工具关闭。
查看 V$DB_CACHE_ADVICE 状态
SQL> select name,block_size,advice_status from v$db_cache_advice;
NAME BLOCK_SIZE ADV
-------------------- ---------- ---
DEFAULT 8192 ON
DEFAULT 8192 ON
修改其状态
SQL> ALTER SYSTEM SET DB_CACHE_ADVICE = OFF;
查看数据缓冲区的最佳大小:
SQL> desc v$db_cache_advice;
Name
---------------------------------
ID 缓冲区编号,1-6分别对应2-32K db_cache_size
7是db_keep_cache_size 8是db_recycle_cache_size
NAME 缓冲区名字
BLOCK_SIZE 块大小
ADVICE_STATUS 建议的状态
SIZE_FOR_ESTIMATE 估计的缓冲区大小,MB为单位
SIZE_FACTOR 与当前数据缓冲区的比值
BUFFERS_FOR_ESTIMATE 估计的缓冲区大小,块为单位
ESTD_PHYSICAL_READ_FACTOR 物理读与当前实际物理读的比值,越小越好,为0时显示为空
ESTD_PHYSICAL_READS 物理读数
ESTD_PHYSICAL_READ_TIME 响应时间
ESTD_PCT_OF_DB_TIME_FOR_READS 物理读磁盘消耗时间百分比
ESTD_CLUSTER_READS 读取数据块总数,仅对RAC有效
ESTD_CLUSTER_READ_TIME 读取花费的总时间,仅对RAC有效
SQL> SELECT SIZE_FOR_ESTIMATE "SIZE",BUFFERS_FOR_ESTIMATE "BUFFERS",
ESTD_PHYSICAL_READ_FACTOR "READ_FACTOR", ESTD_PHYSICAL_READS "READS"
FROM V$DB_CACHE_ADVICE
WHERE NAME='DEFAULT' AND BLOCK_SIZE=
(SELECT VALUE FROM V$PARAMETER WHERE NAME='db_block_size');
SIZE BUFFERS READ_FACTOR READS
---------- ---------- ----------- ----------
16 2002 0
32 4004 0
48 6006 0
64 8008 0
80 10010 0
96 12012 0
112 14014 0
128 16016 0
144 18018 0
160 20020 0
176 22022 0
192 24024 0
208 26026 0
224 28028 0
240 30030 0
256 32032 0
272 34034 0
288 36036 0
304 38038 0
320 40040 0
第三列为空时,则表示内存里的逻辑读没有引起物理读,当前的DB_CACHE够用,非常好
为1的话,当示当前值,其它的行的值为相对于当前值的比率,当然是越低越好。
<重做日志缓冲区〉
记录所有数据库的块改变
主要的目的是用于恢复
大小由参数LOG_BUFFER决定
SQL> show parameter log_buffer
NAME TYPE VALUE
------------------------------------ ----------- ----------
log_buffer integer 7028736
查看log_buffer是否需要调整:
select name,value from v$sysstat
where name='redo buffer allocation retries';
NAME VALUE
---------------------------------------------------------------- ----------
redo buffer allocation retries 2
接近于0时不需要调整
调整时改参数文件
或alter system set log_buffer=10057408 scope=spfile;
<大池>
是系统全局区中可选的一个部分
减轻在共享池中的负担
用于:
共享服务器进程的PGA大部分放入Large pool(stack space除外)
大规模的I/O
备份恢复操作或者是使用RMAN
不使用LRU规则
大小由参数LARGE_POOL_SIZE决定
能够被动态的改变大小
SQL> show parameter large_pool_size
NAME TYPE VALUE
------------------------------------ ----------- -----------
large_pool_size big integer 0
SQL> alter system set large_pool_size=10m;
为了监视大池的使用,可用以下查询来做
SQL> select * from v$sgastat where pool='large pool';
<Java池〉
使用java语言,Java命令的分析需要使用
大小由参数JAVA_POOL_SIZE决定
SQL> show parameter java_pool_size
NAME TYPE VALUE
------------------------------------ ----------- -------------------
java_pool_size big integer 0
SQL> alter system set java_pool_size=10m;
同时还有以下几个参数:
SQL> show parameter java
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_pool_size big integer 0
java_soft_sessionspace_limit integer 0
为了监视JAVA池的使用,可用以下查询来做
SQL> select * from v$sgastat where pool='java pool';
如何将SGA锁定在物理内存中(ORACLE会把一部分SGA放到虚拟内存中,这样比较慢):
show parameter lock_sga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
alter system set lock_sga=true scope=spfile;
ORACLE 9i 以后的版本,SGA是动态的。SGA中内存缓冲区,可动态的增减,ORACLE是利用所谓
的区组(granule)来管理SGA内存的。区组就是一片连续的虚拟内存区,是ORACLE分配和回收内存
的基本单位。在 v$sgainfo或v$sga_dynamic_components中可看到该大小。
ASMM
ORACLE中各部分内存的设置非常麻烦,为了减轻DBA的工作负责
ORACLE10G中引进了一个ASMM(自动共享内存管理),DBA只需初始化参数SGA_TARGET,
ORACLE就会自动对SGA的共享池、JAVA池、大池、数据库高速缓冲区进行自动调整。
但是无法自动调整DB_KEEP_CACHE_SIZE,DB_RECYCLE_CACHE_SIZE,LOG_BUFFER、流池。
sga_target为O(最大不能超过sga_max_size)时,则取消自动共享内存管理,各部分需要手工设定。
当然ASMM工作的前提是,statistics_level 为typical或 all,否则无收集MMAN进程所需的信息。
查看SGA大小:show parameter sga_max_size
动态分配后如何查看各内存区大小:
select * from v$sgainfo
select component,current_size from v$sga_dynamic_components;
select * from v$sgastat;
SGA空间的分配不是以MB为单位,而是以粒度 granule ['gr??nju:l] 为单位。通常如果SGA大小
不超过1G,此值大小一般为4M,如果大于1G(另一说为128M,但实际为1G),那么为16M。不同平台会有所变化,
WINDOWS中SGA大小超过1G,区组大小为8M。
<程序全局区〉
为每个用户进程连接Oracle数据库保留的内存
当进程创建时分配
进程结束后被释放
只能被一个进程使用
APMM(自动PGA内存管理):
参数PGA_AGGREGATE_TARGET指定PGA的总共大小 (启用的前提是workarea_size_policy为auto)
SQL> show parameter workarea_size_policy;
NAME TYPE VALUE
------------------------------------ ----------- -----------
workarea_size_policy string AUTO
当ORACLE创建一个服务器进程的同时,要为这个服务器进程分配一个内存区,该区就称为程序全
局区(PGA),与SGA不同,PGA是一个私有的内存区,不能共享,且只属于一个进程。它随进程的创建
而被分配,随进程的终止而被回收。在专用服务器进程的配置情况下,程序全局区包括了以下结构:
(1)排序区:用于处理SQL语句所需的排序
(2)游标状态区:用于指示会话当前所使用的SQL语句的处理状态
(3)会话信息区:包括了传话的用户权限和优化统计信息
(4)堆栈区:包括了其他的会话变量。
如果是共享服务器进程或多线程的配置,以上这些结构除了堆栈区外大部分都存在于SGA中,如果
有LARGE POOL,存放于Large pool中,否则它们会被存放于共享池中。
SQL> show parameter pga_a
NAME TYPE VALUE
------------------------------------ ----------- -------
pga_aggregate_target big integer 91M
SQL> alter system set pga_aggregate_target=100m;
sga_target 实例一启动便获取指定数量的内存
pga_aggregate_target 内存分配不够时,才获取
查看PGA详细情况,通过这两个查询可看到当前PGA的使用情况,PGA是否够用,从而为优化做参考:
select * from v$pgastat; --其中有PGA的命中率
select * from v$pga_target_advice;
注:要在系统运行了一段时间后,看才是最准确的,空闲时,可能都会显示够用的数据。
PGA最大大小为4G
查看每个用户的PGA分配情况:
SQL> select s.value,s.sid,a.username
from v$sesstat s,v$statname n,v$session a
where n.statistic#=s.statistic# and name='session pga memory'
and s.sid=a.sid order by s.value;
通过内存顾问查看当前最优的PGA设置:
SQL> desc v$pga_target_advice;
Name
---------------------------------
PGA_TARGET_FOR_ESTIMATE 估计的pga_aggregate_target的参数值
PGA_TARGET_FACTOR 与当前pga_aggregate_target的比值
ADVICE_STATUS 是否收集统计信息,以计算建议值,ON启用,OFF禁用
BYTES_PROCESSED 预估操作的字节数
ESTD_EXTRA_BYTES_RW 当pga_aggregate_target参数值为估计值是,需额外处理的字节数
ESTD_PGA_CACHE_HIT_PERCENTAGE 为估计值时,PGA的缓存命中率,计算公式为:
BYTES_PROCESSED/(BYTES_PROCESSED+ESTD_EXTRA_BYTES_RW)
ESTD_OVERALLOC_COUNT 为估计值时,需额外分配的PGA内存,为非0时,说明还太少。
SQL> SELECT ROUND(PGA_TARGET_FOR_ESTIMATE/1024/1024) "PGATARGET SIZE(M)",
ESTD_PGA_CACHE_HIT_PERCENTAGE "EST.CACHE HIT%",
ROUND(ESTD_EXTRA_BYTES_RW/1024/1024) "EST.RW(M)",
ESTD_OVERALLOC_COUNT "EST.OVER-ALLOC"
FROM V$PGA_TARGET_ADVICE;
PGATARGET SIZE(M) EST.CACHE HIT% EST.RW(M) EST.OVER-ALLOC
----------------- -------------- ---------- --------------
24 100 0 0
48 100 0 0
96 100 0 0
143 100 0 0
191 100 0 0
229 100 0 0
267 100 0 0
306 100 0 0
344 100 0 0
382 100 0 0
573 100 0 0
764 100 0 0
1146 100 0 0
1528 100 0 0
要求是第二列(办求最大)和第三列(力求最小)之间的平衡,而获取到的第一列值,
从当前看PGA 24M 已足够,再多也是浪费。
For OLTP 系统
PGA_AGGREGATE_TARGET = (<Total Physical Memory > * 80%) * 20%
For DSS 系统
PGA_AGGREGATE_TARGET = (<Total Physical Memory > * 80%) * 50%
内存信息的获取:
SHOW PARAMETER 显示所有参数信息
SHOW PARAMETER SGA_MAX_SIZE 显示单个参数值
SELECT * FROM v$parameter
SELECT * FROM v$spparameter
ORACLE执行SQL查询语句的步骤:
SQL语句的执行主要由用户进程与服务器进程来完成,其他的一些进程可能要辅助完成这一过程。
查询语句与其它的SQL语句有所不同,如果一个查询语句执行成功,就要返回查询的结果,而其它
的SQL语句只是返回执行成功或失败的信息。查询语句的处理主要包括以下3个阶段:编译、执行和
提取数据。
编译:在进行编译时,服务器进程会将SQL语句的正文放入共享池的库高速缓存中并将完成以下处理。
首先在共享池中搜索是否有相同的SQL语句(正文),如果没有就进行后续处理。
检查该SQL语句的语法是否正确
通过查看数据字典来检查表和列的定义
对所操作的对象加编译锁,以便在编译语句期间这些对象的定义不能被改变
检查所引用对象的用户权限
生成执行该SQL语句所需的优化执行计划
将SQL语句和执行计划装入共享的SQL区
执行
提取数据
〈进程结构〉
Oracle有以下几种进程:
用户进程: 在用户连接数据库时产生
服务器进程: 当连接到Oracle实例并且用户建立会话的时候产生
后台进程: Oracle实例启动的时候产生
<用户进程>
用户进程是要与 Oracle 服务器交互的一种进程
必须要先建立一个连接
不会直接和Oracle服务器联系
<服务器进程>
服务进程是直接和 Oracle 服务器交互的一段程序.
它响应用户要求,向服务器发起呼叫并返回结果给用户.
可用专用服务器模式,也可用共享服务器模式创建对话.
<后台进程>
维持物理和内存之间的关联:
必须要有的后台进程:
DBWn(数据库写) PMON(进程监控) CKPT(检查点)
LGWR(日志写) SMON(系统监控)
可选的后台进程:
ARCn(归档)
RECO(恢复)
Snnn
Pnnn
查看数据库中有哪些后台进程:
select * from v$bgprocess where paddr<>'00';或 ps -ef | grep ora_
数据库写进程 (DBWn),负责把缓冲区的数据写到磁盘,通常一个DBWn能满足需求,为提高性能,可
配置多个DBWn进程。
DBWn的触发条件:
发生检查点
脏缓存到达限制
没有自由的缓存
超时发生
RAC ping请求
表空间离线
表空间只读
表被删除或者截断
开始备份表空间
增加数据库写进程数量(最多10个,基于CPU数量的,设多了,也只一个进程有效):
vim pfile
DB_WRITER_PROCESSES=x 或 alter system set DB_WRITER_PROCESSES=3 scope=spfile;
日志写进程 (LGWR):
LGWR写的条件:
提交的时候
达到三分之一满了
日志的大小到1M
每隔三秒
在DBWn进程写之前
系统监测进程 (SMON):
作用:
实例恢复
前滚所有重做日志中的改变(即数据已经提交了,但还没往数据文件里写完就断电了,要重新应用一次日志)
打开数据库为了用户能访问
回滚没有提交的事务
接合自由的空间
释放临时表空间
进程监测进程 (PMON):
清除失败的进程:
回滚事务、释放锁、释放其他资源
重启死 dispatchers
检查点进程 (CKPT):
作用:
给信号给DBWn
更新数据文件头
更新控制文件
如何将检查点信息写入警告文件:
alter system set log_checkpoints_to_alert=true;
归档进程 (ARCn):
可选的后台进程
当启用归档方式后自动归档重做日志文件
修改归档进程数量:
alter system set log_archive_max_processes = 4;
<数据库逻辑结构>
表明数据库的物理空间如何使用
分为表空间,段,范围,块
修改系统进程数量:
不能动态修改(不会报错,但也不会起作用,要修改参数文件,再将参数文件转换成服务器参数文件)
vim pfile
processes=300
create spfile from pfile
提高oracle的最大会话数
oracle最大会话数由初始化能数processes控制的,并不是由sessions控制,初始化参数sessions
的值由processes衍生出来的session默认等于 1.1 * processes +5
show parameter session
查当前有哪些会话:
select * from v$session where type='USER';
还可查用户test从哪台机器登录的:
杀掉一个进程:
alter system kill session 'SID,SERIAL#';
杀掉后server为pseudo
最好用操作系统来杀kill当一个活动的进程正在执行网络I/O、回滚事务、导入导出等操作时,不能杀掉的,只有等这些操作完成后,才能杀掉
查看ORACLE进程与操作系统进程的对应关系
v$process视图中有关于每个进程的信息,包括其操作系统进程号
SQL> select addr,pid,spid,username,program from v$process;
ADDR PID SPID USERNAME PROGRAM
-------- ---------- ------------ --------------- ------------------------------------------------
5E61628C 1 PSEUDO --数据库的初始化进程
5E616840 2 3405 oracle oracle@James007.ycdata.net (PMON)
5E616DF4 3 3407 oracle oracle@James007.ycdata.net (PSP0)
5E6173A8 4 3409 oracle oracle@James007.ycdata.net (MMAN)
5E61795C 5 3411 oracle oracle@James007.ycdata.net (DBW0)
5E617F10 6 3413 oracle oracle@James007.ycdata.net (LGWR)
5E6184C4 7 3415 oracle oracle@James007.ycdata.net (CKPT)
5E618A78 8 3417 oracle oracle@James007.ycdata.net (SMON)
5E61902C 9 3419 oracle oracle@James007.ycdata.net (RECO)
5E6195E0 10 3421 oracle oracle@James007.ycdata.net (CJQ0)
5E619B94 11 3423 oracle oracle@James007.ycdata.net (MMON)
5E61A148 12 3425 oracle oracle@James007.ycdata.net (MMNL)
5E61A6FC 13 3427 oracle oracle@James007.ycdata.net (D000)
[oracle@James007 ~]$ ps -ef |grep ora_
oracle 3405 1 0 09:19 ? 00:00:00 ora_pmon_orcl
oracle 3407 1 0 09:19 ? 00:00:00 ora_psp0_orcl
oracle 3409 1 0 09:19 ? 00:00:00 ora_mman_orcl
oracle 3411 1 0 09:19 ? 00:00:00 ora_dbw0_orcl
oracle 3413 1 0 09:19 ? 00:00:00 ora_lgwr_orcl
oracle 3415 1 0 09:19 ? 00:00:00 ora_ckpt_orcl
oracle 3417 1 0 09:19 ? 00:00:01 ora_smon_orcl
oracle 3419 1 0 09:19 ? 00:00:00 ora_reco_orcl
oracle 3421 1 0 09:19 ? 00:00:00 ora_cjq0_orcl
oracle 3423 1 0 09:19 ? 00:00:01 ora_mmon_orcl
oracle 3425 1 0 09:19 ? 00:00:00 ora_mmnl_orcl
oracle 3427 1 0 09:19 ? 00:00:00 ora_d000_orcl
系统全局区 (SGA): 在实例启动的时候分配, 是Oracle实例的基础组件
程序全局区 (PGA): 当服务器进程产生时分配
系统全局区包括以下几个部分:
共享池
数据缓冲区
重做日志缓冲区 ,其他 (例如 statistical data)
和两个额外的内存结构:
大池
Java池
流池
<系统全局区>
动态的
由参数SGA_MAX_SIZE决定大小(在32位系统中,SGA有1.7G的限制)
<共享池(由库缓存和数据字典缓存组成)>
用于存储:
最近执行的SQL语句
最近使用的数据定义
由两个与性能相关的部分组成:
库缓存
数据字典缓存
其中各自的大小由共享池内部指定
由参数SHARED_POOL_SIZE决定大小
show parameter shared_pool_size
alter system set shared_pool_size=100m;
查看共享池的使用情况
SQL> select pool,sum(bytes) from v$sgastat group by pool;
SQL> select pool,name,bytes from v$sgastat where name='free memory';
查看共享池的自由空间
select pool,name,bytes/1024/1024 from v$sgastat where name='free memory';
刷新共享池(商用环境不建议执行此命令、除非有指示)
SQL> alter system flush shared_pool;
共享池过小可能会造成实例无法启动
共享池究竟设多大达到最优呢,可借助共享池的内存顾问要查看,前提是要开启统计信息
SQL> show parameter statistics
NAME TYPE VALUE
----------------------------------------------------
statistics_level string TYPICAL 此处为typical或all时,共享池顾问才能使用。
timed_os_statistics integer 0
timed_statistics boolean TRUE
SQL> desc v$shared_pool_advice;
Name
--------------------------------
SHARED_POOL_SIZE_FOR_ESTIMATE 估计的共享池大小,以MB为单位
SHARED_POOL_SIZE_FACTOR 估计的共享池大小与当前共享池的比值
ESTD_LC_SIZE 估计的共享池中用于库缓存的大小,以MB为单位
ESTD_LC_MEMORY_OBJECTS 估计的共享池中库缓存中的对象数
ESTD_LC_TIME_SAVED 可以节省的解析时间
ESTD_LC_TIME_SAVED_FACTOR 可以节省的解析时间与当前共享池所需花费的解析时间的比值
ESTD_LC_LOAD_TIME 解析对象所需花费的时间
ESTD_LC_LOAD_TIME_FACTOR ESTD_LC_LOAD_TIME与当前大小共享池解析对象花费时间的比值
ESTD_LC_MEMORY_OBJECT_HITS 估计的共享池大小时,共享池中对象的命中次数
SQL> 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 AS "ELTS",
ESTD_LC_MEMORY_OBJECT_HITS AS "ELMO"
FROM V$SHARED_POOL_ADVICE;
SP EL ELM ELT ELTS ELMO
-----------------------------------------------------
64 19 2172 162 .9818 21093
80 33 3714 165 1 21703
96 37 4543 165 1 21708
112 37 4543 165 1 21708
128 37 4543 165 1 21708
144 37 4543 165 1 21708
160 37 4543 165 1 21708
从这里可以看到共享池设为80MB达到最优(此处的ELTS为1),再大的话也是浪费,小的
话影响性能。
<库缓存>
存储最近使用的SQL和PL/SQL语句的信息
共享最常用的语句
管理上遵循LRU规则(最近最少使用)
包括两个部分:
共享SQL区
共享PL/SQL区
大小由Shared Pool的大小决定,不能直接设置库缓存的大小。
要想共享SQL或PL/SQL语句:第一,库缓存要足够大,因为只有这样,要共享的SQL或PL/SQL
才不会很快地淘汰出内存;第二,SQL或PL/SQL语句要是能共享的通用代码,因为ORACLE是
通过比较SQL或PL/SQL语句的正文来决定两个语句是否相同的,只有当两个语句的正文完全相
同时,ORACLE才重用已存在的编译后的代码和执行计划。如,以下两个SQL是不相同的
SELECT * FROM emp WHERE sal >=1500;和
SELECT * FROM emp WHERE sal >=1501;是不相同的
查看共享池的一些缓存信息:
select * from v$db_object_cache;
查看库缓存的命中率:
select sum(pinhits)/sum(pins) from v$librarycache;
SUM(PINHITS)/SUM(PINS)
----------------------
.838034828
确定库高速缓存的效率:
SQL> SELECT namespace,pins,pinhits,reloads FROM v$librarycache ORDER BY namespace;
NAMESPACE PINS PINHITS RELOADS
--------------- ---------- ---------- ----------
BODY 2195 2117 0
CLUSTER 285 277 0
INDEX 57 0 0
--如果RELOADS比较大的话,表示许多SQL语句在老化退出后又被重新装入库池。
-----------------
绑定变量的使用:
SQL> select count(*) from v$sql;
COUNT(*)
----------
1262
SQL> select sql_text from v$sql where sql_text like 'SELECT * FROM SCOTT%';
SQL_TEXT
--------------------------------------------
SQL> SELECT * FROM SCOTT.EMP WHERE EMPNO=7788;
SQL> SELECT * FROM SCOTT.EMP WHERE EMPNO=7839;
SQL> select count(*) from v$sql;
COUNT(*)
----------
1264
SQL> select sql_text from v$sql where sql_text like 'SELECT * FROM SCOTT%';
SQL_TEXT
--------------------------------------------
SELECT * FROM SCOTT.EMP WHERE EMPNO=7788
SELECT * FROM SCOTT.EMP WHERE EMPNO=7839
SQL> VARIABLE bindvar NUMBER;
SQL> BEGIN
2 :bindvar:=7369;
3 END;
4 /
SQL> SELECT * FROM scott.emp WHERE empno=:bindvar;
------------------
将对象钉在共享池中:
SQL> EXECUTE SYS.DBMS_SHARED_POOL.KEEP(object_name,object_type);
<数据字典缓存>
存储在数据库中数据文件,表,索引,列,用户和其它的数据对象的定义和权限信息
大小由Shared Pool的大小决定,不能单独指定
注:即使把共享池设得足够大且所使用的SQL或PL/SQL语句是能共享的,ORACLE也并不能一
定使用内存(共享池)中的代码。例如当有用户修改了某个对象的定义后,所有使用这个对象
的内存(共享池)中的代码全部被ORACLE设置为无效,因此在使用时必须重新编译。
<数据缓冲区>
存储从数据文件中获得的数据块的镜像
当获取和更新数据的时候能够大幅度的提高性能
管理上遵循LRU规则
9I之前使用 DB_BLOCK_SIZE和 DB_BLOCK_BUFFERS两个参数来设置数据库高速缓冲区大小
DB_BLOCK_SIZE的值是在创建数据库时设置定的,如果要改变该参数的值一般需要重建数据库
其中:
DB_BLOCK_SIZE ORACLE数据块(内存缓冲区)的大小,ORACLE中内存和外 存的数据块大小是相同的
DB_BLOCK_BUFFERS 内存缓冲区的个数,数据缓冲区大小为这两个参数的积。
显示参数DB_BLOCK_SIZE其块的大小
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ----------
db_block_size integer 8192
select * from v$sgainfo;
NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 1220580 No
Redo Buffers 7168000 No
Buffer Cache Size 348127232 Yes
Shared Pool Size 197132288 Yes
Large Pool Size 4194304 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 4194304 Yes
Granule Size 4194304 No
Maximum SGA Size 566231040 No
Startup overhead in Shared Pool 41943040 No
Free SGA Memory Available 0
ORACLE 9i 及以后,引入另一个参数 DB_CACHE_SIZE 这个动态参数,可以数据库运行时
动态修改该参数。
包括以下独立的子缓存:
DB_CACHE_SIZE
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
能够动态的调整大小
alter system set db_cache_size=100m;
alter system set db_keep_cache_size=12m;
alter system set db_recycle_cace_size=16m;
select component,current_size from v$sga_dynamic_components;
刷新数据缓冲区:
SQL> alter system flush buffer_cache;
查看db_cache的命中率:
SQL> select name,value from v$sysstat where name in
('db block gets from cache','consistent gets from cache',
'physical reads cache');
NAME VALUE
---------------------------------------------------------------- ----------
db block gets from cache 3868
consistent gets from cache 73179
physical reads cache 7061
db_cache命中率=1-(physical reads cache/(db block gets from cache+
consistent gets from cache))
也可以:
SQL> SELECT name,physical_reads,db_block_gets,consistent_gets,
1-(physical_reads/(db_block_gets+consistent_gets)) "HitRation"
FROM v$buffer_pool_statistics;
NAME PHYSICAL_READS DB_BLOCK_GETS CONSISTENT_GETS HitRation
-------------------- -------------- ------------- --------------- ----------
DEFAULT 8993 23945 234148 .965155971
注意:
keep是存取你要保留时间长的 比如经常访问的索引和小表
recycle是FTS多的大表就是你不希望保留时间长的 ,要及时回收再利用的
keep也是使用LRU,但由于缺省情况下,数据块是被缓存到普通的cache中,
因此被指定换存到keep区的数据块就不太可能因为执行其他一些操作被其他数据块交换出,
即使较长时间没使用了。
而设置recycle是因为有时会有一些大的又少使用的表的操作,
如果不设置单独的缓存区,那么缺省的缓存区中的数据块就被这些偶尔使用的数据换出。
设置recycle后(通常recycle都是比较小的),将大的又较少使用的数据放到recycle中,
可以减少缺省缓存区中频繁使用的数据块的换出换入的操作。
可以这样理解,keep区中的数据是最最频繁使用的,或者属于那些需要最快相应时间操作
的数据。
recycle是偶尔使用的数据的缓存区。其他数据都被缓存在缺省区。
将数据放到 DB_KEEP_CACHE
CREATE table test2 (n number) storage(buffer_pool keep)
alter table scott.dept storage(buffer_pool keep)
select segment_name,buffer_pool from dba_segments where buffer_pool='KEEP';
从DB_KEEP_CACHE 移去:
alter table scott.dept storage(buffer_pool default)
内存缓冲区顾问 (V$DB_CACHE_ADVICE)
ORACLE 9i开始提供的一个工具,用来帮助获得调整数据库调整缓冲区的统计信息。内存缓冲
区顾问一共有3种状态。
SQL> show parameter db_cache_advice;
NAME TYPE VALUE
------------------------------------ ----------- -------------
db_cache_advice string ON
(1)ON 工具打开,为该工具分配内存并进行统计信息的收集,要有一定的内存和CPU开销。
(2)READY 工具打开,为该工具分配内存但不进行统计信息的收集,因此没有CPU开销。
(3)OFF 工具关闭。
查看 V$DB_CACHE_ADVICE 状态
SQL> select name,block_size,advice_status from v$db_cache_advice;
NAME BLOCK_SIZE ADV
-------------------- ---------- ---
DEFAULT 8192 ON
DEFAULT 8192 ON
修改其状态
SQL> ALTER SYSTEM SET DB_CACHE_ADVICE = OFF;
查看数据缓冲区的最佳大小:
SQL> desc v$db_cache_advice;
Name
---------------------------------
ID 缓冲区编号,1-6分别对应2-32K db_cache_size
7是db_keep_cache_size 8是db_recycle_cache_size
NAME 缓冲区名字
BLOCK_SIZE 块大小
ADVICE_STATUS 建议的状态
SIZE_FOR_ESTIMATE 估计的缓冲区大小,MB为单位
SIZE_FACTOR 与当前数据缓冲区的比值
BUFFERS_FOR_ESTIMATE 估计的缓冲区大小,块为单位
ESTD_PHYSICAL_READ_FACTOR 物理读与当前实际物理读的比值,越小越好,为0时显示为空
ESTD_PHYSICAL_READS 物理读数
ESTD_PHYSICAL_READ_TIME 响应时间
ESTD_PCT_OF_DB_TIME_FOR_READS 物理读磁盘消耗时间百分比
ESTD_CLUSTER_READS 读取数据块总数,仅对RAC有效
ESTD_CLUSTER_READ_TIME 读取花费的总时间,仅对RAC有效
SQL> SELECT SIZE_FOR_ESTIMATE "SIZE",BUFFERS_FOR_ESTIMATE "BUFFERS",
ESTD_PHYSICAL_READ_FACTOR "READ_FACTOR", ESTD_PHYSICAL_READS "READS"
FROM V$DB_CACHE_ADVICE
WHERE NAME='DEFAULT' AND BLOCK_SIZE=
(SELECT VALUE FROM V$PARAMETER WHERE NAME='db_block_size');
SIZE BUFFERS READ_FACTOR READS
---------- ---------- ----------- ----------
16 2002 0
32 4004 0
48 6006 0
64 8008 0
80 10010 0
96 12012 0
112 14014 0
128 16016 0
144 18018 0
160 20020 0
176 22022 0
192 24024 0
208 26026 0
224 28028 0
240 30030 0
256 32032 0
272 34034 0
288 36036 0
304 38038 0
320 40040 0
第三列为空时,则表示内存里的逻辑读没有引起物理读,当前的DB_CACHE够用,非常好
为1的话,当示当前值,其它的行的值为相对于当前值的比率,当然是越低越好。
<重做日志缓冲区〉
记录所有数据库的块改变
主要的目的是用于恢复
大小由参数LOG_BUFFER决定
SQL> show parameter log_buffer
NAME TYPE VALUE
------------------------------------ ----------- ----------
log_buffer integer 7028736
查看log_buffer是否需要调整:
select name,value from v$sysstat
where name='redo buffer allocation retries';
NAME VALUE
---------------------------------------------------------------- ----------
redo buffer allocation retries 2
接近于0时不需要调整
调整时改参数文件
或alter system set log_buffer=10057408 scope=spfile;
<大池>
是系统全局区中可选的一个部分
减轻在共享池中的负担
用于:
共享服务器进程的PGA大部分放入Large pool(stack space除外)
大规模的I/O
备份恢复操作或者是使用RMAN
不使用LRU规则
大小由参数LARGE_POOL_SIZE决定
能够被动态的改变大小
SQL> show parameter large_pool_size
NAME TYPE VALUE
------------------------------------ ----------- -----------
large_pool_size big integer 0
SQL> alter system set large_pool_size=10m;
为了监视大池的使用,可用以下查询来做
SQL> select * from v$sgastat where pool='large pool';
<Java池〉
使用java语言,Java命令的分析需要使用
大小由参数JAVA_POOL_SIZE决定
SQL> show parameter java_pool_size
NAME TYPE VALUE
------------------------------------ ----------- -------------------
java_pool_size big integer 0
SQL> alter system set java_pool_size=10m;
同时还有以下几个参数:
SQL> show parameter java
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_pool_size big integer 0
java_soft_sessionspace_limit integer 0
为了监视JAVA池的使用,可用以下查询来做
SQL> select * from v$sgastat where pool='java pool';
如何将SGA锁定在物理内存中(ORACLE会把一部分SGA放到虚拟内存中,这样比较慢):
show parameter lock_sga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
alter system set lock_sga=true scope=spfile;
ORACLE 9i 以后的版本,SGA是动态的。SGA中内存缓冲区,可动态的增减,ORACLE是利用所谓
的区组(granule)来管理SGA内存的。区组就是一片连续的虚拟内存区,是ORACLE分配和回收内存
的基本单位。在 v$sgainfo或v$sga_dynamic_components中可看到该大小。
ASMM
ORACLE中各部分内存的设置非常麻烦,为了减轻DBA的工作负责
ORACLE10G中引进了一个ASMM(自动共享内存管理),DBA只需初始化参数SGA_TARGET,
ORACLE就会自动对SGA的共享池、JAVA池、大池、数据库高速缓冲区进行自动调整。
但是无法自动调整DB_KEEP_CACHE_SIZE,DB_RECYCLE_CACHE_SIZE,LOG_BUFFER、流池。
sga_target为O(最大不能超过sga_max_size)时,则取消自动共享内存管理,各部分需要手工设定。
当然ASMM工作的前提是,statistics_level 为typical或 all,否则无收集MMAN进程所需的信息。
查看SGA大小:show parameter sga_max_size
动态分配后如何查看各内存区大小:
select * from v$sgainfo
select component,current_size from v$sga_dynamic_components;
select * from v$sgastat;
SGA空间的分配不是以MB为单位,而是以粒度 granule ['gr??nju:l] 为单位。通常如果SGA大小
不超过1G,此值大小一般为4M,如果大于1G(另一说为128M,但实际为1G),那么为16M。不同平台会有所变化,
WINDOWS中SGA大小超过1G,区组大小为8M。
<程序全局区〉
为每个用户进程连接Oracle数据库保留的内存
当进程创建时分配
进程结束后被释放
只能被一个进程使用
APMM(自动PGA内存管理):
参数PGA_AGGREGATE_TARGET指定PGA的总共大小 (启用的前提是workarea_size_policy为auto)
SQL> show parameter workarea_size_policy;
NAME TYPE VALUE
------------------------------------ ----------- -----------
workarea_size_policy string AUTO
当ORACLE创建一个服务器进程的同时,要为这个服务器进程分配一个内存区,该区就称为程序全
局区(PGA),与SGA不同,PGA是一个私有的内存区,不能共享,且只属于一个进程。它随进程的创建
而被分配,随进程的终止而被回收。在专用服务器进程的配置情况下,程序全局区包括了以下结构:
(1)排序区:用于处理SQL语句所需的排序
(2)游标状态区:用于指示会话当前所使用的SQL语句的处理状态
(3)会话信息区:包括了传话的用户权限和优化统计信息
(4)堆栈区:包括了其他的会话变量。
如果是共享服务器进程或多线程的配置,以上这些结构除了堆栈区外大部分都存在于SGA中,如果
有LARGE POOL,存放于Large pool中,否则它们会被存放于共享池中。
SQL> show parameter pga_a
NAME TYPE VALUE
------------------------------------ ----------- -------
pga_aggregate_target big integer 91M
SQL> alter system set pga_aggregate_target=100m;
sga_target 实例一启动便获取指定数量的内存
pga_aggregate_target 内存分配不够时,才获取
查看PGA详细情况,通过这两个查询可看到当前PGA的使用情况,PGA是否够用,从而为优化做参考:
select * from v$pgastat; --其中有PGA的命中率
select * from v$pga_target_advice;
注:要在系统运行了一段时间后,看才是最准确的,空闲时,可能都会显示够用的数据。
PGA最大大小为4G
查看每个用户的PGA分配情况:
SQL> select s.value,s.sid,a.username
from v$sesstat s,v$statname n,v$session a
where n.statistic#=s.statistic# and name='session pga memory'
and s.sid=a.sid order by s.value;
通过内存顾问查看当前最优的PGA设置:
SQL> desc v$pga_target_advice;
Name
---------------------------------
PGA_TARGET_FOR_ESTIMATE 估计的pga_aggregate_target的参数值
PGA_TARGET_FACTOR 与当前pga_aggregate_target的比值
ADVICE_STATUS 是否收集统计信息,以计算建议值,ON启用,OFF禁用
BYTES_PROCESSED 预估操作的字节数
ESTD_EXTRA_BYTES_RW 当pga_aggregate_target参数值为估计值是,需额外处理的字节数
ESTD_PGA_CACHE_HIT_PERCENTAGE 为估计值时,PGA的缓存命中率,计算公式为:
BYTES_PROCESSED/(BYTES_PROCESSED+ESTD_EXTRA_BYTES_RW)
ESTD_OVERALLOC_COUNT 为估计值时,需额外分配的PGA内存,为非0时,说明还太少。
SQL> SELECT ROUND(PGA_TARGET_FOR_ESTIMATE/1024/1024) "PGATARGET SIZE(M)",
ESTD_PGA_CACHE_HIT_PERCENTAGE "EST.CACHE HIT%",
ROUND(ESTD_EXTRA_BYTES_RW/1024/1024) "EST.RW(M)",
ESTD_OVERALLOC_COUNT "EST.OVER-ALLOC"
FROM V$PGA_TARGET_ADVICE;
PGATARGET SIZE(M) EST.CACHE HIT% EST.RW(M) EST.OVER-ALLOC
----------------- -------------- ---------- --------------
24 100 0 0
48 100 0 0
96 100 0 0
143 100 0 0
191 100 0 0
229 100 0 0
267 100 0 0
306 100 0 0
344 100 0 0
382 100 0 0
573 100 0 0
764 100 0 0
1146 100 0 0
1528 100 0 0
要求是第二列(办求最大)和第三列(力求最小)之间的平衡,而获取到的第一列值,
从当前看PGA 24M 已足够,再多也是浪费。
For OLTP 系统
PGA_AGGREGATE_TARGET = (<Total Physical Memory > * 80%) * 20%
For DSS 系统
PGA_AGGREGATE_TARGET = (<Total Physical Memory > * 80%) * 50%
内存信息的获取:
SHOW PARAMETER 显示所有参数信息
SHOW PARAMETER SGA_MAX_SIZE 显示单个参数值
SELECT * FROM v$parameter
SELECT * FROM v$spparameter
ORACLE执行SQL查询语句的步骤:
SQL语句的执行主要由用户进程与服务器进程来完成,其他的一些进程可能要辅助完成这一过程。
查询语句与其它的SQL语句有所不同,如果一个查询语句执行成功,就要返回查询的结果,而其它
的SQL语句只是返回执行成功或失败的信息。查询语句的处理主要包括以下3个阶段:编译、执行和
提取数据。
编译:在进行编译时,服务器进程会将SQL语句的正文放入共享池的库高速缓存中并将完成以下处理。
首先在共享池中搜索是否有相同的SQL语句(正文),如果没有就进行后续处理。
检查该SQL语句的语法是否正确
通过查看数据字典来检查表和列的定义
对所操作的对象加编译锁,以便在编译语句期间这些对象的定义不能被改变
检查所引用对象的用户权限
生成执行该SQL语句所需的优化执行计划
将SQL语句和执行计划装入共享的SQL区
执行
提取数据
〈进程结构〉
Oracle有以下几种进程:
用户进程: 在用户连接数据库时产生
服务器进程: 当连接到Oracle实例并且用户建立会话的时候产生
后台进程: Oracle实例启动的时候产生
<用户进程>
用户进程是要与 Oracle 服务器交互的一种进程
必须要先建立一个连接
不会直接和Oracle服务器联系
<服务器进程>
服务进程是直接和 Oracle 服务器交互的一段程序.
它响应用户要求,向服务器发起呼叫并返回结果给用户.
可用专用服务器模式,也可用共享服务器模式创建对话.
<后台进程>
维持物理和内存之间的关联:
必须要有的后台进程:
DBWn(数据库写) PMON(进程监控) CKPT(检查点)
LGWR(日志写) SMON(系统监控)
可选的后台进程:
ARCn(归档)
RECO(恢复)
Snnn
Pnnn
查看数据库中有哪些后台进程:
select * from v$bgprocess where paddr<>'00';或 ps -ef | grep ora_
数据库写进程 (DBWn),负责把缓冲区的数据写到磁盘,通常一个DBWn能满足需求,为提高性能,可
配置多个DBWn进程。
DBWn的触发条件:
发生检查点
脏缓存到达限制
没有自由的缓存
超时发生
RAC ping请求
表空间离线
表空间只读
表被删除或者截断
开始备份表空间
增加数据库写进程数量(最多10个,基于CPU数量的,设多了,也只一个进程有效):
vim pfile
DB_WRITER_PROCESSES=x 或 alter system set DB_WRITER_PROCESSES=3 scope=spfile;
日志写进程 (LGWR):
LGWR写的条件:
提交的时候
达到三分之一满了
日志的大小到1M
每隔三秒
在DBWn进程写之前
系统监测进程 (SMON):
作用:
实例恢复
前滚所有重做日志中的改变(即数据已经提交了,但还没往数据文件里写完就断电了,要重新应用一次日志)
打开数据库为了用户能访问
回滚没有提交的事务
接合自由的空间
释放临时表空间
进程监测进程 (PMON):
清除失败的进程:
回滚事务、释放锁、释放其他资源
重启死 dispatchers
检查点进程 (CKPT):
作用:
给信号给DBWn
更新数据文件头
更新控制文件
如何将检查点信息写入警告文件:
alter system set log_checkpoints_to_alert=true;
归档进程 (ARCn):
可选的后台进程
当启用归档方式后自动归档重做日志文件
修改归档进程数量:
alter system set log_archive_max_processes = 4;
<数据库逻辑结构>
表明数据库的物理空间如何使用
分为表空间,段,范围,块
修改系统进程数量:
不能动态修改(不会报错,但也不会起作用,要修改参数文件,再将参数文件转换成服务器参数文件)
vim pfile
processes=300
create spfile from pfile
提高oracle的最大会话数
oracle最大会话数由初始化能数processes控制的,并不是由sessions控制,初始化参数sessions
的值由processes衍生出来的session默认等于 1.1 * processes +5
show parameter session
查当前有哪些会话:
select * from v$session where type='USER';
还可查用户test从哪台机器登录的:
杀掉一个进程:
alter system kill session 'SID,SERIAL#';
杀掉后server为pseudo
最好用操作系统来杀kill当一个活动的进程正在执行网络I/O、回滚事务、导入导出等操作时,不能杀掉的,只有等这些操作完成后,才能杀掉
查看ORACLE进程与操作系统进程的对应关系
v$process视图中有关于每个进程的信息,包括其操作系统进程号
SQL> select addr,pid,spid,username,program from v$process;
ADDR PID SPID USERNAME PROGRAM
-------- ---------- ------------ --------------- ------------------------------------------------
5E61628C 1 PSEUDO --数据库的初始化进程
5E616840 2 3405 oracle oracle@James007.ycdata.net (PMON)
5E616DF4 3 3407 oracle oracle@James007.ycdata.net (PSP0)
5E6173A8 4 3409 oracle oracle@James007.ycdata.net (MMAN)
5E61795C 5 3411 oracle oracle@James007.ycdata.net (DBW0)
5E617F10 6 3413 oracle oracle@James007.ycdata.net (LGWR)
5E6184C4 7 3415 oracle oracle@James007.ycdata.net (CKPT)
5E618A78 8 3417 oracle oracle@James007.ycdata.net (SMON)
5E61902C 9 3419 oracle oracle@James007.ycdata.net (RECO)
5E6195E0 10 3421 oracle oracle@James007.ycdata.net (CJQ0)
5E619B94 11 3423 oracle oracle@James007.ycdata.net (MMON)
5E61A148 12 3425 oracle oracle@James007.ycdata.net (MMNL)
5E61A6FC 13 3427 oracle oracle@James007.ycdata.net (D000)
[oracle@James007 ~]$ ps -ef |grep ora_
oracle 3405 1 0 09:19 ? 00:00:00 ora_pmon_orcl
oracle 3407 1 0 09:19 ? 00:00:00 ora_psp0_orcl
oracle 3409 1 0 09:19 ? 00:00:00 ora_mman_orcl
oracle 3411 1 0 09:19 ? 00:00:00 ora_dbw0_orcl
oracle 3413 1 0 09:19 ? 00:00:00 ora_lgwr_orcl
oracle 3415 1 0 09:19 ? 00:00:00 ora_ckpt_orcl
oracle 3417 1 0 09:19 ? 00:00:01 ora_smon_orcl
oracle 3419 1 0 09:19 ? 00:00:00 ora_reco_orcl
oracle 3421 1 0 09:19 ? 00:00:00 ora_cjq0_orcl
oracle 3423 1 0 09:19 ? 00:00:01 ora_mmon_orcl
oracle 3425 1 0 09:19 ? 00:00:00 ora_mmnl_orcl
oracle 3427 1 0 09:19 ? 00:00:00 ora_d000_orcl