oracle的性能优化[1]
来源:互联网 发布:微信交友网站源码 编辑:程序博客网 时间:2024/06/03 15:45
内容翻译自: 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
未完待续
- oracle的性能优化[1]
- Oracle的性能优化
- Oracle 的性能优化概述
- Oracle数据库性能的优化
- oracle数据库的性能优化
- Oracle 数据库的性能优化
- Oracle sql性能优化1
- oracle :性能优化的一个案例
- Oracle数据性能优化的若干诀窍
- oracle :性能优化的一个案例
- 关于Oracle数据库的性能优化心得
- 优化数据库大幅度提高Oracle的性能
- Oracle中SQL语句的性能优化
- Oracle 性能优化的基本方法概述
- 优化数据库大幅度提高Oracle的性能
- oracle性能优化参数的调整
- 优化数据库大幅度提高Oracle的性能
- 优化数据库大幅度提高Oracle的性能
- 创建Web服务
- AJAX学习必备三本书
- ORACLE函数介绍第一篇 著名函数之单值函数
- ORACLE函数介绍第二篇 非著名函数之单值函数
- 不一样的“能ping通不能上网”解决方法
- oracle的性能优化[1]
- 纪念一年前那个最最幸福的时刻
- ORACLE函数介绍第三篇 著名函数之聚合函数
- ORACLE函数介绍第四篇 非著名函数之聚合函数
- 寻回丢失的照片
- VC文件扩展名解读大全
- ORACLE函数介绍第五篇 分析函数简述
- 刚学ajax--遇到提示:名称以无效字符开头
- (原创)报考计算机博士之前应该注意的几个问题---写给应届硕士毕业生