2022年我的oraclei学习日志归纳 .pdf

《2022年我的oraclei学习日志归纳 .pdf》由会员分享，可在线阅读，更多相关《2022年我的oraclei学习日志归纳 .pdf（18页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Oracle 内存结构 (01)-实例、数据库与SGA 1. 实例与数据库实例 (Instance)：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 18 页 - - - - - - - - - 数据库启动后在内存中的映像，管理数据库正常运行；在 OS中以 ORACLE_SID 标识，在 DB中以 Instance_name 标识；组成：（ System Global Area + Background Process）；查看实例名：$ set | grep -i ora

2、cle_sid SQL show parameter instance_name 数据库 (Database) ：实例所存取的一个数据库文件的集合；在 DB中以 db_name标识；组成： ( 初始化参数文件，控制文件，数据文件，日志文件等) 查看数据库名：SQL show parameter db_name; 2.SGA(System Global Area)SGA是一组包含着一个Oracle 实例的数据库和控制信息的共享内存结构。是为 oracle系统分配的共享内存；是实例的主要组成部分；是影响数据库性能的重要因素；随实例的启动 /关闭而被分配 / 回收；SGA自动对所包含的各种缓冲自动进

3、行动态管理；SGA主要包含 6 类缓存：1.db_buffer_cache 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 18 页 - - - - - - - - - 2.shared_pool 3.large_pool 4.java_pool 5.redo_log_buffer 6.streams_pool 查看 SGA ：SQL show sga; SQL select * from v$sga; SGA相关参数：pre_page_sga ：启动时是否将全部SGA都

4、绑定到物理内存，而不使用交换分区。lock_sga ：运行过程中是否也将全部SGA都绑定到物理内存，而不使用交换分区。sga_max_size ：SGA总大小上限值。sga_target： 启用自动共享内存管理ASMM(Automatic Shared Memory Management)， 指定分配给db_buffer_cache、shared_pool 、 java_pool 、large_pool这四项内存大小之和。注意： sga_max_size 可以改大，改小则受sga_target的限制。另外，sga_target应尽量接近sga_max_size 。当 sga_target小于

5、sga_max_size 时，上述指定的四项动态缓存合计值以sga_target为实际上限。如果sga_target被修改为大于sga_max_size 的值或 sga_max_size 小于 sga_target时，则数据库在下次启动时sga_max_size 值会自动调整为与sga_target值相等。调整 sga_target前，应先查看四项缓存默认值是否都0，若为 0 表示可由 ASSM 在 0 到 sga_target值所规定的区间内自动调整各缓存区大小。查看：SQL show parameter db_cache_size; SQL show parameter shared_po

6、ol_size; SQL show parameter large_pool_size; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 18 页 - - - - - - - - - SQL show parameter java_pool_size; 更改：SQL alter system set db_cache_size=0 scope=both/memory/spfile; 若某项缓存区大小默认设置不为0，而为某一具体值，代表ASMM 在动态调整各缓存区时，该缓冲

7、区不能分配低于该指定值的大小。SGA内存按照颗粒度大小granule_size分配内存，若手工分配内存大小非整数倍于granule_size，则自动凑为granule_size的整数倍大小，常见granule_size如下：若 SGA1G,granule_size=8M(for WIN)或 16M(for Other)；redo_log_buffer以512K 为粒度分配内存。查看 SGA粒度大小：SQL select bytes from v$sgainfo where name=Granule Size; 相关操作：SQL show sga Total System Global Area

8、 201326592 bytes Fixed Size 1218532 bytes Variable Size 83888156 bytes Database Buffers 113246208 bytes Redo Buffers 2973696 bytes Fixed Size：固定值，包括一些开销等其他空间大小。是shared pool内 dictionary cache的一部分与其他部分附加内存信息（如数据库和实例的状态信息）会被实例的后台进程所访问，在实例启动后即被固定在SGA中，是 SGA中固定组件（在编译oracle数据库本身时就固定在其中），大小不变，故该部分缓存又称为固定SG

