疯狂

Oracle调优总结 收藏 转载自：

Oracle调优总结 收藏
 /*==========================================================================
 *Author: MartriWang@gmail.com
 *Date:   09/05/2007
 *Description:ORACLE Summary
 *==========================================================================*/
Problem Description:
1.每个表的结构及主键索引情况
2.每个表的count(*)记录是多少
3.对于创建索引的列，索引的类型是什么？count(distinct indexcol)的值是多少？
4.最后一次对表进行分析是在什么时间，分析后，是否又对相关表做过大的操作
5.索引最后一次rebuild，是在什么时间，此后对表的操作类型又是什么状况？索引中浪费的空间是多少？
6.这些表的存储情况，表的存储参数，表空间的类型，存储参数等
7.执行该SQL语句时，系统等候的资源是什么？ Trace SQL语句的执行过程
8.另一台执行相似SQL速度很快的机器上的相关表的如上信息是什么？

一：SQL tuning 类
1:列举几种表连接方式
  hash join/merge join/nest loop(cluster join)/index join

2:不借助第三方工具，怎样查看sql的执行计划
set autotrace on
set autotrace traceonly

explain plan set statement_id = &item_id for &sql;
select * from table(dbms_xplan.display);
http://download-west.oracle.com/ ... /b10752/ex_plan.htm

3:如何使用CBO,CBO与RULE的区别
  在optimizer_mode=choose时,如果表有统计信息（分区表外）,优化器将选择CBO,否则选RBO。

  RBO遵循简单的分级方法学,使用15种级别要点，当接收到查询，优化器将评估使用到的要点数目,
然后选择最佳级别（最少的数量）的执行路径来运行查询。
CBO尝试找到最低成本的访问数据的方法,为了最大的吞吐量或最快的初始响应时间,计算使用不同
的执行计划的成本，并选择成本最低的一个,关于表的数据内容的统计被用于确定执行计划。

4:如何定位重要(消耗资源多)的SQL
  select sql_text
  from v$sql
where disk_reads > 1000 or (executions > 0 and buffer_gets/executions > 30000);

5:如何跟踪某个session的SQL
  exec dbms_system.set_sql_trace_in_session(sid,serial#,&sql_trace);

select sid,serial# from v$session where sid = (select sid from v$mystat where rownum = 1);
  exec dbms_system.set_ev(&sid,&serial#,&event_10046,&level_12,'');

6:SQL调整最关注的是什么
  查看该SQL的response time(db block gets/consistent gets/physical reads/sorts (disk))

7:说说你对索引的认识（索引的结构、对dml影响、为什么提高查询性能）
  b-tree index/bitmap index/function index/patitional index(local/global)
  索引通常能提高select/update/delete的性能,会降低insert的速度,
 
8:使用索引查询一定能提高查询的性能吗？为什么
  索引就是为了提高查询性能而存在的,
如果在查询中索引没有提高性能,
只能说是用错了索引,或者讲是场合不同

9:绑定变量是什么？绑定变量有什么优缺点？
  绑定变量是相对文本变量来讲的,所谓文本变量是指在SQL直接书写查询条件，
这样的SQL在不同条件下需要反复解析,绑定变量是指使用变量来代替直接书写条件，
查询bind value在运行时传递，然后绑定执行。
 
优点是减少硬解析,降低CPU的争用,节省shared_pool
缺点是不能使用histogram,sql优化比较困难

10:如何稳定(固定)执行计划
  query_rewrite_enabled = true
  star_transformation_enabled = true
  optimizer_features_enable = 9.2.0

创建并使用stored outline
  http://download-west.oracle.com/ ... /outlines.htm#26854
 
这个贴子:
http://www.cnoug.org/viewthread.php?tid=27598
 
11:和排序相关的内存在8i和9i分别怎样调整，临时表空间的作用是什么
   8i中sort_area_size/sort_area_retained_size决定了排序所需要的内存
  如果排序操作不能在sort_area_size中完成,就会用到temp表空间

  9i中如果workarea_size_policy=auto时,
  排序在pga内进行,通常pga_aggregate_target的1/20可以用来进行disk sort;
  如果workarea_size_policy=manual时,排序需要的内存由sort_area_size决定

   在执行order by/group by/distinct/union/create index/index rebuild/minus等操作时,
  如果在pga或sort_area_size中不能完成,排序将在临时表空间进行（disk sort）,
  临时表空间主要作用就是完成系统中的disk sort.

12:存在表T(a,b,c,d),要根据字段c排序后取第21—30条记录显示，请给出sql
   create table t(a number(,b number(,c number(,d number();
  /
  begin      
    for i in 1 .. 300 loop
      insert into t values(mod(i,2),i/2,dbms_random.value(1,300),i/4);
    end loop;
  end;
  /
   select * from (select c.*,rownum as rn from (select * from t order by c desc) c) where rn between 21 and 30;
   /
  select * from (select * from test order by c desc) x where rownum < 30
  minus
   select * from (select * from test order by c desc) y where rownum < 20 order by 3 desc
   相比之 minus性能较差

二：数据库基本概念类

1:pctused and pctfree 表示什么含义有什么作用
  pctused与pctfree控制数据块是否出现在freelist中,
pctfree控制数据块中保留用于update的空间,当数据块中的free space小于pctfree设置的空间时,
该数据块从freelist中去掉,当块由于dml操作free space大于pct_used设置的空间时,该数据库块将
被添加在freelist链表中。
 --PCTFREE存储参数
　　PCTFREE存储参数告诉ORACLE什么时候应该将数据块从对象的空闲列表中移出。ORACLE的默认参数是PCTFREE=10；
也就是说，一旦一个INSERT操作使得数据块的90%被使用，这个数据块就从空闲列表(free list)中移出。
　--PCTUSED存储参数
　　PCTUSED存储参数告诉ORACLE什么时候将以前满的数据块加到空闲列表中。当记录从数据表中删除时，数据库的数
据块就有空间接受新的记录，但只有当填充的空间降到PCTUSED值以下时，该数据块才被连接到空闲列表中，才可以往
其中插入数据。PCTUSED的默认值是PCTUSED=40。
 --存储参数规则小结
　　（1）PCTUSED较高意味着相对较满的数据块会被放置到空闲列表中，从而有效的重复使用数据块的空间，但会导致
I/O消耗。PCTUSED低意味着在一个数据块快空的时候才被放置到空闲列表中，数据块一次能接受很多的记录，因此可以
减少I/O消耗，提高性能。
　　（2）PCTFREE的值较大意味着数据块没有被利用多少就从空闲列表中断开连接，不利于数据块的充分使用。PCTFREE
过小的结果是，在更新时可能会出现数据记录迁移(Migration)的情况。(注：数据记录迁移(Migration)是指记录在是
UPDATE操作扩展了一个VARCHAR2类型的列或BLOB列后,PCTFREE参数所指定的空间不够扩展，从而记录被ORACLE强制迁移到
新的数据块，发生这种情况将较严重的影响ORACLE的性能，出现更新缓慢)。
　　（3）在批量的插入、删除或者更新操作之前，先删除该表上的索引，在操作完毕之后在重新建立，这样有助于提高
批量操作的整体速度，并且保证B树索引在操作之后有良好的性能。

---------------------------------------------------------------------------------------------------------------------------------------------------
--MartriWang@gmail.com 17/05/2007--
--表的pctfree和pctused两个参数进行估算的方法

对于不同的应用系统，表的pctfree 和pctused两个参数有不同的设计原则，以下是根据特定的应用系统进行估算的例子，从中可以掌握基本的估算方法。
 
表的存储参数调整，一般情况，设置为pctfree 5 pctused 85即可（缺省为pctfree 10 pctused 40）
1.对于Pctfree参数
除了可以按字段及字段长度估算平均行长外，下面的方面可以根据已有数据分析出平均行长和每块行数
例：
analyze table 病人信息 compute statistics for table for all indexes for all indexed columns;
Select Num_Rows,Blocks,Round(Num_Rows / Blocks) Avg_Rows_Block, Avg_Row_Len From User_Tables Where Table_Name = '病人信息'

    NUM_ROWS BLOCKS AVG_ROWS_BLOCK AVG_ROW_LEN   
 857291 14161 61 117 

对于一般8192的块，实际可用空间为8100左右.
假设以前该表的pctfree为15，改为5后，pctfree减少10，就可以再存入约7行.

相同的1万4千块就可以多存放约10万行数据，
这10万行数据，如果按每块60行算，就可以少占用约1700块(约13M的空间)
如果全表扫描该表的话，少读1700块数据，少106次IO操作(按缺省db_file_multiblock_read_count=16计算)
少占13M的内存

另外，需要考虑的两个因素
1。更新操作时，数据增长量大不大，例如：主要是把状态字段由1改为3，还是把摘要由空改为一段文字
2。并发事务的多少，因为一个事务信息在块中要占用约24Byte，如果有10个并发事务的话，至少额外考虑240Byte的空闲空间。

2.对于Pctused参数
主要考虑删除后插入数据的情况多不多，以及平均行长大小
例如：
病人费用记录，医保如果存在校对操作的话，是先产生预交结算数据，正式结算时，删除这些数据再重新生成
所以，病人预交记录,Pctused不能设置太高，否则重用那些低于Pctused的块，只能插入少量数据行，增加了IO操作
analyze table 病人预交记录 compute statistics for table for all indexes for all indexed columns;
Select Num_Rows,Blocks,Round(Num_Rows / Blocks) Avg_Rows_Block, Avg_Row_Len From User_Tables Where Table_Name = '病人预交记录'

    NUM_ROWS BLOCKS AVG_ROWS_BLOCK AVG_ROW_LEN   
 181758 2147 85 83 

如果设置pctfree 5 pctused 85，那么当删除一些行使块的已用空间低于85%时，块会被重用，但是因为要预留5%的空闲空间，
所以,对于已用空间刚刚低于85%的块，重用空间就只有10%,对于8K的块，可用810Byte,平均行长83,可以再放入9行，所以这个参数也是可以的。

但是，如果是病人费用记录,平均行长229，这样设置，只能放下3行，这个参数就不太合适了
 
    NUM_ROWS BLOCKS AVG_ROWS_BLOCK AVG_ROW_LEN   
 925133 38278 24 229 

根据分析，病人费用记录的数据更新量不大，但是并发操作比较大，最好把Pctfree设置高一点
所以，可以设置为pctfree 10 pctused 75（重用的块至少可以放5行，约1-2张单据）,甚至pctused 70也是可以的。

如果一个块的数据行数太多，可能造成热块争用，但是相对于减少存储，减少IO，减少内存占用带来的好处来说，热块不是特别突出的情况下可以不考虑。

--MartriWang@gmail.com 17/05/2007--

PCTFREE=(Average Row Size-Initial Row Size)*100/Average Row Size
PCTUSED=(100-PCTFREE) -Average Row Size * 100/Availabe Data Space
　　
　　Oracle的其中一个优点时它可以管理每个表空间中的自由空间。Oracle负责处理表和索引的空间管理，这样就可以让我们无需懂得Oracle的表和索引的
内部运作。不过，对于有经验的Oracle调优专家来说，他需要懂得Oracle是如何管理表的extent和空闲的数据块。对于调整拥有高的insert或者update的系
统来说，这是非常重要的。
　　
　　要精通对象的调整，你需要懂得freelists和freelist组的行为，它们和pctfree及pctused参数的值有关。这些知识对于企业资源计划（ERP）的应用是
特别重要的，因为在这些应用中，不正确的表设置通常是DML语句执行慢的原因。　
　　对于初学者来说，最常见的错误是认为默认的Oracle参数对于所有的对象都是最佳的。除非磁盘的消耗不是一个问题，否则在设置表的pctfree和pctused
参数时，就必须考虑平均的行长和数据库的块大小，这样空的块才会被有效地放到freelists中。当这些设置不正确时，那些得到的freelists也是"dead"块，
因为它们没有足够的空间来存储一行，这样将会导致明显的处理延迟。
　　Freelists对于有效地重新使用Oracle表空间中的空间是很重要的，它和pctfree及pctused这两个存储参数的设置直接相关。如果将pctused设置为一个高的值，
这时数据库就会尽快地重新使用块。不过，高性能和有效地重新使用表的块是对立的。在调整Oracle的表格和索引时，需要认真考虑究竟需要高性能还是有效的空
间重用，并且据此来设置表的参数。以下我们来看一下这些freelists是如何影响Oracle的性能的。
　　
　　当有一个请求需要插入一行到表格中时，Oracle就会到freelist中寻找一个有足够的空间来容纳一行的块。你也许知道，freelist串是放在表格或者索引的第
一个块中，这个块也被称为段头（segment header）。pctfree和pctused 参数的唯一目的就是为了控制块如何在freelists中进出。虽然freelist link和 unlink
是简单的Oracle功能，不过设置freelist link (pctused) 和unlink (pctfree) 对Oracle的性能确实有影响。
　　
　　由DBA的基本知识知道，pctfree参数是控制freelist un-links的（即将块由freelists中移除）。设置pctfree=10 意味着每个块都保留10%的空间用作行扩展。
pctused参数是控制freelist re-links的。设置pctused=40意味着只有在块的使用低于40%时才会回到表格的freelists中。
　　
　　许多新手对于一个块重新回到freelists后的处理都有些误解。其实，一旦由于一个删除的操作而令块被重新加入到freelist中，它将会一直保留在freelist中
即使空间的使用超过了60%，只有在到达pctfree时才会将数据块由freelist中移走。
　　
　　表格和索引存储参数设置的要求总结
　　
　　以下的一些规则是用来设置freelists, freelist groups, pctfree和pctused存储参数的。你也知道，pctused和pctfree的值是可以很容易地通过alter table
命令修改的，一个好的DBA应该知道如何设置这些参数的最佳值。
　　
　　有效地使用空间和高性能之间是有矛盾的，而表格的存储参数就是控制这个方面的矛盾：
　　
　　. 对于需要有效地重新使用空间，可以设置一个高的pctused值，不过副作用是需要额外的I/O。一个高的pctused值意味着相对满的块都会放到freelist中。因
此，这些块在再次满之前只可以接受几行记录，从而导致更多的I/O。
　　
　　. 追求高性能的话，可以将pctused设置为一个低的值，这意味着Oracle不会将数据块放到freelists中直到它几乎是空的。那么块将可以在满之前接收更多的行，
因此可以减少插入操作的I/O。要记住Oracle扩展新块的性能要比重新使用现有的块高。对于Oracle来说，扩展一个表比管理freelists消耗更少的资源。
　　
　　让我们来回顾一下设置对象存储参数的一些常见规则：
　　
　　．经常将pctused设置为可以接收一条新行。对于不能接受一行的free blocks对于我们来说是没有用的。如果这样做，将会令Oracle的性能变慢，因为Oracle将
在扩展表来得到一个空的块之前，企图读取5个"dead" 的free block。
　　
　　．表格中chained rows的出现意味着pctfree太低或者是db_block_size太少。在很多情况下，RAW和LONG RAW列都很巨大，以至超过了Oracle的最大块的大小，
这时chained rows是不可以避免的。
　　
　　．如果一个表有同时插入的SQL语句，那么它需要有同时删除的语句。运行单一个一个清除的工作将会把全部的空闲块放到一个freelist中，而没有其它包含有任何
空闲块的freelists出现。
　　
　　．freelist参数应该设置为表格同时更新的最大值。例如，如果在任何时候，某个表最多有20个用户执行插入的操作，那么该表的参数应该设置为freelists=20。
　　
　　应记住的是freelist groups参数的值只是对于Oracle Parallel Server和Real Application Clusters才是有用的。对于这类Oracle，freelist groups应该设置
为访问该表格的Oracle Parallel Server实例的数目。

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

2:简单描述table / segment / extent / block之间的关系
  table创建时,默认创建了一个data segment,
每个data segment含有min extents指定的extents数,
每个extent据据表空间的存储参数分配一定数量的blocks

3:描述tablespace和datafile之间的关系
一个tablespace可以有一个或多个datafile,每个datafile只能在一个tablespace内,
table中的数据,通过hash算法分布在tablespace中的各个datafile中,
tablespace是逻辑上的概念,datafile则在物理上储存了数据库的种种对象。

4:本地管理表空间和字典管理表空间的特点，ASSM有什么特点
  本地管理表空间（Locally Managed Tablespace简称LMT）
  8i以后出现的一种新的表空间的管理模式，通过位图来管理表空间的空间使用。
  字典管理表空间（Dictionary-Managed Tablespace简称DMT）
  8i以前包括以后都还可以使用的一种表空间管理模式，通过数据字典管理表空间的空间使用。
  动段空间管理（ASSM），
  它首次出现在Oracle920里有了ASSM，链接列表freelist被位图所取代，它是一个二进制的数组，
  能够迅速有效地管理存储扩展和剩余区块（free block），因此能够改善分段存储本质，
  ASSM表空间上创建的段还有另外一个称呼叫Bitmap Managed Segments（BMB 段）。

5:回滚段的作用是什么
事务回滚：当事务修改表中数据的时候，该数据修改前的值（即前影像）会存放在回滚段中，
          当用户回滚事务（ROLLBACK）时，ORACLE将会利用回滚段中的数据前影像来将修改的数据恢复到原来的值。
　
  事务恢复：当事务正在处理的时候，例程失败，回滚段的信息保存在undo表空间中，
           ORACLE将在下次打开数据库时利用回滚来恢复未提交的数据。

　读一致性：当一个会话正在修改数据时，其他的会话将看不到该会话未提交的修改。
           当一个语句正在执行时，该语句将看不到从该语句开始执行后的未提交的修改（语句级读一致性）
      当ORACLE执行SELECT语句时，ORACLE依照当前的系统改变号（SYSTEM CHANGE NUMBER-SCN）
      来保证任何前于当前SCN的未提交的改变不被该语句处理。可以想象：当一个长时间的查询正在执行时，
      若其他会话改变了该查询要查询的某个数据块，ORACLE将利用回滚段的数据前影像来构造一个读一致性视图。
  http://www.itpub.net/showthread. ... E%B5%C4%D7%F7%D3%C3

6:日志的作用是什么
  记录数据库事务,最大限度地保证数据的一致性与安全性

  重做日志文件：含对数据库所做的更改记录，这样万一出现故障可以启用数据恢复,一个数据库至少需要两个重做日志文件
  归档日志文件：是重做日志文件的脱机副本，这些副本可能对于从介质失败中进行恢复很必要。

http://www.cnoug.org/viewthread. ... hlight=%C8%D5%D6%BE

7:SGA主要有那些部分，主要作用是什么
  SGA：db_cache/shared_pool/large_pool/java_pool
db_cache:
  数据库缓存（Block Buffer）对于Oracle数据库的运转和性能起着非常关键的作用，
   它占据Oracle数据库SGA（系统共享内存区）的主要部分。Oracle数据库通过使用LRU
   算法，将最近访问的数据块存放到缓存中，从而优化对磁盘数据的访问.
shared_pool:
  共享池的大小对于Oracle 性能来说都是很重要的。
  共享池中保存数据字典高速缓冲和完全解析或编译的的PL/SQL 块和SQL 语句及控制结构
large_pool:
  使用MTS配置时，因为要在SGA中分配UGA来保持用户的会话，就是用Large_pool来保持这个会话内存
   使用RMAN做备份的时候，要使用Large_pool这个内存结构来做磁盘I/O缓存器
java_pool:
  为java procedure预备的内存区域,如果没有使用java proc,java_pool不是必须的
  
8racle系统进程主要有哪些，作用是什么
  数据写进程(dbwr)：负责将更改的数据从数据库缓冲区高速缓存写入数据文件
  日志写进程(lgwr)：将重做日志缓冲区中的更改写入在线重做日志文件
  系统监控(smon)  ：检查数据库的一致性如有必要还会在数据库打开时启动数据库的恢复
  进程监控(pmon)  ：负责在一个Oracle 进程失败时清理资源
  检查点进程(chpt)：负责在每当缓冲区高速缓存中的更改永久地记录在数据库中时,更新控制文件和数据文件中的数据库状态信息。
  归档进程(arcn)  ：在每次日志切换时把已满的日志组进行备份或归档
  作业调度器(cjq)  :负责将调度与执行系统中已定义好的job,完成一些预定义的工作.
恢复进程(reco)  :保证分布式事务的一致性,在分布式事务中,要么同时commit,要么同时rollback;

三：备份恢复类

1:备份如何分类
逻辑备份：exp/imp
物理备份：
    RMAN备份
     full backup/incremental backup(累积/差异)
     热备份:alter tablespace begin/end backup;
     冷备份:脱机备份(database shutdown)
    
2:归档是什么含义
关于归档日志：Oracle要将填满的在线日志文件组归档时,则要建立归档日志（archived redo log）。
其对数据库备份和恢复有下列用处：
    <1>数据库后备以及在线和归档日志文件，在操作系统和磁盘故障中可保证全部提交的事物可被恢复。
    <2>在数据库打开和正常系统使用下，如果归档日志是永久保存，在线后备可以进行和使用。
  数据库可运行在两种不同方式下：
   NOARCHIVELOG方式或ARCHIVELOG 方式
数据库在NOARCHIVELOG方式下使用时，不能进行在线日志的归档,
如果数据库在ARCHIVELOG方式下运行，可实施在线日志的归档。

3:如果一个表在2004-08-04 10:30:00 被drop，在有完善的归档和备份的情况下，如何恢复?
  手工拷贝回所有备份的数据文件
sql>startup mount;
sql>alter database recover automatic until time '2004-08-04:10:30:00';
sql>alter database open resetlogs;

4:rman是什么,有何特点?
RMAN(Recovery Manager)是DBA的一个重要工具，用于备份、还原和恢复oracle数据库,
RMAN 可以用来备份和恢复数据库文件、归档日志、控制文件、系统参数文件,也可以用来执行完全或不完全的数据库恢复。
RMAN有三种不同的用户接口：
  COMMAND LINE方式、GUI 方式（集成在OEM 中的备份管理器）、API 方式（用于集成到第三方的备份软件中）。
具有如下特点：
1）功能类似物理备份，但比物理备份强大N倍；
2）可以压缩空块；
3）可以在块水平上实现增量；
4）可以把备份的输出打包成备份集，也可以按固定大小分割备份集；
5）备份与恢复的过程可以自动管理；
6）可以使用脚本（存在Recovery catalog 中）
7）可以做坏块监测

5:standby的特点
备用数据库（standby database）：ORACLE推出的一种高可用性(HIGH AVAILABLE)数据库方案，
在主节点与备用节点间通过日志同步来保证数据的同步，备用节点作为主节点的备份
可以实现快速切换与灾难性恢复,从920开始，还开始支持物理与逻辑备用服务器。
  9i中的三种数据保护模式分别是：
  1)、MAXIMIZE PROTECTION ：最大数据保护与无数据分歧，LGWR将同时传送到备用节点，
    在主节点事务确认之前，备用节点也必须完全收到日志数据。如果网络不好，引起LGWR不能传送数据，将引起严重的性能问题，导致主节点DOWN机。
  2)、MAXIMIZE AVAILABILITY ：无数据丢失模式，允许数据分歧，允许异步传送。
    正常情况下运行在最大保护模式，在主节点与备用节点的网络断开或连接不正常时，自动切换到最大性能模式，
   主节点的操作还是可以继续的。在网络不好的情况下有较大的性能影响。
  3)、MAXIMIZE PERFORMANCE：这种模式应当可以说是从8i继承过来的备用服务器模式，异步传送，
    无数据同步检查，可能丢失数据，但是能获得主节点的最大性能。9i在配置DATA GUARD的时候默认就是MAXIMIZE PERFORMANCE

6:对于一个要求恢复时间比较短的系统(数据库50G,每天归档5G)，你如何设计备份策略
  rman/每月一号 level 0 每周末/周三 level 1 其它每天level 2 

四：系统管理类

1:对于一个存在系统性能的系统，说出你的诊断处理思路
1 做statspack收集系统相关信息
  了解系统大致情况/确定是否存在参数设置不合适的地方/查看top 5 event/查看top sql等
2 查v$system_event/v$session_event/v$session_wait
   从v$system_event开始,确定需要什么资源(db file sequential read)等
  深入研究v$session_event,确定等待事件涉及的会话
  从v$session_wait确定详细的资源争用情况(p1-p3的值:file_id/block_id/blocks等)
3 通过v$sql/v$sqltext/v$sqlarea表确定disk_reads、(buffer_gets/executions)值较大的SQL

2:列举几种诊断IO、CPU、性能状况的方法
    top/vmstat
  statspack
  sql_trace/tkprof
    查v$system_event/v$session_event/v$session_wait
  查v$sqlarea(disk_reads或buffer_gets/executions较大的SQL)

3:对statspack有何认识
StapSpack是Oracle公司提供的一个收集数据库运行性能指标的软件包，该软件包从8i起，在9i、10g都有显著的增强
该软件包的辅助表（存储相关参数与收集的性能指标的表）由最初的25个增长到43个
收集级别参数由原来的3个（0、5、10）增加到5个（0、5、6、7、10）
通过分析收集的性能指标，数据库管理员可以详细地了解数据库目前的运行情况，对数据库实例、等待事件、SQL等进行优化调整
利用statspack收集的snapshot,可以统计制作数据库的各种性能指标的统计趋势图表。

4:如果系统现在需要在一个很大的表上创建一个索引，你会考虑那些因素，如何做以尽量减小对应用的影响
在系统比较空闲时
nologging选项（如果有dataguard则不可以使用nologging）
大的sort_ared_size或pga_aggregate_target较大

5:对raid1+0 和raid5有何认识
RAID 10(或称RAID 1＋0)与RAID 0＋1不同，它是用硬盘驱动器先组成RAID 1阵列，然后在RAID 1阵列之间再组成RAID 0阵列。
RAID 10模式同RAID 0+1模式一样具有良好的数据传输性能，但却比RAID 0+1具有更高的可靠性。RAID 10阵列的实际容量为M×n/2，
磁盘利用率为50％。RAID 10也需要至少4个硬盘驱动器构成，因而价格昂贵。
  RAID 10的可靠性同RAID 1一样，但由于RAID 10硬盘驱动器之间有数据分割，因而数据传输性能优良。

RAID 5与RAID 3很相似，不同之处在于RAID 5的奇偶校验信息也同数据一样被分割保存到所有的硬盘驱动器，
而不是写入一个指定的硬盘驱动器，从而消除了单个奇偶校验硬盘驱动器的瓶颈问题。RAID 5磁盘阵列的性能比RAID 3有所提高，
但仍然需要至少3块硬盘驱动器。其实际容量为M×(n-1)，磁盘利用率为(n-1)/n 。

五：综合随意类

1:你最擅长的是oracle哪部分?
pl/sql及sql优化

2:喜欢oracle吗？喜欢上论坛吗？或者偏好oracle的哪一部分？
喜欢,sql的优化

3:随意说说你觉得oracle最有意思的部分或者最困难的部分
latch free的处理

4:为何要选择做DBA呢?
兴趣所在

--MartriWang@gmail.com 17/05/2007--
消耗在准备利用Oracle执行计划机制提高查询性能新的SQL语句的时间是Oracle SQL语句执行时间的最重要的组成部分。
但是通过理解Oracle内部产生执行计划的机制，你能够控制Oracle花费在评估连接顺序的时间数量，并且能在大体上提高查询性能。
准备执行SQL语句
当SQL语句进入Oracle的库缓存后，在该语句准备执行之前，将执行下列步骤：
1) 语法检查：检查SQL语句拼写是否正确和词序。

2) 语义分析：核实所有的与数据字典不一致的表和列的名字。

3) 轮廓存储检查：检查数据字典，以确定该SQL语句的轮廓是否已经存在。

