分类：oracle

论断与IO相关的性能问题的方法有:
statspack或awr报告中在top 5等待事件中与IO相关的等待事件,对数据库做sql跟踪显示主要的限制是IO等待事件,操作系统工具显示了很高的利用率或存储数据文件的磁盘正在饱和的使用

诊断IO问题的步骤
在数据库性能调整中一个关键的活动就是响应时间的分析,找出在数据库中时间花在哪了.时间对于性能调整是一个最重要的属性.用户是通过他们运行业务所经历的时间来进行感知的.

oracle数据库的响应时间使用以下的计算公式:
Response Time = Service Time + Wait Time

‘Service Time’就是用统计信息中的’CPU used by this session’来计算’Wait Time’就是等待事件的总时间

性能调优访问就是使用象awr和statspakc一样的工具来评估各种组件对整个响应时间的影响且直接对消耗时间最大的组件进行调整.

确定真正意义的IO等待事件
许多工具包括awr和statspack列出了最有效的等待事件.直到oracle9ir2 statspack包含一个叫”top 5 wait events”部分.
当面对所罗列的等待事件有时间很容易首先处理这些等待事件相关的问题而忘记了它们在整个响应时间中的影响.

在这种情况下’service time’即cpu使用率比’wait time’更有效,很有可能调查等待事件不会对响应时间有影响.因此总是应该拿top等待事件中的各等待事件所用的时间来与’cpu used by this session’的值进行比较并直接对最消耗时间的事件进行调整.

从oracle9ir2开始,”top 5 wait events’部分被重命名为”top 5 timed events” “service time’即”cpu used by this session’称作’cpu time’这意味着现在很容易精确地测量等待事件在整个响应时间中的影响并且能正确的对其进行调整.

误解等待事件的影响
下面的两个例子当在调查数据库性能问题时最重要的是查看’wait time’和’servie time’

例子1:在oracle9ir2以前的statspack
下面是statspack报告中”top 5 wait events’信息两个快照之间的间隔是46分钟
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
——————————————– ———— ———— ——-
direct path read 4,232 10,827 52.01
db file scattered read 6,105 6,264 30.09
direct path write 1,992 3,268 15.70
control file parallel write 893 198 .95
db file parallel write 40 131 .63
————————————————————-
基于上面的信息我们可能会立即查看造成’direct path read’和’db file scattered read’等待事件并试图对它们进行调整
.但是这种做法没有考虑’service time’.

下面的’service time’信息来自同一个statspack报告:
Statistic Total per Second per Trans
——————————— —————- ———— ————
CPU used by this session 358,806 130.5 12,372.6

下面对这些数字进行一些简单的计算:
‘Wait Time’ = 10,827/ 0.5201 = 20,817 cs
‘Service Time’ = 358,806 cs
‘Response Time’ = 358,806 + 20,817 = 379,623 cs

所以计算后各个组件占所有响应时间的百分比为:
CPU time = 94.52%
direct path read = 2.85%
db file scattered read = 1.65%
direct path write = 0.86%
control file parallel write = 0.05%
db file parallel write = 0.03%

现在很明显IO相关的等待事件不是真正影响整个响应时间(所有的IO等待事件的时间只占整个响应时间的6%)的原因.后续的调整应该直接对服务时间组件即CPU消耗.

例子2:在oracle10gr2以后的awr报告
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
—————————— ———— ———– —— —— ———-
DB CPU 33,615 82.0
db file sequential read 3,101,013 7,359 2 18.0 User I/O
log file sync 472,958 484 1 1.2 Commit
read by other session 46,134 291 6 .7 User I/O
db file parallel read 91,982 257 3 .6 User I/O

在awr中非常容易看出cpu在整个响应时间中占了很大一部分,因为cpu组件已经包含在’top 5 timed foreground events’中
.在上面的信息中我们可以看到等待事件的总时间占整个响应时间不到20%因此后续的调整应该直接对服务时间组件即cpu消耗进行.

一般处理IO问题的方法
在使用statspack或awr分析数据库响应时间后确定性能是由IO相关的等待事件所造成的,那么对于IO问题可能有以下解决方法,有些方法不受限于特定的等待事件,下面将解释说明每一种方法的概念和基本原理.

通过调整sql来减少数据库的IO请求:
一个数据库没有用户sql它将生成极少或没有IO.所有的IO最终都是通过数据库直接或间接的执行sql语句所产生的.这意味着可以通过控制单个sql语句的IO生成量来限制IO请求.这可以通过调整sql语句的执行计划来减少IO操作.通常的情况是数据库中只有少许的sql语句没有使用最佳的执行计划生成了太多的不必要的物理IO影响了数据库的整个性能.从oracle10g开始,addm可能自动识别对性能影响最大的sql语句然后sql调整指导可对其进行自动调整来减少对IO的消耗

通过调整实例参数来减少数据库的IO请求:
1.使用内存缓存来限制IO
通过使用较大的内存缓存象buffer cache,log buffer,各种排序区来限制IO请求的数量.增加buffer cache到一个合适的大小让
数据库进程执行更多的缓存访问(逻辑IO)来代替物理磁盘的访问(物理IO).在内存中使用大的排序区,可能会减少排序操作不得不使用临时表空间的可能性尽让排序在内存中完成.

2.调整多块IO的大小
单个多块IO操作的大小可以通过实例参数来控制.当有大量IO操作要执行时多块IO执行的速度要比更多的小IO操作要快.例如,传输100M的数据执行每次传输1M数据的操作100次要比执行每次传输100KB数据的操作1000次或每次传输10KB数据的操作10000次要快.在这个限制达到后,不同的大小将不再重要:传输1GB的数据执行100次每次传输10MB(如果操作系统允许的最大IO大 小)与一次传输1GB的数据几乎有同样的效率,这是因为IO服务请求所花的时间主要包括两部分:
IO setup time:对于不同的IO大小所花的时间基本上是恒定的且对于小的IO大小它的值趋于总的服务时间
IO transfer time:随着IO的大小而增加且对于小的IO大小通常小于IO setup time
可能通过db_file_multiblock_read_count参数来调整多块IO的大小

在操作系统层优化IO
这涉及到IO能力的使用比如象异步IO或使用带有高级功能的直接IO(跨过操作系统文件缓存)的文件系统.另一个可能的做法是提高每次传输IO最大大小

通过使用oracle asm(自动存储管理)来平衡数据库的IO
在oracle10g中asm被引入.它是一个文件系统且卷管理器被内建在数据库内核中.它能以并行方式跨过所有可用的磁盘设备来自动 进行负载平衡来阻止热点的产生和最大化性能,即使是使用快速变化的数据模式.它能阻止碎片因为这里从来不会为了回收空间来重新放置数据,数据将是平衡且条带化在所有的磁盘.

使用条带化,raid,san或nas来平衡数据库IO
这种方法依赖于存储技术象striping,raid,存储局域网(SAN)和网络连接存储(NAS),当在存储硬件上还有可用的磁盘吞吐量时来自动跨多个可用的物理磁盘来自动平等数据库IO来避免磁盘竞争和IO瓶颈.

通过手动将数据文件跨不同文件系统,控制器和物理设备来存储来重新分配数据库IO
这个方法用于缺少高级存储技术的情况下,当仍有磁盘吞吐量时再次分配数据库IO不使用单个磁盘或控制器达到饱和状态.它很难做到准确无误因此与之前的方法相比很少使用.

最重要的是记住有一些IO将总是存在于大多数数据库中的.在上述方法都已经考虑之后如果性能仍不能满足你可能考虑:
通过移走旧的数据来减少当前数据库的数据量
使用更多或更快的硬件

数据文件IO相关的等待事件
‘df file sequential read’
这是一个最常见的IO相关的等待事件,在大多数情况下是单块读取索引块或通过索引来访问表数据块但也可看作是对数据文件头块的读取.在早期的oracle版本中也可能是从磁盘中的排序段执行多块读在缓冲区缓存中组成连顺的缓存.

如果这个等待事件占了等待时间中的一大部分那么有以下方法可以进行调整:
从statspack或awr报告中的”SQL ordered by Reads”或v$sql视图中找出物理读取的top sql语句,然后对它们进行调整以减少它们的IO请求
如果索引范围扫描被调用,如果索引是非选择性的那么可能与必须要访问的数据块相比会有更多的数据块被访问.

如果索引分布很分散,那么我们将不得不访问更多的数据块因为每一个数据块中的索引数据很少,在这种情况下重建索引让索引数据存放在少理数据块中可以提高性能.

如果被使用的索引有大量的集族因子,那么为了得到每一个索引块会有更多的数据块要求被访问,可以按特定索引列对数据进行排序并按排序的结果重新创建该表来减小集族因子.例如一个表有a,b,c,d四个列且创建一个索引(b,d),那么我们可以使用
CREATE TABLE new AS SELECT * FROM old ORDER BY b,d语句来重建该表.

使用分区让每一个sql语句使用分区修剪功能来减少要被访问的索引数据块和表数据块.

如果没有执行计划很差的特定sql语句执行不必要的物理IO操作的话那么可能出现了以下情况:
特定数据文件的IO由于存储这些数据文件的磁盘上有过度的活动造成了服务缓慢.在这种情况下可以查看statspack或awr报告中的”File I/O Statistics”部分或v$filestat视图来找到哪些热点磁盘并通过手动移到数据文件到其它的存储上或通过使用条带 化,raid和其它自动执行IO负载平衡的技术来分散IO

从oracle9.2开始可以从v$segment_statistics视图中使用新的段统计信息来找到是哪一个段(表或索引)执行了最多的物理读取.
在找出具体的段之后可以对索引,表进行重建或分区来减少IO请求,如果使用statspack来生成”segment statistics”报告需要修 改收集统计的级别为7.
如果没有使用次优执行计划的sql且从所有磁盘执行请求的时间相似IO分布均匀那么设置一个大缓冲区缓存可能有帮助:
在oracle8i中可以通过逐步增加db_block_buffers的值来检测缓冲区缓存的撞击率直到不能再提高缓冲区缓存的撞击率为止.

在oracle9i中我们可以使用缓冲区缓存指导功能来调整缓冲区缓存的大小

在oracle10g中使用自动共享内存管理(asmm)来让数据库自动根据最近的工作负载来设置最佳的缓冲区缓存的大小

对于热点段可以使用多个缓冲池,将哪些热点索引和表放置在保留缓冲池中.

最后你可以考虑减少最频繁访问段中的数据(通过将旧的不需要的数据从数据库中移出)或将这些访问频繁的段移动到新的快速的磁盘上来减少它们IO请求的时间

‘db file scattered read’
这也是一个常见的等待事件,当数据库从执行多块读从磁盘上将数据块读取到缓冲区缓存中不连续的缓存中.这样的读一次能够读取的数据块个数是由db_file_multiblock_read_count参数值所决定的.这样的情况通常是发生在全表扫描和快速完全索引扫描.

如果这个等待事件占了总等待时间中的一大部分那么有以下方法可以进行调整:
找出哪个sql语句执行了全表扫描或快速完全索引扫描并对它们进行调整来确保这些扫描是必需的且不会造成使用一个次优的执行计划.从oracle9i开始新的v$sql_plan视图能帮助找出这些语句.

对于全表扫描:
select sql_text from v$sqltext t, v$sql_plan p
where t.hash_value=p.hash_value and p.operation=’TABLE ACCESS’
and p.options=’FULL’
order by p.hash_value, t.piece;

对于快速完全索引扫描:
select sql_text from v$sqltext t, v$sql_plan p
where t.hash_value=p.hash_value and p.operation=’INDEX’
and p.options=’FULL SCAN’
order by p.hash_value, t.piece;

在oracle8i中可以通过查询v$session_event视图来找出执行多块读取这个等待事件且对它们进行sql跟踪,另外可以查看物理读 取的top sql语句来查看是否它们的执行计划中有没有包含全表扫描或快速完全索引扫描.

在这种情况下当最佳执行计划执行多块读时可以通过设置实例参数db_file_multiblock_read_count来调整多块读的IO大小.因此
db_block_size x db_file_multiblock_read_count=系统的最大IO大小

正如前面所说的,从oracle10gr2开始db_file_multiblock_read_count初始化参数现在是自动调整当这个参数没有被显式设置时 使用缺省值.这个缺省值与能有效执行的最大IO大小有关.这个参数值依赖于平台且对于大多数平台的最大IO大小是1MB.因为这个参数是以数据块为单位的,它能设置成一个等于最大IO大小的值(它的值可以是有效执行最大IO大小的值除以标准块大小)

当使用全表扫描和快速完全索引扫描读取数据块时会将这些数据块放在缓冲区缓存替换列表中的最近最少使用端,有时使用多个缓冲区会有帮助象将段放在保留池中.

当分区修剪能在查询中限制扫描段分区的子集时分区也能被用来减少扫描数据的数量.

最后你可以考虑减少最频繁访问段中的数据(通过将旧的不需要的数据从数据库中移出)或将这些访问频繁的段移动到新的快速的磁盘上来减少它们IO请求的时间

‘db file parallel read’
这个等待事件出现在当oracle以并行读取从多个数据文件中读取数据块到不连续的缓存池时.在恢复操作或当执行缓存预取作为一种优化手段来代替执行多次单块读取是会发生.

如果这个等待事件占了总等待时间中的一大部分,关这个等待事件的优化方法可参考’db file sequential read’事件的优化方法

direct path reads and writes
‘direct path read’
‘direct path write’
‘direct path read(lob)’
‘direct path write(lob)’
这些等待事件当在磁盘和进程pga内存之间执行特定类型的多块IO时会发生.因此跳过了缓冲区缓存.IO可以被同步和异步执行.

它们会在以下情况下出现:
排序IO:当内存排序区已经筋疲力尽且正在使用临时表空间来执行排序操作时.
并行执行(查询和DML)
预取操作(缓冲预取)
直接路径加载操作
LOB段的IO(它们不会被缓存在缓冲区缓存中)

由于这些等待事件的等待时间都被记录(它不检测试执行IO的时间),它们会出现在statspack报告中的”top 5 wait/timed events”中但不能用来评估真实的影响.

调整方法:
当支持异步IO时尽可能的使用异步IO
在oracle8i中最小的IO请求数是通过设置db_file_direct_io_count参数来设置的因此
db_block_size x db_file_direct_io_count=系统的最大IO大小.
税
在oracle8i中这个缺省值是64个数据块.

在oracle9i中,使用bytes为单位的_db_file_direct_io_count来替换.缺省值是1MB但如果系统的max_io_size较小的话会降低这个值.

调整内存排序区来最小化磁盘IO排序操作,在oracle9i及以后的版本中使用自动sql执行内存管理,在8i中要调整各种排序区的大小.

对于lob段,将它们作为操作系统文件存储在文件系统上,缓冲区缓存能提供一些内存缓存.

通过查询v$session_event来识别执行直接IO的会话或通过v$sesstat来识别统计信息:
‘physical reads direct’,’physical reads direct(lob)’,’physical writes direct’,’physical writes direct(lob)’
并调整这些sql语句.

通过使用v$filestat或statspack或awr报告中的”file io statistics”部分来识别存储数据文件的磁盘是否有瓶颈并将其移到其它磁盘上.

控制文件相关的IO等待事件
这些等待事件是在对控制文件的一个或所有副本执行IO时出现,控制文件的访问频率是由日志文件切换和检查点来控制的.因此它只能通过间接地调整这些活动才能受到影响.
‘control file parallel write’
这个等待事件发生在服务器进程正在更新所有控制文件副本时会出现.如果这个等待事件很严重,检查控制文件所有副本的IO路径(控制器,物理磁盘)的瓶颈.可能的解决方法:
减少控制文件的数量来最小化确保在同一时间不会丢失所有控制文件副本.
在你的平台支持异步IO的情况下使用异步IO
移动控制文件副本到很少达到饱和状态的存储中

‘control file sequential read’和’control file single write’
这些等待事件在对单个控制文件执行驶IO时可能会出现.如果这些等待事件很严重找出这些等待事件是出现在哪些控制文件副本上并查看它们的确良IO路径是否已经达到饱和.

下面的查询可以用来找出哪个控制文件正被访问.当出现这些等待事件时可以运行:
select P1 from V$SESSION where EVENT like ‘control file%’ and STATE=’WAITING’;
或
select P1 from V$SESSION_WAIT where EVENT like ‘control file%’ and STATE=’WAITING’;
可能的解决方法:
移动有问题的控制文件副本到很少达到饱和状态的存储中
在你的平台支持异步IO的情况下使用异步IO

重做日志相关的IO等待事件
这里有许多的等待事件发生在重做日志活动期间且它们大多数都是与IO相关的.它们中最重要的两个是’log file sync’和
‘log file parallel write’.oracle前台进程等待’log file sync’而lgwr进行等待’log file parallel write’.

虽然在”top 5 wait/timed events”中经常看到’log file sync’等待事件,为了理解它们首先来看’log file parallel write’:

‘log file parallel write’
当lgwr后台进程从内存日志缓存中复制重做条目到磁盘上的当前重做日志组的成员日志文件中时会等待这个事件.如果支持异步IO的话,异步IO被用来保证以并行方式进写操作否则将会按顺序来对重做日志组中的成员日志文件进行写操作.

然而在这个等待完成之前lgwr进程不得不等到所有成员日志文件的所有IO操作完成.因此因为这个原因IO子系统的写入成员日志文件的速度决定了这个等待的时间长短.

为了减少这个等待事件的等待时间一种文学就是通过数据库来减少生成的重做日志的数量
利用unrecoverable/nologging选项
在保证在同一时间不会丢失所有重做日志成员的前提下减少重做日志组的成员
不要让表空间处于备份模式下超过其必要的时间
使用最小级别的supplemental logging来完成你所要请求完成的功能.例如logminer,logical standby或streams

另一种方法就是调整IO本身:
在存储上放置重做日志组成员因此对于每个成员并行写不会产生竞争
对于重做日志文件不要使用raid-5
对于重做日志文件使用裸设备
对于重做日志文件使用快速磁盘
如果启用归档请单独设置重做存储空间因此这样写当前重做日志组的成员时不会与归档进程读取当前组的成员产生竞争.

‘log file sync’
这个等待事件发生在oracle前台进程中当他们发出一个commit或rollback操作时正等待这个等待事件的应该完成部分,因为这个 等待事件包含了lgwr进程对于这个会话事务从重做日志缓存中复制重做条目到磁盘.所以前台进程正等待’log file sync’而lgwr 进程在这个时间正等待’log file parallel write’

理解是什么延迟’log file sync’是关键是比较’log file sync’和’log file parallel write’的平均等待时间:
如果它们平均等待时间几乎相同,那么重做日志IO是造成这个等待的主要原因
如果’log file parallel wirte’的平均等待时间非常小,那么造成这个等待的主要原因是当发出commit或rollback命令时重做日志机制的其它部分(与IO不相关),有时在重做日志闩锁上存在着闩锁竞争,可以通过’latch free’或’lgwr wait for redo copy’ 等待事件来证实这一点.

‘log file sequential read’和’log file single write’
这两个等待事件是与IO相关的如果在重做日志上存在着IO竞争那么它们会与’log file parallel write’一起出现

‘log file switch(checkpoint incomplete)’ 这个等待事件当检查点活动不能够迅速发生时会出现

‘log switch/archive’ and ‘log file switch(archiving needed)’
这些等待事件当归档启用时归档不能快速完成时会出现
调整这些等待事件的方法与前面所描述的相似.

awr报告是一种极其有效的诊断工具来确定潜在的导致数据库性能问题的原因.

通常当性能问题被检查到时你可以在出现性能问题期间收集一个awr报告.收集awr报告的期间最好不要超过一个小时否则有可能会丢失一些细节.

当数据库性能在可接受期间也可以收集awr报告来作为基线当出现数据库性能问题是可以用来进行比较.要确保性能基线收集的时间与出现性能问题时收集awr的时间相同这样才有可比性.

当我们正在查找性能问题是我们的主要关注点在数据库正在等待什么.当进程等待时它们会被阻止做任何操作.

top等待事件提供了对于问题来说需要我们关注的信息而不用浪费时间去调查其它的原因.

top 5时间事件
注意top等待部分是整个awr报告中的最重要的一个部分它可以用来量化性能和进行诊断比较

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
—————————— ———— ———– —— —— ———-
db file scattered read 10,152,564 81,327 8 29.6 User I/O
db file sequential read 10,327,231 75,878 7 27.6 User I/O
CPU time 56,207 20.5
read by other session 4,397,330 33,455 8 12.2 User I/O
PX Deq Credit: send blkd 31,398 26,576 846 9.7 Other
————————————————————-

top 5等待部分报告了一系列有用的相关等待事件.它记录了在遇到性能期间所发生的等待次数和等待总的时间以及每个事件的平均等待时间.

在上面的这个例子中,几乎60%的等待时间是与IO相关的读取操作
事件’db file scattered read’是典型用于全表扫描和索引快速完全扫描时执行多块读相关的等待事件
事件’db file sequential read’是用于块读和通常用于不能执行多块读时相关的等待事件(例如索引读取)

另外的20%的等待时间划CPU time.高cpu利用率通常是低性能sql(执行昂贵的IO操作)的一个标识符(或者sql语句有使用更少资源的潜能)

基于以上的信息我们将会调查这些等待是否指示了性能问题.如果是解决这些问题,如果不是继续查看下一部分信息是否是造成性能问题的原因

有两个原因让IO相关的等待事件成为top等待事件
1.数据库正在执行大量的读取操作
2.单个读取操作很慢

top5等待事件有以下帮助:
1.数据库正在执行大量的读取操作?
这部分信息显示了在这个awr报告期间这些等待事件中每一个执行了1000万次读取,这个读取次数是否是大量读取操作取决于awr报告的持续时间是1小时还是1分钟.检查报告期间来评估这个问题.如果读取操作过度那么为什么数据库还会执行大量的读取操作?数据库只读取数据是因为执行的sql语句指示它进行读取操作为了调整可以查看sql statistics部分的信息.

2.是不是单个读取操作慢?
这部分显示了两个等待<=8ms的IO相关等待事件,这个是快是慢取于硬件底层的IO子系统,但通常低于20ms是可以接受

如果IO慢,那么可以从’Tablespace IO stats’部分得到以下信息:

Tablespace IO Stats DB/Inst: VMWREP/VMWREP Snaps: 1-15
-> ordered by IOs (Reads + Writes) desc

Tablespace
——————————
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
————– ——- —— ——- ———— ——– ———- ——
TS_TX_DATA
14,246,367 283 7.6 4.6 145,263,880 2,883 3,844,161 8.3
USER
204,834 4 10.7 1.0 17,849,021 354 15,249 9.8
UNDOTS1
19,725 0 3.0 1.0 10,064,086 200 1,964 4.9
AE_TS
4,287,567 85 5.4 6.7 932 0 465,793 3.7
TEMP
2,022,883 40 0.0 5.8 878,049 17 0 0.0
UNDOTS3
1,310,493 26 4.6 1.0 941,675 19 43 0.0
TS_TX_IDX
1,884,478 37 7.3 1.0 23,695 0 73,703 8.3
SYSAUX
346,094 7 5.6 3.9 112,744 2 0 0.0
SYSTEM
101,771 2 7.9 3.5 25,098 0 653 2.7

特别注意的是查看RD(ms)的值,如果每次读取时间的值高于20ms,那么你可以从操作系统层开始调查IO屏颈.注意:你应该忽略相关的空闲的表空间/文件当你发现RD(ms)的值较高时可能是因为磁盘的spinup造成的这与性能无关.如果你读取1000万次读取被认为是IO慢这不太可能它可能是表空间/文件只有10个读取操作造成的问题

虽然高等待’db file scattered read’和’db file sequential read’事件可能与IO相关,但是实际上发现大部分这些等待事件基于数据库正在运行的sql语句来说是正常的.实际上,在一个高度优化的数据中,希望它们出现在top等待事件中,因此这意味着数据库没有性能问题.

