如何对oracle性能参数进行调优

xkmj

目前我的前台系统很慢，大部分时间耗在服务器开销，因为服务器的cpu利用率峰值在70% 但是数据库cpu利用率峰值才30%，所以我想调整数据库性能参数。特向各位大侠请教~

xkmj

一、SGA
1、Shared pool tunning
Shared pool的优化应该放在优先考虑，因为一个cache miss在shared pool中发生比在data buffer中发生导致的成本更高，由于dictionary数据一般比library cache中的数据在内存中保存的时间长，所以关键是library cache的优化。
Gets：（parse）在namespace中查找对象的次数；
Pins：（execution）在namespace中读取或执行对象的次数；
Reloads：(reparse)在执行阶段library cache misses的次数，导致sql需要重新解析。
1） 检查v$librarycache中sql area的gethitratio是否超过90％，如果未超过90％，应该检查应用代码，提高应用代码的效率。
Select gethitratio from v$librarycache where namespace=’sql area’;
2) v$librarycache中reloads/pins的比率应该小于1％，如果大于1％，应该增加参数shared_pool_size的值。
Select sum(pins) “executions”,sum(reloads) “cache misses”,sum(reloads)/sum(pins) from v$librarycache;
reloads/pins>1%有两种可能，一种是library cache空间不足，一种是sql中引用的对象不合法。
3）shared pool reserved size一般是shared pool size的10％，不能超过50％。V$shared_pool_reserved中的request misses＝0或没有持续增长，或者free_memory大于shared pool reserved size的50%，表明shared pool reserved size过大，可以压缩。
4）将大的匿名pl/sql代码块转换成小的匿名pl/sql代码块调用存储过程。
5）从9i开始，可以将execution plan与sql语句一起保存在library cache中，方便进行性能诊断。从v$sql_plan中可以看到execution plans。
6）保留大的对象在shared pool中。大的对象是造成内存碎片的主要原因，为了腾出空间许多小对象需要移出内存，从而影响了用户的性能。因此需要将一些常用的大的对象保留在shared pool中，下列对象需要保留在shared pool中：
a. 经常使用的存储过程；
b. 经常操作的表上的已编译的触发器
c. Sequence，因为Sequence移出shared pool后可能产生号码丢失。
查找没有保存在library cache中的大对象：
Select * from v$db_object_cache where sharable_mem>10000 and type in ('PACKAGE','PROCEDURE','FUNCTION','PACKAGE BODY') and kept='NO';
将这些对象保存在library cache中：
Execute dbms_shared_pool.keep(‘package_name’);
对应脚本：dbmspool.sql
7)查找是否存在过大的匿名pl/sql代码块。两种解决方案：
A．转换成小的匿名块调用存储过程
B．将其保留在shared pool中
查找是否存在过大的匿名pl/sql块：
Select sql_text from v$sqlarea where command_type=47 and length(sql_text)>500;
8）Dictionary cache的 优化
避免出现Dictionary cache的misses，或者misses的数量保持稳定,只能通过调整shared_pool_size来间接调整dictionary cache的大小。
Percent misses应该很低：大部分应该低于2％，合计应该低于15％
Select sum(getmisses)/sum(gets) from v$rowcache;
若超过15％，增加shared_pool_size的值。

xkmj

2、Buffer Cache
1）granule大小的设置，db_cache_size以字节为单位定义了default buffer pool的大小。
如果SGA<128M，granule=4M,否则granule＝16M，即需要调整sga的时候以granule为单位增加大小，并且sga的大小应该是granule的整数倍。
2) 根据v$db_cache_advice调整buffer cache的大小
SELECT size_for_estimate,buffers_for_estimate,estd_physical_read_factor,estd_physical_reads FROM v$db_cache_advice WHERE NAME='DEFAULT' AND advice_status='ON' AND block_size=(SELECT Value FROM v$parameter WHERE NAME='db_block_size');
estd_physical_read_factor<=1
3) 统计buffer cache的cache hit ratio>90%，如果低于90％，可以用下列方案解决：
增加buffer cache的值；
使用多个buffer pool；
Cache table；
为 sorting and parallel reads 建独立的buffer cache；
SELECT NAME,value FROM v$sysstat WHERE NAME IN ('session logical reads','physical reads','physical reads direct','physical reads direct(lob)');
Cache hit ratio=1-(physical reads-physical reads direct-physical reads direct (lob))/session logical reads;
Select 1-(phy.value-dir.value-lob.value)/log.value from v$sysstat log, v$sysstat phy, v$sysstat dir, v$sysstat LOB where log.name='session logical reads' and phy.name='physical reads' and dir.name='physical reads direct' and lob.name='physical reads direct (lob)';
影响cache hit ratio的因素：
全表扫描；应用设计；大表的随机访问；cache hits的不均衡分布
4）表空间使用自动空间管理，消除了自由空间列表的需求，可以减少数据库的竞争