4) 生成执行计划：使用基于成本的优化规则和数据字典中的统计表来决定最佳执行计划。

5) 建立二进制代码：基于执行计划，Oracle生成二进制执行代码。

一旦为执行准备好了SQL语句，以后的执行将很快发生，因为Oracle认可同一个SQL语句，并且重用那些语句的执行。然而，对于生成
特殊的SQL语句，或嵌入了文字变量的SQL语句的系统，SQL执行计划的生成时间就很重要了，并且前一个执行计划通常不能够被重用。
对那些连接了很多表的查询，Oracle需要花费大量的时间来检测连接这些表的适当顺序。

评估表的连接顺序

在SQL语句的准备过程中，花费最多的步骤是生成执行计划，特别是处理有多个表连接的查询。当Oracle评估表的连接顺序时，它必须
考虑到表之间所有可能的连接。例如：六个表的之间连接有720（6的阶乘，或6 * 5 * 4 * 3 * 2 * 1 = 720）种可能的连接线路。
当一个查询中含有超过10个表的连接时，排列的问题将变得更为显著。对于15个表之间的连接，需要评估的可能查询排列将超过1万亿
（准确的数字是1,307,674,368,000）种。

使用optimizer_search_limit参数来设定限制

通过使用optimizer_search_limit参数，你能够指定被优化器用来评估的最大的连接组合数量。使用这个参数，我们将能够防止优化器
消耗不定数量的时间来评估所有可能的连接组合。如果在查询中表的数目小于optimizer_search_limit的值，优化器将检查所有可能的
连接组合。

例如：有五个表连接的查询将有120（5! = 5 * 4 * 3 * 2 * 1 = 120）种可能的连接组合，因此如果optimizer_search_limit等于5
（默认值），则优化器将评估所有的120种可能。optimizer_search_limit参数也控制着调用带星号的连接提示的阀值。当查询中的表的
数目比optimizer_search_limit小时，带星号的提示将被优先考虑。

另一个工具：参数optimizer_max_permutations

初始化参数optimizer_max_permutations定义了优化器所考虑组合数目的上限，且依赖于初始参数optimizer_search_limit。
optimizer_max_permutations的默认值是80,000。

参数optimizer_search_limit和optimizer_max_permutations一起来确定优化器所考虑的组合数目的上限：除非（表或组合数目）
超过参数optimizer_search_limit 或者 optimizer_max_permutations设定的值，否则优化器将生成所有可能的连接组合。一旦优
化器停止评估表的连接组合，它将选择成本最低的组合。