它们被用来评估是否高等待说明了某些sql语句没有使用最佳的访问路径.如果有大量的’db file scattered read’等待事件,那么sql可能没有使用最佳的访问路径因此使用了全表扫描而不是索引扫描(或者可能是丢失索引或者没有最佳的索引可用).此外,大量的’db file sequential read’等待事件可以说明了sql语句正在使用非选择性索引且因此要读取更多的索引块或者使用了错误的索引.因此这些等待事件可能说明sql语句执行计划性能较低.

不管怎样,都应该从awr报告中检查top资源消耗的情况来判断它们是否过度或是否可以改进

注意上面有20%的等待时间是cpu时间.在查看sql统计时也应该被检查.后面的检查是依据tops等待进行的.例如在上面的top5等待事件中前功尽弃3个是指示有性能不佳的sql语句应该进行调查

同样的如果你没有看到latch等待那么latch就不是造成性能问题的原因所以就不需要继续调查latch等待.

一般来说,如果数据库慢那么top5等待事件中包含”cpu”和”db file sequential read”和”db file scattered read”那么这说明将要注意查看top sql(通过逻辑和物理读取分类的)部分和叫做sql调整指导(或手动调整它们)来确保
它们有效的运行.

SQL Statistics
SQL ordered by Elapsed Time
SQL ordered by CPU Time
SQL ordered by User I/O Wait Time
SQL ordered by Gets
SQL ordered by Reads
SQL ordered by Physical Reads(UnOptimized)
SQL ordered by Executions
SQL ordered by Parse Calls
SQL ordered by Sharable Memory
SQL ordered by Version Count
SQL ordered by Cluster Wait Time
Complete List of SQL Text
上面不同的sql统计信息应该根据top5等待事件中的不同等待事件进行查看.例如在我们的例子中,我们看到有’db file scattered read’,’db file sequential read’和cpu.对于这些我们要重点关注SQL ordered by CPU Time,SQL ordered by Gets和SQL ordered by Reads部分.

通常查看’SQL ordered by gets’部分指示sql语句有较高的缓存获取通常需要进行合适的调优:

SQL ordered by Gets
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total Buffer Gets: 4,745,943,815
-> Captured SQL account for 122.2% of Total

Gets CPU Elapsed
Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id
————– ———— ———— —— ——– ——— ————-
1,228,753,877 168 7,314,011.2 25.9 8022.46 8404.73 5t1y1nvmwp2
SELECT ADDRESSID”,CURRENT$.”ADDRESSTYPEID”,CURRENT$URRENT$.”ADDRESS3″,
CURRENT$.”CITY”,CURRENT$.”ZIP”,CURRENT$.”STATE”,CURRENT$.”PHONECOUNTRYCODE”,
CURRENT$.”PHONENUMBER”,CURRENT$.”PHONEEXTENSION”,CURRENT$.”FAXCOU

1,039,875,759 62,959,363 16.5 21.9 5320.27 5618.96 grr4mg7ms81
Module: DBMS_SCHEDULER
INSERT INTO “ADDRESS_RDONLY” (“ADDRESSID”,”ADDRESSTYPEID”,”CUSTOMERID”,”
ADDRESS1″,”ADDRESS2″,”ADDRESS3″,”CITY”,”ZIP”,”STATE”,”PHONECOUNTRYCODE”,”PHONENU

854,035,223 168 5,083,543.0 18.0 5713.50 7458.95 4at7cbx8hnz
SELECT “CUSTOMERID”,CURRENT$.”ISACTIVE”,CURRENT$.”FIRSTNAME”,CURRENT$.”LASTNAME”,CU<
RRENT$.”ORGANIZATION”,CURRENT$.”DATEREGISTERED”,CURRENT$.”CUSTOMERSTATUSID”,CURR
ENT$.”LASTMODIFIEDDATE”,CURRENT$.”SOURCE”,CURRENT$.”EMPLOYEEDEPT”,CURRENT$.

调整可以手动进行也可以通过sql调整指导来进行

对上面的信息进行分析:
Total Buffer Gets: 4,745,943,815
我们假设这是一个时间间隔为1小时的awr报告,这是对于buffer get来说是一个重要的数字因此这证实了为了确保它们正使用最佳的访问路么它们是值得调查的top sql语句.

单个 buffer gets
对于单个语句的buffer gets是非常高得最小的也有854,035,223次.这三个语句实际上指出了有大量buffer gets的两大原因:
过度的buffer gets/exectuion sql_id ‘5t1y1nvmwp2’和’4at7cbx8hnz’仅仅只执行了168次,但每一次执行读取超过500万的buffer.这个语句是要被进行调优的主要对象因为buffer在太高了.

过度的执行
另一方面sql_id ‘grr4mg7ms81’每次执行只读取16个buffer.调整这个语句不能有效的减少buffer 读.然而这个问题可能是由这个语句的执行次数造成的—执行62,959,363次.改变这个语句的调用方式–它很可能在一个循环中一次获取一行记录,可以修改成一次执行获取多条记录那么这样就会有效的减少buffer读取.

记住这些数字对于繁忙的工作环境可能是正常的.可通通过使用这个时间的awr报告与性能基线awr报告进行比较,你可以看看这些语句在数据库性能良好的情况下是不是也执行了这么多的buffer读取.如果也是那么就不用关注这个问题了可以忽略它们(因为改进这些语句可以提高一些性能)

其它的sql统计信息部分
在sql统计信息部分有不同的报告部分用于指示不同的原因,如果你没有特定问题那么查看这部分信息的作用有限.
Waits for ‘Cursor: mutex/pin’
如果在这有mutex等待象”Cursor:pin S wait on X’ or ‘Cursor:mutex X’,这些象征着解析问题.
最基本的就是查看有高解析次数’SQL ordered by Parse Cllas’ 或高版本次数的sql语句 ‘SQL ordered by Version Count’
这是最有可能造成问题的原因.

Load Profile 负载概要
根据等待事件,负载概要部分也提供有用的后台信息或与问题相关的特定信息

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
————— —————
Redo size: 4,585,414.80 3,165,883.14
Logical reads: 94,185.63 65,028.07
Block changes: 40,028.57 27,636.71
Physical reads: 2,206.12 1,523.16
Physical writes: 3,939.97 2,720.25
User calls: 50.08 34.58
Parses: 26.96 18.61
Hard parses: 1.49 1.03
Sorts: 18.36 12.68
Logons: 0.13 0.09
Executes: 4,925.89 3,400.96
Transactions: 1.45

% Blocks changed per Read: 42.50 Recursive Call %: 99.19
Rollback per transaction %: 59.69 Rows per Sort: 1922.64

在这个例子中,等待事件部分显示的问题是sql语句执行的问题所以负载概要也能检查出相关的信息.

如果你正在为了通常的调整在看awr报告,你可以查看负载部分来显示相关的有高物理写的高重做活动.在上面的信息中写与读的负载比值高达到了43.50%.

此外这里硬解析与软解析比较低.如果在top等待事件中有’library cache:mutex X’,那么整个解析率的统计信息与这些等待事件息息相关

与性能基线awr报告进行比较将提供最好的信息,例如,可以通过比较重做的大小,用户的调用和解析来看负载的改变

Instance Efficiency实例的效率
实例的效率统计信息对于通常的调整来定位特定的问题是很有用的(除非等待事件已经指示出问题的原因)

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.91 Redo NoWait %: 100.00
Buffer Hit %: 98.14 In-memory Sort %: 99.98
Library Hit %: 99.91 Soft Parse %: 94.48
Execute to Parse %: 99.45 Latch Hit %: 99.97
Parse CPU to Parse Elapsd %: 71.23 % Non-Parse CPU: 99.00

在上面的这个例子中这部分最重要的统计信息是”% Non-Parse CPU”,因为这指示了在top等待事件中几乎所有的CPU时间
都花在了执行操作上而不是解析操作,这意味着调整sql可能提高性能.

如果我们正在调整,那么94.48%的软解析率显示了硬解析率是较小的.这么高的解析率说明很好的使用了共享游标.通常我们希望这个统计值接近100%,但记住有一小部分的百分比不是依赖于应用程序的.例如在一个数据仓库环境中,硬解析可能由于使用了物化视图或直方图变得比较高.所以在出现性能问题时与性能基线awr报告进行比较是很重要的.

Latch Activity 闩锁活动
在这个例子中我们不能看到有效的闩锁等待可以忽略此部分信息.然而,如果闩锁等待很严重那么我们将基于

Latch Sleep Breakdown来查看闩锁等待相关的信息
Latch Sleep Breakdown

* ordered by misses desc

Latch Name
—————————————-
Get Requests Misses Sleeps Spin Gets Sleep1 Sleep2 Sleep3
————– ———– ———– ———- ——– ——– ——–
cache buffers chains
2,881,936,948 3,070,271 41,336 3,031,456 0 0 0
row cache objects
941,375,571 1,215,395 852 1,214,606 0 0 0
object queue header operation
763,607,977 949,376 30,484 919,782 0 0 0
cache buffers lru chain
376,874,990 705,162 3,192 702,090 0 0 0

这里的顶级闩锁是cache buffers chains,cache buffers chains闩锁是用来保护从磁盘读取到缓冲区缓存中的数据.当看到数据正在被读取时这是很正常的闩锁.当这个出现压力时闩锁sleeps数据就会趋向于这些查询请求的等待次数这些竞争可能是由于低效的sql读取相同的buffer造成的.

在上面的例子中虽然buffer gets中的get请求的次数是2,881,936,948但sleeps次数是41,336是较低的.sleeps与misses的平均比率(avg slps/miss)是较低的.原因是服务器能够处理这样规模的数据因此这里对于cache buffers chains闩锁来说没有什么竞争.

cpu等待事件
仅仅因为cpu等待出现在awr报告中的top等待事件中不能说明什么问题.然而如果性能慢且cpu使用率高那么可以调查cpu等待事件,首先可以检查awr报告中的消耗cpu较多的sql语句
SQL ordered by CPU Time
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total is the CPU Time divided into the Total CPU Time times 100
-> Total CPU Time (s): 56,207
-> Captured SQL account for 114.6% of Total

CPU Elapsed CPU per % Total
Time (s) Time (s) Executions Exec (s) % Total DB Time SQL Id
———- ———- ———— ———– ——- ——- ————-
20,349 24,884 168 121.12 36.2 9.1 7bbhgqykv3cm9
Module: DBMS_SCHEDULER
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :myda
te; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname
VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start
TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME

总的cpu时间:56,207,大概为15分钟.这个信息是否有效依据报告的持续的时间周期.消耗cpu的顶级sql使用了20,349秒大约5分钟占整个数据库时间的9.1%.执行了168次.

什么是死锁
当一个会话A想要获得另一个会话B所持有的资源,但是会话B也想要获得会话A所持有的资源时就会出现死锁.

下面将演示一个死锁的例子:

jy@JINGYONG> insert into test_jy values(1,'First');

已创建 1 行。

jy@JINGYONG> insert into test_jy values(2,'Second');

已创建 1 行。

jy@JINGYONG> commit;

提交完成。

jy@JINGYONG> select rowid,num,txt from test_jy;

ROWID                     NUM TXT
------------------ ---------- ----------
AAASNsAAEAAAAIlAAA          1 First
AAASNsAAEAAAAIlAAB          2 Second

session#1:

SQL> update test_jy set txt='session1' where num=1;

1 row updated.

session#2:
jy@JINGYONG> update test_jy set txt='session2' where num=2;

已更新 1 行。

jy@JINGYONG> update test_jy set txt='session2' where num=1;

现在session#2正等待session#1所持有的TX锁

session#1:

SQL> update test_jy set txt='session1' where num=2;

现在session#1正等待这一行的TX锁,这个锁被session#2所持有,然而session#2也正等待session#1这就形成了死锁,当出现死锁时一个会话会抛出一个ORA-00060错误.
session#2:

       *
第 1 行出现错误:
ORA-00060: 等待资源时检测到死锁

这时session#1仍然被锁直接到session#2提交或回滚出错的ORA-00060的语句它只会回滚当前的语句而不是整个事务.

诊断信息由ora-00060提供
ora-00060错误通常会将错误信息写入alert.log文件并同时创建一个跟踪文件.跟踪文件会根据创建跟踪文件的进程的类型写入user_dump_dest或background_dump_dest目录中.

跟踪文件包含死锁图表信息和其它信息.

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00030004-000002c7        22      26     X             24      29           X
TX-00040014-0000022b        24      29     X             22      26           X
 
session 26: DID 0001-0016-00000073	session 29: DID 0001-0018-000000BE 
session 29: DID 0001-0018-000000BE	session 26: DID 0001-0016-00000073 
 
Rows waited on:
  Session 26: obj - rowid = 0001236C - AAASNsAAEAAAAIlAAA
  (dictionary objn - 74604, file - 4, block - 549, slot - 0)
  Session 29: obj - rowid = 0001236C - AAASNsAAEAAAAIlAAB
  (dictionary objn - 74604, file - 4, block - 549, slot - 1)
 
----- Information for the OTHER waiting sessions -----
Session 29:
  sid: 29 ser: 94 audsid: 410112 user: 91/JY flags: 0x45
  pid: 24 O/S info: user: oracle, term: UNKNOWN, ospid: 3753
    image: oracle@jingyong (TNS V1-V3)
  client details:
    O/S info: user: oracle, term: pts/1, ospid: 3746
    machine: jingyong program: sqlplus@jingyong (TNS V1-V3)
    application name: SQL*Plus, hash value=3669949024
  current SQL:
  update test_jy set txt=:"SYS_B_0" where num=:"SYS_B_1"
 
----- End of information for the OTHER waiting sessions -----
 
Information for THIS session:
 
----- Current SQL Statement for this session (sql_id=b8gxacbadupu7) -----
update test_jy set txt=:"SYS_B_0" where num=:"SYS_B_1"
===================================================
PROCESS STATE
-------------
....

第一部分:Deadlock graph

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00030004-000002c7        22      26     X             24      29           X
TX-00040014-0000022b        24      29     X             22      26           X
 
session 26: DID 0001-0016-00000073	session 29: DID 0001-0018-000000BE 
session 29: DID 0001-0018-000000BE	session 26: DID 0001-0016-00000073 

这上面的信息显示了哪个进程持有的锁和哪个进程正等待的的锁资源的情况.对于每一个资源都有两部分信息与相关的进程相关联
Blockers(s)
Waiters(s)
在Deadlock graph中的信息
Resource Name:被持有或被等待的锁名称
Resource Name由三部分组成:Lock Type_ID1_ID2,ID1和ID2依赖于锁类型会有所不同
TX-00030004-000002c7
Lock Type:TX
ID1(00030004)和ID2(000002c7)指示回滚段和事务的事务表条目.

process 锁/等待会话的v$process.pid
session 锁/等待会话的v$session.sid
holds 持有的锁模式
waits 等待的锁模式

因此
sid 26(process 22)在排他模式下持有TX-00030004-000002c7锁并在排他模式下等待锁TX-00040014-0000022b

sid 29(process 24)在排他模式下持有TX-00040014-0000022b锁并在排他模式下等待TX-00030004-000002c7锁

最重要的是要注意对于每种资源的锁类型,模式的持有者和模式的请求指示了造成死锁的原因

第二部分:Rows waited on

Rows waited on:
  Session 26: obj - rowid = 0001236C - AAASNsAAEAAAAIlAAA
  (dictionary objn - 74604, file - 4, block - 549, slot - 0)
  Session 29: obj - rowid = 0001236C - AAASNsAAEAAAAIlAAB
  (dictionary objn - 74604, file - 4, block - 549, slot - 1)

如果死锁是由于以不同的顺序来获得行级锁造成的那么每个会话都正等待锁定自己的所持有的行源.如果请求的
TX模式 X等待那么’Rows waited on’可能是很有用的信息.而对于其它类型的锁’Rows waited on’通常会显示为”no row”
在上面的例子中
sid 26 was waiting for rowid ‘AAASNsAAEAAAAIlAAA’ of object 74604
sid 29 was waiting for rowid ‘AAASNsAAEAAAAIlAAB’ of object 74604
它们可能来检查实行的行记录:

jy@JINGYONG> select owner,object_name,object_type from dba_objects where object_
id=74604;

OWNER                          OBJECT_NAME                     OBJECT_TYPE
------------------------------ -----------------------------   --------------
JY                             TEST_JY                          TABLE




jy@JINGYONG> select * from test_jy where rowid='AAASNsAAEAAAAIlAAA';

       NUM TXT
---------- ----------
         1 First

第三部分:Information for the OTHER waiting sessions

----- Information for the OTHER waiting sessions -----
Session 29:
  sid: 29 ser: 94 audsid: 410112 user: 91/JY flags: 0x45
  pid: 24 O/S info: user: oracle, term: UNKNOWN, ospid: 3753
    image: oracle@jingyong (TNS V1-V3)
  client details:
    O/S info: user: oracle, term: pts/1, ospid: 3746
    machine: jingyong program: sqlplus@jingyong (TNS V1-V3)
    application name: SQL*Plus, hash value=3669949024
  current SQL:
  update test_jy set txt='session1' where num=2;
 
----- End of information for the OTHER waiting sessions -----

这一部分显示了参与死锁的其它会话的信息,这些信息包括:
会话信息
客户端信息
当前的sql语句
update test_jy set txt=’session1′ where num=2;

第四部分:Information for THIS session

Information for THIS session:
 
----- Current SQL Statement for this session (sql_id=b8gxacbadupu7) -----
update test_jy set txt='session2' where num=1

显示了这个会话造成ora-00060错误的语句

避免死锁
上面死锁发生是因为程序没有限制行被更新的顺序引起的.程序可以避免行级死锁通过强制行更新的顺序例如使用下面的限制就不会发生死锁

Session #1:          update test_jy set txt='session1' where num=1;


Session #2:          update test_jy set txt='session2' where num=1;
                           > Session #2 is now waiting for the 
                             TX lock held by Session #1

Session #1:          update test_jy set txt='session1' where num=2;
                           > Succeeds as no-one is locking this row
                     commit;
                           > Session #2 is released as it is no 
                             longer waiting for this TX

Session #2:          update test_jy set txt='session2' where num=2;
                     commit;

限制行被更新的顺序可以保证不会发生死锁.上面只是一个简单的产生死锁的情况.死锁不一定要是相同表之间的行,也可能是不同表中的行.因此最重要的是限制表更新的顺序和表中行被更新的顺序.

不同锁类型与模式
最常见的死锁类型是TX和TM锁.它们可能出现许多持有/请求模式.
锁模式 模式请求 可能的原因
TX X(mode 6) 程序行级冲突
通过重新编写程序确保行以特定的顺序被锁定
TX S(mode 4)
TM SSX(mode 5) 这通常与存在外键约束但在子表中没有在外键列上创建索引有关
或
S(mode 4)

TM锁
TM锁中的ID1指示了哪个对象正被锁定.这使得当TM锁发生时隔离与死锁相关的对象是非常容易的
TM锁的格式是TM-0001236C-00000000其中0001236C是对象编号的十六进制表示.
1.转换0001236c为十进制
0001236c的十进制是74604
2.定位对象

jy@JINGYONG> select owner,object_name,object_type from dba_objects where object_
id=74604;

OWNER                          OBJECT_NAME                     OBJECT_TYPE
------------------------------ -----------------------------   --------------
JY                             TEST_JY                          TABLE

怎么样获得其它的信息
可以通过在init.ora文件中设置
event=”60 trace name errorstack level 3;name systemstate level 266″
或者通过alter system来设置这个事件
ALTER SYSTEM SET events’60 trace name errorstack level 3;name systemstate level 266′;
注意这个事件会生成很大的跟踪文件你要确保max_dump_file_size的值有足够大来生成跟踪文件

什么是SQL* Net message to client 和SQL * Net more data to client等待事件?
SQL * Net message to client等待事件发生在当一个服务器进程已经发送数据或消息到客户端并正等待回复的时候.这个等待时间是等待从TCP(Transparent Network Substrate)等待响应的时间.这个等待事件通常被认为是一个空闲等待事件,它被看作是服务器进程正在等待其它的回复.在性能调整中如果个别的等待时间很高那么在服务器进行调整的可能性不大而是在其它方面进行调整,如果总的等待时间很高但个别的等待时间较小那么等待可能是由于收集数据所引起的

对于SQL * Net more data to client等待事件,oracle使用SDU(session data unit)会话数据单元将SDU缓存写入到TCP套接字缓存中.如果数据比会话数据单元的初始大小大那么数据需要被多次的发送.如果有大量的数据被发送然后在每批数据发送后这个会话将会等待’SQL * Net more data to client’等待事件.

oracle net允许通过参数SDU(会话数据单元)和TDU(传输数据单元)来控制数据包的大小.它们分别控制’Session’和’Transport’层的缓存大小.TDU在数在oracle net v8.0中已经被废弃.

数据包大小
SDU是会话数据单元它控制着发送和收接数据的大小.SDU值的范围从512到31767字节缺省大小是2048bytes(这个值依赖于数据库的版本).为了最小化oracle net 数据包头的开销和消息碎片.设置SDU的大小作为一个多重的MSS(网络协议被使用的最大段大小).TDU是最大传输单元(MTU)

计算MSS:
MSS=MTU-TCP header size-IP header size

MTU(or TDU)-1500 bytes for Ethernet
TCP-20 bytes
IP-20 bytes
对于以太网的TCP/IP协议的MSS这里还有1460bytes,传输网络底层(TNS)头是额外的30bytes.所以能被发送的数据大小是1430 bytes.国灰TNS头被包含在TCP数据包中,所以在SDU中包含了TNS头的大小.对于实例来说,如果你有5720 bytes的数据要发送,它将分成四个TCP包(5720/1430=4)那么这将有四个TNS包要发送.对于每个包的TNS头要增加30 bytes(1430+30=1460),增加TCP/IP头的大小40 bytes,你将得到四个完全以太网包的发送大小(1460+40=1500).为了得到TCP/IP的最佳效果,应该要配置TCP发送和接收的缓存大小.

TDU是传输数据单元它控制着传输网络层发送和读取数据的大小.TDU缺省的大小是32767 bytes在oracle v8.0和以后的版本中不用配置.TDU值的范围从0到32767.如果不设置TDU那么它将使用缺省值.例如TDU的值如果为1,这将造成在网络层读写数据只有1 bytes.

在SQL *Plus中的arraysize参数决定每一次网络传输获取多少行记录.

对于一个SDU大小大于2048的连接,客户端和服务端必需指定一个较大的SDU值.数据库将选择两者中最低的哪一个.

SDU配置
为了配置SDU,要确保SDU值出现在所有相关的地方
1.客户端的TNSNAMES.ora:这个参数必需出现在DESCRIPTION子句中:
TEST =
(DESCRIPTION =
(SDU=8192)
(TDU=8192) < – 8.0 TDU position
(ADDRESS =(PROTOCOL = TCP)(HOST = jy)(PORT = 1521))
(CONNECT_DATA = (SID = V920)))

LISTENER.ora:这个参数必需出现在SID_DESC子句中:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU = 8192) (TDU = 8192) (SID_NAME = V920)
(ORACLE_HOME = /oracle/product/9.2.0)))

2.从oracle的9.0.1.5,9.2.04和10.2.0.1开始这个缺省的SDU大小对于连接改成使用动态注册了.
在SQLNET.ora中
DEFAULT_SDU_SIZE = 8192
上面的参数,SDU可以在客户端的sqlnet.ora文件和服务端的sqlnet.ora文件中进行设置而不用连接描述符

对于共享服务器的配置
如果使用共享服务器,在DISPATCHERS参数中设置SDU的大小:
DISPATCHERS=”(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP))(SDU=8192))”
对于oracle8使用MTS_DISPATCHERS参数
为了确保SDU的大小与客户端配置的大小相匹配,服务端将选择较小的一个.有些客户端的SDU必需要小于服务端的SDU值

怎样诊断SQL* Net message to client 和SQL * Net more data to client等待事件
诊断SQL* Net message to client 和SQL * Net more data to client等待事件最好的方法就是运行10046跟踪.