xkmj

3、其他SGA对象
1）redo log buffer
对应的参数是log_buffer，缺省值与 OS相关，一般是500K。检查v$session_wait中是否存在log buffer wait,v$sysstat中是否存在redo buffer allocation retries
A、检查是否存在log buffer wait：
Select * from v$session_wait where event=’log buffer wait’ ;
如果出现等待，一是可以增加log buffer的大小，也可以通过将log 文件移到访问速度更快的磁盘来解决。
B、Select name,value from v$sysstat where name in (‘redo buffer allocation retries’,’redo entries’)
Redo buffer allocation retries接近0，小于redo entries 的1％，如果一直在增长，表明进程已经不得不等待redo buffer的空间。如果Redo buffer allocation retries过大，增加log_buffer的值。
C、检查日志文件上是否存在磁盘IO竞争现象
Select event,total_waits,time_waited,average_wait from v$system_event where event like ‘log file switch completion%’;
如果存在竞争，可以考虑将log文件转移到独立的、更快的存储设备上或增大log文件。
D、检查点的设置是否合理
检查alert.log文件中，是否存在‘checkpoint not complete’；
Select event,total_waits,time_waited,average_wait from v$system_event where event like ‘log file switch (check%’;
如果存在等待，调整log_checkpoint_interval、log_checkpoint_timeout的设置。
E、检查log archiver的工作
Select event,total_waits,time_waited,average_wait from v$system_event where event like ‘log file switch (arch%’;
如果存在等待，检查保存归档日志的存储设备是否已满，增加日志文件组，调整log_archiver_max_processes。
F、DB_block_checksum=true，因此增加了性能负担。（为了保证数据的一致性，oracle的写数据的时候加一个checksum在block上，在读数据的时候对checksum进行验证）
2）java pool
对于大的应用，java_pool_size应>=50M，对于一般的java存储过程，缺省的20M已经够用了。
3）检查是否需要调整DBWn
Select total_waits from v$system_event where event=’free buffer waits’;

xkmj

、数据库参数属性
col PROPERTY_NAME format a25
col PROPERTY_VALUE format a30
col DESCRIPTION format a100
select * from database_properties;

select * from v$version;

1、求当前会话的SID，SERIAL#
SELECT Sid, Serial# FROM V$session
WHERE Audsid = Sys_Context('USERENV', 'SESSIONID');

2、查询session的OS进程ID
SELECT p.Spid "OS Thread", b.NAME "Name-User", s.Program, s.Sid, s.Serial#,s.Osuser, s.Machine
FROM V$process p, V$session s, V$bgprocess b
WHERE p.Addr = s.Paddr
AND p.Addr = b.Paddr And (s.sid=&1 or p.spid=&1)
UNION ALL
SELECT p.Spid "OS Thread", s.Username "Name-User", s.Program, s.Sid,s.Serial#, s.Osuser, s.Machine
FROM V$process p, V$session s
WHERE p.Addr = s.Paddr
And (s.sid=&1 or p.spid=&1)
AND s.Username IS NOT NULL;


3、根据sid查看对应连接正在运行的sql
SELECT /*+ PUSH_SUBQ */ Command_Type, Sql_Text, Sharable_Mem, Persistent_Mem, Runtime_Mem, Sorts,
Version_Count, Loaded_Versions, Open_Versions, Users_Opening, Executions,
Users_Executing, Loads, First_Load_Time, Invalidations, Parse_Calls,
Disk_Reads, Buffer_Gets, Rows_Processed, SYSDATE Start_Time,
SYSDATE Finish_Time, '>' || Address Sql_Address, 'N' Status
FROM V$sqlarea WHERE Address = (SELECT Sql_Address
FROM V$session WHERE Sid = &sid );


4、查找object为哪些进程所用
SELECT p.Spid, s.Sid, s.Serial# Serial_Num, s.Username User_Name,
a.TYPE Object_Type, s.Osuser Os_User_Name, a.Owner,
a.OBJECT Object_Name,
Decode(Sign(48 - Command), 1, To_Char(Command), 'Action Code #' || To_Char(Command)) Action,
p.Program Oracle_Process, s.Terminal Terminal, s.Program Program,
s.Status Session_Status
FROM V$session s, V$access a, V$process p
WHERE s.Paddr = p.Addr
AND s.TYPE = 'USER'
AND a.Sid = s.Sid
AND a.OBJECT = '&obj'
ORDER BY s.Username, s.Osuser


5、查看有哪些用户连接
SELECT s.Osuser Os_User_Name,Decode(Sign(48 - Command),1,To_Char(Command),
'Action Code #' || To_Char(Command)) Action,
p.Program Oracle_Process, Status Session_Status, s.Terminal Terminal,
s.Program Program, s.Username User_Name,
s.Fixed_Table_Sequence Activity_Meter, '' Query, 0 Memory,
0 Max_Memory, 0 Cpu_Usage, s.Sid, s.Serial# Serial_Num
FROM V$session s, V$process p
WHERE s.Paddr = p.Addr
AND s.TYPE = 'USER'
ORDER BY s.Username, s.Osuser


6、根据v.sid查看对应连接的资源占用等情况
SELECT n.NAME, v.VALUE, n.CLASS, n.Statistic# FROM V$statname n, V$sesstat v
WHERE v.Sid = &sid
AND v.Statistic# = n.Statistic#
ORDER BY n.CLASS, n.Statistic#


7、查询耗资源的进程（top session）
SELECT s.Schemaname Schema_Name,Decode(Sign(48 - Command),
1, To_Char(Command), 'Action Code #' || To_Char(Command)) Action,Status Session_Status, s.Osuser Os_User_Name, s.Sid, p.Spid,s.Serial# Serial_Num, Nvl(s.Username, '[Oracle process]') User_Name,
s.Terminal Terminal, s.Program Program, St.VALUE Criteria_Value
FROM V$sesstat St, V$session s, V$process p
WHERE St.Sid = s.Sid
AND St.Statistic# = To_Number('38')
AND ('ALL' = 'ALL' OR s.Status = 'ALL')
AND p.Addr = s.Paddr
ORDER BY St.VALUE DESC, p.Spid ASC, s.Username ASC, s.Osuser ASC


8、查看锁（lock）情况
SELECT /*+ RULE */ Ls.Osuser Os_User_Name, Ls.Username User_Name,Decode(Ls.TYPE,
'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock','TX', 'Transaction enqueue lock', 'UL', 'User supplied lock') Lock_Type,o.Object_Name OBJECT,Decode(Ls.Lmode,1, NULL, 2, 'Row Share', 3, 'Row Exclusive',
4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive',NULL) Lock_Mode,o.Owner, Ls.Sid, Ls.Serial# Serial_Num, Ls.Id1, Ls.Id2 FROM Sys.Dba_Objects o,
(SELECT s.Osuser, s.Username, l.TYPE, l.Lmode, s.Sid, s.Serial#, l.Id1,l.Id2 FROM V$session s, V$lock l
WHERE s.Sid = l.Sid) Ls
WHERE o.Object_Id = Ls.Id1
AND o.Owner <> 'SYS'
ORDER BY o.Owner, o.Object_Name;

9、查看等待（wait）情况

SELECT Ws.CLASS, Ws.COUNT COUNT, SUM(Ss.VALUE) Sum_Value
FROM V$waitstat Ws, V$sysstat Ss
WHERE Ss.NAME IN ('db block gets', 'consistent gets')
GROUP BY Ws.CLASS, Ws.COUNT;


10、求process/session的状态
SELECT p.Pid, p.Spid, s.Program, s.Sid, s.Serial#
FROM V$process p, V$session s
WHERE s.Paddr = p.Addr;


11、求谁阻塞了某个session(10g)
SELECT Sid, Username, Event, Blocking_Session, Seconds_In_Wait, Wait_Time
FROM V$session
WHERE State IN ('WAITING')
AND Wait_Class != 'Idle';


12、查会话的阻塞
col user_name format a32
SELECT /*+ rule */ Lpad(' ', Decode(l.Xidusn, 0, 3, 0)) || l.Oracle_Username User_Name,
o.Owner, o.Object_Name, s.Sid, s.Serial#
FROM V$locked_Object l, Dba_Objects o, V$session s
WHERE l.Object_Id = o.Object_Id
AND l.Session_Id = s.Sid
ORDER BY o.Object_Id, Xidusn DESC;
col username format a15
col lock_level format a8
col owner format a18
col object_name format a32
SELECT /*+ rule */ s.Username,Decode(l.TYPE, 'tm', 'table lock', 'tx', 'row lock', NULL) Lock_Level,
o.Owner, o.Object_Name, s.Sid, s.Serial#
FROM V$session s, V$lock l, Dba_Objects o
WHERE l.Sid = s.Sid
AND l.Id1 = o.Object_Id(+)
AND s.Username IS NOT NULL;


13、求等待的事件及会话信息/求会话的等待及会话信息
SELECT Se.Sid, s.Username, Se.Event, Se.Total_Waits, Se.Time_Waited,Se.Average_Wait
FROM V$session s, V$session_Event Se
WHERE s.Username IS NOT NULL
AND Se.Sid = s.Sid
AND s.Status = 'ACTIVE'
AND Se.Event NOT LIKE '%SQL*Net%'
ORDER BY s.Username;
SELECT s.Sid, s.Username, Sw.Event, Sw.Wait_Time, Sw.State,Sw.Seconds_In_Wait
FROM V$session s, V$session_Wait Sw
WHERE s.Username IS NOT NULL
AND Sw.Sid = s.Sid
AND Sw.Event NOT LIKE '%SQL*Net%'
ORDER BY s.Username;


14、求会话等待的file_id/block_id
col event format a24
col p1text format a12
col p2text format a12
col p3text format a12
SELECT Sid, Event, P1text, P1, P2text, P2, P3text, P3
FROM V$session_Wait
WHERE Event NOT LIKE '%SQL%'
AND Event NOT LIKE '%rdbms%'
AND Event NOT LIKE '%mon%'
ORDER BY Event;
SELECT NAME, Wait_Time
FROM V$latch l
WHERE EXISTS (SELECT 1
FROM (SELECT Sid, Event, P1text, P1, P2text, P2, P3text, P3
FROM V$session_Wait
WHERE Event NOT LIKE '%SQL%'
AND Event NOT LIKE '%rdbms%'
AND Event NOT LIKE '%mon%') x
WHERE x.P1 = l.Latch#);


15、求会话等待的对象
col owner format a18
col segment_name format a32
col segment_type format a32
SELECT Owner, Segment_Name, Segment_Type
FROM Dba_Extents
WHERE File_Id = &File_Id
AND &Block_Id BETWEEN Block_Id AND Block_Id + Blocks - 1;


16、求出某个进程，并对它进行跟踪
SELECT s.Sid, s.Serial#
FROM V$session s, V$process p
WHERE s.Paddr = p.Addr
AND p.Spid = &1;
Exec Dbms_System.Set_Sql_Trace_In_Session(&1, &2, TRUE);
Exec Dbms_System.Set_Sql_Trace_In_Session(&1, &2, FALSE);


17、求当前session的跟踪文件
SELECT P1.VALUE || '/' || P2.VALUE || '_ora_' || p.Spid || '.ora' Filename
FROM V$process p, V$session s, V$parameter P1, V$parameter P2
WHERE P1.NAME = 'user_dump_dest'
AND P2.NAME = 'instance_name'
AND p.Addr = s.Paddr
AND s.Audsid = Userenv('SESSIONID')
AND p.Background IS NULL
AND Instr(p.Program, 'CJQ') = 0;


18、求出锁定的对象
SELECT Do.Object_Name, Session_Id, Process, Locked_Mode
FROM V$locked_Object Lo, Dba_Objects Do
WHERE Lo.Object_Id = Do.Object_Id;

19、DB_Cache建议
SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads
FROM V$DB_CACHE_ADVICE
WHERE name = 'DEFAULT'
AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')
AND advice_status = 'ON';

20、查看各项SGA相关参数：SGA，SGASTAT
select substr(name,1,10) name,substr(value,1,10) value
from v$parameter where name = 'log_buffer';

select * from v$sgastat ;

select * from v$sga;

show parameters area_size   #查看 各项区域内存参数， 其中sort_area为排序参数用；

各项视图建议参数值：V$DB_CACHE_ADVICE、V$SHARED_POOL_ADVICE），关于PGA
也有相关视图V$PGA_TARGET_ADVICE 等。

21、内存使用锁定在物理内存：
AIX 5L（AIX 4.3.3 以上）
logon aix as root
cd /usr/samples/kernel
./vmtune (信息如下) v_pingshm已经是1
./vmtune -S 1
然后oracle用户修改initSID.ora 中 lock_sga = true
重新启动数据库

HP UNIX
Root身份登陆
Create the file "/etc/privgroup": vi /etc/privgroup
Add line "dba MLOCK" to file
As root, run the command "/etc/setprivgrp -f /etc/privgroup":
$/etc/setprivgrp -f /etc/privgroup
oracle用户修改initSID.ora中lock_sga=true
重新启动数据库

SOLARIS (solaris2.6以上)
8i版本以上数据库默认使用隐藏参数 use_ism = true ，自动锁定SGA于内存中,不用设置
lock_sga, 如果设置 lock_sga =true 使用非 root 用户启动数据库将返回错误。

WINDOWS （作用不大）
不能设置lock_sga=true,可以通过设置pre_page_sga=true,使得数据库启动的时候就把所有内
存页装载，这样可能起到一定的作用。

22、内存参数调整
数据缓冲区命中率
select value from v$sysstat where name ='physical reads';

select value from v$sysstat where name ='physical reads direct';

select value from v$sysstat where name ='physical reads direct (lob)';

select value from v$sysstat where name ='consistent gets';

select value from v$sysstat where name = 'db block gets';

这里命中率的计算应该是
令 x = physical reads direct + physical reads direct (lob)
命中率 =100 - ( physical reads - x) / (consistent gets + db block gets - x)*100
通常如果发现命中率低于90%,则应该调整应用可可以考虑是否增大数据缓冲区；

共享池的命中率
select sum(pinhits)/sum(pins)*100 "hit radio" from v$librarycache;

假如共享池的命中率低于95%,就要考虑调整应用（通常是没使用bind var ）或者增加内存；

关于排序部分
select name,value from v$sysstat where name like '%sort%';

假如我们发现sorts (disk)/ (sorts (memory)+ sorts (disk))的比例过高，则通常意味着
sort_area_size 部分内存较小，可考虑调整相应的参数。

关于log_buffer
select name,value from v$sysstat
where name in('redo entries','redo buffer allocation retries');

假如 redo buffer allocation retries/ redo entries 的比例超过1%我们就可以考虑增大log_buffer

xkmj

1.缓存命中率：
column phys      format 999,999,999  heading 'Physical Reads'
column gets      format 999,999,999  heading 'DB Block Gets'
column con_gets  format 999,999,999  heading 'Consistent Gets'
column hitratio  format 99.999       heading 'Hit Ratio'

select sum(decode(name,'physical reads',value,0)) phys,
       sum(decode(name,'db block gets',value,0)) gets,
       sum(decode(name,'Consistent Gets',value,0)) con_gets,
       (1-(sum(decode(name,'physical reads',value,0))/(sum(decode(name,'db block gets',value,0))+
       sum(decode(name,'consistent gets',value,0)))))*100 HitRatio
from v$sysstat;

2.数据字典缓存命中率

select  (1-(sum(getmisses)/sum(gets)))*100 "Hit Ratio"
from  v$rowcache;


3.库缓存命中率

select  sum(Pins)/(sum(Pins)+sum(reloads))* 100 "Hit Ratio"
from v$LibraryCache;


4.内存排序命中率

select a.value "Disk Sorts",b.value "Memory Sorts",
       round((100*b.value)/decode((a.value+b.value),0,1,(a.value+b.value)),2)"ct Memory Sorts"
from v$sysstat a,v$sysstat b
where a.name='sorts(disk)' and b.name='sorts(memory)';

5.空闲的数据缓冲区的比例
select decode(state,0,'FREE',
       1,decode(lrba_seq,0,'AVAILABLE','BEING USED'),
       3,'BEING USED',state)"BLOCK STATUS",
       count(*)
from x$bh
group by decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE',
         'BEING USED'),3,'BEING USED',state);

xkmj

一、 为什么要进行数据库优化
　　
　　数据库优化不仅仅是DBA（数据库管理员）的事情，它也是应用设计人员、应用开发人员必须作的事情。
　　在确认了由谁来进行数据库优化之后，就要考虑从何时开始进行数据库优化。许多人认为对数据库的优化不急，等到用户开始抱怨系统运行速度无法忍受时，再进行优化。但此时某些有效的优化手段已无法有效的使用。
　　对于熟悉软件工程的人来说，在一个系统的生命周期内，对系统进行调整，想利用较小的人力、物力而能够收到较好的收益的话，最好在系统的设计和开发期内进行。如果一软件已成为产品，此时再进行系统调整，则耗费的精力最多，而收益最小。同样，对于数据库的优化，最好的时期是在系统的设计和开发阶段，尽量避免在一系统成型之后再进行优化。
　　无论是设计或维护数据库系统，都必须建立专门的性能指标，使人们能够有明确的目标，知道在何时进行调整。调整一个数据库系统的最有效的步骤如下：
　　在设计系统时考虑系统的性能
　　在开发应用程序时考虑系统的性能
　　调整操作系统的硬件和软件设置
　　识别系统的性能的瓶颈
　　确认问题的原因
　　采取纠正的动作
　　对于任何一个系统而言，良好设计的系统可以防止在应用生命周期以后产生的性能问题。同时，每一个系统设计人员和应用开发人员必须了解ORACLE的查询处理机制来编写有效的SQL语句。以下提出进行系统设计时，应尽量遵循的原则： www_bitscn_com中国.网管联盟
　　消除客户机/服务器应用中不必要的网络传输。例如：使用ORACLE的REPORT时，尽可能对单表进行处理，不要对多表进行JOIN处理，以免造成不必要的网络传输。
　　使用适用于自己系统的相应的ORACLE服务器选件（例如：并行查询或分布式数据库等）。
　　除非系统有特殊的需要，请使用缺省的ORACLE锁，无须自己对应用程序进行加锁处理，以免产生不可预测的错误。
　　为了便于对数据库的每个应用进行跟踪调测，尽可能记住每一个用户所运行的模块。便于今后对系统性能的跟踪。
　　在数据库建立时，需从自身的实际出发，建立合适的数据块长度。DB_BLOCK_SIZE
　　
　　二、 数据库优化过程
　　
　　调整数据库的性能必须有一个明确的目标，总的来说可以是以下的 几个目标之一或多个：
　　改善指定类型的SQL语句的性能。
　　改善专门的数据库应用的性能。
　　改善所有同时应用数据库的用户及其应用的所有性能。
　　在调整ORACLE性能之前，首先要有一个性能良好的应用设计及高效的SQL语句，在此基础上调整ORACLE性能的过程有三步：
　　调整内存分配
　　调整I/O
　　调整资源争用
　　因此，根据上述的原则并根据自己的工作经验，认为对数据库的优化大体上可分为如下几个阶段进行： BBS.bitsCN.com网管论坛
　　安装数据库时，对数据库的数据块大小进行确认。此参数在数据库安装之后就不能通过修改初始化参数进行修改或重新创建控制文件进行修改，要改变该值，唯一的方法是重新安装数据库
　　在数据库安装完毕之后，对数据库初始化参数进行修改。一个经过调优过的参数，对一个系统而言，可作到事半功倍的功效。例如：调整数据库SGA大小，主要是DB_BLOCK_BUFFERS, SHARE_POOL_SIZE, OPEN_CURSORS, SORT_AREA_SIZE等参数。
　　调整主机的硬件性能和操作系统的软件性能，使之配合数据库，发挥最大的性能。
　　进行应用系统的物理设计。
　　进行应用程序的编写时，对SQL语句的优化。
　　在试运行时对系统的物理设计以及应用程序的调整。
　　在系统运行过程中，通过对系统的监控，认识到系统的瓶颈，对系统再进行一次性能调整，此步骤在今后的系统运行中可能要反复多次。
　　
　　数据库优化内容
　　
　　1. ORACLE系统的准备知识
　　
　　1) ORACLE数据库系统的数据存储的物理结构和逻辑结构构成
　　
　　2) 模式对象的组成
　　
　　3) ORACLE数据库系统的进程以及内存结构构成
　　
　　4) ORACLE锁的概念介绍
　　
　　5) 二阶段提交的概念



　　
　　6) 用户、角色、权限的概念的介绍
　　
　　7) 举例介绍ORACLE是如何处理一个事务
　　
　　a 首先必须有一台主机或数据库服务器运行一个ORACLE INSTANCE。
　　
　　b 一台本地机器或客户端工作站运行一个应用，它试图通过适当的SQLNET驱动同服务器取得联系。
　　
　　c 如果该服务器也正在运行适当的SQLNET驱动。服务器检测到应用的连接请求，开始为此用户进程创建一个专用的服务器进程。
　　
　　d 客户端的用户执行一个SQL语句并提交此进程。
　　
　　e 服务器进程收到此SQL语句，并开始检验在ORACLE的共享池中是否存在同样的SQL语句。如果在共享池中发现该SQL语句，服务器进程开始检验该用户是否对请求的数据有操作的权限，然后使用在共享池中的SQL语句去执行该语句。如果该SQL语句在共享池中不存在，就为此语句分配一个新的共享池区以便它能够被解析、执行。
　　
　　f 服务器进程从实际的数据文件或共享池中取回必须的数据。
　　
　　g 服务器进程在在共享池中修改数据。在上述所作的生效之后，DBWR后台进程把修改后的数据块永久的写入硬盘。在此事务提交成功之后，LGWR进程立即把此事务记录到在线的redo log file。
　　

