数据库常识以及性能优化概述（转）
任何事情都有它的源头，要解决问题，也得从源头开始，影响ORACLE性能的源头非常多，主要包括如下方面：数据库的硬件配置:CPU、内存、网络条件。
1. CPU:在任何机器中CPU的数据处理能力往往是衡量计算机性能的一个标志，并且ORACLE是一个提供并行能力的数据库系统，在CPU方面的要求就更高了，如果运行队列数目超过了CPU处理的数目，性能就会下降，我们要解决的问题就是要适当增加CPU的数量了，当然我们还可以将需要许多资源的进程KILL掉;
2. 内存:衡量机器性能的另外一个指标就是内存的多少了，在ORACLE中内存和我们在建数据库中的交换区进行数据的交换，读数据时，磁盘I/O必须等待物理I/O操作完成，在出现ORACLE的内存瓶颈时，我们第一个要考虑的是增加内存，由于I/O的响应时间是影响ORACLE性能的主要参数，我将在这方面进行详细的讲解
3. 网络条件:NET*SQL负责数据在网络上的来往，大量的SQL会令网络速度变慢。比如10M的网卡和100的网卡就对NET*SQL有非常明显的影响，还有交换机、集线器等等网络设备的性能对网络的影响很明显，建议在任何网络中不要试图用3个集线器来将网段互联。
OS参数的设置
下表给出了OS的参数设置及说明，DBA可以根据实际需要对这些参数进行设置
内核参数名
说明
bufpages
对buffer空间不按静态分配，采用动态分配，使bufpages值随nbuf一起对buffer空间进行动态分配。
create_fastlinks
对HFS文件系统允许快速符号链接
dbc_max_pct
加大最大动态buffer空间所占物理内存的百分比，以满足应用系统的读写命中率的需要。
dbc_min_pct
设置最小动态buffer空间所占物理内存的百分比
desfree
提高开始交换操作的最低空闲内存下限，保障系统的稳定性，防止出现不可预见的系统崩溃(Crash)。
fs_async
允许进行磁盘异步操作，提高CPU和磁盘的利用率
lotsfree
提高系统解除换页操作的空闲内存的上限值，保证应用程序有足够的可用内存空间。
maxdsiz
针对系统数据量大的特点，加大最大数据段的大小，保证应用的需要。(32位)
maxdsiz_64bit
maximum process data segment size for 64_bit
Maxssiz
加大最大堆栈段的大小。(32_bit)
maxssiz_64bit
加大最大堆栈段的大小。(64_bit)
Maxtsiz
提高最大代码段大小，满足应用要求
maxtsiz_64bit
原值过大，应调小
Minfree
提高停止交换操作的*内存的上限
Shmem
允许进行内存共享，以提高内存的利用率
Shmmax
设置最大共享内存段的大小，完全满足目前的需要
Timeslice
由于系统的瓶颈主要反映在磁盘I/O上，因此 降低时间片的大小，一方面可避免因磁盘I/O不畅造成CPU的等待，从而提高了CPU的综合利用率。另一方面减少了进程的阻塞量。
unlockable_mem
提高了不可锁内存的大小，使可用于换页和交换的内存空间扩大,用以满足系统对内存管理的要求。
oracle性能调整的十大要点
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的值。
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）表空间使用自动空间管理，消除了*空间列表的需求，可以减少数据库的竞争
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’
二、数据库配置和IO问题
降低磁盘的IO
分散磁盘的IO
表空间使用本地管理
1、将文件分散到不同的设备上
1）将数据文件与日志文件分开
2）减少与服务器无关的磁盘IO
3）评估裸设备的使用
4）分割表数据
2、表空间的使用
系统表空间保留给数据字典对象
创建本地管理表空间以避免空间管理问题
将表和索引分散到独立的表空间中
使用独立的回滚表空间
将大的数据库对象保存在各自独立的表空间中
创建一个或多个独立的临时表空间
下列数据库对象应该有单独的表空间：
数据字典、回滚段、索引、临时段、表、大对象
3、检查IO统计数据
Select phyrds,phywrts,d.name from v$datafile d,v$filestat f where f.file#=d.file# order by d.name;
检查最有可能引起磁盘IO瓶颈的文件。
4、分割文件
可以通过RAID和手工进行
Alter table table_name allocate extent (datafile ‘fiile_name’ size 10M);
但手工操作工作量很大。
5、优化全表扫描操作
1）检查有多少全表发生：
Select name,value from v$sysstat where name like ‘%table scan%’;
table scans (short tables)/ table scans (long tables)与全表扫描相关，如果table scans (long tables)的值很高，说明大部分的table access 没有经过索引查找，应该检查应用或建立索引，要确保有效的索引在正确的位置上。
合理的DB_FILE_MULTIBLOCK_READ_COUNT能减少table scan需要调用的IO次数，提高性能（与OS相关）。
2）查看full table scan操作：
Select sid,serial#,opname,target，to_char(start_time,’HH24:MI:SS’) “start”,(sofar/totalwork)*100 “percent_complete” from v$session_longops;
通过v$session_longops里的sql_hash_value与v$sqltext关联，可以查询导致full table scan的sql。
6、Checkpoint
Checkpoint进行的操作：DBWn进行IO操作；CKPT更新数据文件头和控制文件。
经常进行Checkpoint的结果：减少恢复所需的时间；降低了系统运行时的性能。
LGWR以循环的方式将日志写到各个日志组，当一个日志组满时，oracle server必须进行一个Checkpoint，这意味着：DBWn将对应log覆盖的所有或部分脏数据块写进数据文件；CKPT更新数据文件头和控制文件。如果DBWn没有完成操作而LGWR需要同一个文件，LGWR只能等待。
在OLTP环境下，如果SGA很大并且checkpoint的次数不多，在Checkpoint的过程中容易出现磁盘竞争的状况，在这种情况下，经常进行Checkpoint可以减少每次Checkpoint涉及到的脏数据块的数目。
调节Checkpoint次数的办法：
增大日志文件；增加日志组以增加覆盖的时间间隔。
7、日志文件
建立大小合适的日志文件以最小化竞争；
提供足够的日志文件组以消除等待现象；
将日志文件存放在独立的、能快速访问的存储设备上（日志文件可以创建在裸设备上）。日志文件以组的方式组织管理，每个组里的日志文件的内容完全相同。
8、归档日志文件
如果选择归档模式，必须要有两个或两个以后的日志组，当从一个组切换到另一个组时，会引起两种操作：DBWn进行Checkpoint；一个日志文件进行归档。
归档有时候会报错：
ARC0：Beginning to archive log# 4 seq# 2772
Current log# 3 seq# 2773……
ARC0: Failed to archive log# 4 seq# 2772
ARCH: Completed to archiving log#4 seq# 2772
建议init参数修改如下：
log_archive_max_processes=2
#log_archive_dest = ‘/u05/prodarch’
log_archive_dest_1 = "location=/u05/prodarch MANDATORY’
log_archive_dest_state_1 = enable
log_archive_dest_2 = "location=/u05/prodarch2 OPTIONAL reopen=10" （或其它目录）
log_archive_dest_state_2 = enable
log_archive_min_succeed_dest=1
log_archive_dest_state_3 = DEFER
log_archive_dest_state_4 = DEFER
log_archive_dest_state_5 = DEFER
三、优化排序操作
1、概念
服务器首先在sort_area_size指定大小的内存区域里排序，如果所需的空间超过sort_area_size，排序会在临时表空间里进行。在专用服务器模式下，排序空间在PGA中，在共享服务器模式下，排序空间在UGA中。如果没有建立large pool，UGA处于shared pool中，如果建立了large pool，UGA就处于large pool中，而PGA不在sga中，它是与每个进程对应单独存在的。
PGA：program global area,为单个进程（服务器进程或后台进程）保存数据和控制信息的内存区域。PGA与进程一一对应，且只能被起对应的进程读写，PGA在用户登录数据库创建会话的时候建立。
有关排序空间自动管理的两个参数：
Pga_aggregate_target: 10M-4000G，等于分配给oracle instance的所有内存减去SGA后的大小。
Workarea_size_policy: auto/manual，只有Pga_aggregate_target已定义时才能设置为auto。
这两个参数会取代所有的*_area_size参数。
措施：
尽可能避免排序；尽可能在内存中排序；分配合适的临时空间以减少空间分配调用。
2、需要进行排序的操作：
A、创建索引；
B、涉及到索引维护的并行插入
C、order by或者group by（尽可能对索引字段排序）
D、Distinct
E、union/intersect/minus
F、sort-merge join
G、analyze命令（仅可能使用estamate而不是compute）
3、诊断和措施
Select * from v$sysstat where name like ‘%sort%’;
Sort(disk):要求Io去临时表空间的排序数目
Sort(memory)：完全在memory中完成的排序数目
Sort(rows)：被排序的行数合计
Sort（disk）/ Sort（memory）<5%,如果超过5％，增加sort_area_size的值。
SELECT disk.Value disk,mem.Value mem,(disk.Value/mem.Value)*100 ratio FROM v$sysstat disk,v$sysstat mem WHERE mem.NAME='sorts (memory)' AND disk.NAME='sorts (disk)';
4、监控临时表空间的使用情况及其配置
Select tablespace_name,current_users,total_extents,used_extents,extent_hits,max_used_blocks,max_sort_blocks FROM v$sort_segment ;
Column Description
CURRENT_USERS Number of active users
TOTAL_EXTENTS Total number of extents
USED_EXTENTS Extents currently allocated to sorts
EXTENT_HITS Number of times an unused extent was found in the pool
MAX_USED_BLOCKS Maximum number of used blocks
MAX_SORT_BLOCKS Maximum number of blocks used by an individual sort
临时表空间的配置：
A、initial/next设置为sort_area_size的整数倍，允许额外的一个block作为segment的header
B、pctincrease=0
C、基于不同的排序需要建立多个临时表空间
D、将临时表空间文件分散到多个磁盘上
四、诊断latch竞争
1、概念
Latch是简单的、低层次的序列化技术，用以保护SGA中的共享数据结构，比如并发用户列表和buffer cache里的blocks信息。一个服务器进程或后台进程在开始操作或寻找一个共享数据结构之前必须获得对应的latch，在完成以后释放latch。不必对latch本身进行优化，如果latch存在竞争，表明SGA的一部分正在经历不正常的资源使用。
1）Latch的作用：
A、序列化访问：保护SGA中的共享数据结构；保护共享内存的分配。
B、序列化执行：避免同时执行某些关键代码；避免互相干扰。
2）Latch请求的两种类型：
A、willing-to-wait：请求的进程经过短时间的等待后再次发出请求，直到获得latch
B、immediate：如果没有获得latch，请求的进程不等待，而是继续处理其他指令。
2、检查Latch竞争
检查latch free是不是主要的wait event：
Select * from v$system_event order by time_waited;
检查latch的使用情况：
Select * from v$latch:
与willing-to-wait请求有关的列：gets、misses、sleeps、wait_time、cwait_time、spin_gets
与immediate请求有关的列：immediate_gets、immediate_misses
Gets: number of successful willing-to-wait requests for a latch;
Misses: number of times an initial wiling-to-wait request was unsuccessful;
Sleeps: number of times a process waited after an initial willing-to-wait request;
Wait_time: number of milliseconds waited after willing-to-wait request;
Cwait_time: a measure of the cumulative wait time including the time spent spinning and sleeping,the overhead of context switches due to OS time slicing and page faults and interrupts;
Spin_gets: gets that misses first try but succeed after spinning.
Immediate_gets: number of successful immediate requests for each latch;
Immediate_misss: number of unsuccessful immediate requests for each latch;
一般无需调整latch，但是下列的措施是有用的：
A、对处于竞争中的latch做进一步的调查
B、如果竞争主要存在于shared pool和library cache中，可以考虑调整应用
C、如果进一步的调查显示需要调整shared pool和buffer cache，就进行调整
Select * from v$latch where name like ‘%shared pool%’ or name like ‘%library cache%’；
如果竞争是在shared pool或library cache上，表示下列集中情况：
A、不能共享的sql,应检查他们是否相似，考虑以变量代替sql中的常量：
Select sql_text from v$sqlarea where executions=1 order by upper(sql_text);
B、共享sql被重新编译，考虑library cache的大小是否需要调整：
SELECT sql_text,parse_calls,executions FROM v$sqlarea where parse_calls>5;
C、library cache不够大
五、Rollback(undo) Segment 优化
1、概念
Transaction以轮循的方式使用rollback segment里的extent，当前所在的extent满时就移动到下一个extent。可能有多个transaction同时向同一个extent写数据，但一个rollback segment block中只能保存一个transaction的数据。
Oracle 在每个Rollback segment header中保存了一个transaction table，包括了每个rollback segment中包含的事务信息，rollback segment header的活动控制了向rollbak segment写入被修改的数据。rollback segment header是经常被修改的数据库块，因此它应该被长时间留在buffer cache中，为了避免在transaction table产生竞争导致性能下降，应有多个rollback segment或应尽量使用oracle server 自动管理的rollback segment。
2、诊断rollback segment header的竞争
如果rollback segment 由手工管理，下列措施诊断rollback segment header的竞争
SELECT class,count FROM v$waitstat WHERE class LIKE '%undo%' ;
SELECT Sum(Value) sum FROM v$sysstat WHERE NAME IN ('db block gets','consistent gets');
任何类型的等待次数（count）与总请求数(sum)的比率，不能超过1％。
或
select sum(waits)*100/sum(gets) "Ratio", sum(waits) "Waits", sum(gets) "Gets" from v$rollstat;
waits的汇总数与gets的汇总数的比率应低于1％，如果超过1％，应创建更多的rollback segment。
下列字段数值如果大于0，则表明在rollback segment header上存在竞争：
A、v$rollstat 中的waits
B、v$waitstat中的undo header行
C、v$system_event中的undo segment tx slot事件
3、消耗更少的rollback segment
1）如果是删除表里所有的数据，尽可能使用trauncate而不是delete。
2）在应用中允许用户有规律的提交，尽可能不用长事务。
3）• Import
– Set COMMIT = Y
– Size the set of rows with BUFFER
• Export: Set CONSISTENT=N
• SQL*Loader: Set the COMMIT intervals with ROWS
4、小回滚段可能出现的问题
A、事务由于缺少回滚空间失败
B、由于下列原因导致的“Snapshot too old”问题：
Block里的事务列表被刷新，block里的SCN比列表Interested Transaction List（ITL）里起始事务的SCN更新；
Rollback segment header里的Transaction slot被重用；
回滚数据已经被重写；
5、9i的自动回滚管理
Undo_managment指定了回滚空间的管理方式：Auto：自动管理；Manual：手工管理回滚段。
Undo_retention指定了回滚数据的保留期限；
Undo_tablespace指定了被使用的回滚表空间；
Oracle自动管理的表空间可以在常见数据库的时候创建，也可以单独建立。回滚表空间可以相互转换（switch），但在某一时刻只能有一个回滚表空间处于活动状态。回滚表空间处于非活动状态时可以删除，如果有对处于被删除回滚表空间里的已提交事务的查询时，oracle会返回一个错误。
估计undo tablespace大小的公式：
Undo space = (undo_retention * (undo blocks per second * db_block_size)) + db_block_size;
可以使用下列的sql设定undo_retention和undo tablespace：
select (rd*(ups*overhead)+overhead) "bytes" from (select value rd from v$parameter where name ='undo_retention'),(select (sum(undoblks)/sum(((end_time-begin_time)*10800))) ups from v$undostat),(select value overhead from v$parameter where name='db_block_size');
其中：
Rd：undo_retention设置的时间；
Ups：undo blocks per second；
Overhead：rollback segment header
六、Lock Contention
1、概念
DML事务使用row-level locks,查询不会锁定数据。锁有两种模式：exlusive、share。
锁的类型：
• DML or data locks:
– Table-level locks（TM）
– Row-level locks（TX）
• DDL or dictionary locks
一个transaction至少获得两个锁：一个共享的表锁，一个专有的行锁。Oracle server将所有的锁维护在一个队列里，队列跟踪了等待锁的用户、申请锁的类型以及用户的顺序信息。
Lock在下列情况会释放：commit；rollback；terminated（此时由pmon清理locks）。Quiesced database：一个数据库如果除了sys和system之外没有其他活动session，这个数据库即处于quiesced状态。活动session是指这个session当前处于一个transaction中，或一个查询中，一个fetch中，或正占有某种共享资源。
2、可能引起lock contention的原因
不必要的高层次的锁；
长时间运行的transaction；
未提交的修改；
其他产品施加的高层次的锁。
解决lock contention的方法：锁的拥有者提交或回滚事务；杀死用户会话。
3、死锁
Oracle自动检测和解决死锁，方法是通过回滚引起死锁的语句（statement），但是这条语句对应的transaction并没有回滚，因此当收到死锁的错误信息后，应该去回滚改transaction的剩余部分
七、应用优化
1、概念
为了提高性能，可以使用下列数据访问方法：
A、Clusters
B、Indexes
-B-tree（normal or reverse key）
-bitmap
-function-based
C、Index-organized tables
D、Materialized views
索引的层次越多，效率越低，如果索引中含有许多已删除的行，这个索引也会变得低效，如果索引数据的15％已经被删除，应该考虑重建索引。
2、应用问题
A、使用可声明的约束而不是通过代码限制
B、代码共享
C、使用绑定变量而不是文字来优化共享sql
D、调整cursor_sharing的值（EXACT/SIMILAR/FORCE）
八、提升block的效率
1、避免动态分配的缺陷
创建本地管理的表空间；
合理设置segment的大小；
监控将要扩展的segment：
SELECT owner, table_name, blocks, empty_blocks FROM dba_tables WHERE empty_blocks / (blocks+empty_blocks) < .1;
2、high water mark
记录在segment header block中，在segment创建的时候设定在segment的起始位置，当记录被插入的时候以5个block的增量增加，truncate可以重设high water mark的位置，但delete不能。
在full table scan中，oracle会读取high water mark以下的所有的数据块，所以high water mark以上的块也许会浪费存储空间，但不会降低性能。
可以通过下列方法收回表中high water mark以上的块：
Alter table_name deallocate unused；
对于high water mark以下的块：
使用import/export工具：export数据；drop或truncate表；import数据。或者利用alter table tanle_name move命令去移动表的存储位置（此时需要重建索引）。
3、表统计
用analyize命令生成表统计，然后到dba_table查询相关信息。
ANALYZE TABLE ndls.t_wh_shipping_bill COMPUTE STATISTICS;
SELECT num_rows, blocks, empty_blocks as empty,avg_space, chain_cnt, avg_row_len FROM dba_tables WHERE owner ='NDLS' AND table_name='T_WH_SHIPPING_BILL';
Columns Description
NUM_ROWS Number of rows in the table
BLOCKS Number of blocks below the table high-water mark
EMPTY_BLOCKS Number of blocks above the table high-water mark
AVG_SPACE Average free space in bytes in the blocks below high-water mark
AVG_ROW_LEN Average row length, including row overhead
CHAIN_CNT Number of chained or migrated rows in the table
4、block size
通过下列方法可以最小化block的访问次数：
使用更大的block size；紧密压缩行；阻止行镜像。后两者存在冲突，越多的行被压缩在一个block里，越容易产生镜像。Block size 在数据库创建的时候设定，不能被轻易改变，是读取数据文件时最小的IO单元，大小范围是2K－64K，应该设置成OS块的整数倍，小于或等于OS IO时能读取的存储区域。
较小的block size的优点：极少block竞争；有利于较小的行和随机访问。缺点是存在相当高的成本，每个block的行数更少，可能需要读取更多的index块。Block size的选择影响系统的性能，在一个OLTP环境中，较小的block size更合适，而在DSS环境中，适宜选择较大的block size。
5、PCTFREE、PCTUSED
1）PCTFREE、PCTUSED使你能控制一个segment里所有数据块里free space的使用。
PCTFREE：一个数据块保留的用于块里已有记录的可能更新的*空间占block size的最小比例。
PCTUSED：在新记录被插入block里之前这个block可以用于存储行数据和其他信息的空间所占的最小比率。
2）这两个参数的使用
如果创建表的时候指定pctfree＝20％，oracle会在这个表的data segment的每个block都保留20％的空间用于已有记录的更新。Block的已使用空间上升到整个block size的80％时，这个block将移出free list；在提交了delete、update之后，oracle server处理这条语句并检查对应block的已使用空间是否低于PCTUSED，如果是，则这个block放进free list。
3）PCTFREE、PCTUSED的设定
• PCTFREE
– Default 10
– Zero if no UPDATE activity
– PCTFREE = 100 × upd / (average row length)
• PCTUSED
– Default 40
– Set if rows deleted
– PCTUSED = 100 – PCTFREE – 100 × rows × (average row length) / blocksize
其中，upd ： the average amount added by updates, in bytes。This is determined by subtracting the average row length of intercurrent average row length;
average row length：在运行了analyize命令之后，这个值可以从dba_tables中的avg_row_len列中获得。
rows ： the number of rows to be deleted before free list maintenance occurs。
4）Delete、update可以增加block的*空间，但是释放出来的空间有可能是不连续的，oracle在下列情况下会对碎片进行整理：一个block有足够的*空间容纳row piece，但是由于每个碎片都较小以至这个row piece不能存放在一个连续的section中。
6、Migration和Chaining
1）如果一行的数据太大以至一个单独的block容纳不下，会产生两种现象：
A、Chaining：行数据太大以至一个空block容纳不下，oracle会将这一行的数据存放在一个或多个block 组成的block chain中，insert、update都可能导致这个问题，在某些情况下row chaining是不能避免的。
B、Migration：一次update操作可能导致行数据增大，以至它所在的block容纳不下，oracle server会去寻找一个有足够*空间容纳整行数据的block，如果这样的block存在，oracle server把整行移到新的block，在原位置保存一个指向新存放位置的镜像行，镜像行的rowid和原来的rowid一致。
Chaining、Migration的弊端：insert、update的性能降低，索引查询增加了IO次数。
2）检测migration和chaining：
Analyize table table_name compute statistics；
Select num_rows,chain_cnt from dba_tables where table_name=’...’;
查询镜像行：
Analyize table table_name list chained rows；
Select owner_name,table_name,head_rowid from chained_rows where table_name=’...’;
产生Migration的原因可能是由于PCTFREE设置的太低以至没有保留足够的空间用于更新。
可以通过增加PCTFREE的值避免行镜像产生。
3）消除镜像行的步骤：
运行analyize table ... list chained rows;
复制镜像行到另一个表tmp；
从源表中删除这些行；
从tmp中将这些行插回到源表中。
脚本：
/* Get the name of the table with migrated rows */
accept table_name prompt ’Enter the name of the table with migrated rows: ’
/* Clean up from last execution */
set echo off
drop table migrated_rows;
drop table chained_rows;
/* Create the CHAINED_ROWS table */
@?/rdbms/admin/utlchain
set echo on
spool fix_mig
/* List the chained & migrated rows */
analyze table &table_name list chained rows;
/* Copy the chained/migrated rows to another table */
create table migrated_rows as
select orig.* from &table_name orig, chained_rows cr
where orig.rowid = cr.head_rowid
and cr.table_name = upper(’&table_name’);
/* Delete the chained/migrated rows from the original table */
delete from &table_name
where rowid in ( select head_rowid from chained_rows );
/* Copy the chained/migrated rows back into the original table */
insert into &table_name select * from migrated_rows;
spool off
使用这个脚本时，必须将涉及到的外键约束去掉。
7、索引重组
在一个不稳定的表上建索引会影响性能，一个索引block只有完全空时才能进入free list，即使一个索引block里只含有一个条目，它也必须被维护，因此索引需要进行阶段性的重建。
1）检查索引是否需要重组
A、收集一个index的使用统计
ANALYZE INDEX acct_no_idx VALIDATE STRUCTURE;
B、查看收集的统计数据
SELECT NAME,(DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS index_usage FROM index_stats;
Column Description
LF_ROWS Number of values currently in the index
LF_ROWS_LEN Sum in bytes of the length of all values
DEL_LF_ROWS Number of values deleted from the index
DEL_LF_ROWS_LEN Length of all deleted values
C、如果浪费超过20％则索引需要重建
ALTER INDEX acct_no_idx REBUILD;
D、或者对索引进行整理
Alter index acct_no_idx coalesce;
2）标记未使用的索引
A、 开始监测索引的使用
Alter index hr.emp_name_ix monitoring usage;
B、 停止监测索引的使用
Alter index hr.emp_name_ix nomonitoring usage;
C、 查询索引的使用情况
Select index_name,used from v$object_usage;
删除未使用过的索引，可以降低DML操作的成本，从而提升系统性能。
为了尽可能经济的利用block，应对存在较多空block、镜像行的表进行重建，对建立不稳定表上的索引应有规律的进行重建，并尽可能创建本地管理的表空间
九、SQL优化
1、优化器模式
Oracle9i有两种优化器模式可以选择：
• Rule-based:
– Uses a ranking system
– Syntax- and data dictionary–driven
• Cost-based:
– Chooses least-cost path
– Statistics-driven
Rule-based模式满足向后兼容，而Cost-based模式中的成本大部分来自于逻辑读的次数，推荐使用Cost-based模式。
2、固定optimizer plan
1）概念
对于每一个查询，optimizer都会准备一个定义了操作执行顺序和方法的操作树（执行计划），oracle server根据这个执行计划执行语句。通过固定执行计划，可以强制应用通过一种理想的方式访问数据，并且一个稳定的执行计划可以经历数据库的变化而保持不变。固定执行计划通过创建stored outline实现，outline使用cost-based的optimizer，因为其由一系列的hints组成。
执行计划的固定依赖于当判定一个查询是否存在stored outline时查询语句是否完全一致，与判定shared pool里一个执行计划是否可以重用时的匹配方式是一致的。
Outline被保存在outln schema中。
2） 创建stored outline
alter session set CREATE_STORED_OUTLINES = train;
create or replace OUTLINE co_cl_join
FOR CATEGORY train ON
select co.crs_id, ...
from courses co，classes cl
where co.crs_id = cl.crs_id;
stored outline通过category组织，相同的sql语句可以在多个category同时拥有stored outline，如果categoey没有指定，缺省是default category。
当CREATE_STORED_OUTLINES等于true或category名时，oracle会为所有被执行的sql语句创建stored outline，也可以通过create outline手工创建。
3） 使用stored outline
将USE_STORED_OUTLINES设置为true或category名。
alter session set USE_STORED_OUTLINES = train;
当为一个查询寻找stored outline时，查询语句与stored outline里的语句必须完全一致，在outline里的hints也必须在查询语句中出现。
3、private outline
Private outline是当前保存的stored outline的副本，可以被编辑而不影响正在运行的系统，一个private outline只能被当前session看到，它的数据被保存在当前被解析的schema里。，知道显示的将其公布。
当USE_PRIVATE_OUTLINES=TRUE时，一个已有outline的sql被提交时，optimizer会检查是否存在private outline，如果不存在，optimizer就不使用optimizer编译语句，而不会去检查公布的stored outline。
4、在sql中使用hints
Create index gen_idx on customers(cust_gender);
Select /*+ index(customers gen_idx)*/
Cust_last_name,cust_street_address,cust_postal_code
From sh.customers where upper(gender)=’M’;
5、EXPLAIN PLAN
可以不通过tracing，需要建立plan_table表：
Sql>@oracle_home/rdbms/admin/utlxplan;
建立explain plan：
Explain plan for select last_name from hr.emp;
查询plan_table中的explain plan，可以直接查询，也可以通过脚本utlxplx.sql(隐藏并行查询信息)、utlxplp.sql（显示并行查询信息）查询。
6、管理统计信息
利用analyize命令收集或删除信息。
参数：
Compute：统计精确的数据；
Estimate：估计的统计数据。
各类统计数据的位置：
表：dba_tables;
索引：dba_indexes;
列：user_tab_col_statistics;
柱状图（histogram）详细的描述了一个特定列中数据的分布情况，可以通过analyize table ... for columns... 命令创建，保存在dba_histogram/dba_tab_histograms中
十、操作系统优化和使用资源管理器
1、操作系统优化
1）概念
操作系统优化时应该考虑的因素有：内存的使用；Cpu的使用；IO级别；网络流量。各个因素互相影响，正确的优化次序是内存、IO、CPU。
操作系统使用了虚拟内存的概念，虚拟内存使每个应用感觉自己是使用内存的唯一的应用，每个应用都看到地址从0开始的单独的一块内存，虚拟内存被分成4K或8K的page，操作系统通过MMU（memory management unit）将这些page与物理内存映射起来，这个映射关系通过page table控制。
Raw device是没有文件结构或目录结构的磁盘或磁盘分区，由于它忽略了操作系统缓存，在某些情况下可以显著提升性能，但是在windows NT下，由于操作系统IO操作本身不使用文件系统缓存，所以raw device不能显示性能上的优点。
2）Guideline
CPU的最高使用率：90％；
OS/USER进程数之比：40/60；
各个CPU的负载应该大致均衡。
3）服务器安全性检查
A、检查UNIX系统用户口令
检查：/etc/passwd、/etc/shadow，UNIX密码采用了shadow机制，安全性能高
建议：参考UNIX命令passwd，修改/etc/default/passwd文件的某些设置如MAXWEEKS、MINWEEKS、PASSLENGTH使口令修改更加合理化。
建议：定期更改UNIX系统的如下用户口令：
root、oraprod、applprod、appprod
B、检查 Remote Login
启动了rlogin，服务器数据库a、数据库b、数据库c，终端console1、console2、console3及T3形成相互非常信任的关系，用户只要拥有一个服务器的超级权限就可以rlogin到.rhosts指明的任一主机而无需要口令。
建议：非常不安全，参考UNIX命令rlogin和/目录下的文件.rhosts。在正式环境服务器和测试环境服务器之间不要建立这种远程信任的机制。
C、检查FTP服务
检查可以FTP到服务器的用户（/etc/ftpusers），注释了root用户，就是说用户可以用root权限FTP到服务器上。权限太大。
建议：把这种权力取消，将/etc/ftpusers中root的注释符号（#）去掉，在列表中添加oraprod、applprod、appprod等用户使之不能FTP服务器。必要时（如上传PATCH时）再打开applprod的FTP权限。
D、建议：UNIX系统管理员定期检查/var/adm下的messages、sulog；/etc/syslog.conf 等信息。检查是否有非法用户登陆UNIX。
建议：与UNIX工程师探讨更好的监控方式
4）数据库与应用产品安全性检查
A、建议：修改oracle用户根目录下的.profile文件，修改该文件的权限为500。即使得用户登陆时并不执行和数据库或应用相关的环境变量，增加安全性。
B、检查数据库DBA权限的用户密码和应用系统用户密码：SYSTEM、APPS密码都已经改变，SYS密码还是初始安装密码Change_on_install
建议：立即修改SYS用户密码，定期更改APPS、SYSTEM、SYS密码。
C、定期检查并清除$ORACLE_HOME/admin/bdump目录下的alert_PROD.log文件和后台进程trace文件。定期清除$ORACLE_HOME/admin/udump目录下的trc文件。
D、建议：给应用产品登陆的用户设置口令过期限制，如口令访问次数限制或时间（天数）限制。
建议：不要给使用应用产品的用户共享用户名和口令，每个用户分配一个应用产品用户名。
建议：对有应用系统管理员权限的用户登记，不适合有系统管理员权限的用户要把权限回收，统一管理。
E、定期检查并清除与Apache Server有关的log文件，目录为:
/u01/prodora/iAS/Apache/Apache/logs/acccess_log、error_log
/u01/prodora/iAS/Apache/Jserv/logs/jserv.log、mod_jserv.log
F、定期检查清除listener、tnsname的log文件，文件存放在:
/u01/prodora/8.0.6/network/admin/apps_prod.log、
/u01/proddb/8.1.7/network/admin/prod.log
/u01/proddb/8.1.7/network/log/listener.log、sqlnet.log…
G、数据库控制文件做多个镜像，放在多个磁盘位置，提高安全性。
5）网络安全性检查
检查$ORACLE_HOME/dbs/initPROD.ora文件
#remote_login_passwordfile=EXCLUSIVE
设置为REMOTE_LOGIN_PASSWORDFILE=NONE，不允许远程客户用INTERNAL方式登陆。
2、资源管理器（Resource Manager）
通过资源管理器可以管理混合工作负载，控制系统性能。数据库资源管理器包括：
• Resource plans：包括 resource plan directives, 它指定了被分配到各个 resource consumer group的资源。
• Resource consumer groups：定义了具有类似资源使用需求的一组用户。
• Resource plan directives：包括下列内容:为consumer groups 或 subplans 指定resource plans；在各个 consumer groups 或资源计划的subplans 分配资源
Oracle性能调整的误区
为了提高性能,我们针对Oracle数据库本身提供了的方法或方案进行过不少的尝试，主要包括:
共享服务器模式(MTS)
集群技术(Clustering)RAC
分区
并行处理(主要是并行查询)
Oracle提供的这些特性确实是用来进行性能改善的,但我们往往忽略了对自身应用特性的分析,它们是否适合于我们.最近,通过对这方面知识的深入了解,发现我们以前存在一些错误的认识.我觉得有必要,大家一起来改变这种误解.
分析之前,先明确一下我们的应用特性.数据库应用大体可以分为OLAP和OLTP两大类,即:联机事务分析(数据仓库)和联机事务处理(事务应用)我们的应用系统,其应用特性主要是联机事务处理,又包含了少量的数据仓库特性.
1.共享服务器(MTS)
Oracle缺省用的是专用服务器模式,也就是说一个用户连接进程对应一个服务器的进程.记得某大医院刚启用的时候,我们曾经试过MTS.因为听说MTS在不增加内存和CPU的情况下连接更多的客户端,结果并不是我们预期的那样.MTS有问题吗?不是,是因为我们对MTS不了解,并不是它有问题,而是它不是用来在这种情况下做这件事的.
一般情况,只有当并发连接数超过了操作系统的支持时,才建议使用MTS,否则应该使用缺省的专用服务器模式.也就是说,在专用服务器模式下,因为多一个连接就要多消耗一个操作系统的进程,只有当并发应用需求超过操作系统的允许连接数时,才有必要考虑MTS.
如果现有系统,物理上支持100个连接的专用服务器数据库,改为使用共享服务器模式,也许支持1000个连接,但同时活动的连接可能只有100个.一般2到4个CPU的服务器,应对200到400个并发连接是足够的,如果连接增加了,可以增加CPU和内存.
MTS具有以下一些缺点:
1.共享服务器的代码路径比专用服务器长,所以它天生就比专用服务器慢.
2.存在人为死锁的可能,因为它是串行的,所有共享服务器绑定在一起(一个进程),只要一个连接阻塞,则所有用户阻塞,并且极可能死锁.
3.存在独占事务的可能,因为如果一个会话的事务运行时间过长,它独占共享资源,其它用户只能等待.(而专用服务器,每个客户端是一个会话)
4.共享服务器模式限制了某些数据库特性,例如:不能单独启动和关闭实例,不能进行介质恢复,不能使用Log Miner,不能使用,并且SQL_TRACE没有意义(因为是共享而不是当前会话的).
MTS减少的内存实际上是专用服务器模式下每个用户连接到操作系统进程所需的内存,但它却使用SGA的Large_Pool来分配UGA,拆东墙补西墙,所减少的内存是很少的.如果用户会话的连接和断开很频繁,数据库进程的创建和删除的开销会非常大,这种情况最好采用共享服务器模式(否则,应该使用连接池技术).所幸的是,我们产品的设计可能就考虑了这个因素,使用的是一次连接终身使用(会话生命周期内),避免了这种情况.
所以,综上所述,针对我们产品,建议采用缺省的专用服务器模式,连接不够时,通过增加硬件解决,而不是改用MTS.另外,实际上,Oracle可以同时支持共享服务器和专用服务器模式,可以指定一个会话使用专用服务器,另一个会话使用共享服务器.
2.集群技术(RAC)
Oracle RAC(Real Application Clusters),我们说的双机容错就是RAC的一种. 集群技术的优势在在于横向扩展性能,并提供高可用性.32位的操作系统有4G内存的限制,有些Unix系统(以及非高级版本的Windows)有CPU个数的限制.而集群技术通过集合多台机器协同工作,横向打破了这种限制.通过RAC,一台服务器一个实例,多台机器构成一个实例服务集,客户端连接到它上面.这项技术,我们有时对客户说是负载均衡,实际上这是片面的,RAC的主要针对的是CPU和内存的负载均衡,并没有实现磁盘IO的负载均衡.(当然,磁盘IO可以通过Raid或NAS来实现)
RAC还有一个好处是,提高了可用性,也就是说一台服务器坏掉了(注意:不是数据存储介质),不影响正常使用.就像负载均衡一样,它提高了数据层以上的可用性,但不是全部,因为数据坏了,它也没有办法.(数据层,那是Oracle Data Guard的事了,或者干脆说那是存储硬件的事)
但是,RAC带来好处的同时,也带来了性能的影响.因为它要全局协调数据高速缓存,保证每个实例上连接的用户看到的缓存数据是一致的,所以把以下三方面的矛盾放大:
1.高速缓存争用
2.过多的I/O
3.锁定
也就是说,如果这些方面有问题,用了RAC后问题就会更大,例如:由于SQL没有使用绑定变量导致高速缓存争用,用了RAC会更严重.
总之,如果你的服务器的CPU插满了,内存也加到极限了,而并发用户还在不断增长,或者你对故障停机时间要求非常高,RAC无疑是你应该选择的.
3.分区
Oracle的分区用途在于把大的表或索引分成小的片段,以便更容易管理.我们以前可能错误的认为分区就是fast=true,可以提高速度,也在肿瘤和儿科做过这方面的试验.实际上,在事务处理系统中,分区一般不能加快查询速度(某些情况下可能会减少对共享资源的争用).Oracle的分区特性,主要是针对数据仓库来设计的,也就是说你的某张表如果有100G的大小,最好使用分区,好处有以下三个方面:
1.提高可用性
分区的原理就是分而治之,如果一张表划分为多个分区,其中一个分区所在的介质出了问题,不影响整个表的其它分区数据的访问.
2.易于管理
在数据仓库下,表分成小的片断,更容易批量的删除,碎片整理,以及一些并行处理.
3.提高性能
这方面,通过分区来达到是最困难的,必须经过周密的计算来安排分区数据.
分区的规划是复杂的,拿我们产品应用来说,一般查询涉及到多个表,多个索引,假设我们把病人费用记录,药品收发记录,病人医嘱记录这类大表建立分区.显然,范围分区对我们提升性能用处不大,散列分区才是我们查询需求的,但大多数数据的散列又不够集中.再加上,这些表上的索引这么多,常用的ID,时间类索引就不少,很少有人能做到把它们全部进行全局分区或准确的进行范围分区(实际上可能根本无法按需求进行多个索引的范围分区).如果查询经常涉及多个索引,如何保证用到的每个索引都在一个分区上,如果不是,必然扫描多个分区,增加逻辑I/O和CPU时间,从而增加查询时间.(数据分布在不同物理存储介质的情况,在下面的并行处理中再讨论)
再来看一下,某些情况下可能会减少对共享资源的争用是指什么,是指并行修改和更新会更快.仔细分析,我们分区的原则是什么?一般最常用的可能是按时间段进行范围分区,这样,修改和更新绝大多数还是在同一个分区上进行,所以对减少共享资源的争用这方面,基本没有什么效果.(有按科室ID进行散列分区的对应的唯一应用需求吗?有基于列表分区(典型特征值)的对应的唯一应用需求吗?基本上没有.)分区主要从并行的角度来提高性能,但事务处理系统本身应用特性决定了它不适合这种技术.也就是说,针对我们产品的事务处理应用特点,根本没有必要采用分区技术.
4.并行处理
根据我们的应用特点,主要分析并行查询.一般要求配合分区特性,多CPU硬件.自Oracle 8.1.6起,增加了一个自动调节并行查询的选项:PARALLEL_AUTOMATIC_TUNING=TRUE在相应的表上设置PARALLEL参数,Oracle就会在适当的时候自动并行化该表上的操作.并行查询对事务处理系统基本上没有用.因为并行查询的设计是针对数据仓库中的单用户完全消耗100的资源而做的.而事务处理中,往往有很多并发用户,他们争用共用资源,所以你想办法让一个用户占用所有的资源是适得其反
SQL语句性能调整原则
一、问题的提出
在应用系统开发初期，由于开发数据库数据比较少，对于查询SQL语句，复杂视图的的编写等体会不出SQL语句各种写法的性能优劣，但是如果将应用系统提交实际应用后，随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的优化。对于海量数据，劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍，可见对于一个系统不是简单地能实现其功能就可，而是要写出高质量的SQL语句，提高系统的可用性。
在多数情况下，Oracle使用索引来更快地遍历表，优化器主要根据定义的索引来提高性能。但是，如果在SQL语句的where子句中写的SQL代码不合理，就会造成优化器删去索引而使用全表扫描，一般就这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时我们应清楚优化器根据何种原则来删除索引，这有助于写出高性能的SQL语句。
二、SQL语句编写注意问题
下面就某些SQL语句的where子句编写中需要注意的问题作详细介绍。在这些where子句中，即使某些列存在索引，但是由于编写了劣质的SQL，系统在运行该SQL语句时也不能使用该索引，而同样使用全表扫描，这就造成了响应速度的极大降低。
1. IS NULL 与 IS NOT NULL
不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。
任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。
2. 联接列
对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。我们一起来看一个例子，假定有一个职工表(employee)，对于一个职工的姓和名分成两列存放(FIRST_NAME和LAST_NAME)，现在要查询一个叫比尔.克林顿(Bill Cliton)的职工。
下面是一个采用联接查询的SQL语句
select * from employss
where
first_name||''||last_name ='Beill Cliton';
上面这条语句完全可以查询出是否有Bill Cliton这个员工，但是这里需要注意，系统优化器对基于last_name创建的索引没有使用。
当采用下面这种SQL语句的编写，Oracle系统就可以采用基于last_name创建的索引。
Select * from employee
where
first_name ='Beill' and last_name ='Cliton';
遇到下面这种情况又如何处理呢?如果一个变量(name)中存放着Bill Cliton这个员工的姓名，对于这种情况我们又如何避免全程遍历，使用索引呢?可以使用一个函数，将变量name中的姓和名分开就可以了，但是有一点需要注意，这个函数是不能作用在索引列上。下面是SQL查询脚本:
select * from employee
where
first_name = SUBSTR('&&name'，1，INSTR('&&name'，' ')-1)
and
last_name = SUBSTR('&&name'，INSTR('&&name’，' ')+1)
3. 带通配符(%)的like语句
同样以上面的例子来看这种情况。目前的需求是这样的，要求在职工表中查询名字中包含cliton的人。可以采用如下的查询SQL语句:
select * from employee where last_name like '%cliton%';
这里由于通配符(%)在搜寻词首出现，所以Oracle系统不使用last_name的索引。在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用:
select * from employee where last_name like 'c%';
4. Order by语句
ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中(象联接或者附加等)。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。
仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。
5. NOT
我们在查询时经常在where子句使用一些逻辑表达式，如大于、小于、等于以及不等于等等，也可以使用and(与)、or(或)以及not(非)。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子:
... where not (status ='VALID')
如果要使用NOT，则应在取反的短语前面加上括号，并在短语前面加上NOT运算符。NOT运算符包含在另外一个逻辑运算符中，这就是不等于(<>)运算符。换句话说，即使不在查询where子句中显式地加入NOT词，NOT仍在运算符中，见下例:
... where status <>'INVALID';
再看下面这个例子:
select * from employee where salary<>3000;
对这个查询，可以改写为不使用NOT:
select * from employee where salary<3000 or salary>3000;
虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引，而第一种查询则不能使用索引。
6. IN和EXISTS
有时候会将一列和一系列值相比较。最简单的办法就是在where子句中使用子查询。在where子句中可以使用两种格式的子查询。
第一种格式是使用IN操作符:
... where column in(select * from ... where ...);
第二种格式是使用EXIST操作符:
... where exists (select 'X' from ...where ...);
我相信绝大多数人会使用第一种格式，因为它比较容易编写，而实际上第二种格式要远比第一种格式的效率高。在Oracle中可以几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。
第二种格式中，子查询以‘select 'X'开始。运用EXISTS子句不管子查询从表中抽取什么数据它只查看where子句。这样优化器就不必遍历整个表而仅根据索引就可完成工作(这里假定在where语句中使用的列存在索引)。相对于IN子句来说，EXISTS使用相连子查询，构造起来要比IN子查询困难一些。
通过使用EXIST，Oracle系统会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间。Oracle系统在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。这也就是使用EXISTS比使用IN通常查询速度快的原因。
同时应尽可能使用NOT EXISTS来代替NOT IN，尽管二者都使用了NOT(不能使用索引而降低速度)，NOT EXISTS要比NOT IN查询效率更高。
Oracle调优综述
在过去的十年中， Oracle 已经成为世界上最专业的数据库之一。对于 IT 专家来说，就是要确保利用 Oracle 的强大特性来提高他们公司的生产力。最有效的方法之一是通过 Oracle 调优。它有大量的调整参数和技术来改进你的 Oracle 数据库的性能。
Oracle 调优是一个复杂的主题。关于调优可以写整整一本书，不过，为了改善 Oracle 数据库的性能，有一些基本的概念是每个 Oracle DBA 都应该遵从的。
在这篇简介中，我们将简要地介绍以下的 Oracle 主题：
外部调整：我们应该记住 Oracle 并不是单独运行的。因此我们将查看一下通过调整 Oracle 服务器以得到高的性能。
Row re-sequencing 以减少磁盘 I/O ：我们应该懂得 Oracle 调优最重要的目标是减少 I/O 。
Oracle SQL 调整。 Oracle SQL 调整是 Oracle 调整中最重要的领域之一，只要通过一些简单的 SQL 调优规则就可以大幅度地提升 SQL 语句的性能，这是一点都不奇怪的。
调整 Oracle 排序：排序对于 Oracle 性能也是有很大影响的。
调整 Oracle 的竞争：表和索引的参数设置对于 UPDATE 和 INSERT 的性能有很大的影响。
我们首先从调整 Oracle 外部的环境开始。如果内存和 CPU 的资源不足的话，任何的 Oracle 调整都是没有帮助的。
外部的性能问题
Oracle 并不是单独运行的。 Oracle 数据库的性能和外部的环境有很大的关系。这些外部的条件包括有：
．CPU--CPU 资源的不足令查询变慢。当查询超过了 Oracle 服务器的 CPU 性能时，你的数据库性能就受到 CPU 的限制。
．内存 -- 可用于 Oralce 的内存数量也会影响 SQL 的性能，特别是在数据缓冲和内存排序方面。
．网络 -- 大量的 Net8 通信令 SQL 的性能变慢。
许多新手都错误的认为应该首先调整 Oracle 数据库，而不是先确认外部资源是否足够。实际上，如果外部环境出现瓶颈，再多的 Oracle 调整都是没有帮助的。
在检查 Oracle 的外部环境时，有两个方面是需要注意的：
1 、当运行队列的数目超过服务器的 CPU 数量时，服务器的性能就会受到 CPU 的限制。补救的方法是为服务器增加额外的 CPU 或者关闭需要很多处理资源的组件，例如 Oracle Parallel Query 。
2 、内存分页。当内存分页时，内存容量已经不足，而内存页是与磁盘上的交换区进行交互的。补救的方法是增加更多的内存，减少 Oracle SGA 的大小，或者关闭 Oracle 的多线程服务器。
可以使用各种标准的服务器工具来得到服务器的统计数据，例如 vmstat,glance,top 和 sar 。 DBA 的目标是确保数据库服务器拥有足够的 CPU 和内存资源来处理 Oracle 的请求。
以下让我们来看一下 Oracle 的 row-resequencing 是如何能够极大地减少磁盘 I/O 的
Row-resequencing （行的重新排序）
就象我们上面提到的，有经验的 Oracle DBA 都知道 I/O 是响应时间的最大组成部分。其中磁盘 I/O 特别厉害，因为当 Oracle 由磁盘上的一个数据文件得到一个数据块时，读的进程就必须等待物理 I/O 操作完成。磁盘操作要比数据缓冲慢 10,000 倍。因此，如果可以令 I/O 最小化，或者减少由于磁盘上的文件竞争而带来的瓶颈，就可以大大地改善 Oracle 数据库的性能。
如果系统响应很慢，通过减少磁盘 I/O 就可以有一个很快的改善。如果在一个事务中通过按一定的范围搜索 primary-key 索引来访问表，那么重新以 CTAS 的方法组织表将是你减少 I/O 的首要策略。通过在物理上将行排序为和 primary-key 索引一样的顺序，就可以加快获得数据的速度。
就象磁盘的负载平衡一样，行的重新排序也是很简单的，而且也很快。通过与其它的 DBA 管理技巧一起使用，就可以在高 I/O 的系统中大大地减少响应的时间。
在高容量的在线事务处理环境中（ online transaction processing ， OLTP ），数据是由一个 primary 索引得到的，重新排序表格的行就可以令连续块的顺序和它们的 primary 索引一样，这样就可以在索引驱动的表格查询中，减少物理 I/O 并且改善响应时间。这个技巧仅在应用选择多行的时候有用，或者在使用索引范围搜索和应用发出多个查询来得到连续的 key 时有效。对于随机的唯一 primary-key （主键）的访问将不会由行重新排序中得到好处。
让我们看一下它是如何工作的。考虑以下的一个 SQL 的查询，它使用一个索引来得到 100 行：
selectsalaryfromemployeewherelast_name like 'B%';
这个查询将会使用 last_name_index ，搜索其中的每一行来得到目标行。这个查询将会至少使用 100 次物理磁盘的读取，因为 employee 的行存放在不同的数据块中。
不过，如果表中的行已经重新排序为和 last_name_index 的一样，同样的查询又会怎样处理呢？我们可以看到这个查询只需要三次的磁盘 I/O 就读完全部 100 个员工的资料（一次用作索引的读取，两次用作数据块的读取），减少了 97 次的块读取。
重新排序带来的性能改善的程度在于在你开始的时候行的乱序性如何，以及你需要由序列中访问多少行。至于一个表中的行与索引的排序键的匹配程度，可以查看数据字典中的 dba_indexes 和 dba_tables 视图得到。
在 dba_indexes 的视图中，查看 clustering_factor 列。如果 clustering_factor 的值和表中的块数目大致一样，那么你的表和索引的顺序是一样的。不过，如果 clustering_factor 的值接近表中的行数目，那就表明表格中的行和索引的顺序是不一样的。
行重新排序的作用是不可以小看的。在需要进行大范围的索引搜索的大表中，行重新排序可以令查询的性能提高三倍。
一旦你已经决定重新排序表中的行，你可以使用以下的工具之一来重新组织表格。
. 使用 Oracle 的 Create Table As Select (CTAS) 语法来拷贝表格
. Oracle9i 自带的表格重新组织工具
以下，我们来看以下 SQL 语句的调优
SQL 调优
Oracle 的 SQL 调优是一个复杂的主题，甚至是需要整本书来介绍 Oracle SQL 调优的细微差别。不过有一些基本的规则是每个 Oracle DBA 都需要跟从的，这些规则可以改善他们系统的性能。 SQL 调优的目标是简单的：
. 消除不必要的大表全表搜索：不必要的全表搜索导致大量不必要的 I/O ，从而拖慢整个数据库的性能。