9、A （Fixed SGA）。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 18 页 - - - - - - - - - Variable Size：可用大小。为sga_max_size log_bufferdb_buffer_cachefixed_size，剩下的包含 Library cache、Java pool 、 Large pool 、cursor area、control file content等缓存区大小。Database Buffers：db_buffe

10、r_cache实际大小。Redo Buffers：redo_buffer实际大小。SQL select current_size from v$buffer_pool; #查看 data_buffer_cache实际大小（单位M ）。SQL select pool,sum(bytes) from v$sgastat group by pool; #查看 java pool, streams pool,shared pool,large pool实际大小。SQL alter system set sga_target=120M scope=both; #修改 sga_target自动管理缓冲区合

11、计值，在sga_max_size 范围内调整可动态生效。各缓存区大小也将自动调整以适应修改后的sga_target总值。SQL show parameter sga_targe; SQL select current_size from v$buffer_pool; SQL select pool,sum(bytes) from v$sgastat group by pool; #查看修改，可见已跟随sga_target而变化，注意sga 的值是 granule的整数倍。SQL alter system set sga_target=400M scope=both; alter system

12、set sga_target=400M scope=both * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-00823: Specified value of sga_target greater than sga_max_size #若修改 sga_target值大于 sga_max_size ，则 sga_target只能作为静态参数修改：SQL alter system set sga_target=400M scope=spfile; S

13、QL alter system set sga_max_size=200M scope=spfile; #修改 sga_max_size 值，静态参数，重启生效。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 18 页 - - - - - - - - - SQL alter system set large_pool_size=10M scope=memory; #修改制定缓冲区最小值。若想在ASMM 管理下，为某缓存区规定一个最小分配下限值（指示ASMM 该区的分配不能

14、低于该指定值），则可通过设定默认值来达到目的。SQL select pool,sum(bytes) from v$sgastat group by pool; #查看，可见修改已生效。未完，待续。Oracle 内存结构 (02)-缓冲区1、Data_buffer_cache用来保存频繁访问的数据的内存缓冲区域，以便于oracle用户进程需访问的某块数据可以首先到data_buffer_cache中来寻找能命中（ cache hit），从而大大减少从磁盘读取数据所需要的IO 时间。两个不同的列表：待写列表（ write list）：包含已被修改的，但尚未写入磁盘的脏缓存块的指示列表。最近最少使用

15、（ Least recently used/LRU list）：包含空闲缓存块、命中缓存块，及还未移入待写列表的脏缓存块的指示列表。四个不同缓冲池：Default pool Keep pool Recycle pool DB_nk_cache_size 查看 data_buffer_cache：SQL select current_size from v$buffer_pool; #查看当前分配大小。SQL show parameter db%cache_size; #查看最低分配限制（ ASMM 下）(1)Default pool名师资料总结 - - -精品资料欢迎下载 - - - - -

16、- - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 18 页 - - - - - - - - - 数据库对象未特别指定时所使用的默认缓冲池。分别以不同算法处理缓冲区数据，使缓冲数据能尽长或尽短时间地占用default pool缓冲区。工作方式：当 oracle进程访问一块缓冲区后，会将该缓冲区移动到LRU列表的 MRU 端（下图中的 hot area），随着更多被访问的缓冲区移动到LRU列表的 MRU 端， 较早前被访问过的缓冲区就会逐渐向LRU列表的 LRU端（下图中的Cold area ）移动。当用户进程执行全表扫描时，数据首次将会

17、从磁盘被读入内存缓冲区，之后将该缓冲区移动到LRU列表的 LRU端，以使这些通常只是暂时需要的全表扫描数据所占用的缓冲区能被尽快地移出数据缓冲区，为其他使用频率更高的数据块腾出空间。查看当前分配的大小： SQL select component,current_size from v$sga_dynamic_components where component=DEFAULT buffer cache; 或 SQL select name,current_size from v$buffer_pool; 如何指定对象使用default pool缓冲区：默认使用default pool。测试 d