使用ordered提示指定连接顺序

你能够设定优化器所执行的评估数目的上限。但是即使采用有很高价值的排列评估，我们仍然拥有使优化器可以尽早地放弃复杂的查询
的重要机会。回想一下含有15个连接查询的例子，它将有超过1万亿种的连接组合。如果优化器在评估了80,000个组合后停止，那么它才
仅仅评估了0.000006%的可能组合，而且或许还没有为这个巨大的查询找到最佳的连接顺序。

在Oracle SQL中解决此问题的最好的方法是手工指定表的连接顺序。为了尽快创建最小的解决方案集，这里所遵循的规则是将表结合起
来，通常优先使用限制最严格的WHERE子句来连接表。

下面的代码是一个查询执行计划的例子，该例子在emp表的关联查询上强制执行了嵌套的循环连接。注意，我已经使用了ordered提示来
直接最优化表的评估顺序，最终它们表现在WHERE子句上。
select /*+ ordered use_nl(bonus) parallel(e, 4) */
　　  e.ename,
　　 hiredate,
　　 b.comm.
from
　　  emp e,
　　  bonus b
where
　　 e.ename = b.ename
这个例子要求优化器按顺序连接在SQL语句的FROM子句中指定的表，在FROM子句中的第一个表指定了驱动表。ordered提示通常被用来与
其它的提示联合起来来保证采用正确的顺序连接多个表。它的用途更多的是在扭转连接表数在四个以上的数据仓库的查询方面。
另外一个例子，下面的查询使用ordered提示按照指定的顺序来连接表：emp、dept、sal，最后是bonus。我通过指定emp到dept使用哈
希连接和sal到bonus使用嵌套循环连接，来进一步精炼执行计划。 　
select /*+ ordered use_hash (emp, dept) use_nl (sal, bonus) */
　　
from
　　 emp,
　　  dept,
　　  sal,
　　  bonus
where . . .
　　
实践建议
 实际上，更有效率的做法是在产品环境中减小optimizer_max_permutations参数的大小，并且总是使用稳定的优化计划或存储轮廓来
防止出现耗时的含有大量连接的查询。一旦找到最佳的连接顺序，您就可以通过增加ordered提示到当前的查询中，并保存它的存储轮廓，
来为这些表手工指定连接顺序，从而使其持久化。
 当你打算使用优化器来稳定计划，则可以照下面的方法使执行计划持久化，临时将optimizer_search_limit设置为查询中的表的数目，
从而允许优化器考虑所有可能的连接顺序。然后，通过重新编排WHERE子句中表的名字，并使用ordered提示，与存储轮廓一起使变更
持久化，来调整查询。在查询中包含四个以上的表时，ordered提示和存储轮廓将排除耗时的评估SQL连接顺序解析的任务，从而提高
查询的速度。
 一旦检测到最佳的连接顺序，我们就可以使用ordered提示来重载optimizer_search_limit和optimizer_max_permutations参数。
ordered提示要求表按照它们出现在FROM子句中的顺序进行连接，所以优化器没有加入描述。
 作为一个Oracle专业人员，你应该知道在SQL语句第一次进入库缓存时可能存在重大的启动延迟。但是聪明的Oracle DBA和开发人
员能够改变表的搜索限制参数或者使用ordered提示来手工指定表的连接顺序，从而显著地减少优化和执行新查询所需的时间

--MartriWang@gmail.com  14/05/2007--

Oracle专家调优秘密

在过去的十年中， Oracle 已经成为世界上最专业的数据库之一。对于 IT 专家来说，就是要确保利用 Oracle 的强大特性来提高他们公司的生产力。最有效的方法之一
是通过 Oracle 调优。它有大量的调整参数和技术来改进你的 Oracle 数据库的性能。 Oracle 调优是一个复杂的主题。关于调优可以写整整一本书，不过，为了改善
Oracle 数据库的性能，有一些基本的概念是每个 Oracle DBA 都应该遵从的。
　　 在这篇简介中，我们将简要地介绍以下的 Oracle 主题：
　　-- 外部调整：我们应该记住 Oracle 并不是单独运行的。因此我们将查看一下通过调整 Oracle 服务器以得到高的性能。
　　--Row re-sequencing 以减少磁盘 I/O ：我们应该懂得 Oracle 调优最重要的目标是减少 I/O 。
　　--Oracle SQL 调整。 Oracle SQL 调整是 Oracle 调整中最重要的领域之一，只要通过一些简单的 SQL 调优规则就可以大幅度地提升 SQL 语句的性能，这是一点都
不奇怪的。
　　-- 调整 Oracle 排序：排序对于 Oracle 性能也是有很大影响的。
　　-- 调整 Oracle 的竞争：表和索引的参数设置对于 UPDATE 和 INSERT 的性能有很大的影响。

　　 我们首先从调整 Oracle 外部的环境开始。如果内存和 CPU 的资源不足的话，任何的 Oracle 调整都是没有帮助的。

　　外部的性能问题
　　
　　Oracle 并不是单独运行的。 Oracle 数据库的性能和外部的环境有很大的关系。这些外部的条件包括有：
　　 ．CPU--CPU 资源的不足令查询变慢。当查询超过了 Oracle 服务器的 CPU 性能时，你的数据库性能就受到 CPU 的限制。
　　 ．内存 -- 可用于 Oralce 的内存数量也会影响 SQL 的性能，特别是在数据缓冲和内存排序方面。
　　 ．网络 -- 大量的 Net8 通信令 SQL 的性能变慢。
　　 许多新手都错误的认为应该首先调整 Oracle 数据库，而不是先确认外部资源是否足够。实际上，如果外部环境出现瓶颈，再多的 Oracle 调整都是没有帮助的。
　　 在检查 Oracle 的外部环境时，有两个方面是需要注意的：
　　1 、当运行队列的数目超过服务器的 CPU 数量时，服务器的性能就会受到 CPU 的限制。补救的方法是为服务器增加额外的 CPU 或者关闭需要很多处理资源的组件，
例如 Oracle Parallel Query 。
　　2 、内存分页。当内存分页时，内存容量已经不足，而内存页是与磁盘上的交换区进行交互的。补救的方法是增加更多的内存，减少 Oracle SGA 的大小，或者关闭
Oracle 的多线程服务器。
　　 可以使用各种标准的服务器工具来得到服务器的统计数据，例如 vmstat,glance,top 和 sar 。 DBA 的目标是确保数据库服务器拥有足够的 CPU 和内存资源来处理
 Oracle 的请求。
　　 以下让我们来看一下 Oracle 的 row-resequencing 是如何能够极大地减少磁盘 I/O 的。

　　Row-resequencing （行的重新排序）
　　
　　就象我们上面提到的，有经验的 Oracle DBA 都知道 I/O 是响应时间的最大组成部分。其中磁盘 I/O 特别厉害，因为当 Oracle 由磁盘上的一个数据文件得到一个
数据块时，读的进程就必须等待物理 I/O 操作完成。磁盘操作要比数据缓冲慢 10,000 倍。因此，如果可以令 I/O 最小化，或者减少由于磁盘上的文件竞争而带来的瓶颈
，就可以大大地改善 Oracle 数据库的性能。如果系统响应很慢，通过减少磁盘 I/O 就可以有一个很快的改善。如果在一个事务中通过按一定的范围搜索 primary-key
 索引来访问表，那么重新以 CTAS 的方法组织表将是你减少 I/O 的首要策略。通过在物理上将行排序为和 primary-key 索引一样的顺序，就可以加快获得数据的速度。
就象磁盘的负载平衡一样，行的重新排序也是很简单的，而且也很快。通过与其它的 DBA 管理技巧一起使用，就可以在高 I/O 的系统中大大地减少响应的时间。 在高容量
的在线事务处理环境中（ online transaction processing ， OLTP ），数据是由一个 primary 索引得到的，重新排序表格的行就可以令连续块的顺序和它们的 primary
索引一样，这样就可以在索引驱动的表格查询中，减少物理 I/O 并且改善响应时间。这个技巧仅在应用选择多行的时候有用，或者在使用索引范围搜索和应用发出多个查询
来得到连续的 key 时有效。对于随机的唯一 primary-key （主键）的访问将不会由行重新排序中得到好处。
　　 让我们看一下它是如何工作的。考虑以下的一个 SQL 的查询，它使用一个索引来得到 100 行：
select salary from employee where last_name like 'B%';
这个查询将会使用 last_name_index ，搜索其中的每一行来得到目标行。这个查询将会至少使用 100 次物理磁盘的读取，因为 employee 的行存放在不同的数据块中。
　　 不过，如果表中的行已经重新排序为和 last_name_index 的一样，同样的查询又会怎样处理呢？我们可以看到这个查询只需要三次的磁盘 I/O 就读完全部 100 个
员工的资料（一次用作索引的读取，两次用作数据块的读取），减少了 97 次的块读取。
　  重新排序带来的性能改善的程度在于在你开始的时候行的乱序性如何，以及你需要由序列中访问多少行。至于一个表中的行与索引的排序键的匹配程度，可以查看数据
字典中的 dba_indexes 和 dba_tables 视图得到。
　　 在 dba_indexes 的视图中，查看 clustering_factor 列。如果 clustering_factor 的值和表中的块数目大致一样，那么你的表和索引的顺序是一样的。不过，如果
clustering_factor 的值接近表中的行数目，那就表明表格中的行和索引的顺序是不一样的。
　　 行重新排序的作用是不可以小看的。在需要进行大范围的索引搜索的大表中，行重新排序可以令查询的性能提高三倍。
　　 一旦你已经决定重新排序表中的行，你可以使用以下的工具之一来重新组织表格。
　　. 使用 Oracle 的 Create Table As Select (CTAS) 语法来拷贝表格
　　. Oracle9i 自带的表格重新组织工具
　　
SQL 语句的调优
　　SQL 调优
　　Oracle 的 SQL 调优是一个复杂的主题，甚至是需要整本书来介绍 Oracle SQL 调优的细微差别。不过有一些基本的规则是每个
Oracle DBA 都需要跟从的，这些规则可以改善他们系统的性能。 SQL 调优的目标是简单的：
　　消除不必要的大表全表搜索：不必要的全表搜索导致大量不必要的 I/O ，从而拖慢整个数据库的性能。调优专家首先会根据查询
返回的行数目来评价 SQL 。在一个有序的表中，如果查询返回少于 40% 的行，或者在一个无序的表中，返回少于 7% 的行，那么这个
查询都可以调整为使用一个索引来代替全表搜索。对于不必要的全表搜索来说，最常见的调优方法是增加索引。可以在表中加入标准的
 B 树索引，也可以加入 bitmap 和基于函数的索引。要决定是否消除一个全表搜索，你可以仔细检查索引搜索的 I/O 开销和全表搜索
 的开销，它们的开销和数据块的读取和可能的并行执行有关，并将两者作对比。在一些情况下，一些不必要的全表搜索的消除可以通过
 强制使用一个index 来达到，只需要在 SQL 语句中加入一个索引的提示就可以了。
　　在全表搜索是一个最快的访问方法时，将小表的全表搜索放到缓存中，调优专家应该确保有一个专门的数据缓冲用作行缓冲。在
 Oracle7 中，你可以使用 alter table xxx cache 语句，在 Oracle8 或以上，小表可以被强制为放到 KEEP 池中缓冲。
　  确保最优的索引使用 ：对于改善查询的速度，这是特别重要的。有时 Oracle 可以选择多个索引来进行查询，调优专家必须检查
每个索引并且确保 Oracle 使用正确的索引。它还包括 bitmap 和基于函数的索引的使用。
　　. 确保最优的 JOIN 操作：有些查询使用 NESTED LOOP join 快一些，有些则是 HASH join 快一些，另外一些则是
 sort-merge join 更快。
这些规则看来简单，不过它们占 SQL 调优任务的 90% ，并且它们也无需完全懂得 Oracle SQL
的内部运作。以下我们来简单概览以下 Oracle SQL 的优化。
　　 调整 Oracle 的排序操作
　　 排序是 SQL 语法中一个小的方面，但很重要，在 Oracle 的调整中，它常常被忽略。当使用 create index 、 ORDER BY 或者
GROUP BY 的语句时， Oracle 数据库
将会自动执行排序的操作。通常，在以下的情况下 Oracle 会进行排序的操作：
　　 使用 Order by 的 SQL 语句
　　 使用 Group by 的 SQL 语句
　　 在创建索引的时候
　　 进行 table join 时，由于现有索引的不足而导致 SQL 优化器调用 MERGE SORT
　　 当与 Oracle 建立起一个 session 时，在内存中就会为该 session 分配一个私有的排序区域。如果该连接是一个专用的连接
(dedicated connection) ，那么就会根据 init.ora 中 sort_area_size 参数的大小在内存中分配一个 Program Global Area (PGA)
如果连接是通过多线程服务器建立的，那么排序的空间就在 large_pool 中分配。不幸的是，对于所有的 session ，用做排序的内存
量都必须是一样的，我们不能为需要更大排序的操作分配额外的排序区域。因此，设计者必须作出一个平衡，在分配足够的排序区域以
避免发生大的排序任务时出现磁盘排序（ disk sorts ）的同时，对于那些并不需要进行很大排序的任务，就会出现一些浪费。当然，
当排序的空间需求超出了 sort_area_size 的大小时，这时将会在 TEMP 表空间中分页进行磁盘排序。磁盘排序要比内存排序大概慢
14,000 倍。
　　 上面我们已经提到，私有排序区域的大小是有 init.ora 中的 sort_area_size 参数决定的。每个排序所占用的大小由 init.ora
 中的 sort_area_retained_size 参数决定。当排序不能在分配的空间中完成时，就会使用磁盘排序的方式，即在 Oracle 实例中的临
 时表空间中进行。 磁盘排序的开销是很大的，有几个方面的原因。首先，和内存排序相比较，它们特别慢；而且磁盘排序会消耗临时
 表空间中的资源。 Oracle 还必须分配缓冲池块来保持临时表空间中的块。无论什么时候，内存排序都比磁盘排序好，磁盘排序将会
 令任务变慢，并且会影响 Oracle 实例的当前任务的执行。还有，过多的磁盘排序将会令free buffer waits 的值变高，从而令其它
 任务的数据块由缓冲中移走。
　　 接着，让我们看一下 Oracle 的竞争，并且看一下表的存储参数的设置是如何影响 SQL UPDATE 和 INSERT 语句的性能的。

调整 Oracle 的竞争
　　Oracle 的其中一个优点时它可以管理每个表空间中的自由空间。 Oracle 负责处理表和索引的空间管理，这样就可以让我们无需
懂得 Oracle 的表和索引的内部运作。不过，对于有经验的 Oracle 调优专家来说，他需要懂得 Oracle 是如何管理表的 extent 和空
闲的数据块。对于调整拥有高的 insert 或者 update 的系统来说，这是非常重要的。
　　 要精通对象的调整，你需要懂得 freelists 和 freelist 组的行为，它们和 pctfree 及 pctused 参数的值有关。这些知识对于
企业资源计划（ ERP ）的应用是特别重要的，因为在这些应用中，不正确的表设置通常是 DML 语句执行慢的原因。
　　 对于初学者来说，最常见的错误是认为默认的 Oracle 参数对于所有的对象都是最佳的。除非磁盘的消耗不是一个问题，否则在设
置表的 pctfree 和 pctused 参数时，就必须考虑平均的行长和数据库的块大小，这样空的块才会被有效地放到 freelists 中。当这些
设置不正确时，那些得到的 freelists 也是 "dead" 块，因为它们没有足够的空间来存储一行，这样将会导致明显的处理延迟。
 Freelists 对于有效地重新使用 Oracle 表空间中的空间是很重要的，它和 pctfree 及 pctused 这两个存储参数的设置直接相关。