　　h 如果此事务成功，服务器进程通过网络返回一个成功的信息给应用程序。如果该事务不成功，将返回一个适当的信息。
　　
　　i 在上述的事务过程中，其余的后台进程同样在运行，等待着条件符合而被触发。此外，数据库服务器还管理着其他用户的事务，并且在不同事务之间提供数据一致性，防止不同事务对相同数据操作。
　　
　　2. 在安装数据库时作的优化
　　
　　在数据库安装时作的优化工作主要是关于DB_BLOCK_SIZE参数的设置，该参数决定了ORACLE每次操作多少的数据。该参数在安装时一经确认就不能修改，除非重新安装数 据库。对于一个应用而言，一般对于一个中型的应用系统，它的DB_BLOCK_SIZE大小为设为4K，而对于一个较大型的应用而言，它的DB_BLOCK_SIZE一般设为8K或更大一点为16K。
　　
　　对于一个较大的DB_BLOCK_SIZE，不仅可以加快系统的运行速度，（因为从系统的I/O吞吐能力来说，一次性读取较多的数据可以比一次性读取较少的数据的的过程减少I/O的读取次数）而且可以有较大的系统扩展能力。因为对于一个系统而言，在它的DB_BLOCK_SIZE确认之后，它的最大EXTENT的数目其实也已经确认下来。如果一个系统的扩展能力有限的话，则系统容易发生显示终止的事情。而就是说，发生ORA错误，导致系统无法正常运转。截止至目前，在ORACLE7.3之后的版本中，ORACLE在建表空间时，有一个参数autoextent，如果此参数设置为ON时，ORACLE在达到最大的扩展值时，ORACLE就自动扩展，不再受最大扩展数的限制。现就把DB_BLOCK_SIZE和MAX EXTENTS的关系罗列如下：



　　
　　DB_BLOCK_SIZE（数据块数目） MAX EXTENTS（最大扩展数）
　　
　　512BYTES 25
　　
　　1K 57
　　
　　2K 121
　　
　　4K 249
　　
　　8K 505
　　
　　
　　3. 在安装之后，在数据库初始化时对INITXXX.ORA文件作的优化
　　
　　对于SHARE_POOL_SIZE的设置：对于不同的系统根据用户对于内存区的要求，考虑用户是否需要多少的内存空间存放用户的存储过程或要多少空间存放用户要编译的程序。
　　
　　对于需要进行大量数据操作的用户可考虑增大用户的DB_BLOCK_BUFFERS的数目，该参数可以使用户在缓冲区中的数据较大，使用户查找的数据尽可能的在缓冲区中，不要到表中去再次查找。
　　
　　根据用户的实际需要，设置较好的PROCESS该参数决定能够有多少个用户在系统中运行，如果该参数设置不当会导致用户无法正常运行。并且该参数与操作系统的有些参数（如Digital unix的max_proc_per_users）有关，该类型的参数限制了每个用户允许最大多少用户登录的限制，因为对于我们而言，每一个用户最终都体现为一个ORACLE用户，如果此参数开的不够大的话，则会造成后登录的用户无法登录，应用终止。
　　
　　根据用户实际使用系统的SQL语句的多少，决定最终要开的OPEN_CURSORS数目的多少，因为一个SQL的DML语句就是一个隐含的CURSOR，如果上述参数的数目开的不够大的话，系统会提示用户的SGA区不足，导致系统出错。


　　
　　对于要进行大量数据分组和排序工作的应用要加大系统的SORT_AREA_SIZE的大小，该参数决定分配给每一位用户的排序空间，该参数用到系统的内存空间。
　　
　　为保证系统能够正常运转，要保证系统有足够的DML_LOCKS，如果该值不够的话，会导致系统发生中断，半途终止系统。
　　
　　为保证系统能够有足够的数据库链路可用，要保证OPEN_LINKS的数目足够大。
　　
　　对于会发生CORE DUMP的用户的机器，可考虑设置使该CORE DUMP最终不要形成文件，（在ORACLE的init参数中为shadow_core_dump=none，默认为full）。因为系统在许多时候由于文件系统满的缘故，导致系统无法正常运转，最终会导致数据库系统崩溃。
　　
　　4. 在进行空间设计时作的优化
　　
　　在一个数据库安装完毕之后，系统中已存在如下表空间，它们分别是：SYSTEM，TOOLS，RBS，USERS，TEMP等，上述表空间在安装时用户可根据当地的系统的实际情况进行系统表空间的划分，使它们尽可能分离。
　　
　　在系统安装时，还应该考虑控制文件和可重作日志文件要尽可能的分配在不经常使用的盘上。