18、efault pool使用效果：SQL shutdown immediate SQL startup #重启数据库，清空缓存。SQL show parameter db%cache_size #查看 data_buffer_cache大小。SQL conn system/oracle #以非 sysdba 用户登录。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 18 页 - - - - - - - - - SQL create table t1 as select *

19、from dba_objects; #创建测试表。SQL select object_name,a.status,count(*) from v$bh a,user_objects b 2 where a.objd=b.object_id and object_name in (T1) group by object_name,a.status; OBJECT_NAME STATUS COUNT(*) - - - T1 xcur 1 #查看指定对象在default pool中所占用的缓冲区块数目。SQL set autotrace on statistics #开启显示统计信息功能。SQL s

20、elect count(*) from t1; #全表扫描。 COUNT(*) - 49807 Statistics - 28 recursive calls 0 db block gets 761 consistent gets 684 physical reads #有物理读。 0 redo size 413 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk

21、) 1 rows processed 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 18 页 - - - - - - - - - SQL select count(*) from t1; #再次全表扫描。 COUNT(*) - 49807 Statistics - 0 recursive calls 0 db block gets 689 consistent gets 0 physical reads #无物理读。 0 redo size 413 bytes sent

22、 via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL select object_name,a.status,count(*) from v$bh a,user_objects b 2 where a.objd=b.object_id and object_name in (T1) group by object_name,a.status;

23、 OBJECT_NAME STATUS COUNT(*) - - - T1 xcur 685 #查看指定对象在default pool中所占用的缓冲区块数目。（2）Keep pool名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 18 页 - - - - - - - - - 将存储在该缓存的数据一直保存在缓存中。该缓存不参与ASMM 的动态管理，不能自动调整大小。默认未启用，大小为0. 手工修改指定值后，default pool的空间将被相应的消减。经常访问的热点表应该专

24、门放入keep pool ，以防止在默认情况下所有对象都使用default pool时，一个大对象临时调入default pool将其中的真正热点数据挤出default pool带来的性能损失。工作方式：与 default pool 不同，新读入的数据不断以先进先出的方式从LRU表的 MRU 端移动到 LRU端，直到无空闲缓冲时， LRU端最久未使用的数据被调出缓冲区。查看当前分配的大小：SQL select component,current_size from v$sga_dynamic_components 2 where component=KEEP buffer cache; SQL

25、 select name,current_size from v$buffer_pool; 修改当前分配大小：SQL alter system set db_keep_cache_size=10M scope=both; SQL show parameter db_keep_cache SQL select component,current_size from v$sga_dynamic_components 2 where component=KEEP buffer cache; SQL select name,current_size from v$buffer_pool; #可见 de

26、fault pool相应地减少了。指定对象使用keep pool ：SQL create table t_keep(n int) storage(buffer_pool keep); SQL alter table t1 storage(buffer_pool keep); （3）Recycle pool随时清除存储在其中不再被用户需要的数据，该缓存不参与ASMM 的动态管理，不能自动调整大小。默认未启用，大小为0. 手工修改指定值后， default pool的空间将被相应的消减。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - -

27、 名师精心整理 - - - - - - - 第 10 页，共 18 页 - - - - - - - - - 不常访问的大对象应该专门放入recycle pool，以防止一个大对象临时调入default pool，而将其中真正的热点表挤出default pool带来的性能损失。工作方式：与 default pool和 keep pool 都不同，对于recycle pool，先进入缓存的表被保留在recycle pool中。 recycle pool满了以后，读取的数据将不被缓存。查看当前分配的大小：SQL select component,current_size from v$sga_dyn

