OCP读书笔记(13)

bingtuag

　　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=8192
db_cache_size=100m
db_domain='oracle.com'
db_file_multiblock_read_count=16
db_name='orcl'
db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
db_recovery_file_dest_size=4294967296
dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
fast_start_mttr_target=30
java_pool_size=4m
job_queue_processes=2
large_pool_size=4m
log_checkpoint_timeout=0
open_cursors=300
pga_aggregate_target=25165824
processes=150
remote_login_passwordfile='EXCLUSIVE'
shared_pool_size=100m
sort_area_size=65536
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
user_dump_dest='/u01/app/oracle/admin/orcl/udump'
parallel_max_servers=64
parallel_adaptive_multi_user=FALSE
sga_max_size=300m
sga_target=0
View Code　　startup pfile=$ORACLE_HOME/dbs/initorcl.ora
　　2. 创建java存储过程如：
　　vi /u01/java.sql





CONNECT hr/hr
set echo on
DECLARE
i NUMBER;
v_sql VARCHAR2(200);
BEGIN
FOR 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 a12
col value format a8
show parameter sga_
PROMPT
PROMPT *** SGA Dynamic Component Size Information***
col component format a22
col current_size format a15
col min_size format a15
SELECT component,current_size/1048576||'M' current_size,
min_size/1048576||'M' min_size
FROM v$sga_dynamic_components
WHERE component IN ('shared pool','large pool',
'java pool','DEFAULT buffer cache');
col name format a20
col value format a20
PROMPT *** Current parameter settings in V$PARAMETER ***
SELECT name, value, isdefault
FROM v$parameter
WHERE 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/hr
set echo on
DECLARE
i 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;
declare
l_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=8192
db_cache_size=100m
db_domain='oracle.com'
db_file_multiblock_read_count=16
db_name='orcl'
db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
db_recovery_file_dest_size=4294967296
dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
fast_start_mttr_target=30
java_pool_size=4m
job_queue_processes=2
large_pool_size=4m
log_checkpoint_timeout=0
open_cursors=300
processes=150
remote_login_passwordfile='EXCLUSIVE'
shared_pool_size=100m
sort_area_size=65536
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
user_dump_dest='/u01/app/oracle/admin/orcl/udump'
parallel_max_servers=64
parallel_adaptive_multi_user=FALSE
sga_target=300m
pga_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用户下尝试修改，成功。
[iyunv@secDB ~]# ulimit -l unlimited
[iyunv@secDB ~]# ulimit -a
　　6.调整完操作系统的限制后，我们再次尝试启动数据库。成功！
[iyunv@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 p1
as
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 p2
as
begin
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;
select name,value from v$sysstat where name like '%parse%';
　　select * from emp where empno=7788;
select name,value from v$sysstat where name like '%parse%';
　　select * from emp where empno=7900;
select name,value from v$sysstat where name like '%parse%';
　　使用了cursor_sharing之后：
alter system flush shared_pool;
alter system set cursor_sharing=force;
select name,value from v$sysstat where name like '%parse%';
　　select * from emp where empno=7788;
select name,value from v$sysstat where name like '%parse%';
　　select * from emp where empno=7900;
select name,value from v$sysstat where name like '%parse%';
　　注意：参数CURSOR_SHARING有三个值：
FORCE：强制走绑定
SIMILAR： 当表的字段被分析过存在histograms的时候，similar 的表现和exact一样; 当表的字段没被分析，不存在histograms的时候，similar的表现和force一样. 这样避免了一味地如force一样转换成变量形式，因为有histograms的情况下转换成变量之后就容易产生错误的执行计划，没有利用上统计信息。而exact呢,在没有histograms的情况下也要分别产生硬解析，这样的话，由于执行计划不会受到数据分布的影响（因为没有统计信息）重新解析是没有实质意义的，而similar则综合了两者的优点
EXACT：只有完全相同时走绑定
　　5.尽可能使用 PL/SQL
　　6.缓存序列号
create sequence s1
    increment by 2
    start with 1
    maxvalue 10
    minvalue -10
    nocycle
    cache;
　　7.连接库高速缓存中的对象
　　dbms_shared_pool
　　dbms_shared_pool提供以下功能:将object 或者 "sql statement" pin到shared pool
　　keep过程可以将对象pin入shared_pool,而不进入LRU 机制;
unkeep过程将pinned对象unpin,从而进入LRU机制;
　　安装dbms_shared_pool:     @?/rdbms/admin/dbmspool.sql





alter system flush shared_pool;
select name,kept from v$db_object_cache where type='PROCEDURE';
exec dbms_shared_pool.keep('SCOTT.P_TEST');
select name,kept from v$db_object_cache where type='PROCEDURE';
alter system flush shared_pool;
select name,kept from v$db_object_cache where type='PROCEDURE';
exec dbms_shared_pool.unkeep('P_TEST','P');
select name,kept from v$db_object_cache where type='PROCEDURE';
View Code　　AMM
　　查看当前所有内存组件的状态：





col component for a25
set linesize 300
select component,
current_size,
min_size,
max_size,
user_specified_size,
last_oper_type,
last_oper_mode
from v$memory_dynamic_components;
View Code　　使用建议调整memory_target
　　select * from V$MEMORY_TARGET_ADVICE
　　通过以上的查询可以看出当前memory_target的大小600M，当大于600M的时候，ESTD_DB_TIME_FACTOR不会变化
当为450M的时候也为1，所以应该调整为450M
　　v$memory_resize_ops：视图记录了近800次修改内存大小的操作 包括自动和手动修改的，但是不包括 进程内部修改的
SQL> desc v$memory_resize_ops
名称                                       
-----------------------------------------
COMPONENT           对象的名称 比如 pga target，shared pool                                 
OPER_TYPE           操作类型
                     static      
                     initializing  初始化，即未修改过的
                     disabled      禁止的
                     grow          增大
                     shrink        减小
                     shrink_cancel
OPER_MODE           操作方式：manual 手动，deferred 延迟  immediate 立即                                 
PARAMETER           对象的参数比如 java pool 是 java_POOL_SIZE                               
INITIAL_SIZE        初始值，即开始操作时的大小                               
TARGET_SIZE         需要调整到的目标大小                      
FINAL_SIZE          最终的大小                               
STATUS              操作的完成情况
                     INACTIVE
                     PENDING
                     COMPLETE 完成
                     CANCELLED 取消
                     ERROR 错误               
START_TIME          起始时间                              
END_TIME            终止时间
　　下面通过实践来使用该视图，首先查明当前组件的大小





SELECT component, current_size, min_size, max_size
FROM v$memory_dynamic_components
WHERE current_size != 0;
View Code　　--减小pga_aggregate_target
　　alter system set pga_aggregate_target=100M;
　　SQL> show parameter pool
　　--设置java_pool_size 的大小使其增大
　　SQL> alter system set java_pool_size=10M;
　　SQL> select COMPONENT,OPER_TYPE,OPER_MODE from v$memory_resize_ops;
　　alter system set pga_aggregate_target=300M;
　　select COMPONENT,OPER_TYPE,OPER_MODE from v$memory_resize_ops;
　　--由此可以看出该视图是累计记录的
　　alter system set streams_pool_size=16777216;
　　select COMPONENT,OPER_TYPE,OPER_MODE from v$memory_resize_ops;