关于存储过程的一些tips

来源：互联网发布：linux保存并退出命令编辑：程序博客网时间：2024/04/29 10:03

关于存储过程的一些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 PROCEDURE CREATE_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 misses while 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 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