通过将 pctused 设置为一个高的值，这时数据库就会尽快地重新使用块。不过，高性能和有效地重新使用表的块是对立的。在调整
 Oracle 的表格和索引时，需要认真考虑究竟需要高性能还是有效的空间重用，并且据此来设置表的参数。以下我们来看一下这些
 freelists 是如何影响 Oracle 的性能的。
　　当有一个请求需要插入一行到表格中时， Oracle 就会到 freelist 中寻找一个有足够的空间来容纳一行的块。你也许知道，
freelist 串是放在表格或者索引的第一个块中，这个块也被称为段头（ segment header ）。 pctfree 和 pctused 参数的唯一目的就
是为了控制块如何在 freelists 中进出。虽然 freelist link 和 unlink 是简单的 Oracle 功能，不过设置 freelist link (pctused)
和 unlink (pctfree) 对 Oracle 的性能确实有影响。
　　 由 DBA 的基本知识知道， pctfree 参数是控制 freelist un-links 的（即将块由 freelists 中移除）。设置 pctfree=10
意味着每个块都保留 10% 的空间用作行扩展。 pctused 参数是控制 freelist re-links 的。设置 pctused=40 意味着只有在块的
使用低于 40% 时才会回到表格的 freelists 中。
　　 许多新手对于一个块重新回到 freelists 后的处理都有些误解。其实，一旦由于一个删除的操作而令块被重新加入到 freelist
 中，它将会一直保留在 freelist 中即使空间的使用超过了 60% ，只有在到达 pctfree 时才会将数据块由 freelist 中移走。

　　 表格和索引存储参数设置的要求总结
　　 以下的一些规则是用来设置 freelists, freelist groups, pctfree 和 pctused 存储参数的。你也知道， pctused 和 pctfree
的值是可以很容易地通过 alter table 命令修改的，一个好的 DBA 应该知道如何设置这些参数的最佳值。
　　 有效地使用空间和高性能之间是有矛盾的，而表格的存储参数就是控制这个方面的矛盾：
. 对于需要有效地重新使用空间，可以设置一个高的 pctused 值，不过副作用是需要额外的 I/O 。一个高的 pctused 值意味着相对满
的块都会放到 freelist 中。因此，这些块在再次满之前只可以接受几行记录，从而导致更多的 I/O 。
. 追求高性能的话，可以将 pctused 设置为一个低的值，这意味着 Oracle 不会将数据块放到 freelists 中直到它几乎是空的。
那么块将可以在满之前接收更多的行，
因此可以减少插入操作的 I/O 。要记住 Oracle 扩展新块的性能要比重新使用现有的块高。对于 Oracle 来说，扩展一个表比管理
freelists 消耗更少的资源。
　　 让我们来回顾一下设置对象存储参数的一些常见规则：
　　 ．经常将 pctused 设置为可以接收一条新行。对于不能接受一行的 free blocks 对于我们来说是没有用的。如果这样做，
将会令 Oracle 的性能变慢，因为
Oracle 将在扩展表来得到一个空的块之前，企图读取 5 个 "dead" 的 free block 。
　　 ．表格中 chained rows 的出现意味着 pctfree 太低或者是 db_block_size 太少。在很多情况下， RAW 和 LONG RAW 列都很巨
大，以至超过了 Oracle 的最大块的大小，这时 chained rows 是不可以避免的。
　　 ．如果一个表有同时插入的 SQL 语句，那么它需要有同时删除的语句。运行单一个一个清除的工作将会把全部的空闲块放到一个
 freelist 中，而没有其它包含有任何空闲块的 freelists 出现。
　　 ． freelist 参数应该设置为表格同时更新的最大值。例如，如果在任何时候，某个表最多有 20 个用户执行插入的操作，那么
该表的参数应该设置为 freelists=20 。
　　 应记住的是 freelist groups 参数的值只是对于 Oracle Parallel Server 和 Real Application Clusters 才是有用的。对于这
类 Oracle ， freelist groups
应该设置为访问该表格的 Oracle Parallel Server 实例的数目。

---------------------------------------------------------------------------------------------------------------------------------------------------
--MartriWang@gmail.com 13/04/2007--　　
  --在rbo中，连接表的顺序，和predicate的顺序都是有要求的，一般会按照从右往左来访问FROM clause的表，从下往上来访问where clause的predicates，
 选择小表作驱动表是关键的。
  --cbo中，则主要是根据表的统计信息来觉得访问路径，所以保持表的统计信息准确性就很有必要了。
  --在optimizer_mode＝choose时候，系统首先会判断FROM clause中的表是否有统计信息，如果有，则根据CBO来执行sql，否则根据rbo来执行。也可以通过
 hints来改变sql的访问路径。
  --ORACLE檢索順序有關--自底向上，所以小表放在最後面可以縮小檢索范圍,數據量大的放在最左邊（以FROM作參考）

优化器模式
　　rule模式
　　
　　。总忽略CBO和统计信息而基于规则
　　choose模式
　　
　　。Oracle根据情况选择rule or first_rows or all_rows
　　first_rows 模式
　　
　　。基于成本，以最快的速度返回记录，会造成总体查询速度的下降或消耗更多的资源，倾向索引扫描，适合OLTP系统

　　all_rows模式
　　
　　。基于成本，确保总体查询时间最短，倾向并行全表扫描
　　
　　例如：
　　Select last_name from customer order by last_name;用first_rows时，迅速返回记录，但I/O量大，用all_rows时，返回记录慢，但使用资源少。
　　
　　调整SQL表访问
　　
　　全表扫描
　　
　　。返回记录：未排序表>40%，排序表>7%，建议采用并行机制来提高访问速度，DDS；
　　
　　索引访问
　　
　　。最常用的方法，包括索引唯一扫描和索引范围扫描，OLTP；
　　
　　快速完全索引扫描
　　
　　。访问索引中所有数据块，结果相当于全表扫描，可以用索引扫描代替全表扫描，例如：
　　
　　Select serv_id,count(* ) from tg_cdr01 group by serv_id;
　　
　　评估全表扫描的合法性
　　
　　如何实现并行扫描
　　
　　。永久并行化（不推荐）
　　alter table customer parallel degree 8;
　　
　　。单个查询并行化
　　select /*+ full(emp) parallel(emp,8)*/ * from emp;
　　
　　分区表效果明显
　　
　　优化SQL语句排序
　　
　　排序的操作：
　　
　　。order by 子句
　　。group by 子句
　　。select distinct子句
　　。创建索引时
　　。union或minus
　　。排序合并连接
　　
　　如何避免排序
　　
　　。添加索引
　　。在索引中使用distinct子句
　　。避免排序合并连接
　　
　　使用提示进行调整
　　
　　使用提示的原则
　　
　　。语法：/*+ hint */
　　。使用表别名:select /*+ index(e dept_idx)*/ * from emp e
　　。检验提示
　　
　　常用的提示
　　
　　。rule (基于规则)
　　。all_rows
　　。first_rows
　　。use_nl
　　。use_hash
　　。use_merge
　　。index
　　。index_asc
　　。no_index
　　。index_desc（常用于使用max内置函数）
　　。index_combine(强制使用位图索引)
　　。index_ffs（索引快速完全扫描）
　　。use_concat(将查询中所有or条件使用union all)
　　。parallel
　　。noparallel
　　。full
　　。ordered（基于成本）
　　
5.调整表连接
　　
　　表连接的类型
　　
　　。等连接
　　where 条件中用等式连接；
　　。外部连接（左、右连接）
　　
　　在where条件子句的等式谓词放置一个(+)来实现，例如：
　　select a.ename,b.comm from emp a,bonus b where a.ename=b.ename(+);
　　
　　该语句返回所有emp表的记录；
　　。自连接
　　　Select a.value total, B.value hard, (A.value - b.value) soft ,
　　Round((b.value/a.value)*100,1) perc
　　From v$sysstat a,v$sysstat b
　　Where a.statistic# = 179
　　and B.statistic# = 180;
　　
　　反连接
　　
　　反连接常用于not in or not exists中，是指在查询中找到的任何记录都不包含在结果集中的子查询；不建议使用not in or not exists;
　　
　　。半连接
　　
　　查询中使用exists，含义：即使在子查询中返回多条重复的记录，外部查询也只返回一条记录。
　　
　　嵌套循环连接
　　
　　。被连接表中存在索引的情况下使用；
　　。使用use_nl。
　　
　　hash连接
　　
　　。Hash连接将驱动表加载在内存中，并使用hash技术连接第二个表，提高等连接速度。
　　。适合于大表和小表连接；
　　。使用use_hash。
　　
　　排序合并连接
　　
　　。排序合并连接不使用索引
　　。使用原则：
　　
　　连接表子段中不存在可用索引；
　　
　　查询返回两个表中大部分的数据快；
　　
　　CBO认为全表扫描比索引扫描执行的更快。
　　
　　。使用use_merge
　　
　　使用临时/中间表
　　
　　多个大表关联时，可以分别把满足条件的结果集存放到中间表，然后用中间表关联；
　　
　　SQL子查询的调整
　　
　　关联与非关联子查询
　　
　　。关联：子查询的内部引用的是外部表，每行执行一次；
　　。非关联：子查询只执行一次，存放在内存中。
　　
　　调整not in 和not exists语句
　　
　　。可以使用外部连接优化not in子句，例如：
　　select ename from emp where dept_no not in
　　(select dept_no from dept where dept_name =‘Math’);
　　
　　改为：
　　select ename from emp,dept
　　where emp.dept_no=dept.dept_no
　　and dept.dept_name is null;
　　
6.使用索引调整SQL
　　
　　Oracle 为什么不使用索引
　　
　　。检查被索引的列或组合索引的首列是否出现在PL/SQL语句的WHERE子句中，这是“执行计划”能用到相关索引的必要条件。
　　
　　。看采用了哪种类型的连接方式。ORACLE的共有Sort Merge Join（SMJ）、Hash Join（HJ）和Nested Loop Join（NL）。
在两张表连接，且内表的目标列上建有索引时，只有Nested Loop才能有效地利用到该索引。SMJ即使相关列上建有索引，最多
只能因索引的存在，避免数据排序过程。HJ由于须做HASH运算，索引的存在对数据查询速度几乎没有影响。
　　
　　。看连接顺序是否允许使用相关索引。假设表emp的deptno列上有索引，表dept的列deptno上无索引，WHERE语句有
emp.deptno=dept.deptno条件。在做NL连接时，emp做为外表，先被访问，由于连接机制原因，外表的数据访问方式是全表扫描，
emp.deptno上的索引显然是用不上，最多在其上做索引全扫描或索引快速全扫描。
　　
　　。是否用到系统数据字典表或视图。由于系统数据字典表都未被分析过，可能导致极差的“执行计划”。但是不要擅自对数据
字典表做分析，否则可能导致死锁，或系统性能下降。
　　
　　。索引列是否函数的参数。如是，索引在查询时用不上。
　　
　　。是否存在潜在的数据类型转换。如将字符型数据与数值型数据比较，ORACLE会自动将字符型用to_number()函数进行转换，
从而导致上一种现象的发生。
　　
　　。是否为表和相关的索引搜集足够的统计数据。对数据经常有增、删、改的表最好定期对表和索引进行分析，可用SQL语句
“analyze table xxxx compute statistics for all indexes;”。ORACLE掌握了充分反映实际的统计数据，才有可能做出正确的选择。
　　
　　。索引列的选择性不高。 　　我们假设典型情况，有表emp，共有一百万行数据，但其中的emp.deptno列，数据只有4种不同的值，
如10、20、30、40。虽然emp数据行有很多，ORACLE缺省认定表中列的值是在所有数据行均匀分布的，也就是说每种deptno值各有25万
数据行与之对应。假设SQL搜索条件DEPTNO=10，利用deptno列上的索引进行数据搜索效率，往往不比全表扫描的高。
　　
　　。索引列值是否可为空（NULL）。如果索引列值可以是空值，在SQL语句中那些要返回NULL值的操作，将不会用到索引，如COUNT（*）
，而是用全表扫描。这是因为索引中存储值不能为全空。
　　
　　。看是否有用到并行查询（PQO）。并行查询将不会用到索引。
　　
　　。如果从以上几个方面都查不出原因的话，我们只好用采用在语句中加hint的方式强制ORACLE使用最优的“执行计划”。 　hint采用注
释的方式，有行注释和段注释两种方式。 　如我们想要用到A表的IND_COL1索引的话，可采用以下方式：
　SELECT /*+ INDEX（A IND_COL1）*/ * FROM A WHERE COL1 = XXX;
　　
　　如何屏蔽索引
　　
　　语句的执行计划中有不良索引时，可以人为地屏蔽该索引，方法：
　　
　　。数值型：在索引字段上加0，例如
　　select * from emp where emp_no+0 = v_emp_no;
　　
　　。字符型：在索引字段上加‘’，例如
　　select * from tg_cdr01 where msisdn||’’=v_msisdn;
---------------------------------------------------------------------------------------------------------------------------------------------------
--MartriWang@gmail.com 17/04/2007--

（1）RBO&CBO。

Oracle有两种执行优化器，一种是RBO(Rule Based Optimizer)基于规则的优化器，这种优化器是基于sql语句写法选择执行路径的；
另一种是CBO（Cost Based Optimizer）基于规则的优化器，这种优化器是Oracle根据统计分析信息来选择执行路径，如果表和索引
没有进行分析，Oracle将会使用RBO代替CBO；如果表和索引很久未分析，CBO也有可能选择错误执行路径，不过CBO是Oracle发展的
方向，自8i版本来已经逐渐取代RBO.

（2）AUTOTRACE。

要看索引是否被使用我们要借助Oracle的一个叫做AUTOTRACE功能,它显示了sql语句的执行路径，我们能看到Oracle内部是怎么执行
sql的，这是一个非常好的辅助工具，在sql调优里广泛被运用。我们来看一下怎么运用AUTOTRACE：

① 由于AUTOTRACE自动为用户指定了Execution Plan,因此该用户使用AUTOTRACE前必须已经建立了PLAN_TABLE。如果没有的话，请
运行utlxplan.sql脚本（它在$ORACLE_HOME/rdbms/admin目录中）。

② AUTOTRACE可以通过运行plustrce.sql脚本(它在$ORACLE_HOME/sqlplus/admin目录中)来设置，用sys用户登陆然后运行
plustrce.sql后会建立一个PLUSTRACE角色，然后给相关用户授予PLUSTRACE角色，然后这些用户就可以使用AUTOTRACE功能了。