xkmj

　上个月，数据库开始出现问题，负载低，但查询很慢，尤其是在应用程序启动，加载预置数据的时候，以前启动加载数据只需要不到1分钟就可以完成，但当时启动服务却用了1个小时，后来经过分析和测试，找到了3个问题，其中一个问题是是硬件和JVM之间的，可以看海东的<Xeon3.0?RedHat9?JVM>；另一个问题是数据库IO高的问题，这是应用的问题，目前没有太好的解决方式；最后一个问题就是数据库的SGA配置问题，我在ITPUB上发了帖子Oracle 9i 的性能问题，经过高手的指点，重新配置了SGA，从SQLPLUS的表现可以感觉到明显的效果，先把这个总结一下，便于以后查找 ：）

　　首先推荐ITPUT上几篇文章，感觉很有收获：

　　Oracle是如何工作的：这篇文章通过一个生动的例子，解释了Oracle各部分是如何协调工作的，尤其是对于SGA在其中的作用。

　　关于SGA设置的一点总结 和关于2G内存的SGA的参数优化的问题

　　主要原因是当前的数据库SGA配置中的db_cache设置太小，这个在ITPUB上遭到了大家的猛烈批判 ：），下面?切薷那暗那榭觯?

　　SQL>SELECTNAME,VALUE

