对
Oracle
数据库性能优化一点总结
http://blog.csdn.net/lboss_li/archive/2005/09/22/487160.aspx
要提高应用系统的性能，在维持现有硬件平台不变的情况下，主要采用的方法由以下几种：
1)
调整操作系统参数，提高操作系统的管理性能；
2)
调整数据库管理系统的参数，提高数据库管理系统的性能；
3)
修改应用程序，提高应用程序的运行效率。
一、
ORACLE
数据库的内存结构
本文将着重对
SGA
的结构进行介绍：
SGA
通常又被称作
Shared Global Area,
包括以下几个共享区域：
1)
数据缓存（
the Database buffer cache
）
a)
数据缓存存放从数据库文件中读取的数据，可分为剩余缓存（
Free buffer
）、正在使用的缓存（
Pinned buffer
）、已使用缓存（
Dirty buffer
）。
2)
日志缓存（
redo log buffer
）
a)
存放数据库已改变的信息。
3)
共享存储池（
shared pool
）
a)
共享存储池主要由三个方面构成
:
i.
Library cache
：包括共享
SQL
区、用户自有
SQL
区、
PL/SQL
过程和程序包及控制结构；
ii.
Dictionary Cache
：存放数据库字典信息，如：表及视图名、列名及数据类型、各用户的存取权限；
iii.
Control structures
1.
请求及响应队列（
request and response queues
）（仅用于多线索模式）
2.
其他（
other miscellaneous information
）
二、
ORACLE
内存的工作机制
当用户请求被接受后，
ORACLE
的内存分配将分以下几个步骤进行：
1
．首先检查共享
SQL
区有无该
SQL
语句，如有在使用该
SQL
区执行用户的
SQL
语句（称作一次
library cache hit
），否则为该
SQL
语句分配共享
SQL
区
(
称作一次
library cache miss)
，同时为该语句分配自有
SQL
区。
2
．检查
Dictionary cache
中有无要访问的表
/
视图信息，若无则将其读入
Dictionary cache
中
(
称作一次
rowcache miss)
。
3
．检查数据缓存（
Database buffer cache
）
,
有无要操作的数据，如有则使用当前的缓存
(
称作一次
data buffer hit)
，否则，将按下列步骤为该数据请求新的缓存
(
称作一次
data buffer miss)
：
1
）
搜索
least-recently-used(LRU) list
，若发现
dirty buffer
则写入
dirty list
并继续搜索，如发现
free buffer
则将其分配给该用户，同时将该
buffer
移至
most-recently-used(MRU) list
，若未能搜索到
free buffer,
则触发
DBWR
进程将一些
dirty buffer
写入磁盘，并将这部分
dirty buffer
释放为
free buffer
。
2
）
从数据文件中将要操作的数据读入
buffer cache
中；
4
．如果用户执行的是
INSERT
、
DELETE
、
UPDATE
等操作，系统将为其分配
redo log buffer,
用于记录数据的变更情况，当
redo log buffer
中无
free buffer
时触发
LGWR
进程，将
redo log buffer
中的一些信息写如数据库的
LOG FILE
中。
三、内存使用情况分析
要确定一个数据库管理系统中的内存配置的优劣，首先应掌握系统中当前内存的使用情况。
ORACLE
数据库为数据库管理员（
DBA
）提供了相应的查询方法，用于查询数据库的内存使用情况。要提高系统的性能，
DBA
应重点检查以下几个指标：
1)
剩余内存（
free memory
）：
select * from v$sgastat where name=’free memory’;
a)
一般来说，当您在数据库启动并投入使用相当长时间后，系统尚有剩余内存空间，说明您的数据库的
SGA
设置是足够的，无需增加
SGA
空间。
2)
内存击中率
a)
library cache
的击中率：
select sum(pins-reloads)/sum(pins) from v$librarycache;
b)
数据字典的内存击中率：
select sum(gets-getmisses-usage-fixed)/sum(gets) from v$rowcache;
c)
数据共享区的击中率：
i.
select name,value from v$sysstat where name in (‘db block gets’,’consistent gets’,’physical reads’);
ii.
击中率
=1-(physical reads)/(db block gets + consistent gets)
注意：以上几个指标均应在系统运行足够长时间后进行检查。
ORACLE
数据库的初始化参数文件通常存放在
$ORACLE_HOME/dbs
路径下
,
其文件名为
init+
数据库
sid
、后缀为
.ora,
如
initorcl.ora
。其中
,
影响数据库内存大小的参数主要有：
1)
DB_BLOCK_SIZE
：每个数据库块的字节数
,
在数据库建立时已确定
,
为
1024
的整数倍
,
如
2048
、
4096
；
2)
DB_BLOCK_BUFFERS
：数据库数据缓存区的数据块数；
3)
SHARE_POOL_SIZE
：共享存储区的字节数；
4)
SORT_AREA_SIZE
：数据排序区的字节数；
当剩余内存（
free memory
）过少、内存击中率过低，当
library cache
的击中率或数据字典的内存击中率低于
0.95
时，考虑调整
SHARED_POOL_SIZE
，
DB_BLOCK_BUFFER
的大小。
此外，我们可以根据下面的算法，估算
SHARED_POOL_SIZE
的大小
:
SELECT SUM(value) FROM v$sesstat,v$statname
WHERE name=’session uga memory’
AND v$sesstat.statistic#=v$statname.statistic#;
该查询返回的是目前所有用户进程所占用的
SHARED_POOL
字节数；
SELECT SUM(value) FROM v$sesstat,v$statname
WHERE name=’session uga memory max’
AND v$sesstat.statistic#=v$statname.statistic#;
该查询返回的是目前所有用户进程所需占用的
SHARED_POOL
最大字节数；
我们可据此确定
SHARED_POOL_SIZE
的初始大小。
四、
SQL
语句执行效率问题
1
．检查占用
CPU
时间比较长的
sql
语句
:
select sql_text,cpu_time from v$sql where cpu_time >1e7 order by cpu_time
2
．执行效率最差的
10
条
sql
语句
SELECT * FROM ( SELECT PARSING_USER_ID EXECUTIONS SORTS, COMMAND_TYPE, DISK_READS, sql_text FROM v$sqlarea ORDER BY disk_reads DESC) WHERE ROWNUM<10 ;
3
．利用
V_$SQLAREA
视图提供了执行的细节。（执行、读取磁盘和读取缓冲区的次数）
Sselect SQL_TEXT, EXECUTIONS, DISK_READS, COMMAND_TYPE, OPTIMIZER_MODE, SHARABLE_MEM,BUFFER_GETS from v$sqlarea;
数据列
1)
EXECUTIONS
：执行次数
2)
DISK_READS
：读盘次数
3)
COMMAND_TYPE
：命令类型（
3:select,2:insert;6:update;7delete;47:pl/sql
程序单元）
4)
OPTIMIZER_MODE
：优化方式
5)
SQL_TEXT
：
Sql
语句
6)
SHARABLE_MEM
：占用
shared pool
的内存多少
7)
BUFFER_GETS
：读取缓冲区的次数
用途
1)
帮忙找出性能较差的
SQL
语句
2)
帮忙找出最高频率的
SQL
3)
帮忙分析是否需要索引或改善联接
4)
监控当前
Oracle
的
session
，如出现时钟的标志，表示此进程中的
sql
运行时间较长。