1.选用适合的ORACLE优化器
ORACLE的优化器共有3种
A
、
RULE (
基于
规则
) b
、
COST (
基于成本
) c
、
CHOOSE (
选择
性
)
设
置缺省的
优
化器,可以通
过对
init.ora
文件中
OPTIMIZER_MODE
参数的各
种
声明,如
RULE
,
COST
,
CHOOSE
,
ALL_ROWS
,
FIRST_ROWS
。
你当然也在
SQL
句
级
或是会
话
(session)
级对
其
进
行覆盖。
为
了使用基于成本的
优
化器
(CBO
,
Cost-Based Optimizer)
,你必
须经
常运行
analyze
命令,以增加数据
库
中的
对
象
统计
信息
(object statistics)
的准确性。
如果数据
库
的
优
化器模式
设
置
为选择
性
(CHOOSE)
,那
么实际
的
优
化器模式将和是否运行
过
analyze
命令有
关
。
如果
table
已
经
被
analyze
过
,
优
化器模式将自
动
成
为
CBO
,
反之,数据
库
将采用
RULE
形式的
优
化器。
在缺省情况下,
ORACLE
采用
CHOOSE
优
化器,
为
了避免那些不必要的全表
扫
描
(full table scan)
,
你必
须
尽量避免
使用
CHOOSE
优
化器,而直接采用基于
规则
或者基于成本的
优
化器。
2.
访问
Table
的方式
ORACLE
采用两
种访问
表中
记录
的方式:
A 、 全表 扫 描
全表 扫 描就是 顺 序地 访问 表中 每 条 记录 。 ORACLE 采用一次 读 入多个数据 块 (database block) 的方式 优 化全表 扫 描。
B 、 通 过 ROWID 访问 表
你可以采用基于 ROWID 的 访问 方式情况,提高 访问 表的效率, ROWID 包含了表中 记录 的物理位置信息。 ORACLE 采用索引 (INDEX) 实现 了数据和存放数据的物理位置 (ROWID) 之 间 的 联 系。通常索引提供了快速 访问 ROWID 的方法,因此那些基于索引列的 查询 就可以得到性能上的提高。
A 、 全表 扫 描
全表 扫 描就是 顺 序地 访问 表中 每 条 记录 。 ORACLE 采用一次 读 入多个数据 块 (database block) 的方式 优 化全表 扫 描。
B 、 通 过 ROWID 访问 表
你可以采用基于 ROWID 的 访问 方式情况,提高 访问 表的效率, ROWID 包含了表中 记录 的物理位置信息。 ORACLE 采用索引 (INDEX) 实现 了数据和存放数据的物理位置 (ROWID) 之 间 的 联 系。通常索引提供了快速 访问 ROWID 的方法,因此那些基于索引列的 查询 就可以得到性能上的提高。
3.
共享
SQL
语
句
为
了不重
复
解析相同的
SQL
语
句,在第一次解析之后,
ORACLE
将
SQL
语
句存放在内存中。
这块
位于系
统
全局区域
SGA(system global area)
的共享池
(shared buffer pool)
中的内存可以被所有的数据
库
用
户
共享。
因此,当你
执
行一个
SQL
语
句
(
有
时
被称
为
一个游
标
)
时
,如果它和之前的
执
行
过
的
语
句
完全相同,
ORACLE
就能很快
获
得已
经
被解析的
语
句以及最好的
执
行路径。
ORACLE
的
这
个功能大大地提高了
SQL
的
执
行性能并
节
省了内存的使用。
可惜的是 ORACLE 只 对简单 的表提供高速 缓 冲 (cache buffering) , 这 个功能并不适用于多表 连 接 查询 。
数据 库 管理 员 必 须 在 init.ora 中 为这 个区域 设 置合适的参数,当 这 个内存区域越大,就可以保留更多的 语 句,当然被共享的可能性也就越大了。
当你向 ORACLE 提交一个 SQL 语 句, ORACLE 会首先在 这块 内存中 查 找相同的 语 句 。 这 里需要注明的是, ORACLE 对 两者采取的是一 种严 格匹配,要达成共享, SQL 语 句必 须 完全相同 ( 包括空格, 换 行等 ) 。
数据 库 管理 员 必 须 在 init.ora 中 为这 个区域 设 置合适的参数,当 这 个内存区域越大,就可以保留更多的 语 句,当然被共享的可能性也就越大了。
共享的 语 句必 须满 足三个条件:
A 、 字符 级 的比 较 : 当前被 执 行的 语 句和共享池中的 语 句必 须 完全相同。
B 、 两个 语 句所指的 对 象必 须 完全相同:
C 、 两个 SQL 语 句中必 须 使用相同的名字的 绑 定 变 量 (bind variables) 。
可惜的是 ORACLE 只 对简单 的表提供高速 缓 冲 (cache buffering) , 这 个功能并不适用于多表 连 接 查询 。
数据 库 管理 员 必 须 在 init.ora 中 为这 个区域 设 置合适的参数,当 这 个内存区域越大,就可以保留更多的 语 句,当然被共享的可能性也就越大了。
当你向 ORACLE 提交一个 SQL 语 句, ORACLE 会首先在 这块 内存中 查 找相同的 语 句 。 这 里需要注明的是, ORACLE 对 两者采取的是一 种严 格匹配,要达成共享, SQL 语 句必 须 完全相同 ( 包括空格, 换 行等 ) 。
数据 库 管理 员 必 须 在 init.ora 中 为这 个区域 设 置合适的参数,当 这 个内存区域越大,就可以保留更多的 语 句,当然被共享的可能性也就越大了。
共享的 语 句必 须满 足三个条件:
A 、 字符 级 的比 较 : 当前被 执 行的 语 句和共享池中的 语 句必 须 完全相同。
B 、 两个 语 句所指的 对 象必 须 完全相同:
C 、 两个 SQL 语 句中必 须 使用相同的名字的 绑 定 变 量 (bind variables) 。
4.
选择
最有效率的表名
顺
序
(
只在基于
规则
的
优
化器中有效
)
ORACLE
的解析器按照从右到左的
顺
序
处
理
FROM
子句中的表名,因此
FROM
子句中写在最后的表
(
基
础
表
driving table)
将被最先
处
理。在
FROM
子句中包含多个表的情况下,你必
须选择记录
条数最少的表作
为
基
础
表。当
ORACLE
处
理多个表
时
,
会运用排序及合并的方式
连
接它
们
。首先,
扫
描第一个表
(FROM
子句中最后的那个表
)
并
对记录进
行派序,然后
扫
描第二个表
(FROM
子句中最后第二个表
)
,最后将所有从第二个表中
检
索出的
记录
与第一个表中合适
记录进
行合并。
如果有 3 个以上的表 连 接 查询 , 那就需要 选择 交叉表 (intersection table) 作 为 基 础 表, 交叉表是指那个被其他表所引用的表。
如果有 3 个以上的表 连 接 查询 , 那就需要 选择 交叉表 (intersection table) 作 为 基 础 表, 交叉表是指那个被其他表所引用的表。
5.WHERE
子句中的
连
接
顺
序
ORACLE
采用自下而上的
顺
序解析
WHERE
子句,根据
这
个原理,表之
间
的
连
接必
须
写在其他
WHERE
条件之前,
那些可以
过滤
掉最大数量
记录
的条件必
须
写在
WHERE
子句的末尾。
6.SELECT
子句中避免使用
' * '
当你想在
SELECT
子句中列出所有的
COLUMN
时
,使用
动态
SQL
列引用
'*'
是一个方便的方法。不幸的是,
这
是一个非常低效的方法。
实际
上,
ORACLE
在解析的
过
程中,
会将
'*'
依次
转换
成所有的列名,
这
个工作是通
过查询
数据字典完成的,
这
意味着将耗
费
更多的
时间
。
7.
减少
访问
数据
库
的次数
当
执
行
每
条
SQL
语
句
时
,
ORACLE
在内部
执
行了
许
多工作:解析
SQL
语
句,估算索引的利用率,
绑
定
变
量,
读
数据
块
等等。由此可
见
,减少
访问
数据
库
的次数,就能
实际
上减少
ORACLE
的工作量。
8.
使用
DECODE
函数来减少
处
理
时间
使用
DECODE
函数可以避免重
复扫
描相同
记录
或重
复连
接相同的表。
9.
整合
简单
,无
关联
的数据
库访问
如果你有几个
简单
的数据
库查询语
句,你可以把它
们
整合到一个
查询
中
(
即使它
们
之
间
没有
关
系
)
10.
删
除重
复记录
11.
用
TRUNCATE
替代
DELETE
当
删
除表中的
记录时
,在通常情况下,
回
滚
段
(rollback segments )
用来存放可以被恢
复
的信息。
如果你没有
COMMIT
事
务
,
ORACLE
会将数据恢
复
到
删
除之前的状
态
(
准确地
说
是恢
复
到
执
行
删
除命令之前的状况
)
。
而当运用
TRUNCATE
时
,
回
滚
段不再存放任何可被恢
复
的信息。当命令运行后,数据不能被恢
复
。因此很少的
资
源被
调
用,
执
行
时间
也会很短。
12.
尽量多使用
COMMIT
只要有可能,在程序中尽量多使用
COMMIT
,
这样
程序的性能得到提高,需求也会因
为
COMMIT
所
释
放的
资
源而减少
COMMIT 所 释 放的 资 源:
A 、 回 滚 段上用于恢 复 数据的信息。
B 、被程序 语 句 获 得的 锁 。
C 、 redo log buffer 中的空 间 。
D 、 ORACLE 为 管理上述 3 种资 源中的内部花 费 。
COMMIT 所 释 放的 资 源:
A 、 回 滚 段上用于恢 复 数据的信息。
B 、被程序 语 句 获 得的 锁 。
C 、 redo log buffer 中的空 间 。
D 、 ORACLE 为 管理上述 3 种资 源中的内部花 费 。
13.
计
算
记录
条数
和一般的
观
点相反,
count(*)
比
count(1)
稍快,当然如果可以通
过
索引
检
索,
对
索引列的
计
数仍旧是最快的。例如
COUNT(EMPNO)
14.
用
Where
子句替
换
HAVING
子句
避免使用
HAVING
子句,
HAVING
只会在
检
索出所有
记录
之后才
对结
果集
进
行
过滤
。
这
个
处
理需要排序,
总计
等操作。如果能通
过
WHERE
子句限制
记录
的数目,那就能减少
这
方面的
开销
。
15.
减少
对
表的
查询
在含有子
查询
的
SQL
语
句中,要特
别
注意减少
对
表的
查询
。
16.
通
过
内部函数提高
SQL
效率。
17.
使用表的
别
名
(Alias)
当在
SQL
语
句中
连
接多个表
时
,
请
使用表的
别
名并把
别
名前
缀
于
每
个
Column
上。
这样
一来,就可以减少解析的
时间
并减少那些由
Column
歧
义
引起的
语
法
错误
。
18.
用
EXISTS
替代
IN
在
许
多基于基
础
表的
查询
中,
为
了
满
足一个条件,往往需要
对
另一个表
进
行
联
接。在
这种
情况下,使用
EXISTS(
或
NOT EXISTS)
通常将提高
查询
的效率。
19.
用
NOT EXISTS
替代
NOT IN
在子
查询
中,
NOT IN
子句将
执
行一个内部的排序和合并。
无
论
在哪
种
情况下,
NOT IN
都是最低效的
(
因
为
它
对
子
查询
中的表
执
行了一个全表遍
历
)
。
为
了避免使用
NOT IN
,我
们
可以把它改写成外
连
接
(Outer Joins)
或
NOT EXISTS
。
20.
用表
连
接替
换
EXISTS
通常来
说
,
采用表
连
接的方式比
EXISTS
更有效率
21.
用
EXISTS
替
换
DISTINCT
当提交一个包含一
对
多表信息
(
比如部
门
表和雇
员
表
)
的
查询时
,避免在
SELECT
子句中使用
DISTINCT
。
一般可以考
虑
用
EXIST
替
换
Oracle
内存
结
构
(
三
)----Process Memory
的
详细
信息
The Process
Memory
:
除了 SGA(System Global Area) 之外 ,Oracle 进 程 还 使用下面三个全局区 :
The Process Global Area ( PGA )
The User Global Area (UGA)
The Call Global Area (CGA)
除了 SGA(System Global Area) 之外 ,Oracle 进 程 还 使用下面三个全局区 :
The Process Global Area ( PGA )
The User Global Area (UGA)
The Call Global Area (CGA)
很多人都搞不清楚
PGA
和
UGA
两者之
间
的区
别
,
实际
上两者之
间
的区
别
跟一个
进
程和一个会
话
之
间
的区
别
是
类
似的
.
尽管
说进
程和会
话
之
间
一般都是一
对
一的
关
系
,
但
实际
上比
这
个更
复杂
.
一个很明
显
的情况是
MTS
配置
,
会
话
往往会比
进
程多得多
.
在
这种
配置下
,
每
一个
进
程会有一个
PGA
,
每
一个会
话
会有一个
UGA.
PGA
所包含的信息跟会
话
是无任何
关联
的
,
而
UGA
包含的信息是以特定的会
话为
基
础
的
.
The
PGA
:
进
程全局区
(
PGA
即可以理解
)
为
Process Global Area,
也可以理解
为
Program Global Area.
它的内存段是在
进
程私有区
(Process Private
Memory
而不是在共享区
(Shared Memory
它是个全局区意味着它包含了所有代
).
)
码
有可能
进
入的全局
变
量和数据
结
构
,
但是它是不被所有
进
程共享的
.
每
个
Oracle
的服
务
器
进
程都包含有属于自己的
PGA
,
它只包含了本
进
程的相
关
特定信息
.
PGA
中的
结
构不需要由
latches
来保
护
,
因
为
其它的
进
程是不能
进
入到
这
里面来
访问
的
.
PGA
包含的是有
关进
程正在使用的操作系
统资
源信息以及
进
程的状
态
信息
,
而其它的
进
程所使用的
Oracle
的共享
资
源是在
SGA
中
.
PGA
是私有的而不是共享的
,
这
个机制是有必要的
,
因
为
当
进
程死掉后可以把
这
些
资
源清除和
释
放掉
.
PGA
包含两个主要区域
:Fixed
PGA
和
Variable PGA
或称
为
PGA
Heap. Fixed
PGA
的作用跟
Fixed SGA
是
类
似的
,
都包含原子
变
量
(
不可分的
),
小的数据
结
构和指向
Variable
PGA
的指
针
.
Variable
PGA
是一个堆
.
它的
Chunks
可以从
Fixed Table X$KSMPP
查
看得到
,
这
个表的
结
构跟前面有提到的
X$KSMSP
是相同的
.
PGA
包含了一些有
HEAP
关
Fixed Table
的永久性内存
,
它跟某些参数的
设
置有依
赖关
系
.
这
些参数包含
DB_FILES,LOG_FILES,CONTROL_FILES.
The UGA:
UGA(User Global Area)
包含的是特定会
话
的信息
,
有如下一些
:
所打
开
游
标
的持
续
和运行
时间
内的区域
包的状
态
信息
,
特定的
变
量
Java
会
话
状
态
可以用的
ROLES
被
ENABLE
的跟踪事件
起作用的
NLS
参数
设
置
打
开
的
DBLINK
会
话
的入口控制
跟
PGA
一
样
,UGA
也由两区
组
成
:Fixed UGA
和
Variable UGA,
也称
为
UGA HEAP. Fixed UGA
包含了大
约
70
个原子
变
量
,
小的数据
结
构和指向
Variable UGA
的指
针
.
UGA HEAP
中的
Chunks
可以从它
们
自己的会
话
中通
过查
看表
X$KSMUP
获
得相
关
信息
,
这
个表的
结
构跟
X$KSMSP
是一
样
的
.UGA HEAP
包含了一些有
关
fixed tables
的永久性内存段
,
跟一些参数的
设
置有依
赖关
系
.
这
些参数有
OPEN_CURSORS,OPEN_LINKS,
和
MAX
_ENABLE_ROLES.
UGA
在内存中的位置依
赖
于会
话
的配置方式
.
如果会
话连
接的配置方式是
专
用服
务
器模式
(DDS)
即是一个会
话对应
一个
进
程
,
则
UGA
是放在
PGA
中的
.
在
PGA
中
,Fixed UGA
是其中的一个
Chunk,
而
UGA HEAP
是
PGA
的一个子堆
(Subheap).
如果会
话连
接是配置
为
共享服
务
器模式
(MTS), Fixed UGA
是
SHARED POOL
中的一个
Chunk,
而
UGA HEAP
则
是
SHARED POOL
中的子堆
(Subheap)
The CGA:
跟其它的全局区不同
,Call Global Area
是短
暂
性存在的
.
它只有在
调
用数据期
间
存在
,
一般是在
对实
例的最低
级别
的
调
用
时
才需要
CGA,
如下
:
分析一个
SQL
语
句
执
行一个
SQL
语
句
取出一个
SELECT
语
句的
输
出
一个
单
独的
CGA
在
递归调
用
时
是需要的
.
在
SQL
语
句的分析
过
程中
,
对
数据字典信息的
递归调
用是需要的
,
因
为
要
对
SQL
语
句
进
行
语
法分析
,
还
有在
语
句的
优
化期
间
要
计
算
执
行
计
划
.
执
行
PL/SQL
块时
在
处
理
SQL
语
句的
执
行
时
也是需要
递归调
用的
,
在
DML
语
句的
执
行
时
要
处
理触
发
器
执
行也是需要
递归调
用的
.
不管
UGA
是放在
PGA
中
还
是在
SGA
中
,CGA
都是
PGA
的一个子堆
(Subheap).
这
个事
实
的一个重要推
论
是在一个
调
用的期
间
会
话
必
须
是一个
进
程
.
对
于在一个
MTS
的
Oracle
数据
库进
程
应
用
开发时关
于
这
一点的理解是很重要的
.
如果相
应
的
调
用
较
多
,
就得增加
processes
的数量以适
应调
用的增加
.
没有
CGA
中的数据
结
构
,CALLS
是没法工作的
.
而
实际
上跟一次
CALL
相
关
的数据
结
构一般都是放在
UGA
中
,
如
SQL AREA,PL/SQL AREA
和
SORT AREA
它
们
都必
须
在
UGA
中
,
因
为
它
们
要在各
CALLS
之
间
要一直存在并且可用
.
而
CGA
中所包含的数据
结
构是要在一次
CALL
结
束后能
够释
放的
.
例如
CGA
包含了
关
于
递归调
用的信息
,
直接
I/O BUFFER
等
还
有其它的一些
临时
性的数据
结
构
.
Java Call
Memory
也是在
CGA
中
.
这
一段内存比
Oracle
的其它内存段管理得更密集
.
它分成三个
Space: Stack Space, New Space, Old Space.
在
New Space
和
Old Space
中不再被参考使用的
Chunks,
根据它
们
在使用期
间
的
长
度及
SIZE
的不同
,
在
调
用的
执
行
过
程中将被当成不用的
Chunks
收集起来
.New Space Chunks
很多次的不用的
Chunks
的反
复
收集
过
程中没有被收集的
Chunks
将会被放到
Old Space Chunks
中
.
这
是在
Oracle
内存管理中唯一的一个
废
物收集
(garbage collection),
其它的
Oracle
内存段都是
释
放
Dead Chunks.
Process
Memory
Allocation
跟
SGA
不一
样
的是
,SGA
在
实
例启
动
之后
SIZE
就已
经
是定下来的
,
而
PGA
的
SIZE
是会增
长
的
.
通
过
使用
malloc()
或者
sbrk()
系
统调
用来
为进
程增加堆数据段大小而使得
PGA
的
SIZE
的增
长
.OS
的新虚
拟
内存会被做
为
PGA
HEAP
中的一个新的区被加到
PGA
中来
.
这
些区一般只几
KB
大
,
如果有需要
,Oracle
将会
给
分配上千个区
.
操作系
统对每
个
进
程的堆数据段的增
长
是有限制的
.
大部分的情况是操作系
统
的内存参数
进
行限制
(kernel parameter: MAXDSIZ),
有一些情况它的缺省
值
是可以以
每
个
进
程
为
基准
进
行修改的
.
对
于所有的
进
程
,
操作系
统对
整个虚
拟
内存也有一个系
统
全局性的限制
,
这
个限制跟系
统
的
SWAP SPACE
相
关
.
一旦超
过
了
这
两个限制
,Oracle
的
进
程在
执
行中会遇到
ORA-4030
错误
.
ORA-4030
这
个
错误
的
产
生一般不是因
为每
个
进
程的
资
源限制而是因
为
SWAP SPACE
空
间
不足造成
.
为
了
诊
断
这
个
问题
可以使用操作系
统
的一些
选项
来
查
看
SWAP SPACE
的使用情况
.
另外
,
在一些操作系
统
中
,Oracle
包含了一个工具叫
maxmem,
它可以用来
查
看
每
个
进
程可以被分配的堆数据段的最大
SIZE
以及哪一个限制是第一次超
过
的
.
如果
这
个
问题
的出
现
是因
为
SWAP SPACE
空
间
不足
,
而且
换页
的
动
作非常
频
繁而且
较
多
,
则
需要减少系
统
一
级
的虚
拟
内存的使用
,
这
个可以通
过
减少
进
程数也可以通
过
减少
每
个
进
程的内存限制
.
如果
换页动
作不
频
繁而且比
较
少
,
则
需要
调
大
SWAP SPACE SIZE.
Process
Memory
Deallocation:
Oracle
堆的增
长
比它
们
的收
缩
要来得容易
,
当然它
们
的
SIZE
也是可以收
缩
的
.
在
V$MYSTAT
和
V$SESSTAT
视图
中
,
session
的
统计
信息
session
uga
memory
和
session pga
分
memory
别显
示了当前
session
的
UGA
和
PGA
的内存大小
,
包含内部的空
闲
空
间
.
相
应
的
统计
信息
session
uga
memory
和
session pga
分
memory max
max
别显
示了在
session
的生存期
间
所使用
过
得最大的
UGA
和最大的
PGA
.
UGA
和
PGA
只有在特定的操作后才会收
缩
,
这
些操作如一次磁
盘
排序的合并操作
,
或者用程序
DBMS_
SESSION
.FREE_UNUSED_USER_
MEMORY
显
示
释
放内存
.
只有整个
free heap extent
会被
释
放
给
父堆或者是
进
程堆数据段
,
所以有一部分的内部
free space
在内存
释
放后仍然存在于
subheap
中
.
在大多的操作系
统环
境下
,Oracle
是不会减少
进
程堆数据段也不会
释
放虚
拟
内存并将其返
还给
操作系
统
的
.
所以从一个操作系
统
的
查
看中
,
一个
Oracle
的
进
程将会把虚
拟
内存
SIZE
作
为
HWM
而保留着
.
如果有必要
时
,Oracle
是会将一些没用的虚
拟
内存
页换页
出去的
.
因
为这
个原因
,
有
关
Oracle
进
程的虚
拟
内存
页
的操作系
统统计
信息都是很
难
理解的
.
所以一般用的是
Oracle
内部
统计
信息来代替使用操作系
统
的
统计
信息
.
程序
DBMS_
SESSION
只能在
.FREE_UNUSED_USER_
MEMORY
连
接是配置
为
MTS
模式的
应
用才能使用
.
这
个最好是少点使用
,
因
为
它只
释
放大的包的
array
变
量所占用的内存返
还给
Large Pool
或者是
Shared Pool.
一般地
,UGA HEAP
的内存
应该
首先被
释
放
,
可以通
过
指派新的空
array
给
array
变
量使用
,
也可以通
过调
用程序
DBMS_
SESSION
.RESET_PACKAGE.