sys@JINGYONG> oradebug setmypid
已处理的语句
sys@JINGYONG> alter session set events '10046 trace name context forever,level 1
2';

会话已更改。

sys@JINGYONG> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM    DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ----------    ----------
      7369 SMITH      CLERK           7902 17-12月-80            800               20
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300    30
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500    30
      7566 JONES      MANAGER         7839 02-4月 -81           2975               20
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400    30
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850               30
      7782 CLARK      MANAGER         7839 09-6月 -81           2450               10
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000               20
      7839 KING       PRESIDENT            17-11月-81           5000               10
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0    30
      7876 ADAMS      CLERK           7788 23-5月 -87           1100               20
      7900 JAMES      CLERK           7698 03-12月-81            950               30
      7902 FORD       ANALYST         7566 03-12月-81           3000               20
      7934 MILLER     CLERK           7782 23-1月 -82           1300               10

已选择14行。

sys@JINGYONG> alter session set events '10046 trace name context off';

会话已更改。

sys@JINGYONG> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2745.trc

使用tkprof对跟踪文件格式化后可以得到以下内容:

select * 
from
 scott.emp

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.04          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.02          6          8          0          14
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.05       0.07          6          8          0          14

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
     14  TABLE ACCESS FULL EMP (cr=8 pr=6 pw=0 time=94 us cost=3 size=532 card=14)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  Disk file operations I/O                        1        0.00          0.00
  db file sequential read                         1        0.01          0.01
  db file scattered read                          1        0.00          0.00
  SQL*Net message from client                     2        0.00          0.01
********************************************************************************

我们可以看到单个SQL*Net message to client等待事件通常是非常短的(在上面的例子总的等待小于1毫秒).等待时间被记录为0毫秒,这个等待事件不会造成性能问题.

如果当发现这个等待事件的等待时间不同寻常的高.例如在statspack或awr报告中出现在top等待事件中,那么可以通过跟踪程序或sql来进行调整

潜在的几种解决方法
1.SDU大小
记住SQL* Net message to client等待事件通常不是一个网络问题,它基于TCP包的吞吐量.第一阶段发送SDU缓存的内容将其写入TCP缓存中,第二阶段就是等待SQL* Net message to client等待事件,这个等待与下面的原因有关:
orace sdu大小
返回给客户端的数据大小
一种解决方法增加SDU的大小,增加大小的方法上面提到过

2.数组大小
如果程序正在处理大量数据库,可以考虑在程序中增加数组的大小.如果使用较小的数组来获取数据那么查询将会执行多批次的调用,它们的每一次调用都会等待SQL* Net message to client等待事件.使用较小的数组来处理大量的数据SQL* Net message to client等待事件会大量增加.

如果从sqlplus中运行查询,在sqlplus中可以使用”set”命令来增加数组的大小
set arrayzie 1000

从10046跟踪文件中可以从fetch行看到获取的缓存大小或数组大小
FETCH #6:c=1000,e=793,p=0,cr=1,cu=0,mis=0,r=13,dep=0,og=1,plh=3956160932,tim=1381994001395851
上面的r=13指示数组大小是13,13可能太小了所以如果SQL* Net message to client等待事件时间长的话就可考虑增加
数组的大小

3.TCP
调整TCP连接确保TCP配置正确

oracle查询语句执行计划中的表消除
在10gR2中,引入的新的转换表消除(也可以叫连接消除),它将从查询中移除冗余的表.如果一个表的列仅仅只在连接谓词中出现那么这个表是冗余的且它被用来保证这些连接既不执行过滤也不扩展结果集.oracle在以下几种情况下将会消除冗余表.
主键-外键表消除
从10gr2开始,优化器能将由于主键-外键约束造成的冗余表消除例如:

jy@JINGYONG> create table jobs
  2  ( job_id NUMBER PRIMARY KEY,
  3  job_title VARCHAR2(35) NOT NULL,
  4  min_salary NUMBER,
  5  max_salary NUMBER );

表已创建。

jy@JINGYONG> create table departments
  2  ( department_id NUMBER PRIMARY KEY,
  3  department_name VARCHAR2(50) );

表已创建。


jy@JINGYONG> create table employees
  2  ( employee_id NUMBER PRIMARY KEY,
  3    employee_name VARCHAR2(50),
  4    department_id NUMBER REFERENCES departments(department_id),
  5    job_id NUMBER REFERENCES jobs(job_id) );

表已创建。

然后执行下面的查询:

jy@JINGYONG> select e.employee_name
  2  from employees e, departments d
  3  where e.department_id = d.department_id;

未选定行

jy@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  91p4shqr32mcy, child number 0
-------------------------------------
select e.employee_name from employees e, departments d where
e.department_id = d.department_id

Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    40 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("E"."DEPARTMENT_ID" IS NOT NULL)

在上面的查询中,连接department表是冗余的.department表中只有一列出现在连接谓词中且主键-外键约束保证了对于employees表中的每一行在department表中最多只有一行与之匹配.因此,上面的查询与下面的查询是等价的:

jy@JINGYONG> select e.employee_name
  2  from employees e
  3  where e.department_id is not null;

未选定行

jy@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical'))
;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  4dk02pkcxh604, child number 0
-------------------------------------
select e.employee_name from employees e where e.department_id is not
null

Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    40 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("E"."DEPARTMENT_ID" IS NOT NULL)

注意如果表employees中的department列上有not null约束上面的is not null谓词不是必需的.从oracle11gr开始,优化器将会消除哪些半连接或反连接的表,例如下面的查询:

jy@JINGYONG> select e.employee_id, e.employee_name
  2  from employees e
  3  where not exists (select 1
  4                    from jobs j
  5                    where j.job_id = e.job_id);

未选定行

jy@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical'))
;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  2swr3q3drtycz, child number 0
-------------------------------------
select e.employee_id, e.employee_name from employees e where not exists
(select :"SYS_B_0"                   from jobs j
where j.job_id = e.job_id)

Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    53 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("E"."JOB_ID" IS NULL)

因为employees.job_id是引用jobs.job_id的一个外键,对于employees.job_id中的任何不为null的值在jobs表中必需有一个值与之匹配.所以只有employees.job_id为null值的记录才会出现在结果集中.因此上面的查询与下面的查询是等价的:

jy@JINGYONG> select e.employee_id, e.employee_name
  2  from employees e
  3  where job_id is null;

未选定行

jy@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical'))
;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  6uh0534dch5m3, child number 0
-------------------------------------
select e.employee_id, e.employee_name from employees e where job_id is
null

Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    53 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("JOB_ID" IS NULL)

如果employees.job_id有一个not null约束的话:

jy@JINGYONG> alter table employees modify job_id not null;

表已更改。

那么在这种情况下对于上面的查询语句在employees表中没有满足条件的记录,查询优化器可能会选下面的执行执行:

jy@JINGYONG> select e.employee_id, e.employee_name
  2  from employees e
  3  where job_id is null;

未选定行

jy@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical'))
;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  6uh0534dch5m3, child number 0
-------------------------------------
select e.employee_id, e.employee_name from employees e where job_id is
null

Plan hash value: 72609621

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

| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |           |       |       |     1 (100)|          |

|*  1 |  FILTER            |           |       |       |            |          |

|   2 |   TABLE ACCESS FULL| EMPLOYEES |     1 |    53 |     2   (0)| 00:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NULL IS NOT NULL)

上面谓词中的”NULL IS NOT NULL”过滤是一个虚假的常量谓词它将阻止即将发生的表扫描.

在oracle11gR1中对于ANSI兼容的连接优化器也能正确的执行表消除,例如:

jy@JINGYONG> select employee_name
  2  from employees e inner join jobs j
  3  on e.job_id = j.job_id;

未选定行

jy@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical'))
;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  6m6g9pfuvpb69, child number 0
-------------------------------------
select employee_name from employees e inner join jobs j on e.job_id =
j.job_id

Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    27 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

从上面的执行计划可知优化器正确的消除了冗余表jobs

外连接表消除
在oracle11gr1中对于外连接引入了一种新的表消除,它不要求主键-外键约束.例如:先创建一个新的表projects并向employees表中增加project_id列

jy@JINGYONG> create table projects
  2  ( project_id NUMBER UNIQUE,
  3  deadline DATE,
  4  priority NUMBER );

表已创建。

jy@JINGYONG> alter table employees add project_id number;

表已更改。

现在来执行一个外连接查询:

jy@JINGYONG> select e.employee_name, e.project_id
  2  from employees e, projects p
  3  where e.project_id = p.project_id (+);

未选定行

jy@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical'))
;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  bdzav4h1rzn6n, child number 0
-------------------------------------
select e.employee_name, e.project_id from employees e, projects p where
e.project_id = p.project_id (+)

Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    40 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

外连接保证employees表中的每一行将会至少在结果集中出现一次.这唯一约束projects.project_id用来保证在employees中的每一行在projects表中最多有一行与之匹配.这两个属性一起保证了employees表中的每一行正好在结果集中出现一次.因为表projects中没有其它列被引用,projects表能被消除所以优化器选择了上面的查询.

在上面执行的查询都是非常简单的查询,在实际情况不可能都是那样简单的查询.但是在实际情况下表消除也是有好处的包括机器生成的查询和视图中的表消除.例如,一组表可能通过视图来提供访问,其中可能包含连接.通过视图来访问所有的列这个连接可能是必需的.但是有些用户可能只访问这个视图中的一部分列,在这种情况下有些连接表可能会被消除:

jy@JINGYONG> create view employee_directory_v as
  2  select e.employee_name, d.department_name, j.job_title
  3  from employees e, departments d, jobs j
  4  where e.department_id = d.department_id
  5  and e.job_id = j.job_id;

视图已创建。

如果要从上面的视图中通过职称来查看雇员的名字可以使用类似下面的查询:

jy@JINGYONG> select employee_name
  2  from employee_directory_v
  3  where department_name = 'ACCOUNTING';

未选定行

jy@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical'))

  2  ;

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID  4dfdc0m1d05c0, child number 0
-------------------------------------
select employee_name from employee_directory_v where department_name =
:"SYS_B_0"

Plan hash value: 2170245257

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |       |       |     3 (100)|          |
|   1 |  NESTED LOOPS                |              |       |       | |          |
|   2 |   NESTED LOOPS               |              |     1 |    80 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | EMPLOYEES    |     1 |    40 |     2   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | SYS_C0011146 |     1 |       |     0   (0)|          |
|*  5 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS  |     1 |    40 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
   5 - filter("D"."DEPARTMENT_NAME"=:SYS_B_0)

由于job_title列没有被select子句引用,jobs表从这个查询中被消除了所以优化器选择了上面的执行计划.

目前对于表消除有以下限制:
1.多列的主键-外键约束不支持
2.在查询中引用其它的连接键将阻止表消除.对于一个内联连接,连接键在连接的每一边都是等价的,但是如果
查询通过连接键在select子句中引用了表中其它的列这将不会执行表消除.一个解决办表是重写查询.

参考:
https://blogs.oracle.com/optimizer/entry/why_are_some_of_the_tables_in_my_query_missing_from_the_plan

在oracle 11g中auto_sample_size是如何工作的?
当要准备收集统计信息时,一个最重要的决定是你将使用什么样的抽样大小.一个100%的抽样大小能确保生成准确的统计数据但是它可能要收集很长时间.如是执行1%的抽样将会快速完成收集但是可能会生产不准确的统计数据.

在dbms_stats.gather_*_stats过程中estimate_percent参数当收集统计信息时控制着抽样大小,而且它的缺省值是auto_sample_size

首先来看看auto抽样大小在oracle 11g中的增强
oracle管理统计信息是通过pl/sql包dbms_stats来管理的.dbms_stats包提供了一些pl/sql过程来对表,方案或数据库收集统计信息.这些过程有一个estimate_percent参数,它用来指定收集统计信息时抽样大小的百分比.用户可以指定0到100的任何数字.例如,有一个表TEST,可以对它指定1%的抽样百分比:

exec dbms_stats.gather_table_stats(user,'TEST',estimate_percent => 1);

用户要指定一个合适的抽样百分比是不容易的.如果你指定的抽样百分比太高,那么收集统计信息会花费很长的时间.相反如果数据极端的倾斜且指定的抽样大小太低,那么生成的统计信息可能是不准确的.由于这个原因,oracle对estimate_percent参数引入用auto抽样大小.例如,可以对表TEST指定auto抽样大小:

exec dbms_stats.gather_table_stats(null,'TEST',estimate_percent => dbms_stats.auto_sample_size);

使用auto抽样大小比使用固定的抽样大小有两个优势.第一,当指定auto抽样大小时,系统会自动判断一个合适的抽样百分比.第二,auto抽样大小与固定的抽样大小更灵活.一个固定的抽样百分比在有些时候是好的,但是表的数据分布发生变化后可能就不合适了.换句话说当auto值被使用时当数据分布发生改变后oracle将会自动调整抽样大小.

当oracle使用auto抽样大小来让oracle选择一个合适的抽样大小时生成的统计信息是足够准确的.然而,它在数据极端倾斜的情况下收集统计信息是不准确的.在oracle11g中,当使用auto抽样时已经改变了它的行为.第一,auto抽样现在能生成确定性的统计信息.第二也是更重要的是,auto抽样生成的统计信息与100%抽样生成的统计信息几乎是一样的准确但是auto抽样比100%抽样花费的时间要少.下面做一个测试比较使用固定抽样大小的性能,和在oracle10g和oracle11g中比较auto抽样的情况.我们收集的表名为KCR5,表大小有35G,627228900行.

 desc kcr5
Name   Type                      
------ ------------ 
AKB020 VARCHAR2(20)                                   
AAZ218 VARCHAR2(20)                                   
PKA001 NUMBER(5)                                        
PKA438 VARCHAR2(1)                   
PKA435 VARCHAR2(30)                                             
AAE100 VARCHAR2(1)                         
PKA439 VARCHAR2(20) Y                                     
PKA044 VARCHAR2(1)  Y                

下面的表格给出了不同抽样百分比收集统计信息的时间
抽样百分比 运行时间(秒)
1%抽样大小 154
100%抽样大小 3404
oracle10g的auto抽样大小 503
oracle11g的auto抽样大小 356

对35G的表KCR5使用不同抽样百分比收集统计后可以比较收集统计信息的质量.在一个列的所有统计数据中,不重复值的数量的准确性以前是一个问题.列的不重复值的准确率的计算公式定义如下:
accuracy rate=1-(estimated ndv -actual ndv)/actual ndv.
这个accuracy rate准确率从0%到100%.这个准确率越高,收集的统计信息越准确.因此100%的抽样的准确率总是100%.我们不用关注准确率100的数据,只要关注准确率小于99.9%的下面的是使用不同抽样百分比抽样的数据
列名 实际不重复值数量 11g中的auto抽样 1%抽样
AKB020 34000000 98.3% 49.7%
PKA001 12048687 98.7% 23.4%
PKA438 7000458 99.1% 98.4%
PKA435 5084956 99.5% 99.3%
PKA439 3075965 99.6% 99.4%

从上面的信息可以知道,在oracle11g中使用auto抽样大小的收集时间只有使用100%抽样大小的十分之一,但是收集的统计信息准确率是接近的.

在oracle11g中使用auto_sample_size收集统计信息时收集时间和准确性与oracle10g相比都有提高.

这里我们主要是讨论一个与oracle11g中新auto_sample-size算法相近的算法和这个算法是如何影响收集统计信息的准确性的.

在研究新的收集算法之前,先来看一下旧的算法:
第一步:oracle在开始收集统计信息时使用一个较小的抽样百分比,如果有直方图需要被收集,oracle可能会根据抽样的百分比物化这个抽样

第二步:oracle收集基本列的统计信息样本时.例如,表T只有一个列c1,那么基本的统计收集查询语句就类似下面的(它不是一个真实的语法)

select count(*),count(c1),count(distinct c1),sum(sys_op_opnsize(c1)),min(c1),max(c1)
from T sample(x.0000000000);

查询是在oracle10G中使用auto_sample_size来收集基本的列的统计信息.这个查询的select列表中的项目对应查询表t中的行数,不为null值的记录数,不重复值的记录数,总的列长,C1列的最小值和最大值.在from子句中的”x.0000000000″由于oracle决定的抽样百分比.

第三步:如果直方图需要收集,oracle会对每一个请求直方图的列使用sql查询来抽样.

第四步:对于每个列要求直方图时oracle使用几个指标来判断当前抽样是否满足要求.
非重复值指标:对于这个列抽取的样品中是否包含了足够的非重复值
重复值指标:重复值的数量是否能够适当的从抽到的样品是进行扩展

第五步:如果在第四步中的所有指标都通过了,oracle认为当前的抽样大小是足够的且会对列完成直方图的创建.否则会认为抽样大小不够要增加抽样大小且重复上而后步骤直到找到一个满足条件的抽样大小或接近100%的抽样大小.

注意第三步到第五步对于每一个列都要进行.例如,如果表中有3个列请求创建直方图,在第一次迭代中我们得到一个样本并物化它,我们会使用3个查询,每个列一个,在相同的物化样本中收集直方图信息.假设oracle认为抽样大小对于第一列和第二列是足够的但对于第三列是不够的,那么会增加抽样大小.在第二次迭代中只有一个查询在修改抽样大小后的样品中对第三列收集直方图.

就如我们看到的如果有几次迭代被请求时旧的auto_sample_size可能会失效.几次迭代的主要原因是不能使用小的抽样来收集真实的重复值的数量.如果数据有倾斜,那么大量的低频率的值不会被抽取到样品中因为对于重复值指标来说抽样是失败的.

在oracle11g中我们对于基本列统计使用完不同的收集方法.我们使用下面的查询来收集列基本的列统计

select count(c1),min(c1),max(c1) from T;

查询是在oracle11g中使用auto_sample_size选项收集基本列统计信息的查询.注意在新的基本列统计收集查询中,没有抽样子句被使用.替代它的是执行一个全表扫描.所以这里没有count(distinct c1)来收集c1的重复值数量,相反当执行这个查询时会注入特殊的统计信息收集行资源.这个特殊的收集行资源使用一次通过基于哈希的不重复算法来收集重复值的信息.这个算法要求完全扫描数据,使用有限期数量的内存来生成高度精确的重复值数据与100%抽样几乎接近.这种特殊统计收集行资源的方法也收集行的数量,null值的数量和列的平均长度.因为对表执行了完全扫描,行的数量,列的平均长度,最小值和最大值都是100%的准确.

auto_sample_size也会影响直方图和索引统计信息的收集

auto_sample_size对直方图收集的影响
使用新的auto_sample_size算法时,直方图的收集是脱离基本列统计收集的(它们以前是在相同的抽样样品中进行收集的).因此当判断我们是否要增加抽样大小时,新的auto_sample_size算法不再执行重复值指标检查,因为不能从这个样品中得到重复值.对于直方图来说只有当抽样样品包含太多的null值或太少的行源时才需要增加抽样大小.这能够减少创建直方图所需要的迭代次数.

如果最小(或最大)值出现在用于收集直方图的样品中它不是在基本统计信息中被收集的最小(或最大)值,将会修改直方图因此在基本统计中收集的最小(或最大)值在直方图中会作为最一个(或最后一个)桶的端点而出现.

auto_sample_size对索引统计收集的影响
新的auto_sample_size算法也会影响索引统计信息的收集.索引统计信息收集是抽样的基础.它可能要经过几次迭代因为它要么包含太少的数据块要么为了收集重复键值抽样的大小太小.使用新的auto_sample_size算法,如果这个索引定义在一个单列上,或者索引定义在多列(一组列)上,那么列或列组的重复值将会被用作索引的重复键.那么在这种情况下索引统计收集查询将不会再收集重复键.这有助于减少因为索引统计收集而要增加抽样大小的成本.

小结:
1.新的auto_sample_size算法收集基本列统计时执行全表扫描
2.通过新的auto_sample_size收集重复列值与100%抽样大小收集有一样的准确率
3.其它的基本列统计象null值的数量,列的平均长度,最小和最大值与100%抽样大小收集有相同的准确率
4.基于新的auto_sample_size算法,直方图和索引统计收集仍使用抽样,但是新的auto_sample_size算法有助于缓解增加抽样的样本量.

参考
https://blogs.oracle.com/optimizer/entry/how_does_auto_sample_size

讨论自适应游标共享和sql计划管理的相互影响,要记住它们是负责执行不同任务的.ACS自适应游标共享控制在特定执行时间一个子游标是否被共享.对于每个执行的查询,自适应游标会考虑当前的绑定变量值并决定一个存在的子游标是否能被共享或者优化器将给一个机会对于当前的绑定变量值找到更好的执行计划.SPM(sql计划管理)控制着哪个执行计划会被优化器选中.如果一个子游标是ind-aware,那么决定是否共享是不会理睬这个查询是不是由sql计划管理所控制.但是一旦查询和它的当前绑定变量被发送给优化器sql计划管理会约束优化器选择执行计划,而不会考虑这个查询现在是否正在由自适应游标进行优化.

让我们来看一下例子,有许多方法将执行计划加载到sql计划管理中,但是为了简单起见,测试时将手动从游标缓存中加载执行计划将使用下面的语句来创建一个名叫employees_jy的表,下面的语句是向employees_jy表中插入多行记录,在job列上数据有大量的倾斜,且在表上只创建一个索引.

SQL> drop table employees_jy purge;
 
Table dropped
SQL> create table employees_jy as select * from hr.employees;
 
Table created
SQL> insert into employees_jy
  2  select * from employees_jy where job_id not in ('AD_VP','AD_PRES');
 
104 rows inserted
SQL> insert into employees_jy
  2  select * from employees_jy where job_id not in ('AD_VP','AD_PRES');
 
208 rows inserted
SQL> insert into employees_jy
  2  select * from employees_jy where job_id not in ('AD_VP','AD_PRES');
 
416 rows inserted
SQL> insert into employees_jy
  2  select * from employees_jy where job_id not in ('AD_VP','AD_PRES');
 
832 rows inserted
SQL> insert into employees_jy
  2  select * from employees_jy where job_id not in ('AD_VP','AD_PRES');
 
1664 rows inserted
SQL> insert into employees_jy
  2  select * from employees_jy where job_id not in ('AD_VP','AD_PRES');
 
3328 rows inserted
SQL> insert into employees_jy
  2  select * from employees_jy where job_id not in ('AD_PRES');
 
6658 rows inserted
SQL> insert into employees_jy
  2  select * from employees_jy where job_id not in ('AD_PRES');
 
13316 rows inserted
 
SQL> commit;
 
Commit complete
 

SQL> create index EMP_DEPARTMENT_JY_IX on employees_jy (department_id);
 
Index created


SQL> begin
  2   dbms_stats.gather_table_stats(null, 'employees_jy');
  3  end;
  4  /
  
 
PL/SQL procedure successfully completed

sys@JINGYONG> select job_id,count(*) from employees_jy group by job_id
  2  order by 2;

JOB_ID       COUNT(*)
---------- ----------
AD_PRES             1
AD_VP               8
AD_ASST           256
AC_ACCOUNT        256
AC_MGR            256
PU_MAN            256
PR_REP            256
MK_REP            256
MK_MAN            256
HR_REP            256
FI_MGR            256
SA_MAN           1280
IT_PROG          1280
PU_CLERK         1280
FI_ACCOUNT       1280
ST_MAN           1280
SH_CLERK         5120
ST_CLERK         5120
SA_REP           7680

已选择19行。

下面将执行一个简单的查询将这个employees_jy与hr.departments表使用department_id进行连接
使用job_id对表employees_jy进行过滤并产生聚集结果.

select /*+ bind_aware */ d.department_name,avg(e.salary) 
from employees_jy e,hr.departments d
where e.job_id=:job
and e.department_id=d.department_id
group by d.departmentd_name;

我们为了加快在游标缓存中得到bind-aware游标,对上面的查询语句使用了bind_aware提示.