③ AUTOTRACE的默认使用方法是set autotrace on，但是这方法不总是适合各种场合，特别当返回行数很多的时候。
Set autotrace traceonly提供了只查看统计信息而不查询数据的功能。

    SQL> set autotrace on
SQL> select * from test;
         A
----------
         1
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'TEST'
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  bytes sent via SQL*Net to client
          0  bytes received via SQL*Net from client
          0  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
rows processed

SQL> set autotrace traceonly
SQL> select * from test.test;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'TEST'

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  bytes sent via SQL*Net to client
          0  bytes received via SQL*Net from client
          0  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
rows processed

Hints是Oracle提供的一个辅助用法，按字面理解就是‘提示’的意思，确实它起得作用也是提示优化器按它所提供的关键字来
选择执行路径，特别适用于sql调整的时候。使用方法如下：

{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */

具体可参考Oracle SQL Reference。

有了前面这些知识点，接下来让我们来看一下什么时候索引是不起作用的。以下列出几种情况。

（1）类型不匹配时。

SQL> create table test.testindex (a varchar(2),b number);
表已创建。
SQL> create index ind_cola on test.testindex(a);
索引已创建。
SQL> insert into test.testindex values('1',1);
已创建 1 行。
SQL> commit;
提交完成。
SQL> analyze table test.testindex compute statistics for all indexes;
表已分析。
SQL> set autotrace on;

SQL> select /*+RULE */* FROM test.testindex where a='1';(使用基于rule的优化器，数据类型匹配的情况下)
A           B
-- ----------
1           1
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX'
   2    1     INDEX (RANGE SCAN) OF 'IND_COLA' (NON-UNIQUE)（使用了索引ind_cola）
――――――――――――――――――――――――――――――――――
SQL> select /*+RULE */* FROM test.testindex where a=1;（数据类型不匹配的情况）
A           B
-- ----------
1           1
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (FULL) OF 'TESTINDEX'（优化器选择了全表扫描）

（2）条件列包含函数但没有创建函数索引。

SQL> select /*+ RULE */*  FROM test.testindex where upper(a)= 'A';（使用了函数upper()在列a上）;
A           B
-- ----------
a           2
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (FULL) OF 'TESTINDEX'（优化器选择全表扫描）
----------------------------------------------------------
创建基于函数的索引
SQL> create index test.ind_fun on test.testindex(upper(a));
索引已创建。
SQL> insert into testindex values('a',2);
已创建1行。
SQL> commit;
提交完成。
SQL> select /*+ RULE*/*  FROM test.testindex where upper(a)='A';
A           B
-- ----------
a           2
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (FULL) OF 'TESTINDEX'
(在RULE优化器下忽略了函数索引选择了全表扫描)
-----------------------------------------------------------
SQL> select *  FROM test.testindex where upper(a)
='A';
A           B
-- ----------
a           2
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=5)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=2 Card=
          1 Bytes=5)
   2    1     INDEX (RANGE SCAN) OF 'IND_FUN' (NON-UNIQUE) (Cost=1 Car
          d=1)(CBO优化器使用了ind_fun索引)

（3）复合索引中的前导列没有被作为查询条件。

创建一个复合索引
SQL> create index ind_com on test.testindex(a,b);
索引已创建。
SQL> select /*+ RULE*/* from test.testindex where a='1';
A           B
-- ----------
1           2
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   INDEX (RANGE SCAN) OF 'IND_COM' (NON-UNIQUE)(条件列表包含前导列时使用索引ind_com)
SQL> select /*+ RULE*/* from test.testindex where b=1;
未选定行
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (FULL) OF 'TESTINDEX'(条件列表不包括前导列是选择全表扫描)
-----------------------------------------------------------

（4）CBO模式下选择的行数比例过大，优化器采取了全表扫描。

SQL> select * from test.testindex where a='1';
A           B
-- ----------
1           2
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=5)
   1    0   TABLE ACCESS (FULL) OF 'TESTINDEX' (Cost=1 Card=1 Bytes=5)
（表一共2行，选择比例为50%,所以优化器选择了全表扫描）
――――――――――――――――――――――――――――――――――
下面增加表行数
SQL> declare i number;
  2  begin
  3  for i in 1 .. 100 loop
  4  insert into test.testindex values (to_char(i),i);
  5  end loop;
  6  end;
  7  /
PL/SQL 过程已成功完成。
SQL> commit;
提交完成。
SQL> select count(*) from test.testindex;
  COUNT(*)
----------
102
SQL> select * from test.testindex where a='1';
A             B
---- ----------
1             1
1             2
Execution Plan
SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=5)
1    0   INDEX (RANGE SCAN) OF 'IND_COM' (NON-UNIQUE) (Cost=1 Card=1 Bytes=5)
（表一共102行，选择比例为2/102=2%,所以优化器选择了索引扫描）

（5）CBO模式下表很久没分析，表的增长明显，优化器采取了全表扫描。

SQL> select * from test.testindex where a like '1%';
A             B
---- ----------
1             2
1             1
10           10
11           11
12           12
13           13
14           14
15           15
16           16
17           17
18           18
19           19
100         100
已选择13行。
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=13 Bytes=52)
   1   0  TABLE ACCESS (FULL) OF 'TESTINDEX' (Cost=1 Card=13 Bytes=52)
(表一共102行，选择比例为13/102>10%,优化器选择了全表扫描)
――――――――――――――――――――――――――――――――――
增加表行数
SQL> declare i number;
  2  begin
  3  for i in 200 .. 1000 loop
  4  insert into test.testindex values (to_char(i),i);
  5  end loop;
  6  end;
  7  /

PL/SQL 过程已成功完成。
SQL> commit;
提交完成。
SQL> select count(*) from test.testindex;
  COUNT(*)
----------
903
SQL> select * from test.testindex where a like '1%';
A             B
----  ----------
1             2
1             1
10           10
11           11
12           12
13           13
14           14
15           15
16           16
17           17
18           18
19           19
100          100
1000         1000
已选择14行。
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=13 Bytes=52)
   1  0  TABLE ACCESS (FULL) OF 'TESTINDEX' (Cost=1 Card=13 Bytes=52)
  (表一共903行，选择比例为14/903<5%,优化器选择了全表扫描,选择路径是错误的)
―――――――――――――――――――――――――――――
给表做分析

SQL> analyze table test.testindex compute statistics for table for all indexed c
olumns for all indexes;
表已分析。
SQL> select * from test.testindex where a like '1%';
A             B
---- ----------
1             2
1             1
10           10
100         100
1000       1000
11           11
12           12
13           13
14           14
15           15
16           16
17           17
18           18
19           19
已选择14行。
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=24 Bytes=120)
   1   0  TABLE ACCESS (BY INDEX ROWID) OF 'TESTINDEX' (Cost=4 Card=
          24 Bytes=120)
   2  1  INDEX (RANGE SCAN) OF 'IND_COLA' (NON-UNIQUE) (Cost=2 Ca
          rd=24)
（经过分析后优化器选择了正确的路径，使用了ind_cola索引）

---------------------------------------------------------------------------------------------------------------------------------------------------
--MartriWang@gmail.com 18/05/2007--

--PCTFREE和PCTUSED调整

 PCTFREE存储参数
　　PCTFREE存储参数告诉ORACLE什么时候应该将数据块从对象的空闲列表中移出。ORACLE的默认参数是
PCTFREE=10；也就是说，一旦一个INSERT操作使得数据块的90%被使用，这个数据块就从空闲列表(free
list)中移出。
 PCTUSED存储参数
　　PCTUSED存储参数告诉ORACLE什么时候将以前满的数据块加到空闲列表中。当记录从数据表中删除时，
数据库的数据块就有空间接受新的记录，但只有当填充的空间降到PCTUSED值以下时，该数据块才被连接
到空闲列表中，才可以往其中插入数据。PCTUSED的默认值是PCTUSED=40。
　　存储参数规则小结
　　（1）PCTUSED较高意味着相对较满的数据块会被放置到空闲列表中，从而有效的重复使用数据块的空间，
但会导致I/O消耗。PCTUSED低意味着在一个数据块快空的时候才被放置到空闲列表中，数据块一次能接受很多
的记录，因此可以减少I/O消耗，提高性能。
　　（2）PCTFREE的值较大意味着数据块没有被利用多少就从空闲列表中断开连接，不利于数据块的充分使用。
PCTFREE过小的结果是，在更新时可能会出现数据记录迁移(Migration)的情况。(注：数据记录迁移(Migration
)是指记录在是UPDATE操作扩展了一个VARCHAR2类型的列或BLOB列后,PCTFREE参数所指定的空间不够扩展，从而
记录被ORACLE强制迁移到新的数据块，发生这种情况将较严重的影响ORACLE的性能，出现更新缓慢)。
　　（3）在批量的插入、删除或者更新操作之前，先删除该表上的索引，在操作完毕之后在重新建立，这样有
助于提高批量操作的整体速度，并且保证B树索引在操作之后有良好的性能。

--同优化器下的调整；
　　基于成本优化器(CBO)：
　　（1）ORACLE 8i 以上版本更多地使用成本优化器，因为它更加智能；
　　（2）通过optimizer_mode=all_rows 或 first_rows来选择CBO；通过
alter session set optimizer_goal=all_rows 或 first_rows来选择CBO;通过添加hint来选择CBO;
　　（3）使用基于成本优化的一个关键是：存在表和索引的统计资料。通过analyze table 获得表
的统计资料；通过analyze index获得索引的统计资料。
　　（4）对于超过5个表的连接的查询，建议不要使用成本优化器，而是在SQL语句中通过添加
/* + rule */提示或者通过指定的执行计划来避免可能会在20分钟以上的SQL解析时间。
　　基于规则优化器(RBO)：
　　（1）ORACLE 8i以及ORACLE的以前版本主要用(RBO),并且比较有效；
　　（2）通过optimizer_mode=rule来选择RBO；通过alter session set optimizer_goal=rule来选择
RBO; 通过添加/* + rule */来选择RBO;
　　（3）在RBO中，from 子句的表的顺序决定表的连接顺序。From 子句的最后一个表是驱动表，这个
表应该是最小的表。
　　（4）限定性最强的布尔表达式放在最底层。

--跟踪、优化SQL语句的方法
　　保证在实例级将TIMED_STATISTICS设置为TRUE(在 INIT.ORA中永久的设置它或执行 ALTER SYSTEM命
令临时设置它)；
　　保证将MAX_DUMP_FILE_SIZE设置的较高。此参数控制跟踪文件的大小。
　　决定USER_DUMP_DEST所指向的位置，并保证有足够的磁盘空间。这是放置跟踪文件的位置。
　　在应用系统运行时，打开所怀疑的回话的SQL_TRACE.(在 INIT.ORA中通过SQL_TRACE=TRUE永久的设置
对所有的回话进行跟踪或通过使用系统包DBMS_SYSTEM.set_sql_trace_in_session(sid，serial,true);命
令临时设置它)
　　执行业务相关操作；
　　设置跟踪结束(DBMS_SYSTEM.set_sql_trace_in_session(sid,serial,false)，如果没有该步骤，可能
跟踪文件中的信息不全，因为可能有一部分还在缓存中)；
　　定位跟踪文件；
　　对步骤6的跟踪文件进行TKPROF，生成报告文件；
　　研究此报告文件，可以看到CPU、DISK、 QUERY、 COUNT等参数和execution plan(执行计划)，优化开
销最大的SQL；
---------------------------------------------------------------------------------------------------------------------------------------------------
--MartriWang@gmail.com 20/05/2007--
这是因为当进行index full scan的时候 oracle定位到索引的root block，然后到branch block（如果有的话），
再定位到第一个leaf block, 然后根据leaf block的双向链表顺序读取。它所读取的块都是有顺序的，也是经过排序的。
而index fast full scan则不同，它是从段头开始，读取包含位图块，root block,所有的branch block, leaf block，
读取的顺序完全有物理存储位置决定，并采取多块读，没次读取db_file_multiblock_read_count个块。
索引是提高数据查询最有效的方法，也是最难全面掌握的技术，因为正确的索引可能使效率提高10000倍，而无效的索引
可能是浪费了数据库空间，甚至大大降低查询性能。

　　索引的管理成本

　　1、 存储索引的磁盘空间

　　2、 执行数据修改操作(INSERT、UPDATE、DELETE)产生的索引维护

　　3、 在数据处理时回需额外的回退空间。

　　实际数据修改测试:

　　一个表有字段A、B、C，同时进行插入10000行记录测试

　　在没有建索引时平均完成时间是2.9秒

　　在对A字段建索引后平均完成时间是6.7秒

　　在对A字段和B字段建索引后平均完成时间是10.3秒

　　在对A字段、B字段和C字段都建索引后平均完成时间是11.7秒

　　从以上测试结果可以明显看出索引对数据修改产生的影响

　　索引按存储方法分类

　　B*树索引

　　B*树索引是最常用的索引，其存储结构类似书的索引结构，
有分支和叶两种类型的存储数据块，分支块相当于书的大目录，叶块相当于索引到的具体的书页。一般索引及唯一约束索引都使用B*树索引。

　　位图索引

　　位图索引储存主要用来节省空间，减少ORACLE对数据块的访问，它采用位图偏移方式来与表的行ID号对应，采用位图索引一般是重复值
太多的表字段。位图索引在实际密集型OLTP(数据事务处理)中用得比较少，因为OLTP会对表进行大量的删除、修改、新建操作，ORACLE每次
进行操作都会对要操作的数据块加锁，所以多人操作很容易产生数据块锁等待甚至死锁现象。在OLAP(数据分析处理)中应用位图有优势，因
为OLAP中大部分是对数据库的查询操作，而且一般采用数据仓库技术，所以大量数据采用位图索引节省空间比较明显。

　　索引按功能分类

　　唯一索引

　　唯一索引有两个作用，一个是数据约束，一个是数据索引，其中数据约束主要用来保证数据的完整性，唯一索引产生的索引记录中每一
条记录都对应一个唯一的ROWID。

　　主关键字索引

　　主关键字索引产生的索引同唯一索引，只不过它是在数据库建立主关键字时系统自动建立的。

　　一般索引

　　一般索引不产生数据约束作用，其功能主要是对字段建立索引表，以提高数据查询速度。

　　索引按索引对象分类

　　单列索引(表单个字段的索引)

　　多列索引(表多个字段的索引)

　　函数索引(对字段进行函数运算的索引)

　　建立函数索引的方法:

　　create index 收费日期索引 on GC_DFSS(trunc(sk_rq))

　　create index 完全客户编号索引 on yhzl(qc_bh||kh_bh)

　　在对函数进行了索引后，如果当前会话要引用应设置当前会话的query_rewrite_enabled为TRUE。

　　alter session set query_rewrite_enabled=true

　　注:如果对用户函数进行索引的话，那用户函数应加上 deterministic参数，意思是函数在输入值固定的情况下返回值也固定。例:

　　create or replace function trunc_add(input_date date)return date deterministic

　　as

　　begin

　　return trunc(input_date+1);

　　end trunc_add;

　　应用索引的扫描分类

　　INDEX UNIQUE SCAN(按索引唯一值扫描)

　　select * from zl_yhjbqk where hbs_bh='5420016000'