　　FROMv$parameter

　　WHERENAMEIN

　　('sga_max_size','db_cache_size',

　　'shared_pool_size','shared_pool_reserved_size','large_pool_size','java_pool_size',

　　'db_block_size','db_block_buffers','log_buffer','sort_area_size','sort_area_retained_size',

　　'hash_area_size','sessions','open_cursors'

　　)ORDERBYNAME;

　　NAMEVALUE

　　--------------------------------------

　　db_block_buffers0

　　db_block_size4096

　　db_cache_size33554432

　　hash_area_size1048576

　　java_pool_size67108864

　　large_pool_size117440512

　　log_buffer524288

　　open_cursors300

　　sessions225

　　sga_max_size437327188

　　shared_pool_reserved_size10066329

　　shared_pool_size201326592

　　sort_area_retained_size0

　　sort_area_size524288

　　14rowsselected.

　　SQL>showsga

　　TotalSystemGlobalArea437327188bytes

　　FixedSize451924bytes

　　VariableSize402653184bytes

　　DatabaseBuffers33554432bytes

　　RedoBuffers667648bytes

　　Variable Size表示MAX_SGA中除去db_cache后剩余的部分。

　　按照大家的建议，我做了如下修改

　　shared_pool_size=184800000（180M）

　　db_block_size=8192