28、amic_components 2 where component=RECYCLE buffer cache; SQL select name,current_size from v$buffer_pool; 修改当前分配大小：SQL alter system set db_recycle_cache_size=10M scope=both; SQL show parameter db_recycle_cache SQL select component,current_size from v$sga_dynamic_components 2 where component=RECYCLE b

29、uffer cache; SQL select name,current_size from v$buffer_pool; 指定对象使用keep pool ：SQL create table t_rec(n int) storage(buffer_pool recycle); SQL alter table t1 storage(buffer_pool keep); 2、shared pool共享池是 SGA中最关键的内存片段，特别是在性能和可伸缩性上。一个太小的共享池会降低性能，使系统停止；太大的共享池也会有同样的效果，将会消耗大量的CPU来管理这个共享池。shared pool分为 lib

30、rary_cache和 data dictionary cache两个主要的部分及近600 项其余部分。查看 shared_pool 参数默认设置值，为0 则表示 ASMM 已开启：SQL show parameter shared_pool_size; 查看 shared_pool 当前运行期间所分配大小：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 18 页 - - - - - - - - - SQL select pool,sum(bytes) from v$s

31、gastat group by pool; 查看 shared_pool 中所有组成部分：SQL select * from v$sgastat; SQL select pool,sum(bytes) from v$sgastat group by pool; （1）Library Cache 用户提交一个新SQL语句时， Oracle 会分析（ parse ）该句 SQL （硬解析），这个过程将耗费相对较多的时间。分析完毕后，oracle会将该 SQL的分析结果给保存在Library Cache中，当数据库再次执行该 SQL时，oracle将直接取第一次分析结果而不再重新解析，从而减少运行时

32、间。Library Cache四个组成部分：共享 SQL区：保存语句文本，编译后的语法分析树及执行计划。私有 SQL区：保存语句中的变量值。共享 PL/SQL区：保存 PL/SQL语句。控制结构区：保存锁等控制信息。查看 library cache的大小：SQL select sum(sharable_mem) from v$db_object_cache; 测试 library cache的作用：SQL set timing on SQL select count(*) from dba_objects; COUNT(*) - 49809 Elapsed: 00:00:00.20SQL se

33、lect count(*) from dba_objects; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 12 页，共 18 页 - - - - - - - - - COUNT(*) - 49809 Elapsed: 00:00:00.10查看保存在 library cache中保存的已分析的SQL语句：SQL select * from v$sqltext where sql_text like %dba_object%; SQL select sql_text from v

34、$sqlarea where sql_text like %dba_object%; 绑定变量：绑定变量的使用：SQL select object_id,object_name from dba_objects where object_id=5100; SQL select object_id,object_name from dba_objects where object_id=5101; SQL variable i number; #定义变量。SQL exec :i:=5100; #给变量赋值。SQL print :i #打印变量值。SQL select object_id,obje

35、ct_name from dba_objects where object_id=:i; SQL exec :i:=5101; SQL select object_id,object_name from dba_objects where object_id=:i; SQL select sql_text,parse_calls from v$sqlarea where sql_text like %object_id%; SQL_TEXT - PARSE_CALLS - select object_id,object_name from dba_objects where object_id

36、=:i #缓存的 sql语句。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 13 页，共 18 页 - - - - - - - - - 2 #被调用次数。select sql_text,parse_calls from v$sqlarea where sql_text like %object_id% 1 select object_id,object_name from dba_objects where object_id=5100 1 select object_id,obj

37、ect_name from dba_objects where object_id=5101 1 #可见使用绑定变量可以增加sql 语句缓存的命中率，减少硬解析所带来的性能损失。Data Dictionary Cache专供数据字典使用的缓冲区，SQL语句分析过程中需要大量访问系统数据字典（从磁盘数据文件中访问数据字典成为recursive calls ），即可使用该缓存区专门存放数据字典，从而避免过多的recursive calls所带来的性能损失。数据字典是关于数据库的参考信息、数据库的结构信息、数据库的用户信息等各类信息描述的一组表和视图的集合。数据字典用于描述所有数据库对象的数据库对象

