内容翻译自: PLSQL User's Guide and Reference.pdf
程序问题
1.不要进行全表扫瞄.
2. Suppose the subprogram called most often by an application is a lookup function
with hundreds of possible targets. If that function could be written as a hash or a
binary search but, instead, is written as a linear search, overall performance suffers.
3.不要传递不使用的变量或者不使用的变量在循环里初始化和计算.
4.不要编写内置函数的副本,因为它们更高效.
5.使用条件控制表达式if else,exit when 要注意回路的优化.比如: 简单的判断 or 复杂的函数调用 ,
这样如果简单的判断返回true时,复杂的函数调用就不需要计算.
同理: and, not
6.不要使用隐式的数据类型转换
The integer literal 15 is represented internally as a signed 4-byte quantity, so
PL/SQL must convert it to an Oracle number before the addition. However, the
floating-point literal 15.0 is represented as a 22-byte Oracle number, so no
conversion is necessary.
DECLARE
n NUMBER;
c CHAR(5);
BEGIN
n := n + 15; -- converted
n := n + 15.0; -- not converted
...
END;
7. 使用PLS_INTEGER 来声明整形变量,因为它是最高效的.
When you
need to declare an integer variable, use the datatype PLS_INTEGER, which is the
most efficient numeric type. That is because PLS_INTEGER values require less
storage than INTEGER or NUMBER values. Also, PLS_INTEGER operations use
machine arithmetic, so they are faster than BINARY_INTEGER, INTEGER, or
NUMBER operations, which use library arithmetic.
尽量不要使用以下数据类型来声明变量.
INTEGER, NATURAL, NATURALN, POSITIVE, POSITIVEN, and
SIGNTYPE are constrained subtypes. So, their variables require precision checking
at run time, which can affect performance.
8.不要使用not null来声明变量,因为会增加内部的判断消耗.使用程序进行判断.
Because m is constrained by NOT NULL, the value of the expression a + b is
assigned to a temporary variable, which is then tested for nullity.
[不要使用]
PROCEDURE calc_m IS
m NUMBER NOT NULL := 0;
a NUMBER;
b NUMBER;
BEGIN
...
m := a + b;
...
END;
而要使用
[而要使用]
PROCEDURE calc_m IS
m NUMBER; -- no constraint
a NUMBER;
b NUMBER;
BEGIN
...
m := a + b;
IF m IS NULL THEN -- enforce constraint programmatically
...
END IF;
END;
一些定义为not null的数据类型.
Note that the subtypes NATURALN and POSTIVEN are defined as NOT NULL. So,
using them incurs the same performance cost.
9.定义varchar2的大小时,大小要>=2000.>=2000的varchar2 oracle会自动分配空间.
The VARCHAR2 datatype involves a trade-off between memory use and efficiency.
For a VARCHAR2(>= 2000) variable, PL/SQL dynamically allocates only enough
memory to hold the actual value. However, for a VARCHAR2(< 2000) variable,
PL/SQL preallocates enough memory to hold a maximum-size value. So, for
example, if you assign the same 500-byte value to a VARCHAR2(2000) variable and
to a VARCHAR2(1999) variable, the latter uses 1499 bytes more memory.
10.确保oracle有足够的内存池存放编译后的子程序.这样编译后程序生命周期就会很长,
而不会经常读I/O(编译).但是不需要过大造成浪费.
11.一些常用的包可以固定在内存池
When a package is pinned, it is not aged out by the least
recently used (LRU) algorithm that Oracle normally uses.
You can pin packages with the help of the supplied package DBMS_SHARED_POOL.
12.使用decode查询一次来取代update.
select key_name,
count(1) query_num,
sum(decode(act_type, 1, 1, 0)) active_num
from wap_search_log_pic_20071028
where key_name is not null
and search_type = 2
and page_num = 1
group by key_name
未完待续