　　db_cache_size=204800000（200M）

　　log_buffer=1024000(1M)

　　large_pool_size=51200000（50M）

　　java_pool_size=51457280（50M）

　　sort_area_size=524288（512K）

　　sort_area_retained_size=8192

　　调大了db_cache，减小了large_pool.

　　OS AS3U4

　　MEM 4GB

　　CPU 2×2.8GB

　　还有Configuring Linux kernel parameters

　　[etc][sysctl.conf]

　　kernel.msgmnb = 65535

　　kernel.msgmni = 2878

　　fs.file-max = 524288

　　kernel.sem = 256 64000 256 256

　　kernel.shmmax = 3098693632

　　[etc][security][limits.conf]

　　* soft nofile 4096

　　* hard nofile 65536

　　* soft nproc 16384

　　* hard nproc 16384

　　* soft nproc unlimited

　　* hard nproc unlimited

　　[etc][profile]

　　ulimit -u 16384 -n 65536

　　仅供参考

　　我在OCS的压力测试中，针对1000个并发用户在10g中的配置:

　　SQL> show sga

　　Total System Global Area 1677721600 bytes

　　Fixed Size 779628 bytes

　　Variable Size 384047764 bytes

　　Database Buffers 1291845632 bytes

　　Redo Buffers 1048576 bytes

　　－－－－－－－－－－－－－－－－－－－－－

　　db_block_buffers = 0

　　db_block_size = 8192

　　db_cache_size = 419430400

　　hash_area_size = 131072

　　java_pool_size = 134217728

　　large_pool_size = 0

　　log_buffer = 1048576

　　open_cursors = 2000

　　sessions = 1200

　　sga_max_size = 1677721600

　　shared_pool_reserved_size = 10905190

　　shared_pool_size = 218103808

　　sort_area_retained_size = 0

　　sort_area_size = 65536

　　仅供参考

张涵

Mark！