　　INDEX RANGE SCAN(按索引值范围扫描)

　　select * from zl_yhjbqk where hbs_bh>'5420016000'

　　select * from zl_yhjbqk where qc_bh>'7001'

　　INDEX FAST FULL SCAN(按索引值快速全部扫描)

　　select hbs_bh from zl_yhjbqk order by hbs_bh

　　select count(*) from zl_yhjbqk

　　select qc_bh from zl_yhjbqk group by qc_bh

　　什么情况下应该建立索引

　　表的主关键字

　　自动建立唯一索引

　　如zl_yhjbqk(用户基本情况)中的hbs_bh(户标识编号)

　　表的字段唯一约束

　　ORACLE利用索引来保证数据的完整性

　　如lc_hj(流程环节)中的lc_bh+hj_sx(流程编号+环节顺序)

　　直接条件查询的字段

　　在SQL中用于条件约束的字段

　　如zl_yhjbqk(用户基本情况)中的qc_bh(区册编号)

　　select * from zl_yhjbqk where qc_bh=’7001’

　　查询中与其它表关联的字段

　　字段常常建立了外键关系

　　如zl_ydcf(用电成份)中的jldb_bh(计量点表编号)

　　select * from zl_ydcf a,zl_yhdb b where a.jldb_bh=b.jldb_bh and b.jldb_bh=’540100214511’

　　查询中排序的字段

　　排序的字段如果通过索引去访问那将大大提高排序速度

　　select * from zl_yhjbqk order by qc_bh(建立qc_bh索引)

　　select * from zl_yhjbqk where qc_bh='7001' order by cb_sx(建立qc_bh+cb_sx索引，注:只是一个索引，其中包括qc_bh和cb_sx字段)

　　查询中统计或分组统计的字段

　　select max(hbs_bh) from zl_yhjbqk

　　select qc_bh,count(*) from zl_yhjbqk group by qc_bh

　　什么情况下应不建或少建索引

　　表记录太少

　　如果一个表只有5条记录，采用索引去访问记录的话，那首先需访问索引表，再通过索引表访问数据表，一般索引表与数据表不在同一个
数据块，这种情况下ORACLE至少要往返读取数据块两次。而不用索引的情况下ORACLE会将所有的数据一次读出，处理速度显然会比用索引快。

　　如表zl_sybm(使用部门)一般只有几条记录，除了主关键字外对任何一个字段建索引都不会产生性能优化，实际上如果对这个表进行了统
计分析后ORACLE也不会用你建的索引，而是自动执行全表访问。如:

　　select * from zl_sybm where sydw_bh='5401'(对sydw_bh建立索引不会产生性能优化)

　　经常插入、删除、修改的表

　　对一些经常处理的业务表应在查询允许的情况下尽量减少索引，如zl_yhbm，gc_dfss，gc_dfys，gc_fpdy等业务表。

　　数据重复且分布平均的表字段

　　假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库
的查询速度。

　　经常和主字段一块查询但主字段索引值比较多的表字段

　　如gc_dfss(电费实收)表经常按收费序号、户标识编号、抄表日期、电费发生年月、操作标志来具体查询某一笔收款的情况，如果将所有的
字段都建在一个索引里那将会增加数据的修改、插入、删除时间，从实际上分析一笔收款如果按收费序号索引就已经将记录减少到只有几条，如
果再按后面的几个字段索引查询将对性能不产生太大的影响。

　　如何只通过索引返回结果

　　一个索引一般包括单个或多个字段，如果能不访问表直接应用索引就返回结果那将大大提高数据库查询的性能。对比以下三个SQL，其中对表
zl_yhjbqk的hbs_bh和qc_bh字段建立了索引:

　　1 select hbs_bh,qc_bh,xh_bz from zl_yhjbqk where qc_bh=’7001’

　　执行路径:

　　SELECT STATEMENT, GOAL = CHOOSE 11 265 5565

　　TABLE ACCESS BY INDEX ROWID DLYX ZL_YHJBQK 11 265 5565

　　INDEX RANGE SCAN DLYX 区册索引 1 265

　　平均执行时间(0.078秒)

　　2 select hbs_bh,qc_bh from zl_yhjbqk where qc_bh=’7001’

　　执行路径:

　　SELECT STATEMENT, GOAL = CHOOSE 11 265 3710

　　TABLE ACCESS BY INDEX ROWID DLYX ZL_YHJBQK 11 265 3710

　　INDEX RANGE SCAN DLYX 区册索引 1 265

　　平均执行时间(0.078秒)

　　3 select qc_bh from zl_yhjbqk where qc_bh=’7001’

　　执行路径:

　　SELECT STATEMENT, GOAL = CHOOSE 1 265 1060

　　INDEX RANGE SCAN DLYX 区册索引 1 265 1060

　　平均执行时间(0.062秒)

　　从执行结果可以看出第三条SQL的效率最高。执行路径可以看出第1、2条SQL都多执行了TABLE ACCESS BY INDEX ROWID(通过ROWID访问表)
这个步骤，因为返回的结果列中包括当前使用索引(qc_bh)中未索引的列(hbs_bh,xh_bz)，而第3条SQL直接通过QC_BH返回了结果，这就是通过
索引直接返回结果的方法。

　　如何重建索引

　　alter index 表电量结果表主键 rebuild

　　如何快速新建大数据量表的索引

　　如果一个表的记录达到100万以上的话，要对其中一个字段建索引可能要花很长的时间，甚至导致服务器数据库死机，因为在建索引的时候
ORACLE要将索引字段所有的内容取出并进行全面排序，数据量大的话可能导致服务器排序内存不足而引用磁盘交换空间进行，这将严重影响服
务器数据库的工作。解决方法是增大数据库启动初始化中的排序内存参数，如果要进行大量的索引修改可以设置10M以上的排序内存(ORACLE缺省
大小为64K)，在索引建立完成后应将参数修改回来，因为在实际OLTP数据库应用中一般不会用到这么大的排序内存。

--MartriWang@gmail.com 15/06/2007--

通过分析SQL语句的执行计划优化SQL （三）
　　第4章 ORACLE的优化器

　　优化器有时也被称为查询优化器，这是因为查询是影响数据库性能最主要的部分，不要以为只有SELECT语句是查询。实际上，带有任何
WHERE条件的DML(INSERT、UPDATE、DELETE)语句中都包含查询要求，在后面的文章中，当说到查询时，不一定只是指SELECT语句，也有可能
指DML语句中的查询部分。优化器是所有关系数据库引擎中的最神秘、最富挑战性的部件之一，从性能的角度看也是最重要的部分，它性能的
高低直接关系到数据库性能的好坏。

　　我们知道，SQL语句同其它语言(如C语言)的语句不一样，它是非过程化(non-procedural)的语句，即当你要取数据时，不需要告诉数据
库通过何种途径去取数据，如到底是通过索引取数据，还是应该将表中的每行数据都取出来，然后再通过一一比较的方式取数据(即全表扫描)，
这是由数据库的优化器决定的，这就是非过程化的含义，也就是说，如何取数据是由优化器决定，而不是应用开发者通过编程决定。在处理SQL
的SELECT、UPDATE、INSERT或DELETE语句时，Oracle 必须访问语句所涉及的数据，Oracle的优化器部分用来决定访问数据的有效路径，使得语
句执行所需的I/O和处理时间最小。

　　为了实现一个查询，内核必须为每个查询定制一个查询策略，或为取出符合条件的数据生成一个执行计划(execution plan)。典型的，对于
同一个查询，可能有几个执行计划都符合要求，都能得到符合条件的数据。例如，参与连接的表可以有多种不同的连接方法，这取决于连接条件
和优化器采用的连接方法。为了在多个执行计划中选择最优的执行计划，优化器必须使用一些实际的指标来衡量每个执行计划使用的资源(I/0次
数、CPU等)，这些资源也就是我们所说的代价(cost)。如果一个执行计划使用的资源多，我们就说使用执行计划的代价大。以执行计划的代价大
小作为衡量标准，优化器选择代价最小的执行计划作为真正执行该查询的执行计划，并抛弃其它的执行计划。

　　在ORACLE的发展过程中，一共开发过2种类型的优化器：基于规则的优化器和基于代价的优化器。这2种优化器的不同之处关键在于：取得代
价的方法与衡量代价的大小不同。现对每种优化器做一下简单的介绍：

　　基于规则的优化器 -- Rule Based (Heuristic) Optimization(简称RBO)：

　　在ORACLE7之前，主要是使用基于规则的优化器。ORACLE在基于规则的优化器中采用启发式的方法(Heuristic Approach)或规则(Rules)来生
成执行计划。例如，如果一个查询的where条件(where clause)包含一个谓词(predicate，其实就是一个判断条件，如”=”, “>”, ”<”等)，而且
该谓词上引用的列上有有效索引，那么优化器将使用索引访问这个表，而不考虑其它因素，如表中数据的多少、表中数据的易变性、索引的可选
择性等。此时数据库中没有关于表与索引数据的统计性描述，如表中有多上行，每行的可选择性等。优化器也不考虑实例参数，如multi block
i/o、可用排序内存的大小等，所以优化器有时就选择了次优化的计划作为真正的执行计划，导致系统性能不高。

　　如，对于select * from emp where deptno = 10这个查询来说，如果是使用基于规则的优化器，而且deptno列上有有效的索引，则会通过
deptno列上的索引来访问emp表。在绝大多数情况下，这是比较高效的，但是在一些特殊情况下，使用索引访问也有比较低效的时候，现举例说
明： 　
　　1) emp表比较小，该表的数据只存放在几个数据块中。此时使用全表扫描比使用索引访问emp表反而要好。因为表比较小，极有可能数据全
在内存中，所以此时做全表扫描是最快的。而如果使用索引扫描，需要先从索引中找到符合条件记录的rowid，然后再一一根据这些rowid从emp
中将数据取出来，在这种条件下，效率就会比全表扫描的效率要差一些。

　　2) emp表比较大时，而且deptno = 10条件能查询出表中大部分的数据如(50%)。如该表共有4000万行数据，共放在有500000个数据块中，
每个数据块为8k，则该表共有约4G，则这么多的数据不可能全放在内存中，绝大多数需要放在硬盘上。此时如果该查询通过索引查询，则是你梦
魇的开始。db_file_multiblock_read_count参数的值200。如果采用全表扫描，则需要500000/db_file_multiblock_read_count=500000/200=
2500次I/O。但是如果采用索引扫描，假设deptno列上的索引都已经cache到内存中，所以可以将访问索引的开销忽略不计。因为要读出4000万x
50% = 2000万数据，假设在读这2000万数据时，有99.9%的命中率，则还是需要20000次I/O,比上面的全表扫描需要的2500次多多了，所以在这种
情况下，用索引扫描反而性能会差很多。在这样的情况下，用全表扫描的时间是固定的，但是用索引扫描的时间会随着选出数据的增多使查询时
间相应的延长。

　　上面是枯燥的假设数据，现在以具体的实例给予验证：
　　环境: oracle 817 + linux + 阵列柜，表SWD_BILLDETAIL有3200多万数据；
　　表的id列、cn列上都有索引
　　经查看执行计划，发现执行select count(id) from SWD_BILLDETAIL;使用全表扫描，执行完用了大约1.50分钟(4次执行取平均，每次分别为
1.45 1.51 2.00 1.46)。而执行select count(id) from SWD_BILLDETAIL where cn <'6';却用了2个小时还没有执行完，经分析该语句使用了cn列
上的索引，然后利用查询出的rowid再从表中查询数据。我为什么不使用select count(cn) from SWD_BILLDETAIL where cn <'6';呢？
后面在分析执行路径的索引扫描时时会给出说明。

　　下面就是基于规则的优化器使用的执行路径与各个路径对应的等级：
　　RBO Path 1: Single Row by Rowid(等级最高)
　　RBO Path 2: Single Row by Cluster Join
　　RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key
　　RBO Path 4: Single Row by Unique or Primary Key
　　RBO Path 5: Clustered Join
　　RBO Path 6: Hash Cluster Key
　　RBO Path 7: Indexed Cluster Key
　　RBO Path 8: Composite Index
　　RBO Path 9: Single-Column Indexes
　　RBO Path 10: Bounded Range Search on Indexed Columns
　　RBO Path 11: Unbounded Range Search on Indexed Columns
　　RBO Path 12: Sort Merge Join
　　RBO Path 13: MAX or MIN of Indexed Column
　　RBO Path 14: ORDER BY on Indexed Column
　　RBO Path 15: Full Table Scan(等级最低)

　　上面的执行路径中，RBO认为越往下执行的代价越大，即等级越低。在RBO生成执行计划时，如果它发现有等级高的执行路径可用，则肯定
会使用等级高的路径，而不管任何其它影响性能的元素，即RBO通过上面的路径的等级决定执行路径的代价，执行路径的等级越高，则使用该执
行路径的代价越小。如上面2个例子所述，如果使用RBO，则肯定使用索引访问表，也就是选择了比较差的执行计划，这样会给数据库性能带来很
大的负面影响。为了解决这个问题，从ORACLE 7开始oracle引入了基于代价的优化器，下面给出了介绍。

基于代价的优化器 -- Cost Based Optimization(简称CBO)

　　Oracle把一个代价引擎(Cost Engine)集成到数据库内核中，用来估计每个执行计划需要的代价，该代价将每个执行计划所耗费的资源进行
量化，从而CBO可以根据这个代价选择出最优的执行计划。一个查询耗费的资源可以被分成3个基本组成部分：I/O代价、CPU代价、network代价。
I/O代价是将数据从磁盘读入内存所需的代价。访问数据包括将数据文件中数据块的内容读入到SGA的数据高速缓存中，在一般情况下，该代价是
处理一个查询所需要的最主要代价，所以我们在优化时，一个基本原则就是降低查询所产生的I/O总次数。CPU代价是处理在内存中数据所需要的
代价，如一旦数据被读入内存，则我们在识别出我们需要的数据后，在这些数据上执行排序(sort)或连接(join)操作，这需要耗费CPU资源。

　　对于需要访问跨节点(即通常说的服务器)数据库上数据的查询来说，存在network代价，用来量化传输操作耗费的资源。查询远程表的查询
或执行分布式连接的查询会在network代价方面花费比较大。

　　在使用CBO时，需要有表和索引的统计数据(分析数据)作为基础数据，有了这些数据，CBO才能为各个执行计划计算出相对准确的代价，从而
使CBO选择最佳的执行计划。所以定期的对表、索引进行分析是绝对必要的，这样才能使统计数据反映数据库中的真实情况。否则就会使CBO选择
较差的执行计划，影响数据库的性能。分析操作不必做的太频繁，一般来说，每星期一次就足够了。切记如果想使用CBO，则必须定期对表和索引
进行分析。

　　对于分析用的命令，随着数据库版本的升级，用的命令也发生了变换，在oracle 8i以前，主要是用ANALYZE命令。在ORACLE 8I以后，又引入
了DBMS_STATS存储包来进行分析。幸运的是从ORACLE 10G以后，分析工作变成自动的了，这减轻的DBA的负担，不过在一些特殊情况下，还需要一
些手工分析。

