新服务器SGA计算：
物理内存 x 0.55 = SGA内存总量(TSGA)
每个实例的总SGA1 = TSGA/服务器ORACLE实例个数
shared pool = TSGA1 x 0.45
db buffer cache = TSGA1 x 0.45
redo log buffer = TSGA1 x 0.10
(redo log buffer没必要10%，在调整统计数据可用后，可以随时从这部分取走内存给其他pool)
以上适合1GB服务器，如果1GB以上应该把55%改为60-75%
######################################################################################
第四章 调整共享池
======了解共享池
SELECT last_name,first_name
FROM customers
WHERE customer_id=2201;
当发布一条SQL或PL/SQL：
--1.ORACLE把该语句中的那些字符转换成它们的ASCII等效数字码。
--2.传递ASCII给散列算法，产生一个单独的散列值。
--3.该用户的server process查看共享池，如果该散列值存在则该serverprocess就使用高速缓存版本来执行该语句。
--4.如果不存在，则Serverprocess对该语句进行语法分析过程(增加系统开销)，然后执行它。
语法分析步骤：
--1.检查该语句的语义正确性。
--2.执行对象解析，对照数据目录检查被引用对象的名称和结构。
--3.通过检查数据目录来收集与该查询中所引用的各对象有关的统计数据。
--4.准备并从可用的执行计划中选择一个执行计划，其中包括存储大纲或物化视图相关的决定。
--5.通过检查数据目录来确定该查询中所引用的那些对象的安全性。
--6.生成该语句的一个编译代码(P_Code)。
共享池的组件：
library cache
data dictionary cache
user global area
*libarary cache
存储的组件：
该语句的实际文本
该语句关联的散列值
该语句的P_Code
该语句关联的统计数据
该语句的执行计划
--查看每种数据库对象类型的执行次数
SQL> select type,count(executions)
from v$db_object_cache
group by type
order by 2 desc;
TYPE COUNT(EXECUTIONS)
---------------------------- -----------------
CURSOR 36070
NOT LOADED 13066
TABLE 226
SEQUENCE 33
PACKAGE 23
VIEW 22
PACKAGE BODY 20
PROCEDURE 19
SYNONYM 16
FUNCTION 9
PUB_SUB 9
CLUSTER 6
INDEX 4
NON-EXISTENT 4
TRIGGER 3
15 rows selected. -->高CURSOR執行次數和低PACKAGE BODY与PACKAGE执行次数联合表明，大部分应用程式SQL是直接发布而不是利用存储过程或函数。
-->把直接SQL的一部份转换成PL/SQL或函数可以改善共享池性能。
*Data dictionary cache
*UGA
共享服务器选项，UGA存在共享池中;
专用服务器选项，UGA存在PGA中;
====== 测量共享池的性能
主要指标是高速缓存区命中率，命中率可以针对library cache和dictionary cache两者计算.
note:调整db buffer cache前先调整library cache(比DB BUFFER CACHE影响大)并使它命中率最大化。
====library cache性能命中率
---通过SQL语句
SQL> select namespace,gethitratio,reloads,invalidations
from v$librarycache
where namespace in ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER');
NAMESPACE GETHITRATIO RELOADS INVALIDATIONS
--------------- ----------- ---------- -------------
SQL AREA .915171837 140831924 112986
TABLE/PROCEDURE .999651802 3241073 0
BODY .997985721 20476 0
TRIGGER .997609605 2761 0
GETHITRATIO:
用戶能在library cache中查找到他们所发布的SQL语句比例；
主要是针对语法分析；
GET:当一条SQL被语法分析一次，就增加1.
GETHITS:在内存中找到SQL语法分析副本一次就增加1.
GETHIRATIO=(GETHITS*100)/GETS
越大越好
OLTP要大于90%,充分调整后应该有95%以上
PINHITRATIO:
表示被执行语句在library cache中查找到相应的已语法分析SQL的比例
RELOADS:
因语法分析版本过久重新分析的次数。
Reload ratio=sum(reloads)/sum(pins) 充分调整后的其值应该小于1%
SQL> select sum(reloads)/sum(pins) "reload ratio"
from v$librarycache;
reload ratio
------------
.051102766
INVALIDATIONS:
在librarycache中已高速緩存的SQL语句被标记为无效而被迫重做语法分析；
每当已缓存语句所引用的对象以某种方式被修改时，就会被标记为无效；
---通过STATSPACK
*Instance Efficiency Percentages中的librarycache命中率是96.07%
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.93 In-memory Sort %: 100.00
Library Hit %: 96.07 Soft Parse %: 91.50
Execute to Parse %: -30.11 Latch Hit %: 99.90
Parse CPU to Parse Elapsd %: 85.96 % Non-Parse CPU: 90.04
*Library Cache Activity for DB中显示了失敗和重载的信息。
SQL AREA名称空间的get requests操作活動，pct miss是7.6，即7.6%，相反表示librarycache的sqlarea部份在监视期间的命中率是1-7.6%=92.4%
Library Cache Activity for DB: ADB Instance: adb1 Snaps: 891 -911
->"Pct Misses" should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY 13,759 0.8 13,816 1.4 41 0
CLUSTER 1,911 0.9 2,097 1.6 0 0
SQL AREA 8,472,003 7.6 30,536,984 5.2 272,637 247
TABLE/PROCEDURE 3,992,300 0.0 9,979,664 0.2 7,742 0
TRIGGER 28,795 0.0 28,795 0.1 10 0
-------------------------------------------------------------
====Dictionary cache性能命中率
如果小于85%需要考虑调整shared pool
1.通过SQL
SQL> select 1-(sum(getmisses)/sum(gets)) "Data Dictionary Hit Ratio"
from v$rowcache;
Data Dictionary Hit Ratio
-------------------------
.99893883
2.通過statspack
命中率= 1-pctmiss/100
下图列出了data dictionary cache各组件信息
如果dc_free_extents,dc_object_ids等命中率低於大多数组件的98%也要考虑调整shared pool
Dictionary Cache Stats for DB: ADB Instance: adb1 Snaps: 891 -911
->"Pct Misses" should be very low (< 2% in most cases)
->"Cache Usage" is the number of cache entries being used
->"Pct SGA" is the ratio of usage to allocated size for that cache
Get Pct Scan Pct Mod Final
Cache Requests Miss Reqs Miss Reqs Usage
------------------------- ------------ ------ ------- ----- -------- ----------
dc_database_links 2,182 0.9 0 0 6
dc_files 504 22.2 0 0 0
dc_global_oids 12 41.7 0 0 0
dc_histogram_defs 69,847 12.5 0 0 689
dc_object_ids 4,338,544 0.1 0 0 638
dc_objects 1,662,014 0.2 0 0 470
dc_profiles 23,957 0.1 0 0 5
dc_rollback_segments 372,822 0.0 0 0 766
dc_segments 4,820,981 0.1 0 2 793
dc_sequences 1,485 58.2 0 1,485 7
dc_tablespace_quotas 2 100.0 0 2 0
dc_tablespaces 16,317 0.6 0 0 14
dc_user_grants 1,160,669 0.0 0 0 31
dc_usernames 1,641,082 0.0 0 0 17
dc_users 2,341,372 0.0 0 0 36
-------------------------------------------------------------
=============改进shared pool性能的方法
使之更大
为大型PL/SQL语句腾出空间
将重要PL/SQL代码保存在内存中
鼓励代码重用
调整LIBRARY CACHE相关参数
====使之更大
--当前shared pool的大小
SQL> select pool,sum(bytes)
from v$sgastat
where pool='shared pool'
group by pool;
POOL SUM(BYTES)
------------ ----------
shared pool 754974720
--评估适当的shared pool大小：
set echo off
set feedback off
set serveroutput on
DECLARE
v_total_plsql_mem number := 0;
v_total_sql_mem number := 0;
v_total_sharable_mem number := 0;
BEGIN
-- find the total sharable memory used by non-sql objects
-- like packages,views,etc.
SELECT sum(sharable_mem)
INTO v_total_plsql_mem
FROM v$db_object_cache;
-- find the total sharable memory used by sql statements
SELECT sum(sharable_mem)
INTO v_total_sql_mem
FROM v$sqlarea
WHERE EXECUTIONS > 10;
-- now add these two value to get the sharable memory
-- (i.e library cache) requirements
v_total_sharable_mem := v_total_sql_mem + v_total_plsql_mem;
DBMS_OUTPUT.PUT_LINE
('Estimated required shared pool size is: '||TO_CHAR(v_total_sharable_mem,'fm9,999,999,999,999')||' bytes');
END;
/
Estimated required shared pool size is: 440,400,492 bytes
====为大型PL/SQL语句腾出空间
*使用保留区
使用shared_pool_reserved_size保存大型PL/SQL，该值默认是5%，推荐为10%
要确定保留池的最佳大小，可以监视v$db_object_cache
--查询当前已缓存在内存中的PL/SQL程序包名称和大小：
SQL> select owner,name,sharable_mem
from v$db_object_cache
where type in ('PACKAGE','PACKAGE BODY')
order by sharable_mem;
--查询保留区使用情况：
SQL> select REQUEST_MISSES,FREE_SPACE,USED_SPACE,REQUEST_FAILURES,
FREE_SPACE/(FREE_SPACE+USED_SPACE) as "free hit"
from v$shared_pool_reserved;
REQUEST_MISSES FREE_SPACE USED_SPACE REQUEST_FAILURES free hit
-------------- ---------- ---------- ---------------- ----------
14274 5776336 24445664 984 .19113017
保留区大小过大：
-REQUEST_MISSES始终是0或静态；
-FREE_SPACE是分配给保留区的总大小的50%以上，上列为19%；
保留区大小过小：
-REQUEST_FAILURES非零或不断增长表明保留区太小；
保留区使用目标：
-尽量让REQUEST_MISSES,REQUEST_FAILURES保持在零附近；
*使用dbms_shared_pool.aborted_request_threshold
EXECUTE dbms_shared_pool.aborted_request_threshold (10000);
本例允许一个大PL/SQL对象从shared_pool的LRU中清洗多达10,000个字节。
如果超出这个强加限制，ORACLE将返回一个内存溢出错误给用户。
=====把重要的PL/SQL保持在内存中
使用dbms_shared_pool把PL/SQL包pinning在內存中；
步驟：
--建立DBMS_SHARED_POOL
SQL> @$ORACLE_HOME/rdbms/admin/dbmspool.sql
--使用DBMS_SHARED_POOL,KEEP用來锁定，UNKEEP用来取消锁定
SQL> EXECUTE DBMS_SHARED_POOL.KEEP ('APPROVE_PO');
--找出已被pinning的对象:
SQL> select owner,name,type
from v$db_object_cache
where kept='YES';
哪些对象需要锁定？
1、开启ORACLE审计功能，一段时间后查看哪些对象访问频繁，考虑锁定这些对象。
问题：怎么开启审计并查看对象访问情况？
2、改写大的无名PL/SQL块所包含的SQL语句：
--应将超出500字符的任何无名PL/SQL块重写一个存储过程或程序包
SQL> select substr(sql_text,1,45) sql,length(sql_text) "stmt_size"
from v$sqlarea
where command_type=47
order by length(sql_text);
何时锁定：
锁定的对象在实例重启后会消失，需要重新锁定；
可以实例启动后手动执行一个SQL脚本实现锁定；
或实例已启动由一个AFTER STARTUP ON DATABASE触发器自动实现；
====影响LIBRARY CACHE的参数：
shared_pool_size
open_cursors
cursor_space_for_time
session_cached_cursors
cursor_sharing
阅读(3453) | 评论(0) | 转发(0) |
