oracle的性能优化[1]

原创 2007年09月19日 17:43:00

内容翻译自: 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

  • 2014-08-24 23:35
  • 50MB
  • 下载

Oracle性能优化读书笔记(1)-SQL执行计划分析工具

学习主要的SQL执行计划分析工具,参考罗敏的品味Oracle性能优化

ORACLE+SQL性能优化系列 (1

  • 2011-11-19 11:06
  • 196KB
  • 下载

Oracle 12cR1中性能优化新特性之全数据库缓冲模式

通常情况下,Oracle会决定哪些数据会留在缓冲区中。当没足够的空间时,数据会被写出内存。此外,为了避免大量读取将有用的信息挤出缓冲区,Oracle对有些操作也许会才去绕过缓冲区的措施。Oracle ...

oracle 性能调优诊断案例1

问题描述: 这是帮助一个公司的诊断案例. 应用是一个后台新闻发布系统. 症状是,通过连接访问新闻页是极其缓慢 通常需要十数秒才能返回. 这种性能是用户不能忍受的. 操作系统:SunOS 5...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)