　　如果采用了CBO优化器，而没有对表和索引进行分析，没有统计数据，则ORACLE使用缺省的统计数据(至少在ORACLE 9I中是这样)，这可以从
oracle的文档上找到。使用的缺省值肯定与系统的实际统计值不一致，这可能会导致优化器选择错误的执行计划，影响数据库的性能。

　　要注意的是：虽然CBO的功能随着ORACLE新版本的推出，功能越来越强，但它不是能包治百病的神药，否则就不再需要DBA了，那我就惨了
实际上任何一个语句，随着硬件环境与应用数据的不同，该语句的执行计划可能需要随之发生变化，这样才能取得最好的性能。所以有时候不
在具体的环境下而进行SQL性能调整是徒劳的。

　　在ORACLE8I推出的时候，ORACLE极力建议大家使用CBO，说CBO有种种好处，但是在那是ORACLE开发的应用系统还是使用基于规则的优化器，
从这件事上我们可以得出这样的结论：
　　
　　1) 如果团队的数据库水平很高而且都熟悉应用数据的特点，RBO也可以取得很好的性能。
　　
　　2）CBO不是很稳定，但是一个比较有前途的优化器，Oracle极力建议大家用是为了让大家尽快发现它的BUG，以便进一步改善，但是
ORACLE为了对自己开发的应用系统负责，他们还是使用了比较熟悉而且成熟的RBO。从这个事情上给我们的启发就是：我们在以后的开发中，
应该尽量采用我们熟悉并且成熟的技术，而不要一味的采用新技术，一味采用新技术并不一定能开发出好的产品。幸运的是从ORACLE 10G后，
CBO已经足够的强大与智能，大家可以放心的使用该技术，因为ORACLE 10G后，Oracle自己开发的应用系统也使用CBO优化器了。而且ORACLE
规定，从ORACLE 10G开始，开始废弃RBO优化器。这句话并不是指在ORACLE 10G中不能使用RBO，而是从ORACLE 10G开始开始，不再为RBO的
BUG提供修补服务。

　　在上面的第2个例子中，如果采用CBO优化器，它就会考虑emp表的行数，deptno列的统计数据，发现对该列做查询会查询出过多的数据，
并且考虑db_file_multiblock_read_count参数的设置，发现用全表扫描的代价比用索引扫描的代价要小，从而使用全表扫描从而取得良好
的执行性能。

　　判断当前数据库使用何种优化器：

　　主要是由optimizer_mode初始化参数决定的。该参数可能的取值为：first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows |
 choose | rule。具体解释如下：
　　RULE为使用RBO优化器。
　　CHOOSE则是根据实际情况，如果数据字典中包含被引用的表的统计数据，即引用的对象已经被分析，则就使用CBO优化器，否则为RBO优
化器。
　　ALL_ROWS为CBO优化器使用的第一种具体的优化方法，是以数据的吞吐量为主要目标，以便可以使用最少的资源完成语句。
　　FIRST_ROWS为优化器使用的第二种具体的优化方法，是以数据的响应时间为主要目标，以便快速查询出开始的几行数据。
　　FIRST_ROWS_[1 | 10 | 100 | 1000] 为优化器使用的第三种具体的优化方法，让优化器选择一个能够把响应时间减到最小的查询执行
计划，以迅速产生查询结果的前 n 行。该参数为ORACLE 9I新引入的。

　　从ORACLE V7以来，optimizer_mode参数的缺省设置应是"choose"，即如果对已分析的表查询的话选择CBO，否则选择RBO。在此种设置中，
如果采用了CBO，则缺省为CBO中的all_rows模式。

　　注意：即使指定数据库使用RBO优化器，但有时ORACLE数据库还是会采用CBO优化器，这并不是ORACLE的BUG，主要是由于从ORACLE 8I后引
入的许多新特性都必须在CBO下才能使用，而你的SQL语句可能正好使用了这些新特性，此时数据库会自动转为使用CBO优化器执行这些语句。

　　什么是优化

　　优化是选择最有效的执行计划来执行SQL语句的过程，这是在处理任何数据的语句（SELECT,INSERT,UPDATE或DELETE）中的一个重要步骤。
对Oracle来说，执行这样的语句有许多不同的方法，譬如说，将随着以什么顺序访问哪些表或索引的不同而不同。所使用的执行计划可以决定
语句能执行得有多快。Oracle中称之为优化器（Optimizer）的组件用来选择这种它认为最有效的执行计划。

　　由于一系列因素都会会影响语句的执行，优化器综合权衡各个因素，在众多的执行计划中选择认为是最佳的执行计划。然而，应用设计人
员通常比优化器更知道关于特定应用的数据特点。无论优化器多么智能，在某些情况下开发人员能选择出比优化器选择的最优执行计划还要好
的执行计划。这是需要人工干预数据库优化的主要原因。事实表明，在某些情况下，确实需要DBA对某些语句进行手工优化。
　　注：从Oracle的一个版本到另一个版本，优化器可能对同一语句生成不同的执行计划。在将来的Oracle 版本中，优化器可能会基于它可以
用的更好、更理想的信息，作出更优的决策，从而导致为语句产生更优的执行计划。

在物理层，oracle读取数据，一次读取的最小单位为数据库块(由多个连续的操作系统块组成)，一次读取的最大值由操作系统一次I/O的最大值
与multiblock参数共同决定，所以即使只需要一行数据，也是将该行所在的数据库块读入内存。逻辑上，oracle用如下存取方法访问数据：
--MartriWang@gmail.com 15/06/2007--

　　1) 全表扫描（Full Table Scans, FTS）

　　为实现全表扫描，Oracle读取表中所有的行，并检查每一行是否满足语句的WHERE限制条件。Oracle顺序地读取分配给表的每个数据块，直
到读到表的最高水线处(high water mark, HWM，标识表的最后一个数据块)。一个多块读操作可以使一次I/O能读取多块数据块
(db_block_multiblock_read_count参数设定)，而不是只读取一个数据块，这极大的减少了I/O总次数，提高了系统的吞吐量，所以利用多块读
的方法可以十分高效地实现全表扫描，而且只有在全表扫描的情况下才能使用多块读操作。在这种访问模式下，每个数据块只被读一次。由于
HWM标识最后一块被读入的数据，而delete操作不影响HWM值，所以一个表的所有数据被delete后，其全表扫描的时间不会有改善，一般我们需要
使用truncate命令来使HWM值归为0。幸运的是oracle 10G后，可以人工收缩HWM的值。

　　由FTS模式读入的数据被放到高速缓存的Least Recently Used (LRU)列表的尾部，这样可以使其快速交换出内存，从而不使内存重要的数据
被交换出内存。使用FTS的前提条件：在较大的表上不建议使用全表扫描，除非取出数据的比较多，超过总量的5% -- 10%，或你想使用并行查询
功能时。
　　使用全表扫描的例子：
　　~~~~~~~~~~~~~~~~~~~~~~~~
SQL> explain plan for select * from dual;
Query Plan
-----------------------------------------
SELECT STATEMENT [CHOOSE] Cost=
TABLE ACCESS FULL DUAL

　　2) 通过ROWID的表存取（Table Access by ROWID或rowid lookup）

　　行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID来存取数据可以快速定位到目标数据上，是Oracle存取
单行数据的最快方法。为了通过ROWID存取表，Oracle 首先要获取被选择行的ROWID，或者从语句的WHERE子句中得到，或者通过表的一个或多个索
引的索引扫描得到。Oracle然后以得到的ROWID为依据定位每个被选择的行。

　　这种存取方法不会用到多块读操作，一次I/O只能读取一个数据块。我们会经常在执行计划中看到该存取方法，如通过索引查询数据。

　　使用ROWID存取的方法：
SQL> explain plan for select * from dept where rowid = 'AAAAyGAADAAAAATAAF';
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID DEPT [ANALYZED]

3）索引扫描（Index Scan或index lookup）

　　我们先通过index查找到数据对应的rowid值(对于非唯一索引可能返回多个rowid值)，然后根据rowid直接从表中得到具体的数据，这种查找方式
称为索引扫描或索引查找(index lookup)。一个rowid唯一的表示一行数据，该行对应的数据块是通过一次i/o得到的，在此情况下该次i/o只会读取
一个数据库块。

　　在索引中，除了存储每个索引的值外，索引还存储具有此值的行对应的ROWID值。索引扫描可以由2步组成：
　　(1) 扫描索引得到对应的rowid值。
　　(2) 通过找到的rowid从表中读出具体的数据。每步都是单独的一次I/O，但是对于索引，由于经常使用，绝大多数都已经CACHE到内存中，所以第
1步的I/O经常是逻辑I/O，即数据可以从内存中得到。但是对于第2步来说，如果表比较大，则其数据不可能全在内存中，所以其I/O很有可能是物理I/O，
这是一个机械操作，相对逻辑I/O来说，是极其费时间的。所以如果多大表进行索引扫描，取出的数据如果大于总量的5% -- 10%，使用索引扫描会效率
下降很多。
　　如下列所示：
SQL> explain plan for select empno, ename from emp where empno=10;
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX UNIQUE SCAN EMP_I1

　　注意TABLE ACCESS BY ROWID EMP部分，这表明这不是通过FTS存取路径访问数据，而是通过rowid lookup存取路径访问数据的。在此例中，所需要
的rowid是由于在索引查找empno列的值得到的，这种方式是INDEX UNIQUE SCAN查找，后面给予介绍，EMP_I1为使用的进行索引查找的索引名字。

　　但是如果查询的数据能全在索引中找到，就可以避免进行第2步操作，避免了不必要的I/O，此时即使通过索引扫描取出的数据比较多，效率还是很
高的，因为这只会在索引中读取。所以上面我在介绍基于规则的优化器时，使用了select count(id) from SWD_BILLDETAIL where cn <'6'，而没有使
用select count(cn) from SWD_BILLDETAIL where cn <'6'。因为在实际情况中，只查询被索引列的值的情况极为少，所以，如果我在查询中使用count
(cn)，则不具有代表性。

SQL> explain plan for select empno from emp where empno=10; -- 只查询empno列值
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
INDEX UNIQUE SCAN EMP_I1

　　进一步讲，如果sql语句中对索引列进行排序，因为索引已经预先排序好了，所以在执行计划中不需要再对索引列进行排序

SQL> explain plan for select empno, ename from emp
where empno > 7876 order by empno;
Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX RANGE SCAN EMP_I1 [ANALYZED]

　　从这个例子中可以看到：因为索引是已经排序了的，所以将按照索引的顺序查询出符合条件的行，因此避免了进一步排序操作。

　　根据索引的类型与where限制条件的不同，有4种类型的索引扫描：
　　索引唯一扫描(index unique scan)
　　索引范围扫描(index range scan)
　　索引全扫描(index full scan)
　　索引快速扫描(index fast full scan)

　　(1) 索引唯一扫描(index unique scan)

　　通过唯一索引查找一个数值经常返回单个ROWID。如果该唯一索引有多个列组成(即组合索引)，则至少要有组合索引的引导列参
与到该查询中，如创建一个索引：
create index idx_test on emp(ename, deptno, loc)。则select ename from emp where ename = 'JACK' and deptno = 'DEV'语
句可以使用该索引。如果该语句只返回一行，则存取方法称为索引唯一扫描。而select ename from emp where deptno = 'DEV'语句
则不会使用该索引，因为where子句种没有引导列。如果存在UNIQUE 或PRIMARY KEY 约束（它保证了语句只存取单行）的话，

Oracle经常实现唯一性扫描。
　　使用唯一性约束的例子：
SQL> explain plan for
select empno,ename from emp where empno=10;
Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX UNIQUE SCAN EMP_I1

　　(2) 索引范围扫描(index range scan)

　　使用一个索引存取多行数据，同上面一样，如果索引是组合索引，如(1)所示，
而且select ename from emp where ename = 'JACK' and deptno = 'DEV'语句返回多行数据，虽然该语句还是使用该组合索引进行查
询，可此时的存取方法称为索引范围扫描。在唯一索引上使用索引范围扫描的典型情况下是在谓词(where限制条件)中使用了范围操作
符(如>、<、<>、>=、<=、between)

　　使用索引范围扫描的例子：
SQL> explain plan for select empno,ename from emp
where empno > 7876 order by empno;
Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX RANGE SCAN EMP_I1 [ANALYZED]

　　在非唯一索引上，谓词col = 5可能返回多行数据，所以在非唯一索引上都使用索引范围扫描。
　　使用index rang scan的3种情况：
　　(a) 在唯一索引列上使用了range操作符(> < <> >= <= between)
　　(b) 在组合索引上，只使用部分列进行查询，导致查询出多行
　　(c) 对非唯一索引列上进行的任何查询。

　　(3) 索引全扫描(index full scan)

　　与全表扫描对应，也有相应的全索引扫描。在某些情况下，可能进行全索引扫描而不是范围扫描，需要注意的是全索引扫描只在
CBO模式下才有效。CBO根据统计数值得知进行全索引扫描比进行全表扫描更有效时，才进行全索引扫描，而且此时查询出的数据都必
须从索引中可以直接得到。
　　全索引扫描的例子：
An Index full scan will not perform single block i/o s and so it may prove to be inefficient.

e.g.
Index BE_IX is a concatenated index on big_emp (empno, ename)

SQL> explain plan for select empno, ename from big_emp order by empno,ename;
Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT [CHOOSE] Cost=26
INDEX FULL SCAN BE_IX [ANALYZED]

　　(4) 索引快速扫描(index fast full scan)

　　扫描索引中的所有的数据块，与 index full scan很类似，但是一个显著的区别就是它不对查询出的数据进行排序，即数据不是
以排序顺序被返回。在这种存取方法中，可以使用多块读功能，也可以使用并行读入，以便获得最大吞吐量与缩短执行时间。

　　索引快速扫描的例子：
　　BE_IX索引是一个多列索引：big_emp (empno,ename)

SQL> explain plan for select empno,ename from big_emp;
Query Plan
------------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
INDEX FAST FULL SCAN BE_IX [ANALYZED]

　　只选择多列索引的第2列：

SQL> explain plan for select ename from big_emp;
Query Plan
------------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
INDEX FAST FULL SCAN BE_IX [ANALYZED]

--MartriWang@gmail.com 15/06/2007--
表之间的连接

　　Join是一种试图将两个表结合在一起的谓词，一次只能连接2个表，表连接也可以被称为表关联。在后面的叙述中，我们将会使用”row

source”来代替”表”，因为使用row source更严谨一些，并且将参与连接的2个row source分别称为row source1和row source 2。Join过程

的各个步骤经常是串行操作，即使相关的row source可以被并行访问，即可以并行的读取做join连接的两个row source的数据，但是在将表中

符合限制条件的数据读入到内存形成row source后，join的其它步骤一般是串行的。有多种方法可以将2个表连接起来，当然每种方法都有自己

的优缺点，每种连接类型只有在特定的条件下才会发挥出其最大优势。

　　row source(表)之间的连接顺序对于查询的效率有非常大的影响。通过首先存取特定的表，即将该表作为驱动表，这样可以先应用某些限

制条件，从而得到一个较小的row source，使连接的效率较高，这也就是我们常说的要先执行限制条件的原因。一般是在将表读入内存时，应

