关于存储过程的一些tips
关于存储过程的一些tips
注：这篇文章本身基于Oracle，但作为独立的应用开发商来说，考虑将逻辑放在存储过程中也许并不是很明智的做法，我本人并不完全赞同将应用移植到数据库相关的过程中，日后平台转换的代价会非常大。建议将性能相关的少部分应用作为存储过程实现，大部分的应用还是应该使用java之类的代码在应用层封装逻辑。
存储过程和触发器比传统代码速度更快，因此也越来越收到欢迎。当应用代码从外部程序转移到数据库中，DBA们需要知道相关的内存要求，并且知道如何管理他们优化数据库性能。
DBA们需要考虑增加存储过程需要的内存，仔细规划。使用存储过程有以下好处：
l 更好的性能。存储过程一次加载到share pool中，并且一直保存，除非他们被交换出去了。执行的速度也比外部程序更快。
l 连接数据和行为。DBA可以使用习惯命名来连接关系表和他们的行为。如果与employee表相关的所有行为用表名做前缀，如employee.hire、employee.give_raise，可以通过数据字典查询出来所有的相关联的行为(select * from dba_objects where owner='EMPLOYEE')，这样更容易区分和重用代码。
l 隔离代码。因为所有的SQL被转移到了存储过程中，所有应用程序就不需要频繁的访问数据库了。
使用存储过程的最初原因是因为其性能，存储过程和trigger是缓存在SGA中的，基于LRU算法。一旦加载到shared pool中，则存储过程执行的非常快，其策略是防止pool的抖动。
当优化Oracle时，有两个重要的参数，db_cache_size和shared_pool_size。
db_cache_size参数控制被分配给SGA的block的数量，这个参数是缓存命中率的主要决定因素。当然，更大的数据库缓存可以使得更多的block保存在缓存中，因而会减少针对block并发请求时的I/O。
当Oracle收到一个检索数据的请求时，首先检查内部数据结构，检查数据是否已经在buffer中了，这种做法可以使得服务器避免不必要的I/O。理想情况下，dba应该为每个数据库页创建一个buffer，使Oracle server可以只读一次每个block。事实上，考虑成本，这点很难做到。最好的办法，分配很少数量的物理内存buffer，OracleServer会自动管理。Oracle Server使用LRU算法来决定那个数据库页要刷新。
db_cache_size和shared_pool_size参数定义了Oracle内存消费的大部分，决定可用于缓存数据块、SQL、存储过程的内存。也可以通过设置shared_pool_reserved_size和shared_pool_reserved_min_alloc参数来设置保留空间的大小。
Oracle中提供了一种结构，包。包是函数和过程的集合。例如：
|
CREATE PACKAGE employee AS
FUNCTION compute_raise_amount (percentage NUMBER);
PROCEDURE hire_employee();
PROCEDURE fire_employee();
PROCEDURE list_employee_details();
END employee;
|
上面这段代码封装了所有的与employee有关的行为。
l 调整用于ORACLE存储过程的SGA
l 字典缓存
l 库缓存
l 共享SQL区
l 私有SQL区(在游标打开/关闭中存在)。
当Shared pool发生page out时，碎片，或不连续的内存chunk就产生了。
shared_pool_size在内存中的作用就相当于表空间，当在表空间中不能获取连续的可用空间时，会报ORA-1547错误，同样的，在内存中发生这种情况会报ORA-4031错误。当大的存储过程page out出去后，再加载进内存就有可能找不到连续的可用空间。
如何锁住Oracle包
要阻止页面交换出包，可以把包标记为非可交换的，告诉数据库初始化加载完之后，仍然保存在内存中，这个过程叫做pinned。Oracle提供了过程dbms_shared_pool.keep来锁定包。使用dbms_shared_pool.unkeep来解锁。如果没有这个包，则执行D:\app\Administrator\product\11.1.0\db_1\RDBMS\ADMIN\dbmspool.sql脚本。
做是否锁定Oracle包的决定前，先考虑对象的大小和频繁调用程度。只有那些非常大，并且频繁被调用的过程会受益。理想情况下，shared_pool_size参数应该足够大，以容纳应用调用到的所有的包、过程和触发器，事实上，你只能选择一些对象来锁定。通过查询v$db_object_cache中sharable_mem列得知每个包在library cache中消耗多少内存。
Oracle公司推荐锁定
STANDARD,DBMS_STANDARD,DBMS_UTILITY,DBMS_DESCRIBE和DBMS_OUTPUT等常用的包。
|
execute dbms_shared_pool.keep('DBMS_ALERT');
execute dbms_shared_pool.keep('DBMS_DDL');
execute dbms_shared_pool.keep('DBMS_DESCRIBE');
execute dbms_shared_pool.keep('DBMS_LOCK');
execute dbms_shared_pool.keep('DBMS_OUTPUT');
execute dbms_shared_pool.keep('DBMS_PIPE');
execute dbms_shared_pool.keep('DBMS_SESSION');
execute dbms_shared_pool.keep('DBMS_SHARED_POOL');
execute dbms_shared_pool.keep('DBMS_STANDARD');
execute dbms_shared_pool.keep('DBMS_UTILITY');
execute dbms_shared_pool.keep('STANDARD');
|
Oracle存储过程的自动重锁定
unix和linux用户可以通过在/etc/rc文件中增加代码来确保每次数据库启动时包都会被自动锁定，下面是脚本样例。
|
[root]: more pin
#! /bin/ksh
ORACLE_SID=mydata
# run this script as root
# now, sign on as ORACLE, using the SU command
export ORACLE_SID
su oracle -c "/oramag/usr/oracle/bin/sqldba mode=line /<<!
connect internal;
select * from db;
@/usr/local/dba/sql/pin.sql
exit;
!"
|
pin.sql脚本可以根据自己的需要修改。
监控锁定的存储过程
下面脚本是一个查询内存情况的脚本。
|
set pagesize 60;
column executions format 999,999,999;
column Mem_used format 999,999,999;
SELECT SUBSTR(owner,1,10) Owner,
SUBSTR(type,1,12) Type,
SUBSTR(name,1,20) Name,
executions,
sharable_mem Mem_used,
SUBSTR(kept||' ',1,4) "Kept?"
FROM v$db_object_cache
WHERE type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE')
ORDER BY executions desc;
SQL> @memory
OWNER TYPE NAME EXECUTIONS MEM_USED Kept
----- ---- ---- ---------- -------- ----
SYS PACKAGE STANDARD 867,600 151,963 YES
SYS PACKAGE BODY STANDARD 867,275 30,739 YES
SYS PACKAGE DBMS_ALERT 502,126 3,637 NO
SYS PACKAGE BODY DBMS_ALERT 433,607 20,389 NO
SYS PACKAGE DBMS_LOCK 432,137 3,140 YES
SYS PACKAGE BODY DBMS_LOCK 432,137 10,780 YES
SYS PACKAGE DBMS_PIPE 397,466 3,412 NO
SYS PACKAGE BODY DBMS_PIPE 397,466 5,292 NO
HRIS PACKAGE
S3425_PACKAGE 285,700 3,776 YES
SYS PACKAGE DBMS_UTILITY 284,694
3,311 NO
SYS PACKAGE BODY DBMS_UTILITY 284,694 6,159 NO
HRIS PACKAGE HRS_COMN_PACKAGE 258,657 3,382 NO
HRIS PACKAGE BODY S125_PACKAGE 248,857 30,928 NO
HRIS PACKAGE BODY HRS_COM_PACKAGE 242,155 8,638 NO
HRIS PACKAGE GTS_SNAP_UTILITY 168,978 11,056 NO
HRIS PACKAGE BODY GTS_SNAP_UTILITY 89,623 3,232 NO
SYS PACKAGE DBMS_STANDARD 18,953 14,696 NO
SYS PACKAGE BODY DBMS_STANDARD 18,872 3,432 NO
KIS PROCEDURE
RKA_INSERT 7,067 4,949 NO
HRIS PACKAGE HRS_PACKAGE 5,175 3,831 NO
HRIS PACKAGE BODY HRS_PACKAGE 5,157 36,455 NO
SYS PACKAGE DBMS_DESCRIBE 718 12,800 NO
HRIS PROCEDURE
CHECK_APP_ALERT 683 3,763 NO
SYS PACKAGE BODY DBMS_DESCRIBE 350 9,880 NO
SYS PACKAGE DBMS_SESSION 234 3,351 NO
SYS PACKAGE BODY DBMS_SESSION 165 4,543 NO
GIANT PROCEDURECREATE_SESS_RE 62 7,147 NO
HRIS PROCEDURE
INIT_APP_ALERT 6 10,802 NO
|
有比较容易的方法知道被交换出内存的对象又被重加载的次数。
一种方法是，运行estat/bstat工具(~/rdbms/admin/utlbstat.sql 和utlestat.sql)度量SGA在一段时间内的消耗情况。检查v$sgastat，确认"free memory"值低，并且确认library cache,sql area,dictionary cache没有表现出明显的波动，没有明显波动意味着在v$rowcache和v$librarycache中通常有很高的命中率。
另一种方法是写一个捕捉dump的工具，检查SGA，并且标识出任何与libaray cache相关的异常。度量下面的一些值：
l 数据字典命中率
l library cache缺失率
l 所有namespaces的单独的命中率
下面是一个脚本的例子：
|
=========================
LIBRARY CACHE MISS RATIO
=========================
(如果 > 1 则增加shared_pool_size)
executions
Cache misseswhile executing LIBRARY CACHE MISS RATIO
-----------------------
------------
22,909,643
171,127 .0075
=========================
Library Cache Section
=========================
hit ratio 应 > 70, and pin ratio > 70 . . .
NAMESPACE
Hit ratio pin hit ratio reloads
---------
--------------------- -------------
SQL AREA
84 94 125,885
TABLE/PROCEDURE
98 99 43,559
BODY
98 84 486
TRIGGER
98 97 1,145
INDEX
0 0
CLUSTER
31 33
OBJECT
100 100
PIPE
99 99 52
|
下面是sqlplus脚本生成的报告:
|
prompt
prompt
prompt =========================
prompt DATA DICT HIT RATIO
prompt =========================
prompt (应该大于 90 否则增加 shared_pool_size ) prompt
column "Data Dict. Gets" format 999,999,999
column "Data Dict. cache misses" format 999,999,999
select sum(gets) "Data Dict. Gets",
sum(getmisses) "Data Dict. cache misses",
trunc((1-(sum(getmisses)/sum(gets)))*100) "DATA DICT CACHE HIT RATIO"
from v$rowcache;
prompt
prompt =========================
prompt LIBRARY CACHE MISS RATIO
prompt =========================
prompt (如果大于 1 则增加 shared_pool_size)
prompt
column "LIBRARY CACHE MISS RATIO" format 99.9999
column "executions" format 999,999,999
column "Cache misses while executing" format 999,999,999
select sum(pins) "executions", sum(reloads) "Cache misses while executing",
(((sum(reloads)/sum(pins)))) "LIBRARY CACHE MISS RATIO"
from v$librarycache;
prompt
prompt =========================
prompt Library Cache Section
prompt =========================
prompt 命中率应该大于 70, pin ratio > 70 . . .
prompt
column "reloads" format 999,999,999
select namespace, trunc(gethitratio * 100) "Hit ratio",
trunc(pinhitratio * 100) "pin hit ratio", reloads "reloads"
from v$librarycache;
|
注意：运行这类报告时，统计值是从系统运行就开始收集的，数值可能不会有什么意义。在一个运行了6个月的Oracle数据库中，如果你想衡量今天的统计信息是毫无意义的。可以通过运行STATSPACK或AWR报告来产生一段时间的统计。
今天，内存已经越来越便宜，500M的Oracle内存区域很常见，DBA应该考虑将包锁定在SGA中。当应用变得SQL越来越少，都放在过程中，则DBA可以调整和控制的能力就会很大。
Wonder
2007-12-12