oracle 调优3-2988729

693579551

　　（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 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中永久的设置它或执行>　　令临时设置它)；
　　保证将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，其实就是一个判断条件，如”=”, “>”, ” 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  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>　　句可以使用该索引。如果该语句只返回一行，则存取方法称为索引唯一扫描。而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限制条件)中使用了范围操作
　　符(如>、=、 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操作符(> <  >=  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