OCP读书笔记(13) - 管理内存-白红宇

OCP读书笔记(13) - 管理内存

阅读量：6167 次

发布时间：2019-06-21

本文共 22417 字，大约阅读时间需要 74 分钟。

SGA

1. 什么是LRU

LRU表示Least Recently Used，也就是指最近最少使用的buffer header链表

LRU链表串联起来的buffer header都指向可用数据块

2. 什么是检查点队列

就是将脏块按照修改的时间顺序排列

3. 什么是mman

Memory Manager (MMAN）内存管理进程

一：buffer cache：

1.作用：缓存最近使用过的数据块

2.管理方式：LRU和检查点队列

3.buffer的状态：

已连接：

当前正将该块读入高速缓存或正在写入该块，其它会话正等待访问该块

干净的：

该缓冲区目前未连接，如果其当前内容（数据块）将不再引用，则可以立即执行过期处理。这些内容与磁盘保持同步，或者缓冲区包含块的读一致性快照

空闲/未使用：

缓冲区因实例刚启动而处于空白状态，此状态与“干净的”状态非常相似，不同之处在于缓冲区未曾使用过

脏的：

缓冲区不再处于连接状态，但内容（数据块）已更改，因此必须先通过 DBWn 将内容刷新到磁盘，然后才能执行过期处理

4.非标准块缓存区：DB_nK_CACHE_SIZE

5.使用多个缓冲池：default、recyclebin、keep

keep：DB_KEEP_CACHE_SIZE

recyclebin：DB_RECYCLE_CACHE_SIZE

default：DB_CACHE_SIZE

注：保留池或循环池中的内存不是默认缓冲区池的子集

创建一个表，使用keep池：

create table test_k(id number,name varchar2(10)) storage (buffer_pool keep);

创建一个表使用recycle池：

create table test_r(id number,name varchar2(10)) storage(buffer_pool recycle);

更改某个表的缓存池：

alter table test_k storage(buffer_pool recycle);

创建索引，指定keep池：

create index ind_test on test_k(id) storage(buffer_pool keep);

更改索引的缓存池：

alter index ind_test storage(buffer_pool recycle);

查看某个表的缓存池：

select table_name,buffer_pool from user_tables;

查看索引的缓存池：

select index_name,buffer_pool from user_indexes;

6.如何正确设置buffer cache的大小：

我们可以使用建议：

select size_for_estimate, size_factor, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads

from v$db_cache_advice where name='DEFAULT' and block_size=(select value from v$parameter where name='db_block_size')

这里的字段 ESTD_PHYSICAL_READ_FACTOR表示在相应buffer cache的尺寸（由字段SIZE_FOR_ESTIMAT表示）下，估计从硬盘里读取数据的次数除以在内存里发生的逻辑读总次数，如果在内存里逻辑读没有引起物理读，则该比值为空，在内存足够的前提下，这个值应该是越低越好，从以上的输出我们可以看出，当buffer cache为200M的时候，估计产生的物理读是当前buffer cache尺寸下的1.014 倍，也就是增加了（1.014 -1）1.4%左右的物理读而设成280M的时候，与当前的buffer cache尺寸相比，物理读没有增加，而当前的buffer_cache的大小为412M，所以应该将buffer cache设置为240M

7.清空buffer cache里缓存的数据块：alter system flush buffer cache;

二、shared pool

1.组成部分以及各个部分的功能：

由三部分组成：

library cache：缓存最近访问过的sql语句pl/sql的语句文本，执行计划---------->解析后生成的游标

游标分为父游标和子游标，父游标是指sql语句的文本，子游标是指执行计划

举例说明不能共享的原因：

创建实验表：

grant select on dba_objects to scott;

conn scott/tiger

create table shared_test as select * from dba_objects;

清空shared pool：alter system flush shared_pool;

定义绑定变量，并为绑定变量赋值，然后执行查询语句：

variable v_object_id number;

exec :v_object_id := 51148

select object_id,object_name from shared_test where object_id=:v_object_id

接下来，定义一个字符型的绑定变量，变量名与前面的相同，为该绑定变量赋予一个字符型的值后执行一个查询：

variable v_object_id varchar2(10);

exec :v_object_id:='51148';

select object_id,object_name from shared_test where object_id=:v_object_id

在v$sqlarea里找到该sql语句的父游标的信息如下：

select sql_text,version_count from v$sqlarea where sql_text like '%shared_test%';

发现version_count为2，说明有两个子游标

在v$sql里找到该sql语句子游标的信息：

select sql_text,child_address,address from v$sql where sql_text like '%shared_test%';

从v$sql里我们可以看出该sql文本确实有两条，而且sql文本的地址也是一样的，但是子游标的地址

不一样

由此我们可以看出，很多因素可能导致sql语句不能共享，常见的因素包括：sql文本大小写不一致，

sql语句绑定变量的类型不一致等

dictionary cache：缓存最近访问过的数据字典

uga：包含会话信息（如果使用共享服务器）

2.通过顾问，设置shared_pool的大小：

select

shared_pool_size_for_estimate "SP",

shared_pool_size_factor "SF",

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;

第一列显示oracle所估计的shared pool的尺寸值，其他列表示在该估计的shared pool大小下所表现出来的指标值，我们主要关注estd_lc_time_saved_factor的值，当该列值为1时，表示再增加shared pool的大小对性能的提高没有意义，对于上列来说144M是最佳大小，对于设置比144M更大的shared pool来说，就是浪费空间

shared_pool_size_for_estimate：估算的共享池大小（m为单位）

shared_pool_size_factor 估算的共享池大小与当前大小比

estd_lc_memory_objects 估算共享池中库缓存的内存对象数

estd_lc_size 估算共享池中用于库缓存的大小（M为单位）

estd_lc_time_saved 估算将可以节省的解析时间。这些节省的时间来自于请求处理一个对象时

重新将它载入共享池的时间消耗和直接从库缓存中读取的时间消耗的差值

estd_lc_time_saved_factor 估算的节省的解析时间与当前节省解析时间的比

estd_lc_memory_object_hits 估算的可以直接从共享池中命中库缓存的内存对象的命中次数

三、SGA管理方式，分为自动管理和手工管理

自动管理（ASMM）：

1.自动共享内存管理的优势：

（1）自动根据工作量变化调整

（2）最大程度地提高内存利用率

（3）有助于消除内存不足的错误

2.如何设置自动共享内存管理：

设置参数sga_target为非0值，例如：alter system set sga_target=272M;

statistics_level设置为typical或all

sga_target的值不能超过sga_max_size的大小

查看sga各个组件的大小：

select * from v$sgainfo;

确定 SGA 中自动优化的组件的实际大小：

col component for a30

select component,

current_size/1024/1024 current_size ,

min_size/1024/1024 min_size,

max_size/1024/1024 max_size,

last_oper_type

from v$sga_dynamic_components;

3.禁用自动共享内存管理：

设置参数sga_target=0，例如：alter system set sga_target=0;

监视和管理内存

1. 调整数据库参数

shutdown immediate

用参数文件initorcl.ora设置启动数据库,initorcl.ora的内容如下：

background_dump_dest='/u01/app/oracle/admin/orcl/bdump'compatible='10.2.0.1.0'control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl','/u01/app/oracle/oradata/orcl/control03.ctl'core_dump_dest='/u01/app/oracle/admin/orcl/cdump'db_block_size=8192db_cache_size=100mdb_domain='oracle.com'db_file_multiblock_read_count=16db_name='orcl'db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'db_recovery_file_dest_size=4294967296dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'fast_start_mttr_target=30java_pool_size=4mjob_queue_processes=2large_pool_size=4mlog_checkpoint_timeout=0open_cursors=300pga_aggregate_target=25165824processes=150remote_login_passwordfile='EXCLUSIVE'shared_pool_size=100msort_area_size=65536undo_management='AUTO'undo_tablespace='UNDOTBS1'user_dump_dest='/u01/app/oracle/admin/orcl/udump'parallel_max_servers=64parallel_adaptive_multi_user=FALSEsga_max_size=300msga_target=0

View Code

startup pfile=$ORACLE_HOME/dbs/initorcl.ora

2. 创建java存储过程如：

vi /u01/java.sql

CONNECT hr/hrset echo onDECLAREi NUMBER;v_sql VARCHAR2(200);BEGINFOR i IN 1..200 LOOP-- Build up a dynamic statement to create a uniquely named java stored proc.-- The "chr(10)" is there to put a CR/LF in the source code.v_sql := 'create or replace and compile' || chr(10) ||'java source named "SmallJavaProc' || i || '"' || chr(10) ||'as' || chr(10) ||'import java.lang.*;' || chr(10) ||'public class Util' || i || ' extends Object' || chr(10) ||'{ int v1=1;int v2=2;int v3=3;int v4=4;int v5=5;int v6=6;int v7=7; }';EXECUTE IMMEDIATE v_sql;END LOOP;END;/

View Code

SQL> @/u01/java.sql

由于java pool太小，所以报错

3. 查看自动共享内存管理是否打开，java pool的大小

SQL> show parameter sga_target

SQL> show parameter java_pool_size

4. 执行以下脚本，查看当前内存，以及内存组件的大小：

vi /u01/memory.sql

PROMPT *** Current parameter settings ***col name format a12col value format a8show parameter sga_PROMPTPROMPT *** SGA Dynamic Component Size Information***col component format a22col current_size format a15col min_size format a15SELECT component,current_size/1048576||'M' current_size,min_size/1048576||'M' min_sizeFROM v$sga_dynamic_componentsWHERE component IN ('shared pool','large pool','java pool','DEFAULT buffer cache');col name format a20col value format a20PROMPT *** Current parameter settings in V$PARAMETER ***SELECT name, value, isdefault FROM v$parameterWHERE name IN ('shared_pool_size','large_pool_size','java_pool_size', 'db_cache_size');

View Code

SQL> conn /as sysdba

SQL> @/u01/memory.sql

5. 使用自动共享内存管理

SQL> alter system set sga_target = 300M;

System altered.

6. 查看内存组件的大小：

SQL> connect / as sysdba

SQL>@/u01/memory.sql

7. 重新执行/u01/java.sql

SQL> conn /as sysdba

SQL> @/u01/java.sql

PL/SQL procedure successfully completed.

执行成功，因为使用了自动共享内存管理，oracle能够根据数据库的负载自动调内存组件的大小

查看内存组件的大小：

SQL> conn /as sysdba

SQL> @/u01/memory.sql

8.实验完毕，删除java存储过程，重启数据库

执行以下脚本，删除之前创建的java存储过程

vi /u01/java_drop.sql

connect hr/hrset echo onDECLAREi NUMBER;v_sql VARCHAR2(200);BEGIN　　FOR i IN 1..200 LOOP　　　　v_sql := 'drop java source"SmallJavaProc' || i || '"';　　　　EXECUTE immediate v_sql;　　end loop;end;/

View Code

conn /as sysdba

SQL> @/u01/java_drop.sql

PL/SQL procedure successfully completed.

conn /as sysdba

startup force

PGA

1. pga的结构：专用 SQL 区、游标和 SQL 区、SQL工作区、会话内存

2. pga的管理模式

pga有两种管理模式：手动和自动

自动管理设置：1.pga_aggregate_target

2. workarea_size_policy设置为auto

手动管理：1. workarea_size_policy设置为manual

2. 手动设置工作区的大小，设置以下参数：

SORT_AREA_SIZE

HASH_AREA_SIZE

BITMAP_MERGE_AREA_SIZE

CREATE_BITMAP_AREA_SIZE

3.SQL工作区的类型：

optimal尺寸：SQL语句能够完全在所分配的SQL工作区内完成所有的操作，这时的性能最佳

onepass尺寸：SQL语句需要与磁盘上的临时表空间交互一次才能够在所分配的SQL工作区中完成所有的操作

multipass尺寸：由于SQL工作区过小，从而导致SQL语句需要与磁盘上的临时表空间交互多次才能完成所有的操作，这个时候的性能急剧下降

查看某个会话的工作区使用：

select a.statistic#, sid, name, value

from v$sesstat a,v$statname b

where a.statistic#=b.statistic# and b.name like '%workarea executions%' and a.sid=&sid;

查看当前会话的工作区使用：

select a.statistic#, sid, name, value

from v$sesstat a,v$statname b

where a.statistic#=b.statistic# and b.name like '%workarea executions%';

查看某个会话的pga使用情况：

select a.name, b.value, round(b.value/1024/1024,1) mb

from v$statname a, v$mystat b

where a.statistic#=b.statistic# and a.name like '%ga memory%' and b.sid=&sid;

查看当前会话pga的使用情况：

select a.name, b.value, round(b.value/1024/1024,1) mb

from v$statname a, v$mystat b

where a.statistic#=b.statistic# and a.name like '%ga memory%';

4. 查看已为（以及当前为）程序全局区分配了多少内存：

select name, value

from v$pgastat

where name in('maximum PGA allocated','total PGA allocated');

maximum PGA allocated :pga曾经扩张到的最大值

total PGA allocated :当前实例已分配的PGA内存总量

5.使用顾问调整PGA的大小：

select

round(a.PGA_TARGET_FOR_ESTIMATE / 1024 / 1024) PGAMB,

a.ESTD_PGA_CACHE_HIT_PERCENTAGE,

round(ESTD_EXTRA_BYTES_RW/1024/1024) ESTD_EXTRA_BYTES_RW,

a.PGA_TARGET_FACTOR,

a.ESTD_OVERALLOC_COUNT

from v$pga_target_advice a

该输出告诉我们，按照系统当前运转的情况，pga设置不同的值所带来的不同效果

根据该输出，随着不断增加pga的尺寸，ESTD_PGA_CACHE_HIT_PERCENTAGE

不断增加，同时ESTD_EXTRA_BYTES_RW（表示onepass,multipass读写的字节数）

不断减小，从以上的输出我们可以看出设置pga为45M是最合理的

6 .pga_aggregate_target的含义

pga_aggregate_target：是一个上限目标，而不是启动数据库时预分配的内存大小，可以把pga_aggregate_target设置为一个超大值（远远大于服务器上实际可用的物理内存量），我们可以看到oracle并不会因此分配很大的内存pga_aggregate_target是一个目标值，它的意义是在足够的内存时，自动内存管理会让少量的用户尽可能多地使用内存，而过一段时间负载增加时，可以减少分配，在过一段时间，随着负载的减少，为每个操作分配的内存量又增加

超过PGA目标值的情况：

1. 设置PGA的总和为80M

alter system set pga_aggregate_target=80M;

SQL> create table t1 as select * from dba_objects;

SQL> create table big_table as select * from dba_objects;

SQL> insert into big_table select * from big_table;

SQL> commit;

SQL> grant execute on dbms_alert to scott;

2. 创建测试脚本，名为test_hash.sql，内容如下:

vi /u01/test_hash.sql

select sid from v$mystat where rownum=1;declarel_msg long;l_status number;begin    dbms_alert.register('WAITING');    for i in 1..9999999 loop        dbms_application_info.set_client_info(i);        dbms_alert.waitone('WAITING',l_msg,l_status,0);        exit when l_status=0;        for x in(select a.owner from t1 a,big_table b where a.object_id=b.object_id) loop            null;        end loop;    end loop;end;/

View Code

无论在哪个会话中，使用如下脚本为使上述过程终止：

vi /u01/stop.sql

begin    dbms_alert.signal('WAITING','');    commit;end;/

View Code

开5个会话，执行/u01/test_hash.sql

在6个会话中查看pga的总量：

col name for a30

select name,value,value/1024/1024 mb from v$sysstat where name like '%ga memory%'

发现pga的总量已经超出80M

调整PGA

1. 创建实验环境

使用以下参数设置启动数据库：

cd $ORACLE_HOME/dbs

vi initorcl.ora 写入以下参数：

background_dump_dest='/u01/app/oracle/admin/orcl/bdump'compatible='10.2.0.1.0'control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl','/u01/app/oracle/oradata/orcl/control03.ctl'core_dump_dest='/u01/app/oracle/admin/orcl/cdump'db_block_size=8192db_cache_size=100mdb_domain='oracle.com'db_file_multiblock_read_count=16db_name='orcl'db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'db_recovery_file_dest_size=4294967296dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'fast_start_mttr_target=30java_pool_size=4mjob_queue_processes=2large_pool_size=4mlog_checkpoint_timeout=0open_cursors=300processes=150remote_login_passwordfile='EXCLUSIVE'shared_pool_size=100msort_area_size=65536undo_management='AUTO'undo_tablespace='UNDOTBS1'user_dump_dest='/u01/app/oracle/admin/orcl/udump'parallel_max_servers=64parallel_adaptive_multi_user=FALSEsga_target=300mpga_aggregate_target=20971520

View Code

保存退出

shutdown immediate

startup pfile=$ORACLE_HOME/dbs/initorcl.ora

创建新的临时表空间：

CREATE TEMPORARY TABLESPACE TEMP_L

UNIFORM SIZE 64K

TEMPFILE '/u01/app/oracle/oradata/orcl/temp_l01.dbf' Size 150M

AUTOEXTEND ON NEXT 10M MAXSIZE 300M;

将新的临时表空间设置为默认的：

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_L;

2. 执行以下的脚本，让数据库产生负载

解压测试包：

tar -xvf pga_test.tar

cd pga_test

mv * /u01

解锁以下用户：

alter user sh account unlock identified by sh;

alter user hr account unlock identified by hr;

alter user system account unlock identified by a;

启动监听

创建服务名为：orcl.oracle.com的网络连接符orcl

创建快照：

sqlplus / as sysdba

exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

切换到/u01目录，执行脚本pga_workgen.sh

cd /u01

. /u01/pga_workgen.sh

3. 等待两分钟，查看pga的信息

show parameter pga_aggregate_target

查看pga建议：

select round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,

ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,

ESTD_OVERALLOC_COUNT

FROM V$PGA_TARGET_ADVICE;

4. 删除负载

cd /u01

rm runload

5. 针对最近两次快照，生成AWR报告

首先创建快照：

exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

生成AWR报告：

@?/rdbms/admin/awrrpt.sql

Type Specified: text/html

Enter value for num_days: 1

6. 查看awr报告中的症状，寻找建议

a. 首先查看Load Profile中的Physical reads和Physical writes：

Load Profile

~~~~~~~~~~~~ Per Second Per Transaction

--------------- ---------------

Block changes: 4.35 54.63

Physical reads: 278.64 3,496.60

Physical writes: 308.35 3,869.41

User calls: 442.00 5,546.45

发现物理读和写比较高

b. 确定读次数较高的SQL语句

SQL ordered by Reads DB/Inst: ORCL/orcl Snaps: 21-22

-> Total Disk Reads: 405,606

-> Captured SQL account for 99.7% of Total

Reads CPU Elapsed

Physical Reads Executions per Exec %Total Time (s) Time (s) SQL Id

-------------- ----------- ------------- ------ -------- --------- -------------

125,449 13 9,649.9 30.9 13.11 422.05 b57rvwy9jbvv6

Module: SQL*Plus

select * from customers order by cust_gender, cust_marital_status

查找排序的sql语句：

select * from customers order by cust_gender, cust_marital_status

79,009 51 1,549.2 19.5 18.07 350.17 3bq3pfux13pkr

Module: SQL*Plus

select * from (select * from dba_objects order by timestamp) where rownum <

80000

c. 确定由于pga太小而引起的临时写：

PGA Aggr Summary DB/Inst: ORCL/orcl Snaps: 21-22

-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory

PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written

--------------- ------------------ --------------------------

23.8 3,997 12,809

-------------------------------------------------------------

Warning: pga_aggregate_target was set too low for current workload, as this

value was exceeded during this interval. Use the PGA Advisory view

to help identify a different value for pga_aggregate_target.

d. 从AWR报告中找出建议pga的设置：

PGA Memory Advisory DB/Inst: ORCL/orcl Snap: 22

-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value

where Estd PGA Overalloc Count is 0

Estd Extra Estd PGA Estd PGA

PGA Target Size W/A MB W/A MB Read/ Cache Overalloc

Est (MB) Factr Processed Written to Disk Hit % Count

---------- ------- ---------------- ---------------- -------- ----------

10 0.5 7,612.1 12,216.8 38.0 804

15 0.8 7,612.1 12,216.8 38.0 804

选择Estd PGA Overalloc Count is 0的最小值，此例中为80MB，应该找 Estd Extra W/A MB Read/Written to Disk值明显减少的值

7. 将PGA_AGGREGATE_TARGET设置为建议值：

ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 80M;

8. 再次生成数据库负载：

cd /u01

. /u01/pga_workgen.sh

9. 等待一段时间后，查看pga的细节：

a.查看pga的建议大小：

SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,

ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,

ESTD_OVERALLOC_COUNT

FROM V$PGA_TARGET_ADVICE;

10. 删除负载：

cd /u01

rm runload

11. 生成awr快照

SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

12. 生成AWR报告，查看数据库症状，查看关于pga的建议：

a. 生成AWR报告：

@?/rdbms/admin/awrrpt.sql

b. 查看物理读和写：

Load Profile

~~~~~~~~~~~~ Per Second Per Transaction

--------------- ---------------

Redo size: 4,761.78 56,500.51

Logical reads: 2,513.05 29,818.40

Block changes: 17.25 204.64

Physical reads: 65.76 780.30

Physical writes: 88.51 1,050.17

发现物理读和写明显降低

c. 查看sql语句是否有改善：

SQL ordered by Reads DB/Inst: ORCL/orcl Snaps: 23-24

-> Total Disk Reads: 36,674

-> Captured SQL account for 99.9% of Total

Reads CPU Elapsed

Physical Reads Executions per Exec %Total Time (s) Time (s) SQL Id

-------------- ----------- ------------- ------ -------- --------- -------------

11,909 24 496.2 32.5 7.59 37.37 3bq3pfux13pkr

Module: SQL*Plus

select * from (select * from dba_objects order by timestamp) where rownum <

80000

与第六步比较，发现相同sqlid的sql语句物理读明显降低

d. 确定pga的cache-hit

PGA Aggr Summary DB/Inst: ORCL/orcl Snaps: 23-24

-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory

PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written

--------------- ------------------ --------------------------

68.0 1,824 857

-------------------------------------------------------------

发现PGA Cache Hit %已经明显提高，大部分排序都在内存中进行

e. 查看pga的建议：

PGA Memory Advisory DB/Inst: ORCL/orcl Snap: 24

-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value

where Estd PGA Overalloc Count is 0

13. 重启数据库

有效使用内存

1.尽量使SGA适合物理内存：可以使用 LOCK_SGA 初始化参数将 SGA 锁定到物理内存中

使用lock_sga和pre_page_sga参数保证SGA常驻物理内存

通过修改lock_sga和pre_page_sga参数可以保证SGA不被换出到虚拟内存，进而可以提高SGA的使用效率。

当lock_sga参数设置为TRUE时（默认值是FALSE），可以保证整个SGA被锁定在物理内存中，这样可以防止SGA被换出到虚拟内存

只要设置lock_sga为“TRUE”便可保证SGA被锁定在物理内存中，这里之所以顺便将pre_page_sga参数也设置为“TRUE”，是因

为这样可以保证在启动数据库时把整个SGA读入到物理内存中，以便提高系统的效率（虽然会增加系统的启动时间）。

修改过程如下：

1.查看lock_sga和pre_page_sga参数的默认值

sys@ora10g> show parameter sga

2.注意：两个参数都是静态参数。确认之。

sys@ora10g> alter system set lock_sga=true; --error

sys@ora10g> alter system set pre_page_sga=true; --error

3.使用“scope=spfile”选项修改之，成功。

sys@ora10g> alter system set lock_sga=true scope=spfile;

sys@ora10g> alter system set pre_page_sga=true scope=spfile;

4.重新启动Oracle使spfile的修改生效

sys@ora10g> shutdown immediate;

sys@ora10g> startup;

ORA-27102: out of memory

Linux-x86_64 Error: 12: Cannot allocate memory

这里为什么会启动失败呢？

原因很简单，Linux操作系统对每一个任务在物理内存中能够锁住的最大值做了限制，需要手工进行调整。

5.“ORA-27102”及“Cannot allocate memory”问题处理

1）使用“ulimit -a”命令获得“max locked memory”的默认大小

ora10g@secDB /home/oracle$ ulimit -a

可见，一个任务可以锁住的物理内存最大值是32kbytes，这么小的值根本无法满足我们SGA的5G大小需求。

2）将其修改为无限大

（1）oracle用户是无法完成这个修改任务的

ora10g@secDB /home/oracle$ ulimit -l unlimited

-bash: ulimit: max locked memory: cannot modify limit: Operation not permitted

（2）切换到root用户

ora10g@secDB /home/oracle$ su - root

Password:

（3）在root用户下尝试修改，成功。

[root@secDB ~]# ulimit -l unlimited

[root@secDB ~]# ulimit -a

6.调整完操作系统的限制后，我们再次尝试启动数据库。成功！

[root@secDB ~]# su - oracle

ora10g@secDB /home/oracle$ sqlplus / as sysdba

NotConnected@> startup;

通过修改lock_sga和pre_page_sga参数值为“TRUE”可以有效的将整个SGA锁定在物理内存中，这样可以有效的提高系统的性能，推荐酌情进行调整。

注意：不同的操作系统对这lock_sga参数的支持情况是不同的，如果操作系统不支持这种锁定，lock_sga参数将被忽略。

Library Cache的使用准则

1.为开发人员制定格式使用约定，以便 SQL 语句符合高速缓存的要求

2.使用绑定变量

create table t1(id number(10));

使用绑定变量：

create or replace procedure p1as begin    for i in 1..1000000 loop        execute immediate ' insert into t1 values(:x)' using i;    end loop;end;/

View Code

不使用绑定变量：

create or replace procedure p2asbegin    for i in 1..1000000 loop        execute immediate 'insert into t1 values('||i||')';    end loop;end;/

View Code

set timing on

exec p1

drop table t1 purge;

create table t1(id number(10));

set timing on

exec p2

3.消除不必要的重复 SQL

select * from emp;

select * from EMP;

select * FROM EMP;

select sql_text,version_count from v$sqlarea where lower(sql_text) like '%select * from emp%';

查找version_count大于1的SQL语句：

grant alter session to scott;

conn scott/tiger

alter session set optimizer_mode=ALL_ROWS;

select * from emp where empno=7788;

alter session set optimizer_mode=first_rows_10;

select * from emp where empno=7788;

select sql_text,version_count from v$sqlarea where version_count>1;

4.考虑使用 CURSOR_SHARING

在没有使用cursor_sharing之前：

alter system flush shared_pool;