第七章：数据库调优艺术
点击数：2024发布日期：2020-04-19 14:44:51 来源：oracle实战与提高
第七章：数据库调优艺术
数据库的性能至关重要。在你看不见想不到的时候，在你看不见想不到的地方，速度就影响了生产效率。在速度慢的时候，必须有性能优化的意识，不能一味的增加硬件。128GB内存，128颗CPU不一定使得上。常常见到96GB内存，使用了6GB，只用了一个零头，白白浪费了硬件，硬件不会自动用上，需要我们调整。
曾经见过一个客户，由于性能慢，使用系统的客户端部门和管理运维的部门吵起来了，不可开交。花点钱做个调优不就完了嘛，由于甲方流程原因根本走不动调优这条路，我只能感到惋惜和遗憾了。这么好的机器，这么慢的速度，可惜。
7.1背景
随着单位业务的发展，数据库的数据量呈线性增长。系统所承受的压力越来越大。运行几个月或几年，数据库系统很容易出现性能问题，可能会导致前台响应变慢，业务处理时间变长，CPU瓶颈突出，内存不足，I/O瓶颈等问题。特别是在业务高峰期。针对这些问题，我们要对数据库系统进行整体优化，优化方面包括应用程序，数据库，主机和存储。为了更好地完成数据库系统的优化，我们将对系统进行多次的性能评估，收集相关的信息，然后对收集的信息进行评估，提出我们的解决方案。
数据库调优是一门艺术。调整完成之后，可以让数据库看起来更完美，用起来没有最快只有更快。
有些问题是一开始安装的时候，就没有安装好，参数也没有设置好。我们做调优的时候，尽可能的一并把这些问题解决掉。如果由于种种原因做不了的，也尽量让它的影响降到最低。最大限度的追求完美。做不了的，虽留有遗憾，也瑕不掩瑜。最后带给客户的是没有最快只有更快。
7.2收集和了解哪些信息
数据库存在性能问题，我们就要寻找性能瓶颈，瓶颈一般又不是一个，需要从各个方面加以考虑。以下是我们需要了解的一些信息，有了这些信息，我们就可以知道调优从什么地方着手了。当然探寻这些问题的答案最好是调优工程师亲自登录系统去看，如果光远程传一个日志报告之类的，效率低下不说，调优工程师也很难了解的很全面。除非是断判一个问题，只从AWR报告或执行计划中就可以大概的判定，整体的调优，肯定是要现场操作。
1、数据库的AWR报告，ASH报告。
2、操作系统的CPU利用率，内存使用率，I/O响应时间I/O等待情况。
3、数据库版本。PSU, CPU补丁情况。
4、数据库参数，包括隐含参数；操作系统参数。
5、日志的组数，member组员的大小，日志的写速度。
6、高峰时间段的警告日志，相对应的trace文件。
7、硬件资源是否充足，是否需要扩展，如增加内存。
8、数据库OLTP的整体响应时间；报表批处理的响应时间。
9、客户端期望的数据库响应时间；某个业务以前的响应时间。
10、响应慢的应用主要等待时间耗在什么事件上？
11、由于长年累积，数据段中的数据是否需要整理碎片，如表和索引的整理。
12、内存充足的情况下，为了减少I/O等待，可以将表和常用的程序包keep到内存里。
13、对于大表大索引绑定变量等问题，是否可以联合发开商进行操作？
14、历史数据是否可以迁入历史库，减少核心库压力。
15、Oracle的自动任务？如审计，统计信息，直方图，auto space advisor，auto sql tuning等。
16、哪些SQL或程序运行较慢。SQL是否有调优的空间。
17、是否存在内存锁的争用？如gc开头的等待事件,latch free, library cache等。
18、热块问题。
19、网络速度。
7.3调优的依据和手段
为了把调优的手段介绍的更加全面，我们假设我们的数据库存在各种问题，都需要一一调整，目的是根据我们的判断，介绍更多的解决问题的方法。
redo调整。
增加redo日志组数，将redo由RAID5移至RAID10，将member大小由50MB扩至200MB。
判断的依据：
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 94,674 39.25
db file sequential read 6,098,736 65,327 27.08
log file sync 1,293,986 43,891 18.20
db file scattered read 2,092,560 24,769 10.27
buffer busy waits 840,944 6,457 2.68
-------------------------------------------------------------
Wait Events for DB: YSDB Instance: ysdb Snaps: 20064 -20065
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file sequential read 6,098,736 0 65,327 11 4.0
log file sync 1,293,986 1,065 43,891 34 0.9
说明：1、log file sync等待事件已经进入top5,证明我们的redo写存在速度问题；
2、log file sync的等待时间，一小时的快照间隔，它总共等待了43,891秒钟，12.2小时，总的等待时间也是蛮长的，是一个可以提高速度的部分。
3、再看平均等待时间log file sync，平均等待时间为34ms，而Oracle建议log file sync的平均等待时间不宜超过5ms，由此大致的可以推断，我们的redo日志是放在了RAID5上。建议调整到RAID1上。
4、警告日志和redo相关的
Fri May 19 09:07:49 2015
Thread 1 cannot allocate new log, sequence 117398
Checkpoint not complete
... ...
Fri May 19 10:12:32 2015
Thread 1 cannot allocate new log, sequence 117421
Checkpoint not complete
警告日志报不能分配新的redo日志，检查点没有完成。也是由于磁盘的速度不好，redo日志太小，日志组数不够三个方面可能都有责任。
5、看一下v$log
SQL> select group#,thread#,bytes,members,status from v$log;
GROUP# THREAD# BYTES MEMBERS STATUS
---------- ---------- ---------- ---------- ------------------------------------------------
1 1 52428800 2 ACTIVE
2 1 52428800 2 ACTIVE
3 1 52428800 2 CURRENT
说明，由于I/O速度慢，单位时间redo写的多，致redo状态处于active。如果底层做了RAID，这里也没有必要使用两个member了。如果使用两个member，必须把两个member分别放在不同的磁盘上。放在一个磁盘上，同时写，这不是给磁盘增加压力吗？况且要坏都坏。如果非要防止人为删除，应该把两个member放在不同的挂载点下，不同的文件夹里。
以上的种种迹象表明，我们的redo需要调整。调至最优的方案是1、将redo挪至15000转的磁盘做的RAID1上去。2、将日志组数增加至6组。3、将redo大小改为一个400MB。然后再执行以上的检查。
调整的方法如下：
增加三个日志组至新的挂载点/ora_redo，这个挂载点是我们在存储上新划的RAID1磁盘。
SQL> alter database add logfile group 4 '/ora_redo/redo04.log' size 400m;
Database altered.
SQL> alter database add logfile group 5 '/ora_redo/redo05.log' size 400m;
Database altered.
SQL> alter database add logfile group 6 '/ora_redo/redo06.log' size 400m;
Database altered.
将当前的日志切换到4,5,6上的任意一个，然后稍等几分钟，等第1，2，3组日志不活动了，将1，2，3删除。
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
1，2，3组日志删除完成之后，再添加新的1，2，3组至/ora_redo.
alter database add logfile group 1 '/ora_redo/redo01.log' size 400m;
alter database add logfile group 2 '/ora_redo/redo02.log' size 400m;
alter database add logfile group 3 '/ora_redo/redo03.log' size 400m;
新的日志添加完成后，将旧的1，2，3组日志在物理上使用rm删除。
[oracle@yingshu ysdb]$ pwd
/u01/app/oracle/oradata/ysdb
[oracle@yingshu ysdb]$ rm -r redo*
至此我们的redo调整就完成了。我们重新查看新成生的AWR报告,再观察相应的等待事件，肯定有了很大的改观。警告日志也不再报无法分配新的redo了。
AIX5L、6.1操作系统参数调整。
运行在IBM aix上的Oracle数据库调优过程中，相关的操作系统参数，主要有以下几个需要关注。如果我们的生产库为AIX请关注以下几个参数。我这里直接给出了调整的命令，然后重新启动主机使参数生效。作用是让操作系统文件型内存使用的内存限制在30%以下，让更多的内存留给Oracle用做ASM、裸设备、SGA，如果客户用的是文件系统，则以下的值则不适用。需要另行评估计算参数的新值。
vmo -p -o maxclient%=30
vmo -p -o maxperm%=30
vmo -p -o minperm%=5
vmo -p -o strict_maxperm=1
linux操作系统参数调整。
kernel.shmmax参数：Linux进程可以分配的单独共享内存段的最大值。一般情况下，该值应该大于SGA_MAX_TARGET或MEMORY_MAX_TARGET的值。如果主机上只运行一个实例，则可以设置为总内存大小的65%。数据库占用的总内存大小也为60左右。
kernel.shmmni参数：设置系统级最大共享内存段的数量。Oracle推荐值为4096。
kernel.shmall参数：设置共享内存的总页数，算法为总的物理内存大小除以一个分页的大小，这个值太小有可能导致数据库启动报错。分页大小由命令getconf PAGE_SIZE获取。对于主机内存为96G的情况，该值的计算方法是：
[root@yingshu ~]# getconf PAGE_SIZE
4096
SQL> select 96*1024*1024*1024/4096 from dual;
96*1024*1024*1024/4096
----------------------
25165824
semaphores信号量是用来调协进程对共享资源的访问的，进程间访问共享内存时提供同步。
semmsl参数：每个信号量组中信号量最大数量，推荐的最小值是250。对于系统中存在大量并发连接的系统，推荐将这个值设置为PROCESSES初始化参数加10。
semmni参数：系统中信号量组的最大数量。Oracle10g和11g的推荐值为142。
semmns参数：系统中信号量的最大数量。semmns的值不能超过semmsl*semmni，超过了semmsl*semmni是非法的，因此推荐semmns的值就设置为semmsl*semmni。oracle推荐semmns的设置不小于32000，假如数据库的processes参数设置为1500，则semmns的设置应为：
select (1500+10)*142 from dual;
(1500+10)*142
-------------
214420
semopm参数：每次系统调用可以同时执行的最大信号量的数量。因为一个信号量组最大拥有semmsl个，因此可以将semopm设置为semmsl的值。Oracle验证的10g和11g的semopm的配置为100。
修改以上参数的方法，可以用vi编辑器编辑/etc/sysctl.conf这个文件，然后使用sysctl -p命令使其生效。
数据库参数调整。
从Oracle10g开始，内存可以自动管理了，ASMM即(Automatic Shared Memory Management)，我们只需要设一个sga_target，sga_max_size或memory_target，memory_max_target就不用管下面各个池的大小了。但是，自动管理内存好吗？事实证明，内存自动管理在数据库压力比较大的情况下，会使分配给某个池的内存一会大一会小，容易出事。所以我是不建议大家用内存自动管理，还是手工管理内存来的稳定。
Oracle10g内存自动管理还受statistics_level参数的影响，该参数必须设置为typical或all，内存自动管理才生效，如果此参数的值是basic，则内存自动管理无效。
Oracle10g内存自动管理可以管理5个内存池的大小，它们是：buffer cache、shared pool、large pool、java pool和stream pool。其它的内存参数仍然需要手工设置。视图v$sga_dynamic_components记录了可以动态调整的内存的大小。通过这个视图可以查询当前的自动设置的值。
内存自动管理受后台进程MMAN（Memory Manager）的控制。由此后台进程进行内存建议的收集，收集来建议之后，也是由此进程自动调置对应的参数。它设置的参数都是以两个下划线开始，如"__shared_pool_size"，是自动调整的参数。数据库启动时，会比较dba设置的参数和自动调整的参数，shared_pool_size设置的比"__shared_pool_size"大，则用shared_pool_size，否则用"__shared_pool_size"。
我的习惯是不用内存自动管理，建库的时候就不用内存自动管理建，使用手工设置内存的大小，省的内存自动管理在以后的运行中出故障。如果目前数据库内存为自动管理，我们也建议改为手工管理，下面介绍一下内存自动管理改为手工管理的方法。
为了避免修改的过程中出现错误致数据库起不来，我们先备份一下当前的spfile。拷贝也行，创建一个pfile到某个目录下也行。
SQL> create pfile='/tmp/pfile20150512' from spfile;
File created.
主机的总内存是160GB，我们设置的各个内存池的大小，都跟总内存有关系，可以根据自身的情况再做一下大小的微调，总的比例应该是在sga中db_cache_size用于缓存数据的内存区域最大，用于存放数据字典和SQL语句的shared_pool_size内存区域第二大。如果此主机上只运行一个实例，那么sga占主机总内存的60%左右。
alter system set sga_max_size=120g scope=spfile sid='*';
alter system set sga_target=0 scope=spfile sid='*';
alter system set db_cache_size=80g scope=spfile sid='*';
alter system set shared_pool_size=10g scope=spfile sid='*';
alter system set java_pool_size=120m scope=spfile sid='*';
alter system set large_pool_size=500m scope=spfile sid='*';
alter system set log_buffer=79193088 scope=spfile sid='*';
alter system set pga_aggregate_target=32g scope=spfile sid='*';
将常用的应用程序包keep到内存中。
对于频繁使用的数据库对象而言，如存储过程，触发器，序列，游标等，将其keep到内存中，可以减少物理I/O提高用户的响应时间。keep的过程需要一个软件包dbms_shared_pool，这个软件包在Oracle 11g中不是被默认创建的，需要执行dbmspool.sql来创建。有了dbms_shared_pool这个包之后，用户就可以将自己常用的程序keep到内存中了。
我们可以先从v$db_object_cache，v$sqlarea里将执行次数多的程序包查询出来。然后根据结果判断，哪些包是我们最常用的，是需要keep的。被经常使用的程序名用下面的语句查询，其中执行的次数，和包占用内存的大小可以做适量的调整。
select a.owner, //对象的owner
a.name, //对象的名称
a.sharable_mem, //共享内存占用大小
a.kept, //是否被keep在内存中
a.executions , //执行的次数
b.address, //语句的地址
b.hash_value //hash值
from v$db_object_cache a,
v$sqlarea b
where a.kept = 'NO' and
(( a.executions > 1000 //执行次数
and a.sharable_mem > 50000) //共享内存占用大小
or a.executions > 10000) //执行次数
and substr(b.sql_text,1,50) = substr(a.name,1,50);
如果了解我们的应用程序，知道哪个用户下的哪些包会被经常使用，也可以通过用户名，程序名确定我们要keep的内容。用的视图就是dba_objects。
select object_name,object_type from dba_objects where owner='YS';
keep对象之前我们先使用dbmspool.sql创建一下dbms_shared_pool包。使用sys用户以dba身份执行。
SQL> @?/rdbms/admin/dbmspool.sql
Package created.
Grant succeeded.
创建完成了之后，我们就可以把常用的程序包放在内存里了。比如通过上面的查询判断，我们得出以下的操作系统包被经常使用，这些包属于sys用户，用的很频繁，就可以用下面的命令将它们keep到内存中。如果是应用用户的程序，keep的方法也一样。
exec dbms_shared_pool.keep('STANDARD');
exec dbms_shared_pool.keep('DBMS_SYS_SQL');
exec dbms_shared_pool.keep('DBMS_SQL');
exec dbms_shared_pool.keep('DBMS_UTILITY');
exec dbms_shared_pool.keep('DBMS_DESCRIBE');
exec dbms_shared_pool.keep('DBMS_JOB');
exec dbms_shared_pool.keep('DBMS_STANDARD');
exec dbms_shared_pool.keep('DBMS_OUTPUT');
exec dbms_shared_pool.keep('DIANA');
exec dbms_shared_pool.keep('PIDL');
我们的应用开发商应该知道哪些表比较常用；通过AWR报告里的物理读（Segments by Physical Reads），也可以得知哪些段比较消耗物理I/O；我们可以通过dba_segments查询到这个表有多大。如果大小合适，内存足够放的下，这时，如果I/O又成为我们性能的瓶颈，我们就可以把对应的表keep到内存中来。一旦实现keep，省去了I/O的时间，我们的SQL执行速度将明显提升。就算存储的性能不好，也没有关系，因为除第一次读取，我们这些表从此不在存储里读取了。
... ...
Segments by Physical Reads DB/Inst: YSDB/ysdb Snaps: 10762-10763
-> Total Physical Reads: 1,572,493
-> Captured Segments account for 38.9% of Total
Tablespace Subobject Obj. Physical
Owner Name Object Name Name Type Reads %Total
---------- ---------- -------------------- ---------- ----- ------------ -------
YS YS_DATA01 YS_KUCUNZ TABLE 114 35.74
YS YS_INDEX01 IDX_CAIWUZ INDEX 6 1.88
YS YS_INDEX01 PK_YS_RENINFO INDEX 2 .63
YS YS_INDEX01 IDX_RENINFO_PWD INDEX 1 .31
SYS SYSAUX WRI$_ADV_OBJECTS TABLE 1 .31
-------------------------------------------------------------
... ...
上面的AWR报告显示，表YS.YS_KUCUNZ占用的I/O最大，物理读最频繁。
我们查看一下它有多大？
SQL> col segment_name for a15
SQL> col segment_type for a15
SQL> select owner,segment_name,segment_type,bytes/1024/1024 from dba_segments where segment_name=' YS_KUCUNZ ';
OWNER SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024
-------------------- --------------- --------------- ---------------
YS YS_KUCUNZ TABLE 80.0625
ys.ys_kucunz这张表有80MB，不算太大，可以将它keep到内存中。keep表的语句如下：
SQL> alter table ys.kucunz storage(buffer_pool keep);
Table altered.
如果还有其它的表需要常驻内存，方法和上述的一样。
日积月累的表的碎片，也是我们要调整的对象。经过几年的增删改操作，表里已经积累的大量的碎片。查看表的碎片之前，我们先收集一下表的统计信息，统计信息不准确，我们查的碎片程度也不准确。
SQL> exec dbms_stats.gather_table_stats(ownname=>'SHU',tabname=> 'STUD');
PL/SQL procedure successfully completed.
可以用以下的语句来查询表的碎片程度。浪费空间比较多的表，就是我们要重组的重点了。
select table_name, //表名
round((blocks * 8), 4) "highwm_k", //高水位线占用的大小
round((num_rows * avg_row_len / 1024), 4) "real_use_k", //真实的大小
round((blocks * 10 / 100) * 8, 4) "free_spc_(pctfree) k", //有多少预留空间
round((blocks * 8 - (num_rows * avg_row_len / 1024) - blocks * 8 * 10 / 100), 4) "waste_k"
from dba_tables //（上一行）浪费的空间
where table_name = 'STUD'; //想了解碎片的表名
TABLE HighWM_k Real_Use_k Free_Spc_(Pctfree) k Waste_k
----- ---------- ---------- -------------------- ----------
STUD 11024 0 1102.4 9921.6
对于数据变化量比较大的表，我们可以对其进行重组。重组的方法有多种。
1、expdp导出来，将原表删除，impdp再导进去，实现了对表碎片的整理。这种办法做数据备份迁移的时候常用，整理碎片一般不用这个方法。
2、create table TEACH2 as select * from teach;这种方法也不怎么常用，因为它生成一个新表，还要修改表名，又不能将表中的默认值插到新表里去，用的时候需要谨慎。
3、第三种方法是move表，就是我们最常用的方式，执行的时间一定是在业务不忙的时候，保证表空间的剩余空间，要比此表大。如果表空间没有剩余空间，可以将表move到其它表空间，再move回来。对于特别大的表或分区，一定要计算出大概的move时间，在规定的停业务时间必须完成操作，不能因为move时间过长，影响了营业。注意：move表之后，此表上面所有的索引都将失效，需要重建所有此表上的索引。
SQL> alter table ys.teach move;
Table altered.
将表move至其它表空间，再move回来。
SQL> alter table ys.teach move tablespace users;
Table altered.
SQL> alter table ys.teach move tablespace ystbs;
Table altered.
如果是move分区表，需要一个分区一个分区的做。
SQL> alter table ys.stud move partition part_001;
Table altered.
SQL> alter table ys.stud move partition part_002;
Table altered.
shrink table
4、在Oracle10开始，又推出了一种新的整理表碎片的方法，就是shrink收缩。这个功能必须打开行移动，有三个不同的选项表示三种不同的整理级别。shrink不需要重建索引，是比move进步的一点，但shrink会改变数据的rowid，如果应用在使用的过程中调用了rowid，那么就不能用shrink了，使用LOGMINER恢复过程中也会用到rowid，看来shrink还会影响LOGMINER，但以上两种假设在我们的实际生产中很少用到，shrink还是可以用的。
打开行移动
SQL> show user
USER is "SHU"
SQL> alter table stud enable row movement;
Table altered.
收缩表之前先收集一下表的统计信息。统计信息里有表的高水位线，我们在查询高水位线之前必须先收集统计信息，否则，我们的高水位线有可能是不准确的。
SQL> exec dbms_stats.gather_table_stats(ownname=>'SHU',tabname=> 'STUD');
PL/SQL procedure successfully completed.
我们先了解一下High Water Mark的计算方法，高水位线的概念。
查询表的高水位线。
SQL> select blocks, empty_blocks,num_rows from dba_tables where table_name='STUD' and owner='SHU';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
370 0 100000
结果显示，表里没有空块，这个表不需要我们整理。
为了达成我们的实验环境，我们将表里的数据删除一半。
SQL> delete from stud where studid<=50000;
50000 rows deleted.
查询在高水位线以下，有多少MB空闲空间。
select table_name,
(blocks * 8192 / 1024 / 1024) -
(num_rows * avg_row_len/ 1024 / 1024) "data lower than hwm in mb"
from user_tables
where table_name = 'STUD';
TABLE_NAME Data lower than HWM in MB
----------------------- -------------------------
STUD 2.17536926
SQL> alter table stud shrink space compact;
Table altered.
收缩表，但高水位线保留。
SQL> select blocks, empty_blocks,num_rows from dba_tables where table_name='STUD' and owner='SHU';
Used Blocks EMPTY_BLOCKS NUM_ROWS
----------- ------------ ----------
370 0 50000
高水位线并没有降低。
SQL> alter table stud shrink space;
Table altered.
收缩表，降低高水位线。
SQL> alter table stud shrink space cascade;
Table altered.
收缩表，降低高水位线，相关索引也收缩一下。
再收集一下表的统计信息。
SQL> exec dbms_stats.gather_table_stats(ownname=>'SHU',tabname=> 'STUD');
PL/SQL procedure successfully completed.
再看表的高水位线，已经降下来了。
SQL> select (b.blocks - a.empty_blocks - 1) hwm
from user_tables a,
user_segments b
where a.table_name = b.segment_name and
a.table_name='STUD';
HWM
----------
46
select segment_name,blocks from user_segments where segment_name='STUD';
整理索引
索引碎片多了之后，也会发生性能问题，我们在AWR报告里看到db file sequential read等待事件，通常和效率不高的索引有关。如果我们的索引被删除的数据超过20%，或BLEVEL大于等于4，则我们的索引就需要整理了。补充一句，任何一种方法整理索引，都不应该在业务生产时间，都应该在停业时间，系统不繁忙的时间进行。也就是你要确认这个时间段此索引无人使用才可以整理。
查询数据库中blevel大于等于4的系引。
select owner||'.'||index_name as "owner.index_name",blevel
from dba_indexes
where blevel>=4
order by blevel desc;
如果被删除的行del_lf_rows/lf_rows*100大于百分之20，则此索引建议整理。
col name for a20
select name,blocks,del_lf_rows/lf_rows*100,del_lf_rows/lf_rows*blocks from index_stats;
如果索引的状态为UNUSABLE,则需要整理索引。
select owner,index_name,status from dba_indexes where status='UNUSABLE';
在整理索引之前，我们需要看一下索引的owner是谁，只有和我们应用相关的才需要整理。
整理索引的方法有以下几种。
1、先准备好建立索引的脚本，然后将索引删除，重建索引。
select index_name,dbms_metadata.get_ddl('INDEX',INDEX_NAME,TABLE_OWNER) index_ddl
from user_indexes
where table_name ='STUD';
drop index shu.ind_stud_id;
create unique index shu.ind_stud_id on shu.stud ("STUD_ID");
这种方法速度快，好理解。影响关于此索引的dml。在实际生产中，可以使用。
2、rebuild，扫描现有索引块进行索引重建，需要两倍索引大小的空间，降低高水位线，执行速度快，会全程对表加锁，阻塞DML操作。是最常用的整理索引的方法。
alter index shu.ind_stud_id rebuild;
3、rebuild online对表进行全扫实现索引重建，会降低索引高水位线，一般执行速度慢，只在开始和结束时对表加锁，执行中间不阻塞DML操作。因为速度比较慢，中间过程又要维护DML的日志表，如果有DML操作，那么整理时间将大大拉长。所以谨慎使用。
alter index shu.ind_stud_id rebuild online;
4、coalesce接合索引，只对索引块做合并操作，不下降高水位线，可随时中断。全程不阻塞DML操作。因为会产生更多的redo日志。
alter index shu.ind_stud_id coalesce;
5、shrink索引，原理请参见shrink table。
alter index shu.ind_stud_id shrink space;
alter index shu.ind_stud_id shrink space compact;
alter index shu.ind_stud_id shrink space cascade;
7.4、性能优化的定义和范围
本次性能优化的服务范围为樱澍保险的财险核心生产系统。
将从应用层、数据库层、操作系统层到存储层对樱澍保险的核心生产系统进行全面的优化。
本次数据库的优化可能涉及到对应用SQL进行调整，并针对应用中存在的待改进的问题提出解决建议。在优化过程中部分对应用的调整需要开发商的配合。
7.5、性能优化的目标：
数据库的响应速度主要体现在数据库的等待时间和响应时间上。体现在如下几个方面：
响应时间（Response time）
CPU时间(Service Time)
等待时间(wait time)
查询量（User calls）
事务量（transactions）
Redo size/秒
系统CPU(usr+sys%)
系统IO等待(wio%)
系统内存使用
系统总体性能提升25%以上
访问最少的块数
将数据块保留在内存中
7.6、深入研究数据库系统的五大资源
这五大资源和性能息息相关，需要一一排查是否存在性能瓶颈。
1、内存
2、程序(SQL)
3、I/O
4、CPU
5、网络
七、数据库首次优化方案
1、测量并记录当前性能。
2、确定当前Oracle的性能瓶颈（等待什么）。
使用awrrpt,sqlrpt,addmrpt,sqltrace,ashrpt,tuning包等。
操作系统工具如top,sar,vmstat,iostat,osw等。
3、确定当前的os是否有瓶颈，若有分析瓶颈的原因。
4、操作系统核心参数的调整建议；包括信号量，内存分配限制。
5、数据库内存配置的调整建议。
包括share pool, db_cache_size,keep池，recycle池，写进程数等。尽量使用手动管理内存大小，避免内存忽大忽小，减少抖动。
6、I/O配置的调整。分散I/O，redo使用RAID10，热表cache到内存里,数据文件响应时间。
7、清理不需要的历史数据，或把历史数据导入历史库。
8、分析主要业务表, 为优化器的工作提供足够准确的统计分析数据,使CBO优化器能够生成优化的执行计划。关闭自动统计信息？
9、使用keep池，将常用的表或其它对象keep到内存里来。
10、使用rebuild online重建重点业务表索引。
11、keep常用应用程序包。
12、Keep常用小表。
13、大表重组。
14、关键索引重建。
15、制定历史数据处理细则。
比如将一年前的数据导出生产库，导入历史库。
16、将特大的生库表且又不能导入历史库的大表考虑进行分区。
比如按月分区，或按(半)年分区；按列表分区。
17、确保代码可共享。
为了优化共享的SQL,使用绑定变量而不是文本值。
18、尽可能避免或者减少排序操作。
尽可能确保排序在内存中执行，减少temp表空间使用量。
19、减少分页和交换。
20、应用分别分布于RAC两个或多个节点，避免交插访问。DRM的禁用？
21、每一阶段调优完成，记录当前性能，和初期的（上一阶段的）性能做前后比较，量化调优收益。
八、风险防范措施
1、确保数据库备份完整可用。
2、所有操作和检查环节都使用事前完成并预演测试通过的脚本，避免临时修改脚本。
3、每部分完成，通过检查确认无误，再进行其它部分，避免互相干扰。
4、Oracle工程师和应用系统专家现场支持，及时处理突发问题。
九、数据库优化结果的保持
在数据库系统没有大的变动的情况下，在服务期内，甚至更长的一段时间，通过定期的维护及相应的维护手段保证数据库优化后性能稳定。
定期清理表中的垃圾数据或导入历史库，协助制定常用表的相关操作维护计划。
优化表结构。
减少操作语句间的相互影响（如锁表问题）。
提高表操作语句的执行效率。
十、客户收益
提高投资回报率
合理、充分利用资源，延长系统使用寿命；
优化运维成本，降低IT系统管理成本。
提高系统稳定性
在问题发生之前就发现并解决问题，因而可做到高枕无忧。
提高运维水平
系统优化的知识与经验能够得到传递，提高运维人员的能力。
提高企业核心竞争力
市场需求适应性增加，缩短新业务上线周期，提高IT系统的灵敏度，适应了飞速发展的业务需求。
系统运行速度更快，可用性更高，客户满意度升高，企业竞争力提升。
下一篇：第八章：Oracle数据库的迁移
上一篇：第六章：数据库故障处理
图文推荐
序言
2020-04-19 查看：2272
第十五章：Oracle 12c介绍
2020-04-19 查看：2210
第十四章：常用Oracle工具
2020-04-19 查看：1934
第十三章：Oracle Golde...
2020-04-19 查看：2199
第十二章：DataGuard
2020-04-19 查看：1796