38、的集合。SQL select * from v$fixed_table; #查看系统中所有的动态表。SQL select * from dict; #查看数据字典。SQL select object_name,object_type,created from dba_objects where object_name in (TEST,T1); #从数据字典的 dba_objects 表中查询指定数据库对象的相关描述。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 14 页，共 18

39、 页 - - - - - - - - - SQL select * from dba_users; #从数据字典 dba_users 表中查询数据库用户的相关描述。SQL select * from dba_data_files; #从数据字典 dba_data_files表中查询所有数据库文件的相关描述。数据字典中表的分类：静态表：对各类数据库对象的各类属性的描述，常见的有下面三类字母开头：1.dba_*：存储当前数据库中所有数据库对象的描述2.all_*：存储当前用户能够访问的数据对象的描述3.user_* ：存储当前用户所拥有的数据库对象的描述动态表：不断动态更新以反映数据库当前运行状况

40、，常见以“v$”开头。查看 data dictionary cache的大小：SQL select sum(sharable_mem) from v$sqlarea; 测试的 data dictionary cache作用：SQL set autot on stat #打开统计信息显示。SQL select count(*) from dba_source; #查询一张表。 COUNT(*) - 292167 Statistics - 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第

41、15 页，共 18 页 - - - - - - - - - 312 recursive calls #recursive calls 次数。 0 db block gets 1998 consistent gets 698 physical reads 0 redo size 413 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 1 rows proces

42、sed SQL select count(*) from dba_source; #再次查同一张表。 COUNT(*) - 292167 Statistics - 0 recursive calls #可见 recursive calls 次数明显减少。 0 db block gets 1927 consistent gets 0 physical reads 0 redo size 413 bytes sent via SQL*Net to client 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - -

43、 - - - - 第 16 页，共 18 页 - - - - - - - - - 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 3、Large pool large_pool用来分配大内存块，来处理比 shared pool 更大的内存，实际常用做备用池， 用以缓解 oracle对共享池和 PGA 区内存的使用压力。使用large pool的对象主要有：多线程服务器MTS ：在 SGA

44、的 large_pool中分配 UGA; 语句的并行查询Parallel Exection：用作进程间的消息缓冲器；恢复管理器 RMAN ：备份时用作磁盘I/O 缓冲区。SQL show parameter large_pool #查看 large_pool参数默认值，为0 则应表示 ASMM已开启。SQL select pool,sum(bytes) from v$sgastat group by pool; # 查看 large_pool当前运行期间ASMM 实际分配大小。4、Java pool oracle在内核中加入了对java 的支持。该缓冲区转为java 开发和应用所设， 若不用

45、java 程序则没必要改变该缓冲区的默认大小。SQL show parameter java_pool #查看 java_pool参数默认值，为0 则应表示 ASMM 已开启。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 17 页，共 18 页 - - - - - - - - - SQL select pool,sum(bytes) from v$sgastat group by pool; # 查看 java_pool当前运行期间ASMM 实际分配大小。5、redo_log_bu

46、ffer 对数据库的任何修改都按顺序被记录在该缓冲区，然后由 LGWR 进程根据条件将更改信息批量写入磁盘上的 redo log文件，以节省磁盘IO。该缓存不参与ASMM 的动态管理，不能自动调整大小。SQL select name,bytes from v$sgastat where name=log_buffer; #查看 log_buffer实际大小。SQL show parameter log_buffer #查看 log_buffer预设大小。SQL alter system set log_buffer=2000000 scope=spfile; #手工修改 log_buffer大小，需重启。6、streams_buffer 用于对流复制进行缓冲。SQL show parameter streams_pool #查看 streams_pool参数默认值。oracle物理结构准备中。本文出自“冰冷的太阳 ” 博客，请务必保留此出处http:/ - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 18 页，共 18 页 - - - - - - - - -