用where子句中对该表的限制条件。

　　根据2个row source的连接条件的中操作符的不同，可以将连接分为等值连接(如WHERE A.COL3 = B.COL4)、非等值连接(WHERE A.COL3 >

B.COL4)、外连接(WHERE A.COL3 = B.COL4(+))。上面的各个连接的连接原理都基本一样，所以为了简单期间，下面以等值连接为例进行介绍。

在后面的介绍中，都已：
　　SELECT A.COL1, B.COL2
　　FROM A, B
　　WHERE A.COL3 = B.COL4;

　　为例进行说明，假设A表为Row Soruce1，则其对应的连接操作关联列为COL 3；B表为Row Soruce2，则其对应的连接操作关联列为COL 4；

　　连接类型：

　　目前为止，无论连接操作符如何，典型的连接类型共有3种：
　　排序 - - 合并连接(Sort Merge Join (SMJ) )
　　嵌套循环(Nested Loops (NL) )
　　哈希连接(Hash Join)

　　排序 - - 合并连接(Sort Merge Join, SMJ)

　　内部连接过程：
　　1) 首先生成row source1需要的数据，然后对这些数据按照连接操作关联列(如A.col3)进行排序。
　　2) 随后生成row source2需要的数据，然后对这些数据按照与sort source1对应的连接操作关联列(如B.col4)进行排序。
　　3) 最后两边已排序的行被放在一起执行合并操作，即将2个row source按照连接条件连接起来
　　下面是连接步骤的图形表示：
　　　　　　　　　　　　　MERGE
　　　　　　　　　　　　/　　　 \
　　　　　　　　　　 SORT 　　　 SORT
　　　　　　　　　　　| 　　　　　|
　　　　　　　Row Source 1　　　 Row Source 2

　　如果row source已经在连接关联列上被排序，则该连接操作就不需要再进行sort操作，这样可以大大提高这种连接操作的连接速度，因为

排序是个极其费资源的操作，特别是对于较大的表。 预先排序的row source包括已经被索引的列(如a.col3或b.col4上有索引)或row source已

经在前面的步骤中被排序了。尽管合并两个row source的过程是串行的，但是可以并行访问这两个row source(如并行读入数据，并行排序).

　　SMJ连接的例子：

SQL> explain plan for
select /*+ ordered */ e.deptno, d.deptno
from emp e, dept d
where e.deptno = d.deptno
order by e.deptno, d.deptno;

Query Plan
-------------------------------------
SELECT STATEMENT [CHOOSE] Cost=17
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL EMP [ANALYZED]
SORT JOIN
TABLE ACCESS FULL DEPT [ANALYZED]

　　排序是一个费时、费资源的操作，特别对于大表。基于这个原因，SMJ经常不是一个特别有效的连接方法，但是如果2个row source都已经

预先排序，则这种连接方法的效率也是蛮高的。

　　嵌套循环(Nested Loops, NL)

　　这个连接方法有驱动表(外部表)的概念。其实，该连接过程就是一个2层嵌套循环，所以外层循环的次数越少越好，这也就是我们为什么将

小表或返回较小row source的表作为驱动表(用于外层循环)的理论依据。但是这个理论只是一般指导原则，因为遵循这个理论并不能总保证使

语句产生的I/O次数最少。有时不遵守这个理论依据，反而会获得更好的效率。如果使用这种方法，决定使用哪个表作为驱动表很重要。有时如

果驱动表选择不正确，将会导致语句的性能很差、很差。

　　 内部连接过程：
　　Row source1的Row 1 -------------- -- Probe -> Row source 2
　　Row source1的Row 2 -------------- -- Probe -> Row source 2
　　Row source1的Row 3 -------------- -- Probe -> Row source 2
　　…….
　　Row source1的Row n -------------- -- Probe -> Row source 2

　　从内部连接过程来看，需要用row source1中的每一行，去匹配row source2中的所有行，所以此时保持row source1尽可能的小与高效的访

问row source2(一般通过索引实现)是影响这个连接效率的关键问题。这只是理论指导原则，目的是使整个连接操作产生最少的物理I/O次数，

而且如果遵守这个原则，一般也会使总的物理I/O数最少。但是如果不遵从这个指导原则，反而能用更少的物理I/O实现连接操作，那尽管违反

指导原则吧！因为最少的物理I/O次数才是我们应该遵从的真正的指导原。

　　在上面的连接过程中，我们称Row source1为驱动表或外部表。Row Source2被称为被探查表或内部表。

　　在NESTED LOOPS连接中，Oracle读取row source1中的每一行，然后在row sourc2中检查是否有匹配的行，所有被匹配的行都被放到结果集

中，然后处理row source1中的下一行。这个过程一直继续，直到row source1中的所有行都被处理。这是从连接操作中可以得到第一个匹配行

的最快的方法之一，这种类型的连接可以用在需要快速响应的语句中，以响应速度为主要目标。

　　如果driving row source(外部表)比较小，并且在inner row source(内部表)上有唯一索引，或有高选择性非唯一索引时，使用这种方法

可以得到较好的效率。NESTED LOOPS有其它连接方法没有的的一个优点是：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返

回数据，这可以实现快速的响应时间。

　　如果不使用并行操作，最好的驱动表是那些应用了where 限制条件后，可以返回较少行数据的的表，所以大表也可能称为驱动表，关键看

限制条件。对于并行查询，我们经常选择大表作为驱动表，因为大表可以充分利用并行功能。当然，有时对查询使用并行操作并不一定会比查

询不使用并行操作效率高，因为最后可能每个表只有很少的行符合限制条件，而且还要看你的硬件配置是否可以支持并行(如是否有多个CPU，

多个硬盘控制器)，所以要具体问题具体对待。

　　NL连接的例子：
SQL> explain plan for
select a.dname,b.sql
from dept a,emp b
where a.deptno = b.deptno;

Query Plan
-------------------------
SELECT STATEMENT [CHOOSE] Cost=5
NESTED LOOPS
TABLE ACCESS FULL DEPT [ANALYZED]
TABLE ACCESS FULL EMP [ANALYZED]

　　哈希连接(Hash Join, HJ)

　　这种连接是在oracle 7.3以后引入的，从理论上来说比NL与SMJ更高效，而且只用在CBO优化器中。较小的row source被用来构建hash

table与bitmap，第2个row source被用来被hansed，并与第一个row source生成的hash table进行匹配，以便进行进一步的连接。Bitmap被用

来作为一种比较快的查找方法，来检查在hash table中是否有匹配的行。特别的，当hash table比较大而不能全部容纳在内存中时，这种查找

方法更为有用。这种连接方法也有NL连接中所谓的驱动表的概念，被构建为hash table与bitmap的表为驱动表，当被构建的hash table与

bitmap能被容纳在内存中时，这种连接方式的效率极高。

　　HASH连接的例子：
SQL> explain plan for
select /*+ use_hash(emp) */ empno
from emp, dept
where emp.deptno = dept.deptno;

Query Plan
----------------------------
SELECT STATEMENT [CHOOSE] Cost=3
HASH JOIN
TABLE ACCESS FULL DEPT
TABLE ACCESS FULL EMP

　　要使哈希连接有效，需要设置HASH_JOIN_ENABLED=TRUE，缺省情况下该参数为TRUE，另外，不要忘了还要设置hash_area_size参数，以使

哈希连接高效运行，因为哈希连接会在该参数指定大小的内存中运行，过小的参数会使哈希连接的性能比其他连接方式还要低。

　　总结一下，在哪种情况下用哪种连接方法比较好：

　　排序 - - 合并连接(Sort Merge Join, SMJ)：
　　a) 对于非等值连接，这种连接方式的效率是比较高的。
　　b) 如果在关联的列上都有索引，效果更好。
　　c) 对于将2个较大的row source做连接，该连接方法比NL连接要好一些。
　　d) 但是如果sort merge返回的row source过大，则又会导致使用过多的rowid在表中查询数据时，数据库性能下降，因为过多的I/O。

　　嵌套循环(Nested Loops, NL)：
　　a) 如果driving row source(外部表)比较小，并且在inner row source(内部表)上有唯一索引，或有高选择性非唯一索引时，使用这种方

法可以得到较好的效率。
　　b) NESTED LOOPS有其它连接方法没有的的一个优点是：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以

实现快速的响应时间。

　　哈希连接(Hash Join, HJ)：
　　a) 这种方法是在oracle7后来引入的，使用了比较先进的连接理论，一般来说，其效率应该好于其它2种连接，但是这种连接只能用在CBO

优化器中，而且需要设置合适的hash_area_size参数，才能取得较好的性能。
　　b) 在2个较大的row source之间连接时会取得相对较好的效率，在一个row source较小时则能取得更好的效率。
　　c) 只能用于等值连接中

　　笛卡儿乘积(Cartesian Product)

　　当两个row source做连接，但是它们之间没有关联条件时，就会在两个row source中做笛卡儿乘积，这通常由编写代码疏漏造成(即程序员

忘了写关联条件)。笛卡尔乘积是一个表的每一行依次与另一个表中的所有行匹配。在特殊情况下我们可以使用笛卡儿乘积，如在星形连接中，

除此之外，我们要尽量使用笛卡儿乘积，否则，自己想结果是什么吧！

　　注意在下面的语句中，在2个表之间没有连接。
SQL> explain plan for
select emp.deptno,dept,deptno
from emp,dept

Query Plan
------------------------------
SLECT STATEMENT [CHOOSE] Cost=5
MERGE JOIN CARTESIAN
TABLE ACCESS FULL DEPT
SORT JOIN
TABLE ACCESS FULL EMP

　　CARTESIAN关键字指出了在2个表之间做笛卡尔乘积。假如表emp有n行，dept表有m行，笛卡尔乘积的结果就是得到n * m行结果。
使用全套的hints：
　　当使用hints时，在某些情况下，为了确保让优化器产生最优的执行计划，我们可能指定全套的hints。例如，如果有一个复杂的查询，
包含多个表连接，如果你只为某个表指定了INDEX提示(指示存取路径在该表上使用索引)，优化器需要来决定其它应该使用的访问路径和相
应的连接方法。因此，即使你给出了一个INDEX提示，优化器可能觉得没有必要使用该提示。这是由于我们让优化器选择了其它连接方法和
存取路径，而基于这些连接方法和存取路径，优化器认为用户给出的INDEX提示无用。为了防止这种情况，我们要使用全套的hints，如不
但指定要使用的索引，而且也指定连接的方法与连接的顺序等。
　　
--MartriWang@gmail.com 15/06/2007--
使用全套hints的例子，ORDERED提示指出了连接的顺序，而且为不同的表指定了连接方法：

SELECT /*+ ORDERED INDEX (b, jl_br_balances_n1) USE_NL (j b)
USE_NL (glcc glf) USE_MERGE (gp gsb) */
b.application_id, b.set_of_books_id ,
b.personnel_id, p.vendor_id Personnel,
p.segment1 PersonnelNumber, p.vendor_name Name
FROM jl_br_journals j, jl_br_balances b,
gl_code_combinations glcc, fnd_flex_values_vl glf,
gl_periods gp, gl_sets_of_books gsb, po_vendors p
WHERE ...

　　指示优化器的方法与目标的hints：

ALL_ROWS -- 基于代价的优化器，以吞吐量为目标
FIRST_ROWS(n) -- 基于代价的优化器，以响应时间为目标
CHOOSE -- 根据是否有统计信息，选择不同的优化器
RULE -- 使用基于规则的优化器

　　例子：
SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id
FROM employees
WHERE department_id = 20;

SELECT /*+ CHOOSE */ employee_id, last_name, salary, job_id
FROM employees
WHERE employee_id = 7566;

SELECT /*+ RULE */ employee_id, last_name, salary, job_id
FROM employees
WHERE employee_id = 7566;

　　指示存储路径的hints：

FULL 　　/*+ FULL ( table ) */
　　　　　指定该表使用全表扫描
ROWID 　/*+ ROWID ( table ) */
　　　　　指定对该表使用rowid存取方法，该提示用的较少
INDEX 　/*+ INDEX ( table [index]) */
　　　　　使用该表上指定的索引对表进行索引扫描
INDEX_FFS /*+ INDEX_FFS ( table [index]) */
　　　　　使用快速全表扫描
NO_INDEX /*+ NO_INDEX ( table [index]) */
　　　　　不使用该表上指定的索引进行存取，仍然可以使用其它的索引进行索引扫描

SELECT /*+ FULL(e) */ employee_id, last_name
FROM employees e
WHERE last_name LIKE :b1;

SELECT /*+ROWID(employees)*/ *
FROM employees
WHERE rowid > 'AAAAtkAABAAAFNTAAA' AND employee_id = 155;

SELECT /*+ INDEX(A sex_index) use sex_index because there are few
male patients */ A.name, A.height, A.weight
FROM patients A
WHERE A.sex = 'm';

SELECT /*+NO_INDEX(employees emp_empid)*/ employee_id
FROM employees
WHERE employee_id > 200;

　　指示连接顺序的hints:
ORDERED 　/*+ ORDERED */
　　　　　按from 字句中表的顺序从左到右的连接
STAR 　　/*+ STAR */
　　　　　指示优化器使用星型查询

SELECT /*+ORDERED */ o.order_id, c.customer_id, l.unit_price * l.quantity
FROM customers c, order_items l, orders o
WHERE c.cust_last_name = :b1
AND o.customer_id = c.customer_id
AND o.order_id = l.order_id;

/*+ ORDERED USE_NL(FACTS) INDEX(facts fact_concat) */

指示连接类型的hints：
USE_NL 　/*+ USE_NL ( table [,table, ...] ) */
　　　　　使用嵌套连接
USE_MERGE /*+ USE_MERGE ( table [,table, ...]) */
　　　　　使用排序- -合并连接
USE_HASH /*+ USE_HASH ( table [,table, ...]) */
　　　　　使用HASH连接
注意：如果表有alias(别名)，则上面的table指的是表的别名，而不是真实的表名

--MartriWang@gmail.com 25/06/2007--

oracle最重要的9个动态性能视图！

v$session + v$session_wait
v$process
v$sql
v$sqltext
v$bh (更宁愿是x$bh)
v$lock
v$latch_children
v$sysstat
v$system_event

按组分的几组重要的性能视图

1。System 的 over view
v$sysstat , v$system_event , v$parameter

2。某个session 的当前情况
v$process , v$session , v$session_wait ,v$session_event , v$sesstat

3。SQL 的情况
v$sql , v$sqlarea , v$SQL_PLAN , V$SQL_PLAN_STATISTICS, v$sqltext_with_newlines

3. Latch / lock /ENQUEUE
v$latch , v$latch_children , v$latch_holder , v$lock ,V$ENQUEUE_STAT ,V$ENQUEUE_LOCK

4. IO 方面的
v$segstat , v$filestat , v$tempstat ,v$datafile , v$tempfile

5.shared pool / Library cache
v$Librarycache , v$rowcache , x$ksmsp

6.几个advice也不错
v$db_cache_advice , v$PGA_TARGET_ADVICE, v$SHARED_POOL_ADVICE

 

转载：http://blog.csdn.net/wonth/archive/2007/06/28/1670366.aspx