如果我们对job_id使用三种不同的绑定变量值,AD_PRES,SA_MAN和SA_REP来执行上面的查询,那么优化器会选择三种不同的执行计划.

sys@JINGYONG> select /*+ bind_aware */ avg(e.salary),d.department_name
  2  from employees_jy e,hr.departments d
  3  where e.job_id='AD_PRES'
  4  and e.department_id=d.department_id
  5  group by d.department_name;

AVG(E.SALARY) DEPARTMENT_NAME
------------- ------------------------------
        24000 Executive

sys@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical'));

Plan hash value: 912418101

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |       |       |    79 (100)|          |
|   1 |  HASH GROUP BY                |              |     3 |   165 |    79   (3)| 00:00:01 |
|   2 |   NESTED LOOPS                |              |       |       |            |          |
|   3 |    NESTED LOOPS               |              |     3 |   165 |    78   (2)| 00:00:01 |
|   4 |     VIEW                      | VW_GBC_5     |     3 |   117 |    77   (2)| 00:00:01 |
|   5 |      HASH GROUP BY            |              |     3 |    99 |    77   (2)| 00:00:01 |
|*  6 |       TABLE ACCESS FULL       | EMPLOYEES_JY |     3 |    99 |    76   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN         | DEPT_ID_PK   |     1 |       |     0   (0)|          |
|   8 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS  |     1 |    16 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------



sys@JINGYONG> select /*+ bind_aware */ avg(e.salary),d.department_name
  2  from employees_jy e,hr.departments d
  3  where e.job_id='SA_MAN'
  4  and e.department_id=d.department_id
  5  group by d.department_name;

AVG(E.SALARY) DEPARTMENT_NAME
------------- ------------------------------
        12200 Sales

sys@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical')
);



Plan hash value: 2162091158

------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |       |       |    80 (100)|          |
|   1 |  HASH GROUP BY      |              |    27 |  1323 |    80   (3)| 00:00:01 |
|*  2 |   HASH JOIN         |              |  1505 |  73745|    79   (2)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES_JY |    27 |  50127|     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| DEPARTMENTS  |  1519 |    432|    76   (0)| 00:00:01 |
------------------------------------------------------------------------------------

sys@JINGYONG> select /*+ bind_aware */ avg(e.salary),d.department_name
  2  from employees_jy e,hr.departments d
  3  where e.job_id='SA_REP'
  4  and e.department_id=d.department_id
  5  group by d.department_name;

AVG(E.SALARY) DEPARTMENT_NAME
------------- ------------------------------
   8396.55172 Sales

sys@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical')
);


Plan hash value: 4206419095


------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |       |       |    81 (100)|          |
|   1 |  HASH GROUP BY      |              |    27 |  1323 |    81   (3)| 00:00:01 |
|*  2 |   HASH JOIN         |              |  9050 |   433K|    80   (2)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPARTMENTS  |    27 |   432 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| EMPLOYEES_JY |  9136 |   294K|    76   (0)| 00:00:01 |
------------------------------------------------------------------------------------

下面我们加载两个执行计划到sql计划管理中,再来使用绑定变量值AD_PRES,SA_REP来执行查询,这里有两个子游标有不同的执行计划.

SQL> select child_number,plan_hash_value
  2  from v$sql
  3  where sql_id='48ndug79z68zn'
  4  ;
 
CHILD_NUMBER PLAN_HASH_VALUE
------------ ---------------
           0      912418101
           1      4206419095


sys@JINGYONG> var loaded number
sys@JINGYONG> exec :loaded:=dbms_spm.load_plans_from_cursor_cache('48ndug79z68zn');

PL/SQL 过程已成功完成。

sys@JINGYONG> print loaded

    LOADED
----------
         2

现在如果我们同样使用上面三个绑定变量值来执行查询,sql计划管理将会约束优化器从sql计划基线中的两个可接受
的执行计划中选择,我们还是使用相同的执行顺序来看一下优化器会选择哪一个.

 select /*+ bind_aware */ avg(e.salary),d.department_name
  2  from employees_jy e,hr.departments d
  3  where e.job_id='AD_PRES'
  4  and e.department_id=d.department_id
  5  group by d.department_name;

AVG(E.SALARY) DEPARTMENT_NAME
------------- ------------------------------
        24000 Executive

sys@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical')
);


Plan hash value: 912418101

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |       |       |    79 (100)|          |
|   1 |  HASH GROUP BY                |              |     3 |   165 |    79   (3)| 00:00:01 |
|   2 |   NESTED LOOPS                |              |       |       |            |          |
|   3 |    NESTED LOOPS               |              |     3 |   165 |    78   (2)| 00:00:01 |
|   4 |     VIEW                      | VW_GBC_5     |     3 |   117 |    77   (2)| 00:00:01 |
|   5 |      HASH GROUP BY            |              |     3 |    99 |    77   (2)| 00:00:01 |
|*  6 |       TABLE ACCESS FULL       | EMPLOYEES_JY |     3 |    99 |    76   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN         | DEPT_ID_PK   |     1 |       |     0   (0)|          |
|   8 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS  |     1 |    16 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------


Note
-----
   - dynamic sampling used for this statement (level=2)
   - SQL plan baseline SQL_PLAN_5rjzd2w0wwnak39ef2806 used for this statement

对于这个绑定变量值,选择了正确的执行计划没有因为sql计划基线而混淆.这是因为这个执行计划被加载到
sql计划基线中且是可接受的.所以优化器允许选择它.

 select /*+ bind_aware */ avg(e.salary),d.department_name
  2  from employees_jy e,hr.departments d
  3  where e.job_id='SA_MAN'
  4  and e.department_id=d.department_id
  5  group by d.department_name;

AVG(E.SALARY) DEPARTMENT_NAME
------------- ------------------------------
        12200 Sales

sys@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical')
);

Plan hash value: 4206419095


------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |       |       |    81 (100)|          |
|   1 |  HASH GROUP BY      |              |    27 |  1323 |    81   (3)| 00:00:01 |
|*  2 |   HASH JOIN         |              |  9050 |   433K|    80   (2)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPARTMENTS  |    27 |   432 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| EMPLOYEES_JY |  9136 |   294K|    76   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)
   - SQL plan baseline SQL_PLAN_15f1skdhjq6mx641797f3 used for this statement

对于这个绑定变量值,优化器选择了一个在sql计划基数中不存在的执行计划,所以我们选择了一个可以接受的最好的执行计划来执行这个查询,优化器提出将基于成本的执行计划添加到sql计划基线中,但它将不会被考虑直到它已经被改进之前.

SQL> select sql_handle,plan_name,accepted
  2  from dba_sql_plan_baselines
  3  where sql_handle='SYS_SQL_5bc7ed1701ce5152';
 
SQL_HANDLE                     PLAN_NAME                      ACCEPTED
------------------------------ ------------------------------ --------
SYS_SQL_5bc7ed1701ce5152       SQL_PLAN_5rjzd2w0wwnak39ef2806 YES
SYS_SQL_5bc7ed1701ce5152       SQL_PLAN_15f1skdhjq6mx641797f3 YES
SYS_SQL_5bc7ed1701ce5152       SQL_PLAN_5rjzd2w0wwnakecea1efa NO 

SQL> select * from table(dbms_xplan.display_sql_plan_baseline('SYS_SQL_5bc7ed1701ce5152','SQL_PLAN_5rjzd2w0wwnakecea1efa','basic'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_5bc7ed1701ce5152
SQL text: select /*+ bind_aware */ avg(e.salary),d.department_name from
          employees_jy e,hr.departments d where e.job_id=:job and
          e.department_id=d.department_id group by d.department_name
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_5rjzd2w0wwnakecea1efa         Plan id: 2162091158
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 2162091158


------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |       |       |    80 (100)|          |
|   1 |  HASH GROUP BY      |              |    27 |  1323 |    80   (3)| 00:00:01 |
|*  2 |   HASH JOIN         |              |  1505 |  73745|    79   (2)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES_JY |    27 |  50127|     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| DEPARTMENTS  |  1519 |    432|    76   (0)| 00:00:01 |
------------------------------------------------------------------------------------

sys@JINGYONG> select /*+ bind_aware */ avg(e.salary),d.department_name
  2  from employees_jy e,hr.departments d
  3  where e.job_id='SA_REP'
  4  and e.department_id=d.department_id
  5  group by d.department_name;

AVG(E.SALARY) DEPARTMENT_NAME
------------- ------------------------------
   8396.55172 Sales

sys@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical')
);


Plan hash value: 4206419095


------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |       |       |    81 (100)|          |
|   1 |  HASH GROUP BY      |              |    27 |  1323 |    81   (3)| 00:00:01 |
|*  2 |   HASH JOIN         |              |  9050 |   433K|    80   (2)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPARTMENTS  |    27 |   432 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| EMPLOYEES_JY |  9136 |   294K|    76   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)
   - SQL plan baseline SQL_PLAN_15f1skdhjq6mx641797f3 used for this statement

和我们所期待的一样,和原来得到的执行计划一样,因为这个执行计划被加载到sql计划基线中了.因为第二个与第三个查询使用了相同的执行计划,而在游标缓存中只有一个能被共享.因此现在这个游标将会匹配与SA_MAN或SA_REP(或者在它们两者之间)有相似选择性的绑定变量.

今天expdp导出时卡死不跟踪文件中出现类似下面的错误内容：
WARNING:Could not increase the asynch I/O limit to 3328 for SQL direct I/O. It is set to 128
是oracle的BUG，编号：9949948，只发生在10.2.0.5.0和11.2.0.1.0，解决方法有2种，一种是在操作系统层面修改相关参数另一种方法就是打补丁

MOS上的相关内容为：Warning:Could Not Increase The Asynch I/O Limit To XX For Sql Direct I/O [ID 1302633.1]

In this Document
#SYMPTOM”>Symptoms

#CAUSE”>Cause

#FIX”>Solution

#REF”>References
Applies to:

Oracle Server – Enterprise Edition – Version:
10.2.0.5 and
later [Release:
10.2 and later ]
Generic Linux
disk_asynch_io = TRUE
filesystemio_options = none

[root@hnz ~]# cat /proc/sys/fs/aio-max-size
cat: /proc/sys/fs/aio-max-size: No such file or directory
[root@hnz ~]# cat /proc/sys/fs/aio-max-nr
65536

Solution

The aio-max-size kernel parameter doesn’t exist in the 2.6.x Linux
kernels.
This feature is now “automatic” in the 2.6.x kernel, based on the
physical capabilities of the disk device driver.
This should mean that the Linux Kernel is ready to perform ASYNC
I/O.

All install requirements should be met.

To ensure ASYNC I/O can be performed by Oracle Database you need to
verify or set the following parameters in the Database:
sql>alter system set disk_asynch_io=true scope=spfile;
sql> alter system set filesystemio_options=setall scope=spfile;

Then shutdown and startup the database and check if the warning
reappears.
An HCVE report (refer to Note 250262.1) should report no remaining
issues

If the above doesn’t resolve the problem, then increase
fs.aio-max-nr
References

BUG:10334897 –
COULD NOT INCREASE THE ASYNCH I/O LIMIT TO NNN FOR SQL DIRECT I/O.
IT IS SET TO

BUG:9772888 – WARNING:COULD NOT LOWER THE
ASYNCH I/O LIMIT TO 160 FOR SQL DIRECT I/O. IT IS SE

NOTE:205259.1 – Howto Enable Asynchoronous I/O
on Red Hat Linux 2.1

NOTE:225751.1 – Asynchronous I/O (aio) on
RedHat Advanced Server 2.1 and RedHat Enterprise Linux 3

检查相关内容，
[oracle@hnz ~]$ sqlplus /as sysdba

SQL*Plus: Release 10.2.0.5.0 – Production on Sat Oct 22
15:57:01 2013

Copyright (c) 1982, 2010, Oracle. All Rights
Reserved.

Connected to:
Oracle Database
10g Enterprise Edition Release 10.2.0.5.0 – 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application
Testing options

SQL> show parameter disk_asynch_io

NAME TYPE VALUE
———————————— ———– ——————————
disk_asynch_io boolean TRUE
SQL> show parameter filesystemio_options

NAME TYPE VALUE
———————————— ———– ——————————
filesystemio_options string none
SQL>

SQL> alter system set filesystemio_options=setall scope=spfile;

修改内核参数的值：aio-max-nr设置太低，推荐设置为fs.aio-max-nr= 3145728。修改参数使用/sbin/sysctl -p重新加载参数后，重启数据库即可

ardinality Feedback基数反馈是版本11.2中引入的关于SQL 性能优化的新特性，该特性主要针对 统计信息陈旧、无直方图或虽然有直方图但仍基数计算不准确的情况， Cardinality基数的计算直接影响到后续的JOIN COST等重要的成本计算评估，造成CBO选择不当的执行计划
在几乎同时执行如下语句:

select count(1)
  from (select PKA020,
               PKA022,
               PKA023,
               rowno,
               hospital_id,
               hospital_name,
               serial_no,
               biz_type,
               case_id,
               biz_stat,
               name,
               sex,
               pers_type,
               begin_date,
               end_date,
               fin_date,
               indi_id,
               corp_id,
               idcard,
               district_code,
               office_grade,
               office_grade as official_code,
               injury_borth_sn,
               corp_name,
               disease,
               in_area_name,
               in_dept_name,
               in_bed,
               bed_type,
               patient_id,
               remark,
               pos_code,
               reimburse_flag,
               fin_disease,
               ic_no,
               treatment_type,
               treatment_name,
               decl_sn,
               sure_date,
               indi_code,
               insr_code
          from (select /*+ index(e IDX_KA06_AKA120) index(f IDX_KA06_AKA120) index(g IDX_KAA1_AKA130) index(h IDX_KAA2_PKA006)*/
                 b.PKA020,
                 b.PKA022,
                 b.PKA023,
                 rownum rowno,
                 t2.AKB020 as hospital_id,
                 a.AAB069 as hospital_name,
                 b.AAZ218 as serial_no,
                 b.PKA009 as case_id,
                 GETCODENAME('aka130', b.AKA130) as biz_type,
                 g.PKA154 as biz_stat,
                 b.AAC003 as name,
                 GETCODENAME('aac004', b.AAC004) as sex,
                 GETCODENAME('pka004', b.PKA004) as pers_type,
                 to_char(b.PKA017, 'yyyy-mm-dd') as begin_date,
                 to_char(b.PKA032, 'yyyy-mm-d d') as end_date,
                 b.PKA042 as injury_borth_sn,
                 to_char(b.PKA045, 'yyyy-mm-dd hh24:mi:ss') as fin_date,
                 b.AAC001 as indi_id,
                 b.AAB001 as corp_id,
                 b.AAC002 as idcard,
                 GETCODENAME('aaa027', b.AAA027) as district_code,
                 b.PKA005 as office_grade,
                 b.PKA005 as official_code,
                 b.PKA008 as corp_name,
                 e.AKA121 as disease,
                 b.PKA022 as in_area_name,
                 b.PKA020 as in_dept_name,
                 b.PKA023 as in_bed,
                 b.PKA024 as bed_type,
                 b.PKA025 as patient_id,
                 b.PKA043 as remark,
                 b.PKA040 as pos_code,
                 b.PKA037 as reimburse_flag,
                 f.AKA121 as fin_disease,
                 b.PKA100 as ic_no,
                 GETCODENAME('pka006', b.PKA006) as treatment_type,
                 h.PKA155 as treatment_name,
                 (select t1.PAC028 from ACK1 t1 where t1.aac001 = b.aac001) as indi_code,
                 (select t1.PAC028 from ACK1 t1 where t1.aac001 = b.aac001) as insr_code,
                 (select max(PAE010)
                    from KCE6 b1
                   where b1.AKB020 = b.AKB020
                     and b1.AAZ218 = b.AAZ218
                     and b1.AAE100 = '1') as decl_sn,
                 (select max(c2.AAE015)
                    from KCE6 b2, KCB4 c2
                   where b2.AKB020 = b.AKB020
                     and b2.AAZ218 = b.AAZ218
                     and b2.PAE010 = c2.PKB019
                     and b2.AAE100 = '1'
                     and c2.AAE100 = '1') as sure_date
                  from AEZ1 a,
                       KC21 b,
                       KA06 e,
                       KA06 f,
                       KAA1 g,
                       KAA2 h,
                       KB01 t2
                 where a.AAZ001 = t2.AAZ269
                   and t2.AKB020 = b.AKB020
                   and b.AKA130 = g.AKA130
                   and b.PKA026 = e.AKA120(+)
                   and b.PKA031 = f.AKA120(+)
                   and b.PKA006 = h.PKA006(+)
                   and b.AAE100 = '1'
                   and b.PKA004 in ('1', '4', '5', '6', '2', '7')
                   and ('A' = 'A' or 'A' = b.AAA027)
                   and b.akb020 = '002001'
                   and b.AKA130 = '12'
                   and nvl(b.PKA039, '0') = '1'
                   and b.PKA045 >=
                       to_date('20130901 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
                   and b.PKA045 < =
                       to_date('20130930 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
                   and b.PKA037 = '0') w) t1_

SQL>select plan_hash_value,id,operation,options,object_name,depth,cost,timestamp,child_address
  from v$SQL_PLAN
   where sql_id ='64q8v2p41c1vc'
  and plan_hash_value in (1059287951,3791045646);

PLAN_HASH_VALUE     ID       OPERATION            OPTIONS           OBJECT_NAME          DEPTH    COST       TIMESTAMP
---------------     --       ----------------     --------       ------------------     -------   ------     -------------
3791045646          0        SELECT STATEMENT                                            0        43960      2013-10-17 14:52:53
3791045646          1        SORT                 AGGREGATE                              1                   2013-10-17 14:52:53
3791045646          2        VIEW                                                        2        43960      2013-10-17 14:52:53
3791045646          3        COUNT                                                       3                   2013-10-17 14:52:53
3791045646          4        FILTER                                                      4                   2013-10-17 14:52:53
3791045646          5        HASH JOIN            OUTER                                  5        43960      2013-10-17 14:52:53
3791045646          6        NESTED LOOPS         OUTER                                  6        43873      2013-10-17 14:52:53
3791045646          7        NESTED LOOPS         OUTER                                  7        43822      2013-10-17 14:52:53
3791045646          8        MERGE JOIN           CARTESIAN                              8        43822      2013-10-17 14:52:53
3791045646          9        MERGE JOIN           CARTESIAN                              9        43822      2013-10-17 14:52:53
3791045646          10       NESTED LOOPS                                                10       2          2013-10-17 14:52:53
3791045646          11       TABLE ACCESS         BY INDEX ROWID    KB01                 11       2          2013-10-17 14:52:53
3791045646          12       INDEX                RANGE SCAN        IDX_KB01_AKB020      12       1          2013-10-17 14:52:53
3791045646          13       INDEX                UNIQUE SCAN       PK_AEZ1              11       0          2013-10-17 14:52:53
3791045646          14       BUFFER               SORT                                   10       43822      2013-10-17 14:52:53
3791045646          15       TABLE ACCESS         BY INDEX ROWID    KC21                 11       43820      2013-10-17 14:52:53
3791045646          16       INDEX                RANGE SCAN        IDX_KC21_PKA025      12       192        2013-10-17 14:52:53
3791045646          17       BUFFER               SORT                                   9        2          2013-10-17 14:52:53
3791045646          18       INDEX                RANGE SCAN        IDX_KAA1_AKA130      10       0          2013-10-17 14:52:53
3791045646          19       INDEX                RANGE SCAN        IDX_KAA2_PKA006      8        0          2013-10-17 14:52:53
3791045646          20       INDEX                RANGE SCAN        IDX_KA06_AKA120      7        1          2013-10-17 14:52:53
3791045646          21       INDEX                FULL SCAN         IDX_KA06_AKA120      6        86         2013-10-17 14:52:53
1059287951          0        SELECT STATEMENT                                            0        51         2013-10-17 14:52:03
1059287951          1        SORT                 AGGREGATE                              1                   2013-10-17 14:52:03
1059287951          2        VIEW                                                        2        51         2013-10-17 14:52:03
1059287951          3        COUNT                                                       3                   2013-10-17 14:52:03
1059287951          4        FILTER                                                      4                   2013-10-17 14:52:03
1059287951          5        NESTED LOOPS         OUTER                                  5        51         2013-10-17 14:52:03
1059287951          6        NESTED LOOPS         OUTER                                  6        50         2013-10-17 14:52:03
1059287951          7        NESTED LOOPS                                                7        49         2013-10-17 14:52:03
1059287951          8        NESTED LOOPS         OUTER                                  8        49         2013-10-17 14:52:03
1059287951          9        MERGE JOIN           CARTESIAN                              9        49         2013-10-17 14:52:03
1059287951          10       MERGE JOIN           CARTESIAN                              10       49         2013-10-17 14:52:03
1059287951          11       TABLE ACCESS         BY INDEX ROWID    KB01                 11       2          2013-10-17 14:52:03
1059287951          12       INDEX                RANGE SCAN        IDX_KB01_AKB020      12       1          2013-10-17 14:52:03
1059287951          13       BUFFER               SORT                                   11       47         2013-10-17 14:52:03
1059287951          14       TABLE ACCESS         BY INDEX ROWID    KC21                 12       47         2013-10-17 14:52:03
1059287951          15       INDEX                RANGE SCAN        IDX_KC21_PKA045      13       3          2013-10-17 14:52:03
1059287951          16       BUFFER               SORT                                   10       2          2013-10-17 14:52:03
1059287951          17       INDEX                RANGE SCAN        IDX_KAA1_AKA130      11       0          2013-10-17 14:52:03
1059287951          18       INDEX                RANGE SCAN        IDX_KAA2_PKA006      9        0          2013-10-17 14:52:03
1059287951          19       INDEX                UNIQUE SCAN       PK_AEZ1              8        0          2013-10-17 14:52:03
1059287951          20       INDEX                RANGE SCAN        IDX_KA06_AKA120      7        1          2013-10-17 14:52:03
1059287951          21       INDEX                RANGE SCAN        IDX_KA06_AKA120      6        1          2013-10-17 14:52:03
1059287951          0        SELECT STATEMENT                                            0        51         2013-10-17 14:50:37
1059287951          1        SORT                 AGGREGATE                              1                   2013-10-17 14:50:37
1059287951          2        VIEW                                                        2        51         2013-10-17 14:50:37
1059287951          3        COUNT                                                       3                   2013-10-17 14:50:37
1059287951          4        FILTER                                                      4                   2013-10-17 14:50:37
1059287951          5        NESTED LOOPS         OUTER                                  5        51         2013-10-17 14:50:37
1059287951          6        NESTED LOOPS         OUTER                                  6        50         2013-10-17 14:50:37
1059287951          7        NESTED LOOPS                                                7        49         2013-10-17 14:50:37
1059287951          8        NESTED LOOPS         OUTER                                  8        49         2013-10-17 14:50:37
1059287951          9        MERGE JOIN           CARTESIAN                              9        49         2013-10-17 14:50:37
1059287951          10       MERGE JOIN           CARTESIAN                              10       49         2013-10-17 14:50:37
1059287951          11       TABLE ACCESS         BY INDEX ROWID    KB01                 11       2          2013-10-17 14:50:37
1059287951          12       INDEX                RANGE SCAN        IDX_KB01_AKB020      12       1          2013-10-17 14:50:37
1059287951          13       BUFFER               SORT                                   11       47         2013-10-17 14:50:37
1059287951          14       TABLE ACCESS         BY INDEX ROWID    KC21                 12       47         2013-10-17 14:50:37
1059287951          15       INDEX                RANGE SCAN        IDX_KC21_PKA045      13       3          2013-10-17 14:50:37
1059287951          16       BUFFER               SORT                                   10       2          2013-10-17 14:50:37
1059287951          17       INDEX                RANGE SCAN        IDX_KAA1_AKA130      11       0          2013-10-17 14:50:37
1059287951          18       INDEX                RANGE SCAN        IDX_KAA2_PKA006      9        0          2013-10-17 14:50:37
1059287951          19       INDEX                UNIQUE SCAN       PK_AEZ1              8        0          2013-10-17 14:50:37
1059287951          20       INDEX                RANGE SCAN        IDX_KA06_AKA120      7        1          2013-10-17 14:50:37
1059287951          21       INDEX                RANGE SCAN        IDX_KA06_AKA120      6        1          2013-10-17 14:50:37

得到以下不同的执行计划(这个语句执行了两次同一个sql_id产生不两个子游标使用不同的执行计划)

SQL_ID  64q8v2p41c1vc, child number 0
-------------------------------------
select count(1)
  from (select PKA020,
               PKA022,
               PKA023,
               rowno,
               hospital_id,
               hospital_name,
               serial_no,
               biz_type,
               case_id,
               biz_stat,
               name,
               sex,
               pers_type,
               begin_date,
               end_date,
               fin_date,
               indi_id,
               corp_id,
               idcard,
               district_code,
               office_grade,
               office_grade as official_code,
               injury_borth_sn,
               corp_name,
               disease,
               in_area_name,
               in_dept_name,
               in_bed,
               bed_type,
               patient_id,
               remark,
               pos_code,
               reimburse_flag,
               fin_disease,
               ic_no,
               treatment_type,
               treatment_name,
               decl_sn,
               sure_date,
               indi_code,
               insr_code
          from (select /*+ index(e IDX_KA06_AKA120) index(f IDX_KA06_AKA120) index(g IDX_KAA1_AKA130) index(h IDX_KAA2_PKA006)*/
                 b.PKA020,
                 b.PKA022,
                 b.PKA023,
                 rownum rowno,
                 t2.AKB020 as hospital_id,
                 a.AAB069 as hospital_name,
                 b.AAZ218 as serial_no,
                 b.PKA009 as case_id,
                 GETCODENAME('aka130', b.AKA130) as biz_type,
                 g.PKA154 as biz_stat,
                 b.AAC003 as name,
                 GETCODENAME('aac004', b.AAC004) as sex,
                 GETCODENAME('pka004', b.PKA004) as pers_type,
                 to_char(b.PKA017, 'yyyy-mm-dd') as begin_date,
                 to_char(b.PKA032, 'yyyy-mm-d d') as end_date,
                 b.PKA042 as injury_borth_sn,
                 to_char(b.PKA045, 'yyyy-mm-dd hh24:mi:ss') as fin_date,
                 b.AAC001 as indi_id,
                 b.AAB001 as corp_id,
                 b.AAC002 as idcard,
                 GETCODENAME('aaa027', b.AAA027) as district_code,
                 b.PKA005 as office_grade,
                 b.PKA005 as official_code,
                 b.PKA008 as corp_name,
                 e.AKA121 as disease,
                 b.PKA022 as in_area_name,
                 b.PKA020 as in_dept_name,
                 b.PKA023 as in_bed,
                 b.PKA024 as bed_type,
                 b.PKA025 as patient_id,
                 b.PKA043 as remark,
                 b.PKA040 as pos_code,
                 b.PKA037 as reimburse_flag,
                 f.AKA121 as fin_disease,
                 b.PKA100 as ic_no,
                 GETCODENAME('pka006', b.PKA006) as treatment_type,
                 h.PKA155 as treatment_name,
                 (select t1.PAC028 from ACK1 t1 where t1.aac001 = b.aac001) as indi_code,
                 (select t1.PAC028 from ACK1 t1 where t1.aac001 = b.aac001) as insr_code,
                 (select max(PAE010)
                    from KCE6 b1
                   where b1.AKB020 = b.AKB020
                     and b1.AAZ218 = b.AAZ218
                     and b1.AAE100 = '1') as decl_sn,
                 (select max(c2.AAE015)
                    from KCE6 b2, KCB4 c2
                   where b2.AKB020 = b.AKB020
                     and b2.AAZ218 = b.AAZ218
                     and b2.PAE010 = c2.PKB019
                     and b2.AAE100 = '1'
                     and c2.AAE100 = '1') as sure_date
                  from AEZ1 a,
                       KC21 b,
                       KA06 e,
                       KA06 f,
                       KAA1 g,
                       KAA2 h,
                       KB01 t2
                 where a.AAZ001 = t2.AAZ269
                   and t2.AKB020 = b.AKB020
                   and b.AKA130 = g.AKA130
                   and b.PKA026 = e.AKA120(+)
                   and b.PKA031 = f.AKA120(+)
                   and b.PKA006 = h.PKA006(+)
                   and b.AAE100 = '1'
                   and b.PKA004 in ('1', '4', '5', '6', '2', '7')
                   and ('A' = 'A' or 'A' = b.AAA027)
                   and b.akb020 = '002001'
                   and b.AKA130 = '12'
                   and nvl(b.PKA039, '0') = '1'
                   and b.PKA045 >=
                       to_date('20130901 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
                   and b.PKA045 < =
                       to_date('20130930 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
                   and b.PKA037 = '0') w) t1_

Plan hash value: 1059287951

----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |       |       |    51 (100)|          |
|   1 |  SORT AGGREGATE                        |                 |     1 |       |            |          |
|   2 |   VIEW                                 |                 |     1 |       |    51   (0)| 00:00:01 |
|   3 |    COUNT                               |                 |       |       |            |          |
|*  4 |     FILTER                             |                 |       |       |            |          |
|   5 |      NESTED LOOPS OUTER                |                 |     1 |   115 |    51   (0)| 00:00:01 |
|   6 |       NESTED LOOPS OUTER               |                 |     1 |   106 |    50   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                    |                 |     1 |    97 |    49   (0)| 00:00:01 |
|   8 |         NESTED LOOPS OUTER             |                 |     1 |    90 |    49   (0)| 00:00:01 |
|   9 |          MERGE JOIN CARTESIAN          |                 |     1 |    85 |    49   (0)| 00:00:01 |
|  10 |           MERGE JOIN CARTESIAN         |                 |     1 |    82 |    49   (0)| 00:00:01 |
|  11 |            TABLE ACCESS BY INDEX ROWID | KB01            |     1 |    15 |     2   (0)| 00:00:01 |
|* 12 |             INDEX RANGE SCAN           | IDX_KB01_AKB020 |     1 |       |     1   (0)| 00:00:01 |
|  13 |            BUFFER SORT                 |                 |     1 |    67 |    47   (0)| 00:00:01 |
|* 14 |             TABLE ACCESS BY INDEX ROWID| KC21            |     1 |    67 |    47   (0)| 00:00:01 |
|* 15 |              INDEX RANGE SCAN          | IDX_KC21_PKA045 |    60 |       |     3   (0)| 00:00:01 |
|  16 |           BUFFER SORT                  |                 |     1 |     3 |     2   (0)| 00:00:01 |
|* 17 |            INDEX RANGE SCAN            | IDX_KAA1_AKA130 |     1 |     3 |     0   (0)|          |
|* 18 |          INDEX RANGE SCAN              | IDX_KAA2_PKA006 |     1 |     5 |     0   (0)|          |
|* 19 |         INDEX UNIQUE SCAN              | PK_AEZ1         |     1 |     7 |     0   (0)|          |
|* 20 |        INDEX RANGE SCAN                | IDX_KA06_AKA120 |     1 |     9 |     1   (0)| 00:00:01 |
|* 21 |       INDEX RANGE SCAN                 | IDX_KA06_AKA120 |     1 |     9 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$F5BB74E1
   2 - SEL$3        / W@SEL$2
   3 - SEL$3
  11 - SEL$3        / T2@SEL$3
  12 - SEL$3        / T2@SEL$3
  14 - SEL$3        / B@SEL$3
  15 - SEL$3        / B@SEL$3
  17 - SEL$3        / G@SEL$3
  18 - SEL$3        / H@SEL$3
  19 - SEL$3        / A@SEL$3
  20 - SEL$3        / F@SEL$3
  21 - SEL$3        / E@SEL$3

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SEL$4")
      OUTLINE_LEAF(@"SEL$5")
      OUTLINE_LEAF(@"SEL$6")
      OUTLINE_LEAF(@"SEL$7")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      NO_ACCESS(@"SEL$F5BB74E1" "W"@"SEL$2")
      INDEX_RS_ASC(@"SEL$3" "T2"@"SEL$3" ("KB01"."AKB020"))
      INDEX_RS_ASC(@"SEL$3" "B"@"SEL$3" ("KC21"."AKB020" "KC21"."PKA045"))
      INDEX(@"SEL$3" "G"@"SEL$3" ("KAA1"."AKA130"))
      INDEX(@"SEL$3" "H"@"SEL$3" ("KAA2"."PKA006"))
      INDEX(@"SEL$3" "A"@"SEL$3" ("AEZ1"."AAZ001"))
      INDEX(@"SEL$3" "F"@"SEL$3" ("KA06"."AKA120"))
      INDEX(@"SEL$3" "E"@"SEL$3" ("KA06"."AKA120"))
      LEADING(@"SEL$3" "T2"@"SEL$3" "B"@"SEL$3" "G"@"SEL$3" "H"@"SEL$3" "A"@"SEL$3" "F"@"SEL$3"
              "E"@"SEL$3")
      USE_MERGE_CARTESIAN(@"SEL$3" "B"@"SEL$3")
      USE_MERGE_CARTESIAN(@"SEL$3" "G"@"SEL$3")
      USE_NL(@"SEL$3" "H"@"SEL$3")
      USE_NL(@"SEL$3" "A"@"SEL$3")
      USE_NL(@"SEL$3" "F"@"SEL$3")
      USE_NL(@"SEL$3" "E"@"SEL$3")
      INDEX_RS_ASC(@"SEL$7" "B2"@"SEL$7" ("KCE6"."AKB020" "KCE6"."AAZ218" "KCE6"."AAE100"))
      INDEX(@"SEL$7" "C2"@"SEL$7" ("KCB4"."PKB019"))
      LEADING(@"SEL$7" "B2"@"SEL$7" "C2"@"SEL$7")
      USE_NL(@"SEL$7" "C2"@"SEL$7")
      NLJ_BATCHING(@"SEL$7" "C2"@"SEL$7")
      INDEX_RS_ASC(@"SEL$6" "B1"@"SEL$6" ("KCE6"."AKB020" "KCE6"."AAZ218" "KCE6"."AAE100"))
      INDEX_RS_ASC(@"SEL$5" "T1"@"SEL$5" ("ACK1"."AAC001"))
      INDEX_RS_ASC(@"SEL$4" "T1"@"SEL$4" ("ACK1"."AAC001"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter(TO_DATE('20130901 00:00:00','yyyy-mm-dd hh24:mi:ss')<=TO_DATE('20130930
              23:59:59','yyyy-mm-dd hh24:mi:ss'))
  12 - access("T2"."AKB020"='002001')
  14 - filter(("B"."AKA130"='12' AND "B"."AAE100"='1' AND "B"."PKA039"='1' AND "B"."PKA037"='0'
              AND INTERNAL_FUNCTION("B"."PKA004")))
  15 - access("B"."AKB020"='002001' AND "B"."PKA045">=TO_DATE('20130901 00:00:00','yyyy-mm-dd
              hh24:mi:ss') AND "B"."PKA045"< =TO_DATE('20130930 23:59:59','yyyy-mm-dd hh24:mi:ss'))
  17 - access("G"."AKA130"='12')
  18 - access("B"."PKA006"="H"."PKA006")
  19 - access("A"."AAZ001"="T2"."AAZ269")
  20 - access("B"."PKA031"="F"."AKA120")
  21 - access("B"."PKA026"="E"."AKA120")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   6 - "B"."PKA026"[VARCHAR2,20]
   7 - "B"."PKA026"[VARCHAR2,20], "B"."PKA031"[VARCHAR2,20]
   8 - "T2"."AAZ269"[NUMBER,22], "B"."PKA026"[VARCHAR2,20], "B"."PKA031"[VARCHAR2,20]
   9 - "T2"."AAZ269"[NUMBER,22], "B"."PKA006"[VARCHAR2,6], "B"."PKA026"[VARCHAR2,20],
       "B"."PKA031"[VARCHAR2,20]
  10 - "T2"."AAZ269"[NUMBER,22], "B"."PKA006"[VARCHAR2,6], "B"."PKA026"[VARCHAR2,20],
       "B"."PKA031"[VARCHAR2,20]
  11 - "T2"."AAZ269"[NUMBER,22]
  12 - "T2".ROWID[ROWID,10]
  13 - (#keys=0) "B"."PKA006"[VARCHAR2,6], "B"."PKA026"[VARCHAR2,20], "B"."PKA031"[VARCHAR2,20]
  14 - "B"."PKA006"[VARCHAR2,6], "B"."PKA026"[VARCHAR2,20], "B"."PKA031"[VARCHAR2,20]
  15 - "B".ROWID[ROWID,10]
  16 - (#keys=0)

Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing

SQL_ID  64q8v2p41c1vc, child number 1
-------------------------------------
select count(1)
  from (select PKA020,
               PKA022,
               PKA023,
               rowno,
               hospital_id,
               hospital_name,
               serial_no,
               biz_type,
               case_id,
               biz_stat,
               name,
               sex,
               pers_type,
               begin_date,
               end_date,
               fin_date,
               indi_id,
               corp_id,
               idcard,
               district_code,
               office_grade,
               office_grade as official_code,
               injury_borth_sn,
               corp_name,
               disease,
               in_area_name,
               in_dept_name,
               in_bed,
               bed_type,
               patient_id,
               remark,
               pos_code,
               reimburse_flag,
               fin_disease,
               ic_no,
               treatment_type,
               treatment_name,
               decl_sn,
               sure_date,
               indi_code,
               insr_code
          from (select /*+ index(e IDX_KA06_AKA120) index(f IDX_KA06_AKA120) index(g IDX_KAA1_AKA130) index(h IDX_KAA2_PKA006)*/
                 b.PKA020,
                 b.PKA022,
                 b.PKA023,
                 rownum rowno,
                 t2.AKB020 as hospital_id,
                 a.AAB069 as hospital_name,
                 b.AAZ218 as serial_no,
                 b.PKA009 as case_id,
                 GETCODENAME('aka130', b.AKA130) as biz_type,
                 g.PKA154 as biz_stat,
                 b.AAC003 as name,
                 GETCODENAME('aac004', b.AAC004) as sex,
                 GETCODENAME('pka004', b.PKA004) as pers_type,
                 to_char(b.PKA017, 'yyyy-mm-dd') as begin_date,
                 to_char(b.PKA032, 'yyyy-mm-d d') as end_date,
                 b.PKA042 as injury_borth_sn,
                 to_char(b.PKA045, 'yyyy-mm-dd hh24:mi:ss') as fin_date,
                 b.AAC001 as indi_id,
                 b.AAB001 as corp_id,
                 b.AAC002 as idcard,
                 GETCODENAME('aaa027', b.AAA027) as district_code,
                 b.PKA005 as office_grade,
                 b.PKA005 as official_code,
                 b.PKA008 as corp_name,
                 e.AKA121 as disease,
                 b.PKA022 as in_area_name,
                 b.PKA020 as in_dept_name,
                 b.PKA023 as in_bed,
                 b.PKA024 as bed_type,
                 b.PKA025 as patient_id,
                 b.PKA043 as remark,
                 b.PKA040 as pos_code,
                 b.PKA037 as reimburse_flag,
                 f.AKA121 as fin_disease,
                 b.PKA100 as ic_no,
                 GETCODENAME('pka006', b.PKA006) as treatment_type,
                 h.PKA155 as treatment_name,
                 (select t1.PAC028 from ACK1 t1 where t1.aac001 = b.aac001) as indi_code,
                 (select t1.PAC028 from ACK1 t1 where t1.aac001 = b.aac001) as insr_code,
                 (select max(PAE010)
                    from KCE6 b1
                   where b1.AKB020 = b.AKB020
                     and b1.AAZ218 = b.AAZ218
                     and b1.AAE100 = '1') as decl_sn,
                 (select max(c2.AAE015)
                    from KCE6 b2, KCB4 c2
                   where b2.AKB020 = b.AKB020
                     and b2.AAZ218 = b.AAZ218
                     and b2.PAE010 = c2.PKB019
                     and b2.AAE100 = '1'
                     and c2.AAE100 = '1') as sure_date
                  from AEZ1 a,
                       KC21 b,
                       KA06 e,
                       KA06 f,
                       KAA1 g,
                       KAA2 h,
                       KB01 t2
                 where a.AAZ001 = t2.AAZ269
                   and t2.AKB020 = b.AKB020
                   and b.AKA130 = g.AKA130
                   and b.PKA026 = e.AKA120(+)
                   and b.PKA031 = f.AKA120(+)
                   and b.PKA006 = h.PKA006(+)
                   and b.AAE100 = '1'
                   and b.PKA004 in ('1', '4', '5', '6', '2', '7')
                   and ('A' = 'A' or 'A' = b.AAA027)
                   and b.akb020 = '002001'
                   and b.AKA130 = '12'
                   and nvl(b.PKA039, '0') = '1'
                   and b.PKA045 >=
                       to_date('20130901 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
                   and b.PKA045 < =
                       to_date('20130930 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
                   and b.PKA037 = '0') w) t1_

Plan hash value: 3791045646

---------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |       |       | 43960 (100)|          |
|   1 |  SORT AGGREGATE                       |                 |     1 |       |            |          |
|   2 |   VIEW                                |                 |  1079 |       | 43960   (1)| 00:08:48 |
|   3 |    COUNT                              |                 |       |       |            |          |
|*  4 |     FILTER                            |                 |       |       |            |          |
|*  5 |      HASH JOIN OUTER                  |                 |  1079 |   121K| 43960   (1)| 00:08:48 |
|   6 |       NESTED LOOPS OUTER              |                 |  1079 |   111K| 43873   (1)| 00:08:47 |
|   7 |        NESTED LOOPS OUTER             |                 |  1079 |   102K| 43822   (1)| 00:08:46 |
|   8 |         MERGE JOIN CARTESIAN          |                 |  1079 | 99268 | 43822   (1)| 00:08:46 |
|   9 |          MERGE JOIN CARTESIAN         |                 |  1079 | 96031 | 43822   (1)| 00:08:46 |
|  10 |           NESTED LOOPS                |                 |     1 |    22 |     2   (0)| 00:00:01 |
|  11 |            TABLE ACCESS BY INDEX ROWID| KB01            |     1 |    15 |     2   (0)| 00:00:01 |
|* 12 |             INDEX RANGE SCAN          | IDX_KB01_AKB020 |     1 |       |     1   (0)| 00:00:01 |
|* 13 |            INDEX UNIQUE SCAN          | PK_AEZ1         |     1 |     7 |     0   (0)|          |
|  14 |           BUFFER SORT                 |                 |  1079 | 72293 | 43822   (1)| 00:08:46 |
|* 15 |            TABLE ACCESS BY INDEX ROWID| KC21            |  1079 | 72293 | 43820   (1)| 00:08:46 |
|* 16 |             INDEX RANGE SCAN          | IDX_KC21_PKA025 | 56948 |       |   192   (2)| 00:00:03 |
|  17 |          BUFFER SORT                  |                 |     1 |     3 |     2   (0)| 00:00:01 |
|* 18 |           INDEX RANGE SCAN            | IDX_KAA1_AKA130 |     1 |     3 |     0   (0)|          |
|* 19 |         INDEX RANGE SCAN              | IDX_KAA2_PKA006 |     1 |     5 |     0   (0)|          |
|* 20 |        INDEX RANGE SCAN               | IDX_KA06_AKA120 |     1 |     9 |     1   (0)| 00:00:01 |
|  21 |       INDEX FULL SCAN                 | IDX_KA06_AKA120 | 22655 |   199K|    86   (2)| 00:00:02 |
---------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$F5BB74E1
   2 - SEL$3        / W@SEL$2
   3 - SEL$3
  11 - SEL$3        / T2@SEL$3
  12 - SEL$3        / T2@SEL$3
  13 - SEL$3        / A@SEL$3
  15 - SEL$3        / B@SEL$3
  16 - SEL$3        / B@SEL$3
  18 - SEL$3        / G@SEL$3
  19 - SEL$3        / H@SEL$3
  20 - SEL$3        / F@SEL$3
  21 - SEL$3        / E@SEL$3

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SEL$4")
      OUTLINE_LEAF(@"SEL$5")
      OUTLINE_LEAF(@"SEL$6")
      OUTLINE_LEAF(@"SEL$7")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      NO_ACCESS(@"SEL$F5BB74E1" "W"@"SEL$2")
      INDEX_RS_ASC(@"SEL$3" "T2"@"SEL$3" ("KB01"."AKB020"))
      INDEX(@"SEL$3" "A"@"SEL$3" ("AEZ1"."AAZ001"))
      INDEX_RS_ASC(@"SEL$3" "B"@"SEL$3" ("KC21"."AKB020" "KC21"."PKA025"))
      INDEX(@"SEL$3" "G"@"SEL$3" ("KAA1"."AKA130"))
      INDEX(@"SEL$3" "H"@"SEL$3" ("KAA2"."PKA006"))
      INDEX(@"SEL$3" "F"@"SEL$3" ("KA06"."AKA120"))
      INDEX(@"SEL$3" "E"@"SEL$3" ("KA06"."AKA120"))
      LEADING(@"SEL$3" "T2"@"SEL$3" "A"@"SEL$3" "B"@"SEL$3" "G"@"SEL$3" "H"@"SEL$3" "F"@"SEL$3"
              "E"@"SEL$3")
      USE_NL(@"SEL$3" "A"@"SEL$3")
      USE_MERGE_CARTESIAN(@"SEL$3" "B"@"SEL$3")
      USE_MERGE_CARTESIAN(@"SEL$3" "G"@"SEL$3")
      USE_NL(@"SEL$3" "H"@"SEL$3")
      USE_NL(@"SEL$3" "F"@"SEL$3")
      USE_HASH(@"SEL$3" "E"@"SEL$3")
      INDEX_RS_ASC(@"SEL$7" "B2"@"SEL$7" ("KCE6"."AKB020" "KCE6"."AAZ218" "KCE6"."AAE100"))
      INDEX(@"SEL$7" "C2"@"SEL$7" ("KCB4"."PKB019"))
      LEADING(@"SEL$7" "B2"@"SEL$7" "C2"@"SEL$7")
      USE_NL(@"SEL$7" "C2"@"SEL$7")
      NLJ_BATCHING(@"SEL$7" "C2"@"SEL$7")
      INDEX_RS_ASC(@"SEL$6" "B1"@"SEL$6" ("KCE6"."AKB020" "KCE6"."AAZ218" "KCE6"."AAE100"))
      INDEX_RS_ASC(@"SEL$5" "T1"@"SEL$5" ("ACK1"."AAC001"))
      INDEX_RS_ASC(@"SEL$4" "T1"@"SEL$4" ("ACK1"."AAC001"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter(TO_DATE('20130901 00:00:00','yyyy-mm-dd hh24:mi:ss')<=TO_DATE('20130930
              23:59:59','yyyy-mm-dd hh24:mi:ss'))
   5 - access("B"."PKA026"="E"."AKA120")
  12 - access("T2"."AKB020"='002001')
  13 - access("A"."AAZ001"="T2"."AAZ269")
  15 - filter(("B"."AKA130"='12' AND "B"."AAE100"='1' AND "B"."PKA039"='1' AND "B"."PKA037"='0'
              AND INTERNAL_FUNCTION("B"."PKA004") AND "B"."PKA045"<=TO_DATE('20130930 23:59:59','yyyy-mm-dd
              hh24:mi:ss') AND "B"."PKA045">=TO_DATE('20130901 00:00:00','yyyy-mm-dd hh24:mi:ss')))
  16 - access("B"."AKB020"='002001')
  18 - access("G"."AKA130"='12')
  19 - access("B"."PKA006"="H"."PKA006")
  20 - access("B"."PKA031"="F"."AKA120")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   5 - (#keys=1)
   6 - "B"."PKA026"[VARCHAR2,20]
   7 - "B"."PKA026"[VARCHAR2,20], "B"."PKA031"[VARCHAR2,20]
   8 - "B"."PKA006"[VARCHAR2,6], "B"."PKA026"[VARCHAR2,20], "B"."PKA031"[VARCHAR2,20]
   9 - "B"."PKA006"[VARCHAR2,6], "B"."PKA026"[VARCHAR2,20], "B"."PKA031"[VARCHAR2,20]
  11 - "T2"."AAZ269"[NUMBER,22]
  12 - "T2".ROWID[ROWID,10]
  14 - (#keys=0) "B"."PKA006"[VARCHAR2,6], "B"."PKA026"[VARCHAR2,20], "B"."PKA031"[VARCHAR2,20]
  15 - "B"."PKA006"[VARCHAR2,6], "B"."PKA026"[VARCHAR2,20], "B"."PKA031"[VARCHAR2,20]
  16 - "B".ROWID[ROWID,10]
  17 - (#keys=0)
  21 - "E"."AKA120"[VARCHAR2,50]

Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing
   - cardinality feedback used for this statement

从上面两个游标的执行计划可以看到对于表kc21的访问 plan_hash_value=1059287951使用
INDEX RANGE SCAN | IDX_KC21_PKA045 | 60 | 使用索引IDX_KC21_PKA045进行索引范围扫描评估的记录是60
而plan_hash_value=3791045646使用
INDEX RANGE SCAN | IDX_KC21_PKA025 | 56948 | 使用索引IDX_KC21_PKA025 进行索引范围扫描评估的记录是56948

Plan hash value: 1059287951

----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |       |       |    51 (100)|          |
|   1 |  SORT AGGREGATE                        |                 |     1 |       |            |          |
|   2 |   VIEW                                 |                 |     1 |       |    51   (0)| 00:00:01 |
|   3 |    COUNT                               |                 |       |       |            |          |
|*  4 |     FILTER                             |                 |       |       |            |          |
|   5 |      NESTED LOOPS OUTER                |                 |     1 |   115 |    51   (0)| 00:00:01 |
|   6 |       NESTED LOOPS OUTER               |                 |     1 |   106 |    50   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                    |                 |     1 |    97 |    49   (0)| 00:00:01 |
|   8 |         NESTED LOOPS OUTER             |                 |     1 |    90 |    49   (0)| 00:00:01 |
|   9 |          MERGE JOIN CARTESIAN          |                 |     1 |    85 |    49   (0)| 00:00:01 |
|  10 |           MERGE JOIN CARTESIAN         |                 |     1 |    82 |    49   (0)| 00:00:01 |
|  11 |            TABLE ACCESS BY INDEX ROWID | KB01            |     1 |    15 |     2   (0)| 00:00:01 |
|* 12 |             INDEX RANGE SCAN           | IDX_KB01_AKB020 |     1 |       |     1   (0)| 00:00:01 |
|  13 |            BUFFER SORT                 |                 |     1 |    67 |    47   (0)| 00:00:01 |
|* 14 |             TABLE ACCESS BY INDEX ROWID| KC21            |     1 |    67 |    47   (0)| 00:00:01 |
|* 15 |              INDEX RANGE SCAN          | IDX_KC21_PKA045 |    60 |       |     3   (0)| 00:00:01 |
|  16 |           BUFFER SORT                  |                 |     1 |     3 |     2   (0)| 00:00:01 |
|* 17 |            INDEX RANGE SCAN            | IDX_KAA1_AKA130 |     1 |     3 |     0   (0)|          |
|* 18 |          INDEX RANGE SCAN              | IDX_KAA2_PKA006 |     1 |     5 |     0   (0)|          |
|* 19 |         INDEX UNIQUE SCAN              | PK_AEZ1         |     1 |     7 |     0   (0)|          |
|* 20 |        INDEX RANGE SCAN                | IDX_KA06_AKA120 |     1 |     9 |     1   (0)| 00:00:01 |
|* 21 |       INDEX RANGE SCAN                 | IDX_KA06_AKA120 |     1 |     9 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------


Plan hash value: 3791045646

---------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |       |       | 43960 (100)|          |
|   1 |  SORT AGGREGATE                       |                 |     1 |       |            |          |
|   2 |   VIEW                                |                 |  1079 |       | 43960   (1)| 00:08:48 |
|   3 |    COUNT                              |                 |       |       |            |          |
|*  4 |     FILTER                            |                 |       |       |            |          |
|*  5 |      HASH JOIN OUTER                  |                 |  1079 |   121K| 43960   (1)| 00:08:48 |
|   6 |       NESTED LOOPS OUTER              |                 |  1079 |   111K| 43873   (1)| 00:08:47 |
|   7 |        NESTED LOOPS OUTER             |                 |  1079 |   102K| 43822   (1)| 00:08:46 |
|   8 |         MERGE JOIN CARTESIAN          |                 |  1079 | 99268 | 43822   (1)| 00:08:46 |
|   9 |          MERGE JOIN CARTESIAN         |                 |  1079 | 96031 | 43822   (1)| 00:08:46 |
|  10 |           NESTED LOOPS                |                 |     1 |    22 |     2   (0)| 00:00:01 |
|  11 |            TABLE ACCESS BY INDEX ROWID| KB01            |     1 |    15 |     2   (0)| 00:00:01 |
|* 12 |             INDEX RANGE SCAN          | IDX_KB01_AKB020 |     1 |       |     1   (0)| 00:00:01 |
|* 13 |            INDEX UNIQUE SCAN          | PK_AEZ1         |     1 |     7 |     0   (0)|          |
|  14 |           BUFFER SORT                 |                 |  1079 | 72293 | 43822   (1)| 00:08:46 |
|* 15 |            TABLE ACCESS BY INDEX ROWID| KC21            |  1079 | 72293 | 43820   (1)| 00:08:46 |
|* 16 |             INDEX RANGE SCAN          | IDX_KC21_PKA025 | 56948 |       |   192   (2)| 00:00:03 |
|  17 |          BUFFER SORT                  |                 |     1 |     3 |     2   (0)| 00:00:01 |
|* 18 |           INDEX RANGE SCAN            | IDX_KAA1_AKA130 |     1 |     3 |     0   (0)|          |
|* 19 |         INDEX RANGE SCAN              | IDX_KAA2_PKA006 |     1 |     5 |     0   (0)|          |
|* 20 |        INDEX RANGE SCAN               | IDX_KA06_AKA120 |     1 |     9 |     1   (0)| 00:00:01 |
|  21 |       INDEX FULL SCAN                 | IDX_KA06_AKA120 | 22655 |   199K|    86   (2)| 00:00:02 |
---------------------------------------------------------------------------------------------------------

我们可以通过可以通过V$SQL_SHARED_CURSOR和来找出现有系统shared pool中存在的使用了Cardinality Feedback基数反馈的子游标

SQL>select sql_id,child_number,executions,loads,child_address from v$sql where sql_id='64q8v2p41c1vc';
SQL_ID        CHILD_NUMBER  EXECUTIONS	LOADS	CHILD_ADDRESS
------------- ------------  ----------  ----- -----------------
64q8v2p41c1vc 	1	            5	         3	  07000010AC4EAAE8
64q8v2p41c1vc	  2            	1	         1	  07000010AA093B00


SQL>select sql_id,address,child_address,child_number,user_feedback_stats
 from v$sql_shared_cursor where sql_id='64q8v2p41c1vc';
SQL_ID	      ADDRESS	      CHILD_ADDRESS	CHILD_NUMBER    USE_FEEDBACK_STATS
------------- ----------------      ----------------  ------------    ---------------
64q8v2p41c1vc	07000010AD35F368	07000010AC4EAAE8	1                Y
64q8v2p41c1vc	07000010AD35F368	07000010AA093B00	2                N

可以看到两个子游标的USE_FEEDBACK_STATS的值不一样,正是因这Cardinality Feedback基数反馈造成的

如果当这个特性产生更差的执行计划时可以考虑禁用这个特性
可以通过多种方法禁用该特性
1. 使用 _optimizer_use_feedback 隐藏参数 session 级别

SQL> alter session set “_optimizer_use_feedback”=false;
会话已更改。

system级别

SQL> alter system set “_optimizer_use_feedback”=false;
系统已更改。

2. 使用opt_param(‘_optimizer_use_feedback’ ‘false’) HINT在语句级进行禁用

select /*+ opt_param(‘_optimizer_use_feedback’ ‘false’)*/ count(*) from jy;

如果要强制使用Cardinality Feedback可以使用cardinality HINT

select /*+ cardinality(jy,  1) */ count(*) from jy;

SQL*Loader（SQLLDR）是Oracle的高速批量数据加载工具。这是一个非常有用的工具，可用于多种平面文件格式向Oralce数据库 中加载数据。SQLLDR可以在极短的时间内加载数量庞大的数据。它有两种操作模式：
传统路径：（conventional path）：SQLLDR会利用SQL插入为我们加载数据。
直接路径(direct path)：采用这种模式，SQLLDR不使用SQL；而是直接格式化数据库块。
利用直接路径加载，能从一个平面文件读数据，并将其直接写至格式化的数据库块，而绕过整个SQL引擎和undo生成，同时还 可能避开redo生成。要在一个没有任何数据的数据库中充分加载数据，最快的方法就是采用并行直接路径加载。

如果不带任何输入地从命令行执行SQLLDR，它会提供以下帮助：

[oracle@jy ~]$ sqlldr

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 11:32:22 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Usage: SQLLDR keyword=value [,keyword=value,...]

Valid Keywords:

userid -- ORACLE username/password
control -- control file name
log -- log file name
bad -- bad file name
data -- data file name
discard -- discard file name
discardmax -- number of discards to allow (Default all)
skip -- number of logical records to skip (Default 0)
load -- number of logical records to load (Default all)
errors -- number of errors to allow (Default 50)
rows -- number of rows in conventional path bind array or between direct path data saves
(Default: Conventional path 64, Direct path all)
bindsize -- size of conventional path bind array in bytes (Default 256000)
silent -- suppress messages during run (header,feedback,errors,discards,partitions)
direct -- use direct path (Default FALSE)
parfile -- parameter file: name of file that contains parameter specifications
parallel -- do parallel load (Default FALSE)
file -- file to allocate extents from
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions (Default FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (Default FALSE)
commit_discontinued -- commit loaded rows when load is discontinued (Default FALSE)
readsize -- size of read buffer (Default 1048576)
external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE (Default NOT_USED)
columnarrayrows -- number of rows for direct path column array (Default 5000)
streamsize -- size of direct path stream buffer in bytes (Default 256000)
multithreading -- use multithreading in direct path
resumable -- enable or disable resumable for current session (Default FALSE)
resumable_name -- text string to help identify resumable statement
resumable_timeout -- wait time (in seconds) for RESUMABLE (Default 7200)
date_cache -- size (in entries) of date conversion cache (Default 1000)

PLEASE NOTE: Command-line parameters may be specified either by
position or by keywords. An example of the former case is 'sqlldr
scott/tiger foo'; an example of the latter is 'sqlldr control=foo
userid=scott/tiger'. One may specify parameters by position before
but not after parameters specified by keywords. For example,
'sqlldr scott/tiger control=foo logfile=log' is allowed, but
'sqlldr scott/tiger control=foo log' is not, even though the
position of the parameter 'log' is correct.

要使用SQLLDR,需要有一个控制文件(control file).控制文件中包含描述输入数据的信息(如输入数据的布局、数据类型等),另外还包含有关目标表的信息.控制文件甚至还可以包含要加载的数据.在下面的例子中,我们将一步一步地建立一个简单的控制文件，并对这些命令提供必须的解释
(1) LOAD DATA
(2) INFILE *
(3) INTO TABLE DEPT
(4) FIELDS TERMINATED BY ‘,’
(5) (DEPTNO, DNAME, LOC )
(6) BEGINDATA
(7) 10,Sales,Virginia
(8) 20,Accounting,Virginia
(9) 30,Consulting,Virginia
(10) 40,Finance,Virginia

[oracle@jy ~]$ vi demo1.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ','
(DEPTNO, DNAME, LOC )
BEGINDATA
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia

LOAD DATA:这会告诉SQLLDR要做什么(在这个例子中,则指示要加载数据).SQLLDR还可以执行CONTINUE_LOAD,也就是继续加载.只有在继续一个多表直接路径加载时才能使用后面这个选项.

INFILE *:这会告诉SQLLDR所要加载的数据实际上包含在控制文件本身上,如第6～10行所示.也可以指定包含数据的另一个文件的文件名.如果愿意,可以使用一个命令行参数覆盖这个INFILE语句.要注意的是命令行选项总会涵盖控制文件设置.

INTO TABLE DEPT:这会告诉SQLLDR要把数据加载到哪个表中(在这个例子中,数据要加载到DEPT表中).

FIELDS TERMINATED BY ‘,’:这会告诉SQLLDR数据的形式应该是用逗号分隔的值.为SQLLDR描述输入数据的方式有数十种;这只是其中较为常用的方法之一.

(DEPTNO, DNAME, LOC):这会告诉SQLLDR所要加载的列,这些列在输入数据中的顺序以及数据类型.这是指输入流中数据的数据类型,而不是数据库中的数据类型.在这个例子中,列的数据类型默认为CHAR(255),这已经足够了.

BEGINDATA:这会告诉SQLLDR你已经完成对输入数据的描述,后面的行(第7～10行)是要加载到DEPT表的具体数据.

这个控制文件采用了最简单,最常用的格式之一:将定界数据加载到一个表.要使用这个控制文件(名为demo1.ctl),只需创建一个空的DEPT表：

sys@JINGYONG> create table dept
2 (deptno number(2) constraint dept_pk primary key,
3 dname varchar2(14),
4 loc varchar2(14)
5 );

表已创建。

并运行以下命令：

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo1.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 11:43:20 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 5

如果表非空，就会收到一个错误消息：

SQLLDR-601: For INSERT option, table must be empty. Error on table DEPT

这是因为,这个控制文件中几乎所有选项都取默认值,而默认的加载选项是INSERT(而不是APPEND,TRUNCATE或REPLACE).要执行INSERT,SQLLDR就认为表为空.如果想向DEPT表中增加记录,可以指定加载选项为APPEND;或者,为了替换DEPT表中的数据,可以使用REPLACE或TRUNCATE.REPLACE使用一种传统DELETE语句;因此,如果要加载的表中已经包含许多记录,这个操作可能执行得很慢.TRUNCATE则不同,它使用TRUNCATE SQL命令,通常会更快地执行,因为它不必物理地删除每一行.

每个加载都会生成一个日志文件,以上这个简单加载的日志文件如下：

[oracle@jy ~]$ cat demo1.log

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 11:43:20 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Control File: demo1.ctl
Data File: demo1.ctl
Bad File: demo1.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table DEPT, loaded from every logical record.
Insert option in effect for this table: INSERT

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO FIRST * , CHARACTER
DNAME NEXT * , CHARACTER
LOC NEXT * , CHARACTER

Record 5: Rejected - Error on table DEPT, column DEPTNO.
Column not found before end of logical record (use TRAILING NULLCOLS)

Table DEPT:
4 Rows successfully loaded.
1 Row not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Space allocated for bind array: 49536 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 5
Total logical records rejected: 1
Total logical records discarded: 0

Run began on Thu Oct 03 11:43:20 2013
Run ended on Thu Oct 03 11:43:21 2013

Elapsed time was: 00:00:00.38
CPU time was: 00:00:00.09

日志文件会告诉我们关于加载的很多方面,从中可以看到我们所用的选项(默认或默认选项);可以看到读取了多少记录,加载 了多少记录等.日志文件指定了所有BAD文件和DISCARD文件的位置,甚至还会告诉我们加载用了多长时间.每个日志文件对于验 证加载是否成功至关重要,另外对于诊断错误也很有意义.如果所加载的数据导致SQL错误(也就是说,输入数据是”坏的”,并在BAD文件中建立了记录),这些错误就会记录在这个日志文件中.

如何加载定界数据
定界数据(delimited data)即用某个特定字符分隔的数据,可以用引号括起,这是当前平面文件最常见的数据格式.在大型机 上,定长,固定格式的文件可能是最可识别的文件格式,但是在UNIX和NT上,定界文件才是”标准”.

对于定界数据,最常用的格式是逗号分隔值(comma-separated values，CSV)格式.采用这种文件格式,数据中的每个字段与 下一个字段用一个逗号分隔.文本串可以用引号括起,这样就允许串本身包含逗号.如果串还必须包含引号,一般约定是使用两个引号(在下面的代码中,将使用””而不是”).要加载定界数据,相应的典型控制文件与前面第一个例子很相似,但是FIELDS TERMINATED BY 子句通常如下指定:

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

它指定用逗号分隔数据字段,每个字段可以用双引号括起,如果我们把这个控制文件的最后部分修改如下:

[oracle@jy ~]$ cat demo2.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO, DNAME, LOC )
BEGINDATA
10,Sales,"Virginia,USA"
20,Accounting,"Va,""USA"""
30,Consulting,Virginia
40,Finance,Virginia

使用这个控制文件运行SQLLDR时，结果如下：

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo2.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:01:03 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 5

使用这个控制文件运行SQLLDR时，结果如下：

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC
---------- -------------- --------------
10 Sales Virginia,USA
20 Accounting Va,"USA"
30 Consulting Virginia
40 Finance Virginia

要注意以下几点：
部门10中的Virginia.USA:这是因为输入数据是”Virginia,USA”.输入数据字段必须包括在引号里才能保留数据中的逗号.否 则,数据中的这个逗号会被认为是字段结束标记,这样就会只加载Virginia,而没有USA文本.

Va,”USA”:这是因为输入数据是”Va,””USA”””.对于引号括起的串,SQLLDR会把其中”的两次出现计为一次出现.要加载一个包含可选包围字符(enclosure character)的串,必须保证这个包围字符出现两次.

另一种常用的格式是制表符定界数据(tag-delimited data),这是用制表符分隔而不是逗号分割的数据.有两种方法使用 TERMINATED BY子句来加载这种数据：
TERMINATED BY X’09′(使用十六进制格式的制表符;采用ASCII时,制表符为9)
TERMINATED BY WHITESPACE
这两种方法在实现上有很大差异,下面将会说明.还是用前面的DEPT表,我们将使用以下控制文件加载这个表:

[oracle@jy ~]$ vi demo3.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY WHITESPACE
(DEPTNO, DNAME, LOC )
BEGINDATA
10 Sales Virginia

从字面上不太容易看得出来,不过要知道,在这里各部分数据之间都有两个制表符.这里的数据行实际上是:
10\t\tSales\t\tVirginia
在此\t是普通可识别的制表符转义字符.使用这个控制文件时(包含如前所示的TERMINATED BY WHITESPACE),表DEPT中的数据将是:

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC
---------- -------------- --------------
10 Sales Virginia

TERMINATED BY WHITESPACE会解析这个串,查找空白符(制表符,空格和换行符)的第一次出现,然后继续查找,直至找到下一 个非空白符.

另一方面,如果要使用FIELDS TERMINATED BY X’09’,如以下控制文件所示,这里稍做修改:

[oracle@jy ~]$ cat demo4.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY "X'09'"
TRAILING NULLCOLS
(DEPTNO,DNAME,LOC )
BEGINDATA
10 sales virginia

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo4.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Oct 4 08:07:43 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 1

加载这样的定界数据时,很可能想逃过输入记录中的某些列.例如,你可能加载字段1,3和5,而跳过第2列和第4列.为此SQLLDR提供了FILLER关键字.这允许你映射一个输入记录中的一列,但不把它放在数据库中.例如,给定DEPT表以及先前的一个控制文件,可以修改这个控制文件,使用FILLER关键字正确地加载数据(跳过制表符):

[oracle@jy ~]$ vi demo3.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY WHITESPACE
(DEPTNO, dummy1 filler, DNAME, dummy2 filler, LOC)
BEGINDATA
10 Sales Virginia

所得到的表DEPT现在如下所示：

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC
---------- -------------- --------------
10 Sales Virginia

如何加载固定格式数据
通常会有一个有某个外部系统生成的平面文件,而且这是一个定长文件,其中包含着固定位置的数据(positional data).例 如,NAME字段位于第1～10字节,ADDRESS字段位于地11～35字节等.

这种定宽的固定位置数据是最适合SQLLDR加载的数据格式.要加载这种数据,使用SQLLDR是最快的处理方法,因为解析输入数据流相当容易.SQLLDR会在数据记录中存储固定字节的偏移量和长度,因此抽取某个给定字段相当简单.如果要加载大量数据，将其转换为一种固定位置格式通常是最好的办法.当然,定宽文件也有一个缺点,它比简单的定界文件格式可能要大得多.

要加载定宽的固定位置数据,将会在控制文件中使用POSITION关键字,例如:

[oracle@jy ~]$ vi demo5.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
(DEPTNO position(1:2),DNAME position(3:16),LOC position(17:30) )
BEGINDATA
10Accounting Virginia,USA

QL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:21:04 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 2

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC
---------- -------------- --------------
10 Accounting Vir ginia,USA

这个控制文件没有使用FIELDS TERMINATED BY子句;而是使用了POSITION来告诉SQLLDR字段从哪里开始,到哪里结束.关于 POSITION子句有意思的是,我们可以使用重叠的位置,可以在记录中来回反复.例如,如果如下修改DEPT表:

jy@JINGYONG> alter table dept add entire_line varchar2(30);

表已更改。

并使用以下控制文件:

[oracle@jy ~]$ vi demo6.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
(DEPTNO position(1:2),
DNAME position(3:16),
LOC position(17:30),
ENTIRE_LINE position(1:30)
)
BEGINDATA
10Accounting Virginia,USA

字段ENTIRE_LINE定义的POSITION(1:30).这会从所有30字节的输入数据中抽取出这个字段的数据,而其他字段都是输入数据的子串.这个控制文件的输出如下:

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC ENTIRE_LINE
---------- -------------- -------------- -----------------------------
10 Accounting Vir ginia,USA 10Accounting Virginia,USA

使用POSITION时,可以使用相对偏移量,也可以使用绝对偏移量.在前面的例子中使用了绝对偏移量,我们明确地指示了字段从 哪里开始,到哪里结束.也可以把前面的控制文件写作:

[oracle@jy ~]$ vi demo7.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
(DEPTNO position(1:2),
DNAME position(*:16),
LOC position(*:30),
ENTIRE_LINE position(1:30)
)
BEGINDATA
10Accounting Virginia,USA

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo7.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:25:53 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 2

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC ENTIRE_LINE
---------- -------------- -------------- -----------------------------
10 Accounting Vir ginia,USA 10Accounting Virginia,USA

*指示控制文件得出上一个字段在哪里结束.因此,在这种情况下,(*:16)与(3:16)是一样的.注意,控制文件中可以混合使用相对位置和绝对位置.另外,使用*表示法时,可以把它与偏移量相加.例如,如果DNAME从DEPTNO结束之后的2个字节处开始,可以使用(*+2:16).在这个例子中,其作用就相当于使用(5:16).

POSITION子句中的结束位置必须是数据结束的绝对列位置.有时,可能指定每个字段的长度更为容易,特别是如果这些字段是连 续的(就像前面的例子一样).采用这种方式,只需告诉SQLLDR:记录从第1个字节开始,然后指定每个字段的长度就行了.这样我们就可以免于计算记录中的开始和结束偏移量,这个计算有时可能很困难.为此,可以不指定结束位置,而是指定定长记录中各个字段的长度,如下:

[oracle@jy ~]$ vi demo8.ctl
REPLACE
LOAD DATA
INFILE *
INTO TABLE DEPT
(DEPTNO position(1) char(2),
DNAME position(*) char(14),
LOC position(*) char(14),
ENTIRE_LINE position(1:30)
)
BEGINDATA
10Accounting Virginia,USA

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo8.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:29:01 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 2

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC ENTIRE_LINE
---------- -------------- -------------- -----------------------------
10 Accounting Vir ginia,USA 10Accounting Virginia,USA

在此只需告诉SQLLDR第一个字段从哪里开始及其长度.后面的每个字段都从上一个字段结束处开始,并具有指定的长度.直至最 后一个字段才需要再次指定位置,因为这个字段又要从记录起始处开始.

如何加载日期
使用SQLLDR加载日期相当简单,但是看起来这个方面经常导致混淆.你只需在控制文件中使用DATE数据类型,并指定要使用的日 期格式,这个日期格式与数据库中TO_CHAR和TO_DATE中使用的日期格式是一样的.SQLLDR会向数据应用这个日期格式,并为你完成加载.

例如,如果再把DEPT表修改如下:

jy@JINGYONG> alter table dept add last_updated date;

表已更改。

可以用以下控制文件加载它：

[oracle@jy ~]$ vi demo9.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
(DEPTNO,
DNAME,
LOC,
ENTIRE_LINE,
LAST_UPDATED date 'yyyy-mm-dd'
)
BEGINDATA
10,Sales,Virginia,USA,2000-05-01
20,Accounting,Virginia,USA,1999-06-21
30,Consulting,Virginia,USA,2000-01-05
40,Finance,Virginia,USA,2001-03-15

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo9.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:47:17 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 5

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED
---------- -------------- -------------- ----------------------------- --------------
10 Sales Virginia USA 01-5月 -00
20 Accounting Virginia USA 21-6月 -99
30 Consulting Virginia USA 05-1月 -00
40 Finance Virginia USA 15-3月 -01

就这么简单,只需在控制文件中应用格式,SQLLDR就会为我们完成日期转换.在某些情况下可能使用一个更强大的SQL函数更为合适.例如,如果你的输入文件包含多种不同格式的日期:有些有时间分量,有些没有;有些采用DD-MON-YYYY格式;有些格式为DD/MM/YYYY等等.

如果使用函数加载数据
在SQLLDR中使用函数很容易.要在SQLLDR脚本中向某个字段应用一个函数,只需将这个函数增加到控制文件中(用两个引号括起).例如,假设有前面的DEPT表,你想确保所加载的数据都是大写的.可以使用以下控制文件来加载:

[oracle@jy ~]$ vi demo10.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
ENTIRE_LINE "upper(:entire_line)",
LAST_UPDATED date 'yyyy-mm-dd'
)
BEGINDATA
10,Sales,Virginia,USA,2000-05-01
20,Accounting,Virginia,USA,1999-06-21
30,Consulting,Virginia,USA,2000-01-05
40,Finance,Virginia,USA,2001-03-15

"demo10.ctl" 17L, 342C written
[oracle@jy ~]$ sqlldr userid=jy/jy control=demo10.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:50:46 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 5

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED
---------- -------------- -------------- ----------------------------- --------------
10 SALES VIRGINIA USA 01-5月 -00
20 ACCOUNTING VIRGINIA USA 21-6月 -99
30 CONSULTING VIRGINIA USA 05-1月 -00
40 FINANCE VIRGINIA USA 15-3月 -01

可以注意到,只需向一个绑定变量应用UPPER函数就可以很容易地将数据变为大写.要注意SQL函数可以引用任何列,而不论将 函数实际上应用于哪个列.这说明一个列可以是对两个或更多其他列应用一个函数的结果.例如,如果你想加载ENTIRE_LINE 列,可以使用SQL连接运算符.不过这种情况下这样做稍有些麻烦.现在,输入数据集中有4个数据元素.如果只是向控制文件中加入如下字符ENTIRE_LINE：

[oracle@jy ~]$ vi demo11.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED date 'yyyy-mm-dd',
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated"

)
BEGINDATA
10,Sales,Virginia,USA,2000-05-01
20,Accounting,Virginia,USA,1999-06-21
30,Consulting,Virginia,USA,2000-01-05
40,Finance,Virginia,USA,2001-03-15

~
~
~
~
~
"demo11.ctl" 18L, 360C written
[oracle@jy ~]$ sqlldr userid=jy/jy control=demo11.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 12:55:32 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 5

在demo11.log日志文件中可以看到:

Record 5: Rejected - Error on table DEPT, column DEPTNO.
Column not found before end of logical record (use TRAILING NULLCOLS)

在此,SQLLDR告诉你,没等处理完所有列,记录中就没有数据了.这种情况下,解决方案很简单.实际上,SQLLDR甚至已经告诉了我们该怎么做:这就是使用TRAILING NULLCOLS.这样一来,如果输入记录中不存在某一列的数据,SQLLDR就会为该列绑定一个NULL值.在这种情况下,增加TRAILING NULLCOLS会导致绑定变量:ENTIRE_LINE成为NULL,所以再尝试这个控制文件：

[oracle@jy ~]$ vi demo11.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated",
LAST_UPDATED date 'yyyy-mm-dd'
)
BEGINDATA
10,Sales,Virginia,USA,2000-05-01
20,Accounting,Virginia,USA,1999-06-21
30,Consulting,Virginia,USA,2000-01-05
40,Finance,Virginia,USA,2001-03-15

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo11.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 13:00:49 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 5

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED
---------- -------------- -------------- ------------------------------ --------------
10 SALES VIRGINIA 10SalesVirginia2000-05-01 01-5月 -00
20 ACCOUNTING VIRGINIA 20AccountingVirginia1999-06-21 21-6月 -99
30 CONSULTING VIRGINIA 30ConsultingVirginia2000-01-05 05-1月 -00
40 FINANCE VIRGINIA 40FinanceVirginia2001-03-15 15-3月 -01

之所以可以这样做,原因在于SQLLDR构建其INSERT语句的做法.SQLLDR会查看前面的控制文件,并看到控制文件中的DEPTNO, DNAME,LOC,LAST_UPDATED和ENTIRE_LINE这几列.它会根据这些列建立5个绑定变量.通常,如果没有任何函数,所建立的 INSERT语句就是:

INSERT INTO DEPT ( DEPTNO, DNAME, LOC, LAST_UPDATED, ENTIRE_LINE )

VALUES (&DEPTNO,&DNAME,&LOC,&LAST_UPDATED,&ENTIRE_LINE);

然后再解析输入流,将值赋给相应的绑定变量,然后执行语句.如果使用函数,SQLLDR会把这些函数结合到INSERT语句中.在上 一个例子中,SQLLDR建立的INSERT语句如下所示：

INSERT INTO T (DEPTNO, DNAME, LOC, LAST_UPDATED, ENTIRE_LINE)
VALUES ( &DEPTNO, upper(&dname), upper(&loc), &last_updated,
&deptno||&dname||&loc||&last_updated );

然后再做好准备,把输入绑定到这个语句,再执行语句.所以SQL中能做的事情都可以结合到SQLLDR脚本中.由于SQL中增加了 CASE语句,所以这样做不仅功能极为强大,而且相当容易.例如,假设你的输入文件有以下格式的日期:
HH24:MI:SS:只有一个时间;日期默认为SYSDATE.
DD/MM/YYYY:只有一个日期;时间默认为午夜0点.
HH24:MI:SS DD/MM/YYYY:日期和时间都要显式提供。
可以使用如下的一个控制文件:

[oracle@jy ~]$ vi demo12.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated",
LAST_UPDATED
"case
when length(:last_updated)>9
then to_date(:last_updated,'yyyy-mm-dd hh24:mi:ss')
when instr(:last_updated,':')>0
then to_date(:last_updated,'hh24:mi:ss')
else to_date(:last_updated,'yyyy-mm-dd')
end"
)
BEGINDATA
10,Sales,Virginia,USA,2000-05-01 12:03:03
20,Accounting,Virginia,USA,02:23:54
30,Consulting,Virginia,USA,2000-01-05 01:24:00
40,Finance,Virginia,USA,2001-03-15

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo12.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 13:06:49 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 5

jy@JINGYONG> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

会话已更改。

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED
---------- -------------- -------------- --------------------------------------- ---------------------
10 SALES VIRGINIA 10SalesVirginia2000-05-01 12:03:03 2000-05-01 12:03:03
20 ACCOUNTING VIRGINIA 20AccountingVirginia02:23:54 2013-10-01 02:23:54
30 CONSULTING VIRGINIA 30ConsultingVirginia2000-01-05 01:24:00 2000-01-05 01:24:00
40 FINANCE VIRGINIA 40FinanceVirginia2001-03-15 2001-03-15 00:00:00

如何加载有内嵌换行符的数据
过去,如果要加载可能包含换行符的自由格式的数据,这对于SQLLDR来说很成问题.换行符是SQLLDR的默认行结束符,要加载有内嵌换行符的数据有以下方法:
加载数据,其中用非换行符的其他字符来表示换行符(例如,在文本中应该出现换行符的位置上放上串\n),并在加载时使用一个SQL函数用一个CHR(10)替换该文本.

在INFILE指令上使用FIX属性,加载一个定长平面文件.

在INFILE指令上使用VAR属性,加载一个定宽文件,在该文件使用的格式中,每一行的前几个字节指定了这一行的长度(字节数 ).

在INFILE指令上使用STR属性,加载一个变宽文件,其中用某个字符序列来表示行结束符,而不是用换行符来表示.

使用一个非换行符的字符
如果你能对如何生成输入数据加以控制,这就是一种很容易的方法.如果创建数据文件时能很容易地转换数据,这种方法就能奏 效.其思想是,就数据加载到数据库时对数据应用一个SQL函数,用某个字符串来替换换行符.下面向DEPT表再增加另一个列:

jy@JINGYONG> alter table dept add comments varchar2(4000);

表已更改。

将使用这一列来加载文本,下面是一个有内联数据的示例控制文件:

[oracle@jy ~]$vi demo13.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated",
LAST_UPDATED
"case
when length(:last_updated)>9
then to_date(:last_updated,'yyyy-mm-dd hh24:mi:ss')
when instr(:last_updated,':')>0
then to_date(:last_updated,'hh24:mi:ss')
else to_date(:last_updated,'yyyy-mm-dd')
end",
COMMENTS "replace(:comments,'\\n',chr(10))"
)
BEGINDATA
10,Sales,Virginia,USA,2000-05-01 12:03:03,This is the Sales\nOffice in Virginia
20,Accounting,Virginia,USA,02:23:54,This is the Accountin\nOffice in Virginia
30,Consulting,Virginia,USA,2000-01-05 01:24:00,This is the Consulting\nOffice in Virginia
40,Finance,Virginia,USA,2001-03-15,This is the Finance\nOffice in Virginia

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo13.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 14:45:48 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 5

注意,调用中必须使用\\n来替换换行符,而不只是\n.这是因为\n会被SQLLDR识别为一个换行符,而且SQLLDR会把它转换为一 个换行符,而不是一个两字符的串.利用以上控制文件执行SQLLDR时,DEPT表中将加载以下数据:

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED COMMENTS
--------------------------------------------------------------------------------------------------------------- -------------------------------------------------
10 SALES VIRGINIA 10SalesVirginia2000-05-01 12:03:03 2000-05-01 12:03:03 This is the Sales Office in Virginia

20 ACCOUNTING VIRGINIA 20AccountingVirginia02:23:54 2013-10-01 02:23:5 This is the Accountin Office in Virginia

30 CONSULTING VIRGINIA 30ConsultingVirginia2000-01-05 01:24:00 2000-01-05 01:24:00 This is the Consulting Office in Virginia

40 FINANCE VIRGINIA 40FinanceVirginia2001-03-15 2001-03-15 00:00:00 This is the Finance Office in Virginia

使用IFX属性
另一种可用的方法是使用FIX属性.如果使用这种方法,输入数据必须出现在定长记录中.每个记录与输入数据集中所有其他记 录的长度都相同,即有相同的字节数.对于固定位置的数据,使用FIX属性就特别适合.这些文件通常是定长输入文件.使用自由格式的定界数据时,则不太可能是一个定长文件,因为这些文件通常是变长的(这正是定界文件的关键:每一行不会不必要地过长).
使用FIX属性时,必须使用一个INFILE子句,因为FIX属性是INFILE的一个选项.另外,如果使用这个选项,数据必须在外部存储 ,而并非存储在控制文件本身.因此,假设有定长的输入记录,可以使用如下的一个控制文件:

[oracle@jy ~]$ vi demo14.ctl
LOAD DATA
INFILE demo14.dat "fix 80"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated",
LAST_UPDATED
"case
when length(:last_updated)>9
then to_date(:last_updated,'yyyy-mm-dd hh24:mi:ss')
when instr(:last_updated,':')>0
then to_date(:last_updated,'hh24:mi:ss')
else to_date(:last_updated,'yyyy-mm-dd')
end",
COMMENTS
)

[oracle@jy ~]$ vi demo14.dat
10,Sales,Virginia,USA,2000-05-01 12:03:03,This is the Sales\nOffice in Virginia
20,Accounting,Virginia,USA,02:23:54,This is the Accountin\nOffice in Virginia\n
30,Consulting,Virginia,USA,2000-01-05 01:24:00,This is the Consulting\nOffice\n
40,Finance,Virginia,USA,2001-03-15,This is the Finance\nOffice in Virginia \n

这个文件指定了一个输入数据文件(domo14.dat),这个文件中每个记录有80字节,这包括尾部的换行符(每个记录最后可能有换行符,也可能没有).在这种情况下,输入数据文件中的换行符并不是特殊字符.这只是要加载(或不加载)的另一个字符而已.要知道:记录的最后如果有换行符,它会成为这个记录的一部分.为了充分理解这一点,我们需要一个实用程序将文件的内容转储在屏幕上,以便我们看到文件中到底有什么.使用Linux(或任何Unix版本)利用od就很容易做到,这个程序可以将文件以八进制(和其他格式)转储到屏幕上.我们将使用下面的demo.dat文件.注意以下输入中的第一列实际上是八进制,所以第2行上的数字0000012是一个八进制数,不是十进制数10.由此我们可以知道所查看的文件中有哪些字节.我对这个输出进行了格式化,使得每行显示10个字符(使用-w10),所以0,12,24和36实际上就是0,10,20和30.

[oracle@jy ~]$ od -c -w10 -v demo14.dat
0000000 a l e s , V i r g i
0000012 n i a , U S A , 2 0
0000024 0 0 - 0 5 - 0 1 1
0000036 2 : 0 3 : 0 3 , T h
0000050 i s i s t h e
0000062 S a l e s \ n O f f
0000074 i c e i n V i r
0000106 g i n i a \n 2 0 , A
0000120 c c o u n t i n g ,
0000132 V i r g i n i a , U
0000144 S A , 0 2 : 2 3 : 5
0000156 4 , T h i s i s
0000170 t h e A c c o u n
0000202 t i n \ n O f f i c
0000214 e i n V i r g i
0000226 n i a \n 3 0 , C o n
0000240 s u l t i n g , V i
0000252 r g i n i a , U S A
0000264 , 2 0 0 0 - 0 1 - 0
0000276 5 0 1 : 2 4 : 0 0
0000310 , T h i s i s t
0000322 h e C o n s u l t
0000334 i n g \ n O f f i c
0000346 e i n V i r g i
0000360 n i a \n 4 0 , F i n
0000372 a n c e , V i r g i
0000404 n i a , U S A , 2 0
0000416 0 1 - 0 3 - 1 5 , T
0000430 h i s i s t h e
0000442 F i n a n c e \ n
0000454 O f f i c e i n
0000466 V i r g i n i a \n
0000477

注意,在这个输入文件中,并没有用换行符(\n)来指示SQLLDRE记录在哪里结束;这里的换行符只是要加载的数据而已.SQLLDR使用FIX宽度(80字节)来得出要读取多少数据.实际上,如果查看输入数据,可以看到,输入文件中提供给SQLLDR的记录甚至并非以\n结束.部门20的记录之前的字符是一个空格,而不是换行符.

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo14.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 15:03:05 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 4

我们知道了每个记录的长度为80字节,现在就可以用前面有FIX80子句的控制文件来加载这些数据了.完成加载后,可以看到以 下结果:

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED COMMENTS
--------------------------------------------------------------------------------------------------------------- -------------------
10 SALES VIRGINIA10SalesVirginia2000-05-01 12:03:03 2000-05-01 12:03:03 This is the Sales\nOffice in Virginia

20 ACCOUNTING VIRGINIA20AccountingVirginia02:23:54 2013-10-01 02:23:54 This is the Accountin\nOffice in Virginia\n

30 CONSULTING VIRGINIA30ConsultingVirginia2000-01-05 01:24:00 2000-01-05 01:24:00 This is the Consulting\nOffice\n

40 FINANCE VIRGINIA40FinanceVirginia2001-03-15 2001-03-15 00:00:00 This is the Finance\nOffice in Virginia \n

你可能需要截断这个数据,因为尾部的空白符会保留.可以在控制文件中使用TRIM内置SQL函数来完成截断.
Linux上 ,行结束标记就是\n(SQL中的CHR(10)).在Windows NT上,行结束标记却是\r\n(SQL中的CHR(13)||CHR(10)).一般来讲,如果使用FIX方法,就要确保是在同构平台上创建和加载文件(Linux上创建,Linux上加载;或者Windows上创建,Windows上加载)

使用VAR属性
要加载有内嵌换行符的数据,另一种方法是使用VAR属性.使用这种格式时,每个记录必须以某个固定的字节数开始,这表示这 个记录的总长度.通过使用这种格式,可以加载包含内嵌换行符的变长记录,但是每个记录的开始处必须有一个记录长度字段. 因此,如果使用如下的一个控制文件:

[oracle@jy ~]$ vi demo15.ctl
LOAD DATA
INFILE 'demo15.dat' "var 3"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated",
LAST_UPDATED
"case
when length(:last_updated)>9
then to_date(:last_updated,'yyyy-mm-dd hh24:mi:ss')
when instr(:last_updated,':')>0
then to_date(:last_updated,'hh24:mi:ss')
else to_date(:last_updated,'yyyy-mm-dd')
end",
COMMENTS
)

VAR 3指出每个输入记录的前3个字节是输入记录的长度,如果取以下数据文件：

[oracle@jy ~]$ cat demo15.dat
07910,Sales,Virginia,USA,2000-05-01 12:03:03,This is the Sales Office in Virginia
07820,Accounting,Virginia,USA,02:23:54,This is the Accounting Office in Virginia
08930,Consulting,Virginia,USA,2000-01-05 01:24:00,This is the Consulting Office in Virginia
07440,Finance,Virginia,USA,2001-01-15,This is the Finance Office in Virginia

可以使用该控制文件来加载.在输入数据文件中有4行数据.第一行从079开始,这说明接下来79字节是第一个输入记录.这79字 节包括单词Virginia后的结束换行符.下一行从078开始,这一行有78字节的文本,依此类推.使用这种格式数据文件,可以很 容易地加载有内嵌换行符的数据.
同样,如果在使用Linux和Windows(前面的例子都在Linux上完成,其中换行符只是一个字符长),就必须调整每个记录的长度字 段.在Windows上,前例.dat文件中的长度字段应该是80,79,90和75.

使用STR属性 要加载有内嵌换行符的数据,这可能是最灵活的一种方法.通过使用STR属性,可以指定一个新的行结束符(或字符序列).就能创建一个输入数据文件,其中每一行的最后有某个特殊字符,换行符不再有特殊含义. 我更喜欢使用字符序列,通常会使用某个特殊标记,然后再加一个换行符.这样,在一个文本编辑器或某个实用程序中查看输入 数据时,就能很容易地看到行结束符,因为每个记录的最后仍然有一个换行符.STR属性以十六进制指定,要得到所需的具体十 六进制串,最容易的方法是使用SQL和UTL_RAW来生成十六进制串.例如,假设使用的是Linux平台,行结束标记是CHR(10)(换行 ),我们的特殊标记字符是一个管道符号(|),则可以写为:

jy@JINGYONG>select utl_raw.cast_to_raw('|'||chr(10)) from dual;

UTL_RAW.CAST_TO_RAW('|'||CHR(1
--------------------------------------------------------------------------------
7C0A

由此可知,在Linux上我们需要使用的STR是X’7C0A’.
在Windows上,要使用UTL_RAW.CAST_TO_RAW(‘|’||chr(13)||chr(10))
为了使用这个方法,要有以下控制文件:

[oracle@jy ~]$ vi demo16.ctl
LOAD DATA
INFILE demo16.dat "str X'7C0A'"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated",
LAST_UPDATED
"case
when length(:last_updated)>9
then to_date(:last_updated,'yyyy-mm-dd hh24:mi:ss')
when instr(:last_updated,':')>0
then to_date(:last_updated,'hh24:mi:ss')
else to_date(:last_updated,'yyyy-mm-dd')
end",
COMMENTS
)

因此,如果输入数据如下:

[oracle@jy ~]$ cat demo16.dat
10,Sales,Virginia,USA,2000-05-01 12:03:03,This is the Sales Office in Virginia|
20,Accounting,Virginia,USA,02:23:54,This is the Accounting Office in Virginia|
30,Consulting,Virginia,USA,2000-01-05 01:24:00,This is the Consulting Office in Virginia|
40,Finance,Virginia,USA,2001-01-15,This is the Finance Office in Virginia|

其中,数据文件中的每个记录都以|\n结束,前面的控制文件就会正确地加载这些数据.

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo16.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Oct 4 07:45:30 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 4

jy@JINGYONG> select * from dept;

DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED COMMENTS
--------------------------------------------------------------------------------------------------------------- -----------------
10 SALES VIRGINIA 10SalesVirginia2000-05-01 12:03:03 2000-05-01 12:03:03 This is the Sales Office in Virginia

20 ACCOUNTING VIRGINIA 20AccountingVirginia02:23:54 2013-10-01 02:23:54 This is the Accounting Office in Virginia

30 CONSULTING VIRGINIA 30ConsultingVirginia2000-01-05 01:24:00 2000-01-05 01:24:00 This is the Consulting Office in Virginia

40 FINANCE VIRGINIA 40FinanceVirginia2001-01-15 2001-01-15 00:00:00 This is the Finance Office in Virginia

如果加载LOB
现在来考虑在LOB的一些方法.这不是一个LONG或LONG RAW字段,而是更可取的数据类型BLOB和CLOB.这些数据类型是Oracle 8.0及以后版本中引入的,与遗留的LONG和LONG RAW类型相比,它们支持更丰富的接口/功能集.

将分析两种加载这些字段的方法:SQLLDR和PL/SQL.
通过PL/SQL加载LOB
DBMS_LOB包的入口点为LoadFromFile,LoadBLOBFromFile和LoadCLOBFromFile.通过这些过程,我们可以使用一个BFILE(用于读取操作系统文件)来填充数据库中的BLOB或CLOB.LoadFromFile和LoadBLOBFromFile例程之间没有显著的差别,只不过后者会返回一些OUT参数,指示已经向BLOB列中加载了多少数据.不过,LoadCLOBFromFile例程还提供了一个突出的特性:字符集转换.使用LoadCLOBFromFile时,我们可以告诉数据库:这个文件将以另外某种字符集(不同于数据库正在使用的字符集)来加载,而且要执行必要的字符集转换.例如,可能有一个UTF8兼容的数据库,但是收到的要加载的文件却以WE8ISO8859P1字符集编码,或反之利用这个函数就能成功地加载这些文件.

要使用这些过程,需要在数据库中创建一个DIRECTORY对象.这个对象允许我们创建并打开BFILE(BFILE指向文件系统上数据库 服务器能访问的一个现有文件).DBMS_LOB包完全在服务器中执行.它只能看到服务器能看到的文件系统.特别是,如果你通过 网络访问Oracle,DBMS_LOB包将无法看到你的本地文件系统.

所以,需要先在数据库中创建一个DIRECTORY对象.

jy@JINGYONG> create or replace directory dir1 as '/home/oracle';

目录已创建。

jy@JINGYONG> create or replace directory "dir2" as '/home/oracle';

目录已创建。

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH
------ --------------- -----------------
SYS dir2 /home/oracle
SYS DIR1 /home/oracle

我们创建的dir1在数据字典中为大写

下面,将一些数据加载到BLOB或CLOB中:

jy@JINGYONG> create table demo
2 (id int primary key,
3 theClob clob
4 );

表已创建。

[oracle@jy ~]$ echo 'Hello World!'>/home/oracle/demo.txt

jy@JINGYONG> declare
2 l_clob clob;
3 l_bfile bfile;
4 begin
5 insert into demo values(1,empty_clob()) returning theclob into l_clob;
6 l_bfile:=bfilename('DIR1','demo.txt');
7 dbms_lob.fileopen(l_bfile);
8 dbms_lob.loadfromfile(l_clob,l_bfile,dbms_lob.getlength(l_bfile));
9 dbms_lob.fileclose(l_bfile);
10 end;
11 /

PL/SQL 过程已成功完成。

jy@JINGYONG> select dbms_lob.getlength(theClob), theClob from demo;

DBMS_LOB.GETLENGTH(THECLOB) THECLOB
--------------------------- ----------------------------------------------------
13 Hello World!

在第5行我们在表中创建了一行,将CLOB设置为一个EMPTY_CLOB(),并从一个调用获取其值.除了临时LOB外,其余的LOB都住在数据库中,如果没有指向一个临时LOB的指针,或者指向一个已经在数据库中的LOB,将无法写至LOB变量.EMPTY_CLOB()不是一个NULL CLOB;而是指向一个空结构的合法指针(非NULL),它还有一个作用,可以得到一个LOB定位器,指向已锁定行中的数据.如果要选择这个值,而没有锁定底层的行,写数据就会失败,因为LOB在写之前必须锁定(不同于其他结构化数据).通过插入一行,当然我们也就锁定了这一行.如果我们要修改一个现有的行而不是插入新行,则可以使用SELECT FOR UPDATE来获取和锁定这一行.

在第6行上,我们创建了一个BFILE对象.注意,这里DIR1用的是大写,稍后就会看到,这是一个键.这是因为我们向BFILENAME() 传入了一个对象的名称,而不是对象本身.因此,必须确保这个名称与Oracle所存储的对象名称大小写匹配.

第7行打开了LOB,以便读取.

在第8行将操作系统文件/home/oracle/demo.txt的完整内容加载到刚插入的LOB定位器.这里使用DBMS_LOB.GETLENGTH()告诉LOADFROMFILE()例程要加载多少字节的BFILE(这里就是要加载全部字节).
最后,在第9行我们关闭了所打开的BFILE,CLOB已加载.

如果需要在加载文件的同时处理文件的内容,还可以在BFILE上使用DBMS_LOB.READ来读取数据.如果读取的数据实际上是文本,而不是RAW,那么使用UTL_RAW.CAST_TO_VARCHAR2会很方便.然后可以使用DBMS_LOB.WRITE或WRITEAPPEND将数据放入一个CLOB或BLOB.

通过SQLLDR加载LOB数据
现在我们来分析如何通过SQLLDR向LOB加载数据.对此方法不止一种,但是我们主要讨论两种最常用的方法:
数据内联在其他数据中。
数据外联存储(在外部存储),输入数据包含一个文件名,指示该行要加载的数据在哪个文件中.在SQLLDR术语中,这也称为二级数据文件(secondary data file，SDF).
先从内联数据谈起.
加载内联的LOB数据,这些LOB通常内嵌有换行符和其他特殊字符.

下面先来修改dept表,使COMMENTS列是一个CLOB而不是一个大的VARCHAR2字段:

jy@JINGYONG> truncate table dept;

表被截断。

jy@JINGYONG> alter table dept drop column comments;

表已更改。

jy@JINGYONG> alter table dept add comments clob;

表已更改。

例如,假设有一个数据文件(demo17.dat),它有以下内容:

[oracle@jy ~]$ cat demo17.dat
10,Sales,Virginia,USA,2000-05-01 12:03:03,This is the Sales Office in Virginia|
20,Accounting,Virginia,USA,02:23:54,This is the Accounting Office in Virginia|
30,Consulting,Virginia,USA,2000-01-05 01:24:00,This is the Consulting Office in Virginia|
40,Finance,Virginia,USA,2001-01-15,"This is the Finance
Office in Virginia, it has embedded commas and is
much longer than the other comments field. If you
feel the need to add double quoted text in here like
this: ""You will need to double up those quotes!"" to
preserve them in the string. This field keeps going for up to
1000000 bytes (because of the control file definition I used)
or until we hit the magic end of record marker,
the | followed by an end of line - it is right here ->"|

每个记录最后都是一个管道符号(|),后面是行结束标记.部门40的文本比其他部门的文本长得多,有多个换行符,内嵌的引号以及逗号.给定这个数据文件,可以创建一个如下的控制文件:

[oracle@jy ~]$ cat demo17.ctl
LOAD DATA
INFILE demo17.dat "str X'7C0A'"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated",
LAST_UPDATED
"case
when length(:last_updated)>9
then to_date(:last_updated,'yyyy-mm-dd hh24:mi:ss')
when instr(:last_updated,':')>0
then to_date(:last_updated,'hh24:mi:ss')
else to_date(:last_updated,'yyyy-mm-dd')
end",
COMMENTS char(1000000)
)

注意:这个例子在Linux上执行,Linux平台上行结束标记长度为1字节,因此可以使用以上控制文件中的STR设置.在Windows上,STR设置则必须是’7C0D0A’.

要加载这个数据文件,我们在COMMENTS列上指定了CHAR(1000000),因为SQLLDR默认所有人们字段都为CHAR(255).CHAR (1000000)则允许SQLLDR处理多达1,000,000字节的输入文本.可以把这个长度值设置为大于输入文件中任何可能文本块的大小.通过查看所加载的数据,可以看到以下结果:

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo17.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Oct 4 09:08:31 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 1
Commit point reached - logical record count 2
Commit point reached - logical record count 3
Commit point reached - logical record count 4

SQL> set long 999;
SQL> select comments from jy.dept;

COMMENTS
--------------------------------------------------------------------------------
This is the Sales Office in Virginia
This is the Accounting Office in Virginia
This is the Consulting Office in Virginia
This is the Finance
Office in Virginia, it has embedded commas and is
much longer than the other comments field. If you
feel the need to add double quoted text in here like
this: "You will need to double up those quotes!" to
preserve them in the string. This field keeps going for up to
1000000 bytes (because of the control file definition I used)
or until we hit the magic end of record marker,
the | followed by an end of line - it is right here ->

这里可以观察到:原来重复两次的引号不再重复,SQLLDR去除了在此放置的额外的引号.

加载外联的LOB数据,可能要把包含有一些文件名的数据文件加载在LOB中,而不是让LOB数据与结构化数据混在一起,这种情况 很常见.这提供了更大程度的灵活性,因为提供给SQLLDR的数据文件不必使用上述的4种方法之一来避开输入数据中的内嵌换行 符问题,而这种情况在大量的文本或二进制数据中会频繁出现.SQLLDR称这种额外的数据文件为LOBFILE.

SQLLDR还可以支持加载结构化数据文件(指向另外单独一个数据文件).我们可能告诉SQLLDR如何从另外这个文件分析LOB数据 ,这样就可以加载其中的一部分作为结构化数据中的每一行.这种模式的用途很有限.SQLLDR把这种外部引用的文件称为复杂二级数据文件(complex secondary data file).

LOBFILE是一种相对简单的数据文件,旨在简化LOB加载.在LOBFILE中,没有记录的概念,因此换行符不会成为问题,正是这个 性质使得LOBFILE与主要数据文件有所区别.在LOBFILE中,数据总是采用以下某种格式:
定长字段(例如,从LOBFILE加载字节100到1,000)
定界字段(以某个字符结束,或者用某个字符括起)
长度/值对,这是一个变长字段

其中最常见的类型是定界字段,实际上就是以一个文件结束符(EOF)结束.一般来讲,可能有这样一个目录,其中包含你想加载到LOB列中的文件,每个文件都要完整地放在一个BLOB中.此时,就可以使用带TERMINATED BY EOF子句的LOBFILE语句.

假设我们有一个目录,其中包含想要加载到数据库中的文件.我们想加载文件的OWNER,文件的TIME_STAMP,文件的NAME以及文 件本身.要加载数据的表如下所示:

jy@JINGYONG> create table lob_demo
2 (owner varchar2(255),
3 time_stamp date,
4 filename varchar2(255),
5 data blob
6 );

表已创建。

在Linux上使用一个简单的ls –l来捕获输出(或者在Windows上使用dir/q/n),我们就能生成输入文件,并使用如下的一个控制文件加载:

[oracle@jy ~]$ cat demo19.ctl
LOAD DATA
INFILE *
INTO TABLE LOB_DEMO
REPLACE
(owner position(14:28),
time_stamp position(36:46) date "MM DD HH24:MI",
filename position(48:100),
data LOBFILE(filename) TERMINATED BY EOF
)
BEGINDATA
-rw-r--r-- 1 oracle oinstall 18432 10 3 10:42 bifile.bbd
-rw-r--r-- 1 oracle oinstall 1 10 3 12:50 demo10.bad
-rw-r--r-- 1 oracle oinstall 342 10 3 12:50 demo10.ctl
-rw-r--r-- 1 oracle oinstall 1948 10 3 12:50 demo10.log
-rw-r--r-- 1 oracle oinstall 144 10 3 12:59 demo11.bad
-rw-r--r-- 1 oracle oinstall 377 10 3 13:00 demo11.ctl
-rw-r--r-- 1 oracle oinstall 1913 10 3 13:00 demo11.log
-rw-r--r-- 1 oracle oinstall 89 10 3 13:06 demo12.bad
-rw-r--r-- 1 oracle oinstall 582 10 3 13:05 demo12.ctl
-rw-r--r-- 1 oracle oinstall 2147 10 3 13:06 demo12.log
-rw-r--r-- 1 oracle oinstall 790 10 3 14:45 demo13.ctl
-rw-r--r-- 1 oracle oinstall 2289 10 3 14:45 demo13.log
-rw-r--r-- 1 oracle oinstall 80 10 3 15:01 demo14.bad
-rw-r--r-- 1 oracle oinstall 441 10 3 15:00 demo14.ctl
-rw-r--r-- 1 oracle oinstall 320 10 3 15:02 demo14.dat
-rw-r--r-- 1 oracle oinstall 2230 10 3 15:03 demo14.log
-rw-r--r-- 1 oracle oinstall 332 10 4 05:42 demo15_bak.dat
-rw-r--r-- 1 oracle oinstall 442 10 4 05:31 demo15.ctl
-rw-r--r-- 1 oracle oinstall 332 10 4 05:45 demo15.dat
-rw-r--r-- 1 oracle oinstall 256 10 4 05:36 demo15.dat.bak
-rw-r--r-- 1 oracle oinstall 2229 10 4 05:45 demo15.log
-rw-r--r-- 1 oracle oinstall 3 10 4 07:44 demo16.bad
-rw-r--r-- 1 oracle oinstall 446 10 4 07:44 demo16.ctl
-rw-r--r-- 1 oracle oinstall 324 10 4 07:45 demo16.dat
-rw-r--r-- 1 oracle oinstall 2235 10 4 07:45 demo16.log
-rw-r--r-- 1 oracle oinstall 487 10 4 09:07 demo17.ctl
-rw-r--r-- 1 oracle oinstall 741 10 4 09:02 demo17.dat
-rw-r--r-- 1 oracle oinstall 2321 10 4 09:08 demo17.log
-rw-r--r-- 1 oracle oinstall 213 10 4 09:05 demo18.ctl
-rw-r--r-- 1 oracle oinstall 665 10 4 09:05 demo18.dat
-rw-r--r-- 1 oracle oinstall 1860 10 4 09:06 demo18.log
-rw-r--r-- 1 oracle oinstall 1 10 3 11:43 demo1.bad
-rw-r--r-- 1 oracle oinstall 177 10 3 11:43 demo1.ctl
-rw-r--r-- 1 oracle oinstall 1648 10 3 11:43 demo1.log
-rw-r--r-- 1 oracle oinstall 1 10 3 12:01 demo2.bad
-rw-r--r-- 1 oracle oinstall 214 10 3 12:00 demo2.ctl
-rw-r--r-- 1 oracle oinstall 1648 10 3 12:01 demo2.log
-rw-r--r-- 1 oracle oinstall 2 10 4 07:59 demo3.bad
-rw-r--r-- 1 oracle oinstall 121 10 4 07:59 demo3.ctl
-rw-r--r-- 1 oracle oinstall 1525 10 4 07:59 demo3.log
-rw-r--r-- 1 oracle oinstall 18 10 4 08:07 demo4.bad
-rw-r--r-- 1 oracle oinstall 141 10 4 08:11 demo4.ctl
-rw-r--r-- 1 oracle oinstall 1657 10 4 08:11 demo4.log
-rw-r--r-- 1 oracle oinstall 137 10 3 12:20 demo5.ctl
-rw-r--r-- 1 oracle oinstall 1560 10 3 12:21 demo5.log
-rw-r--r-- 1 oracle oinstall 175 10 3 12:23 demo6.ctl
-rw-r--r-- 1 oracle oinstall 1641 10 3 12:23 demo6.log
-rw-r--r-- 1 oracle oinstall 174 10 3 12:25 demo7.ctl
-rw-r--r-- 1 oracle oinstall 1641 10 3 12:25 demo7.log
-rw-r--r-- 1 oracle oinstall 184 10 3 12:28 demo8.ctl
-rw-r--r-- 1 oracle oinstall 1640 10 3 12:29 demo8.log
-rw-r--r-- 1 oracle oinstall 1 10 3 12:47 demo9.bad
-rw-r--r-- 1 oracle oinstall 290 10 3 12:47 demo9.ctl
-rw-r--r-- 1 oracle oinstall 1809 10 3 12:47 demo9.log
-rw-r--r-- 1 oracle oinstall 13 10 4 08:22 demo.txt
-rw-r--r-- 1 oracle oinstall 547 10 3 10:44 log.bbd

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo19.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Oct 4 10:26:54 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 56

现在,运行SQLLDR之后检查LOB_DEMO表的内容,会发现以下结果:

SQL> select owner, time_stamp, filename, dbms_lob.getlength(data) from jy.lob_demo;

OWNER TIME_STAMP FILENAME DBMS_LOB.GETLENGTH(DATA)
----------------------------- --------------------------------------------
oracle oinstall 2013-10-4 9 demo17.log 2321
oracle oinstall 2013-10-4 9 demo18.dat 665
oracle oinstall 2013-10-4 9 demo18.log 1860
oracle oinstall 2013-10-3 1 demo1.log 1648
oracle oinstall 2013-10-3 1 bifile.bbd 18432
oracle oinstall 2013-10-3 1 demo10.bad 1
oracle oinstall 2013-10-3 1 demo10.ctl 342
oracle oinstall 2013-10-3 1 demo10.log 1948
oracle oinstall 2013-10-3 1 demo11.bad 144
oracle oinstall 2013-10-3 1 demo11.ctl 377
oracle oinstall 2013-10-3 1 demo12.bad 89
oracle oinstall 2013-10-3 1 demo12.ctl 582
oracle oinstall 2013-10-3 1 demo14.bad 80
oracle oinstall 2013-10-3 1 demo14.ctl 441
oracle oinstall 2013-10-4 5 demo15.dat.bak 256
oracle oinstall 2013-10-4 7 demo16.bad 3
oracle oinstall 2013-10-4 9 demo18.ctl 213
oracle oinstall 2013-10-3 1 demo1.bad 1
oracle oinstall 2013-10-3 1 demo1.ctl 177
oracle oinstall 2013-10-3 1 demo2.bad 1
oracle oinstall 2013-10-3 1 demo2.ctl 214
oracle oinstall 2013-10-4 7 demo3.bad 2
oracle oinstall 2013-10-4 7 demo3.ctl 121
oracle oinstall 2013-10-4 8 demo4.bad 18
oracle oinstall 2013-10-3 1 demo5.ctl 137
oracle oinstall 2013-10-3 1 demo6.ctl 175
oracle oinstall 2013-10-3 1 demo8.ctl 184
oracle oinstall 2013-10-3 1 demo9.bad 1
oracle oinstall 2013-10-4 8 demo.txt 13
oracle oinstall 2013-10-3 1 demo11.log 1913
oracle oinstall 2013-10-3 1 demo12.log 2147
oracle oinstall 2013-10-3 1 demo13.ctl 790
oracle oinstall 2013-10-3 1 demo13.log 2289
oracle oinstall 2013-10-3 1 demo14.dat 320
oracle oinstall 2013-10-3 1 demo14.log 2230
oracle oinstall 2013-10-4 5 demo15_bak.dat 332
oracle oinstall 2013-10-4 5 demo15.ctl 442
oracle oinstall 2013-10-4 5 demo15.dat 332
oracle oinstall 2013-10-4 7 demo16.ctl 446
oracle oinstall 2013-10-4 5 demo15.log 2229
oracle oinstall 2013-10-4 7 demo16.dat 324
oracle oinstall 2013-10-4 7 demo16.log 2235
oracle oinstall 2013-10-4 9 demo17.ctl 487
oracle oinstall 2013-10-4 9 demo17.dat 741
oracle oinstall 2013-10-3 1 demo2.log 1648
oracle oinstall 2013-10-4 7 demo3.log 1525
oracle oinstall 2013-10-4 8 demo4.ctl 141
oracle oinstall 2013-10-4 8 demo4.log 1657
oracle oinstall 2013-10-3 1 demo5.log 1560
oracle oinstall 2013-10-3 1 demo6.log 1641
oracle oinstall 2013-10-3 1 demo7.ctl 174
oracle oinstall 2013-10-3 1 demo7.log 1641
oracle oinstall 2013-10-3 1 demo8.log 1640
oracle oinstall 2013-10-3 1 demo9.ctl 290
oracle oinstall 2013-10-3 1 demo9.log 1809
oracle oinstall 2013-10-3 1 log.bbd 547

这不光适用于BLOB,也适用于CLOB.以这种方式使用SQLLDR来加载文本文件的目录会很容易.

将LOB数据加载到对象列.既然知道了如何将数据加载到我们自己创建的一个简单表中,可能会发现,有时需要将数据加载到一 个复杂的表中,其中可能有一个包含LOB的复杂对象类型(列).使用图像功能时这种情况最为常见.图像功能使用一个复杂的对象类型ORDSYS.ORDIMAGE来实现,我们需要告诉SQLLDR如何向其中加载数据.

要把一个LOB加载到一个ORDIMAGE类型的列中,首先必须对ORDIMAGE类型的结构有所了解.在SQL*Plus中使用要加载的一个目标表以及该表上的DESCRIBE,可以发现表中有一个名为IMAGE的ORDSYS.ORDIMAGE列,最终我们想在这一列中加载 IMAGE.SOURCE.LOCALDATA,只有安装并配置好interMedia,项目的例子才能正常工作;否则,数据类型ORDSYS.ORDIMAGE将是一 个未知类型:

jy@JINGYONG> create table image_load
2 (id number,
3 name varchar2(255),
4 image ordsys.ordimage
5 );

表已创建。

SQL> desc jy.image_load
Name Type Nullable Default Comments
----- --------------- -------- ------- --------
ID NUMBER Y
NAME VARCHAR2(255) Y
IMAGE ORDSYS.ORDIMAGE Y

SQL> desc ordsys.ordimage
Element Type
-------------------- ----------------
SOURCE ORDSYS.ORDSOURCE
HEIGHT INTEGER
WIDTH INTEGER
CONTENTLENGTH INTEGER
FILEFORMAT VARCHAR2(4000)
CONTENTFORMAT VARCHAR2(4000)
COMPRESSIONFORMAT VARCHAR2(4000)
MIMETYPE VARCHAR2(4000)
INIT FUNCTION
COPY PROCEDURE
PROCESS PROCEDURE
PROCESSCOPY PROCEDURE
SETPROPERTIES PROCEDURE
CHECKPROPERTIES FUNCTION
GETHEIGHT FUNCTION
GETWIDTH FUNCTION
GETFILEFORMAT FUNCTION
GETCONTENTFORMAT FUNCTION
GETCOMPRESSIONFORMAT FUNCTION
GETMETADATA FUNCTION
PUTMETADATA PROCEDURE
SETLOCAL PROCEDURE
CLEARLOCAL PROCEDURE
ISLOCAL FUNCTION
GETUPDATETIME FUNCTION
SETUPDATETIME PROCEDURE
GETMIMETYPE FUNCTION
SETMIMETYPE PROCEDURE
GETCONTENTLENGTH FUNCTION
GETCONTENT FUNCTION
GETBFILE FUNCTION
DELETECONTENT PROCEDURE
GETDICOMMETADATA FUNCTION
SETSOURCE PROCEDURE
GETSOURCE FUNCTION
GETSOURCETYPE FUNCTION
GETSOURCELOCATION FUNCTION
GETSOURCENAME FUNCTION
IMPORT PROCEDURE
IMPORTFROM PROCEDURE
EXPORT PROCEDURE
PROCESSSOURCECOMMAND FUNCTION
OPENSOURCE FUNCTION
CLOSESOURCE FUNCTION
TRIMSOURCE FUNCTION
READFROMSOURCE PROCEDURE
WRITETOSOURCE PROCEDURE
GETPROPERTIES PROCEDURE

SQL> desc ordsys.ordsource
Element Type
-------------------- --------------
LOCALDATA BLOB
SRCTYPE VARCHAR2(4000)
SRCLOCATION VARCHAR2(4000)
SRCNAME VARCHAR2(4000)
UPDATETIME DATE
LOCAL NUMBER
SETLOCAL PROCEDURE
CLEARLOCAL PROCEDURE
ISLOCAL FUNCTION
GETUPDATETIME FUNCTION
SETUPDATETIME PROCEDURE
SETSOURCEINFORMATION PROCEDURE
GETSOURCEINFORMATION FUNCTION
GETSOURCETYPE FUNCTION
GETSOURCELOCATION FUNCTION
GETSOURCENAME FUNCTION
GETBFILE FUNCTION
IMPORT PROCEDURE
IMPORTFROM PROCEDURE
EXPORT PROCEDURE
GETCONTENTLENGTH FUNCTION
GETSOURCEADDRESS FUNCTION
GETLOCALCONTENT FUNCTION
GETCONTENTINTEMPLOB PROCEDURE
DELETELOCALCONTENT PROCEDURE
OPEN FUNCTION
CLOSE FUNCTION
TRIM FUNCTION
READ PROCEDURE
WRITE PROCEDURE
PROCESSCOMMAND FUNCTION

加载这种数据的控制文件可能如下所示:

[oracle@jy ~]$ cat demo20.ctl
LOAD DATA
INFILE *
INTO TABLE image_load
REPLACE
FIELDS TERMINATED BY ','
(ID,
NAME,
file_name FILLER,
IMAGE column object
(
SOURCE column object
(
LOCALDATA LOBFILE(file_name) TERMINATED BY EOF
NULLIF file_name='NONE'
)
)
)
BEGINDATA
1,psu3,psu3.jpg

这里引入了两个新构造:
COLUMN OBJECT:这会告诉SQLLDR这不是一个列名;而是列名的一部分.它不会映射到输入文件中的一个字段,只是用来构建正 确的对象列引用,从而在加载中使用.在前面的文件中有两个列对象标记,其中一个SOURCE嵌入在另一个SOURCE嵌入在 另一个IMAGE中.因此,根据我们的需要,要使用的列名是IMAGE.SOURCE.LOCALDATA.注意,我们没有加载这两个对象类型 的任何其他属性(例如,IMAGE.HEIGHT,IMAGE.CONTENTLENGTH和IMAGE.SOURCE.SRCTYPE).

NULL IF FILE_NAME = ‘NONE’:这会告诉SQLLDR,如果字段FILE_NAME包含单词NONE,则向对象列中加载一个NULL.

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo20.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Oct 4 10:38:14 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 1

SQL> select * from jy.image_load ;

ID NAME IMAGE
--- ------- -----
1 psu3 <Obje