（我的学习笔记）
1、 基础概念及理论
（1） 优化器：我们公司现在用的ORACLE 版本是ORACLE 10G，此版本用到的优化器是CBO，也就是通常所说的基于成本的优化器，所谓的成本的就是ORACLE在处理一条具体的语句时候CPU成本，I/O成本,网络成本的总和，当然ORACLE具体的如何算的比较复杂，此处只给出概念。
（2） 执行计划：执行计划对于DBA或者AD来说就像财务报表基于财务一样重要，执行计划给出了当前SQL运行的轨迹。给出了SQL的运行时每步所消耗的代价（COST），并且给出了ORACLE在遇到JOIN（连接），索引（INDEX）等操作的时候如何处理的，处理了输入了多少条记录，输入记录的字节数。
（3） 什么是优化：优化是选择最有效的执行计划来执行SQL语句的过程，这是在处理任何数据的语句（SELECT,INSERT,UPDATE或DELETE）中的一个重要步骤
（4） 何时停止优化：当优化效果达到我们预先制定的目标的时候，优化就应该结束，否则优化将无止境。
（5） 为何添加索引会加快查询：索引是以一种B-TREE的结构来存储数据，并且在块上记录的是键值的数据，以及此行的ROWID，（ROWID是一种伪劣，在ORACLE中ROWID是最快对用到行的方式，因为每行只有唯一ROWID），比如我们的语句如下：SELECT * FROM TEST WHERE TEST.ID=111。如果ID列上建立有索引，ORACLE优化器一般会，首先进行INDEX 唯一或者范围扫描，找出ID=111的行然后通过ROWID对应到相应的表中的行，完成此次查询。
试验：
SQL> select * from test
2 where it=100;
IT
----------
100
执行计划
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=ALL_ROWS (Cost=1 Card=1 Bytes=13)
1 0 INDEX (RANGE SCAN) OF 'TEST_INDEX' (INDEX) (Cost=1 Card=1
2、 如何看懂执行计划
一般说来执行计划遵循至右至上的原则（ORACLE 10G中）下面是一个复杂点的执行计划。
第一步：首先进行全表扫描TEST2，返回4行
第二步：对TEST3进行全表扫描返回5行
第三步：对TEST3和TEST2进行HASH JION也就是语句中的（and test3.id=test2.id）
返回5行
第四步：进行INDEX RANGE SCAN，因为表TEST存在着索引，优化器会选择索引扫描而不会进行全表扫描
第五步：以HASH JION返回的5行做驱动表，INDEX RANGE SCAN作为外部表进行NESTED LOOPS JION也就是语句中的（where test.it=test2.id）返回了72行
最后进行语句的总的总结：消耗代价7 返回72行 作用行的大小4680字节。
如何理解（Cost=1 Card=1 Bytes=13）:
这里说明代价为1 ，返回行1 ，此行的大小13字节.
3、 索引种类
(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经常实现唯一性扫描。
(2) 索引范围扫描(index range scan)
使用一个索引存取多行数据，同上面一样，如果索引是组合索引，如(1)所示，而且select ename from emp where ename = ‘JACK’ and deptno = ‘DEV’语句返回多行数据，虽然该语句还是使用该组合索引进行查询，可此时的存取方法称为索引范围扫描。在唯一索引上使用索引范围扫描的典型情况下是在谓词(where限制条件)中使用了范围操作符(如>、<、<>、>=、<=、between)
使用index rang scan的3种情况：
(a) 在唯一索引列上使用了range操作符(> < <> >= <= between)
(b) 在组合索引上，只使用部分列进行查询，导致查询出多行。
(c) 对非唯一索引列上进行的任何查询。
(3) 索引全扫描(index full scan)
与全表扫描对应，也有相应的全索引扫描。在某些情况下，可能进行全索引扫描而不是范围扫描，需要注意的是全索引扫描只在CBO模式下才有效。CBO根据统计数值得知进行全索引扫描比进行全表扫描更有效时，才进行全索引扫描，而且此时查询出的数据都必须从索引中可以直接得到。
(4) 索引快速扫描(index fast full scan)
扫描索引中的所有的数据块，与 index full scan很类似，但是一个显著的区别就是它不对查询出的数据进行排序，即数据不是以排序顺序被返回。在这种存取方法中，可以使用多块读功能，也可以使用并行读入，以便获得最大吞吐量与缩短执行时间。
4、 JOIN 类型
（1）排序 - - 合并连接(Sort Merge Join, SMJ)
内部连接过程：
1.首先生成row source1需要的数据，然后对这些数据按照连接操作关联列(如A.col3)进行排序。
2. 随后生成row source2需要的数据，然后对这些数据按照与sort source1对应的连接操作关联列(如B.col4)进行排序。
3. 最后两边已排序的行被放在一起执行合并操作，即将2个row source按照连接条件连接起来。
如果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都已经预先排序，则这种连接方法的效率也是蛮高的。
（2）嵌套循环(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] |
（3）哈希连接(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 |
5、 公司团险具体语句优化。
上周团险的CIRC功能出现问题，然后对数据库进行检测发现主要问题如下
SQL ordered by Reads
- Total Disk Reads: 1,997,604
- Captured SQL account for 99.9% of Total
| Physical Reads | Executions | Reads per Exec | %Total | CPU Time (s) | Elapsed Time (s) | SQL Id | SQL Module | SQL Text |
| 1,995,633 | 11 | 181,421.18 | 99.90 | 845.14 | 2737.80 |
| select distinct policy.polic... | |
| 8 | 2 | 4.00 | 0.00 | 0.65 | 2.05 |
| begin prvt_hdm.auto_execute( :... | |
| 0 | 10 | 0.00 | 0.00 | 0.00 | 0.00 |
| INSERT INTO sys.wri$_adv_objec... | |
| 0 | 2 | 0.00 | 0.00 | 0.00 | 0.03 |
| SELECT decode(value, 'FATAL', ... | |
| 0 | 2 | 0.00 | 0.00 | 0.04 | 0.07 |
| SELECT count(*) over () as tot... | |
| 0 | 1 | 0.00 | 0.00 | 24.05 | 24.05 |
| select rownumx, t2_12404754155... | |
| 0 | 1,459 | 0.00 | 0.00 | 0.17 | 0.17 |
| SELECT A.PRODUCT_ID, ... | |
| 0 | 14 | 0.00 | 0.00 | 0.00 | 0.00 |
| INSERT INTO sys.wri$_adv_actio... | |
| 0 | 4 | 0.00 | 0.00 | 0.02 | 0.02 |
| SELECT dbin.db_name, dbin.ins... | |
| 0 | 36 | 0.00 | 0.00 | 0.01 | 0.01 |
| SELECT I.ORG_ID AS id, ... |
SQL ordered by Elapsed Time
- Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
- % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
| Elapsed Time (s) | CPU Time (s) | Executions | Elap per Exec (s) | % Total DB Time | SQL Id | SQL Module | SQL Text |
| 2,738 | 845 | 11 | 248.89 | 93.40 |
| select distinct policy.polic... | |
| 74 | 74 | 3 | 24.71 | 2.53 |
| select count(*) from ( ... | |
| 41 | 41 | 1 | 40.52 | 1.38 |
| select rownumx, t2_12404754970... | |
| 40 | 40 | 1 | 39.72 | 1.35 |
| select rownumx, t2_12404755745... | |
| 24 | 24 | 1 | 24.05 | 0.82 |
| select rownumx, t2_12404754155... | |
| 3 | 3 | 2 | 1.63 | 0.11 |
| insert into wrh$_sga_target_ad... | |
| 2 | 2 | 1 | 2.16 | 0.07 | sqlplus@D0-LNXDB30 (TNS V1-V3) | select sql_text from v$sort_us... | |
| 2 | 1 | 2 | 1.03 | 0.07 |
| begin prvt_hdm.auto_execute( :... | |
| 1 | 1 | 1 | 0.71 | 0.02 |
| select count(*) from ( selec... | |
| 1 | 1 | 1 | 0.67 | 0.02 |
| select rownumx, t2_12404756207... |
语句gn32k8dc7uwav出现了大量的物理读，时间消耗也基本消耗此语句上，同时此期间数据库的物理写也是非常大的，当然通过其他一些语句也抓出了这条语句，可以确定是他的问题，现语句如下：
SQL> select distinct policy.policy_id policyId,
2 policy.policy_code PolNo,
3 policy.policy_code CertNo,
4 '' Pol_Prt_Code,
5 policy.proposal_code Prpl_No,
6 '' Prpl_Prt_Code,
7 hoa.post_code Post_Code,
8 io.org_code Branch_Code,
9 'G' GP_Type,
10 ho.hold_org_name App_Name,
11 '' App_Idcard_Type,
12 '' App_Idcard_No,
13 '' App_Income,
14 la.life_assured_name InsName,
15 it.TYPE_ID Ins_Idcard_Type,
16 la.id_code Ins_Idcard_No,
17 '' App_Ins_Relation,
18 b.beneficiary_name BeneName,
19 '' Sum_Ins,
20 '' Sum_Ins_Death,
21 policy.proposal_date App_Date,
22 policy.insert_date Input_Date,
23 uci.uw_end_date Underwr_date,
24 policy.commencement_date Eff_Date,
25 '' Recp_date,
26 '' Revisit_date,
27 policy.risk_cessation_date Matu_Date,
28 ho.linkman_name Conta_Name,
29 ho.linkman_tel Conta_Tel,
30 cgc.code_circ BUSI_SRC_TYPE,
31 agency.agency_code AGT_CODE,
32 '' Salesman_No,
33 '' Banc_Speci_No,
34 policy.issue_agent Staff_No,
35 policy.assumpsit Extra_Memo,
36 '' Reins_Type
37 from t_policy policy
38 left join t_holder_org ho on policy.company_holder = ho.party_id
39 left join t_address hoa on ho.address_id = hoa.address_id
40 left join t_insurance_org io on io.org_id = policy.ISSUE_AGENT_ORG
41 left join t_life_assured la on policy.policy_id = la.policy_id
42 left join t_id_type it on la.id_type_id = it.type_id
43 left join t_certificate c on policy.policy_id = c.policy_id
44 left join t_beneficiary b on b.certificate_id = c.certificate_id
45 left join t_uw_case_info uci on policy.proposal_code = uci.proposal_code
46 left join t_channel_gims_circ cgc on policy.sales_channel = cgc.channel_id_gims
47 left join t_agency agency on policy.issue_agency = agency.agency_id
48 where policy.policy_id = 352;
查看执行计划如下：
Execution Plan
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT |
|
|
| 198 (100) |
|
| 1 | HASH UNIQUE |
| 42 | 15708 | 198 (3) | 00:00:03 |
| 2 | HASH JOIN OUTER |
| 42 | 15708 | 197 (3) | 00:00:03 |
| 3 | HASH JOIN OUTER |
| 42 | 15162 | 155 (2) | 00:00:02 |
| 4 | NESTED LOOPS OUTER |
| 1 | 352 | 71 (3) | 00:00:01 |
| 5 | HASH JOIN OUTER |
| 1 | 341 | 70 (3) | 00:00:01 |
| 6 | NESTED LOOPS OUTER |
| 1 | 331 | 67 (3) | 00:00:01 |
| 7 | HASH JOIN OUTER |
| 1 | 328 | 67 (3) | 00:00:01 |
| 8 | HASH JOIN OUTER |
| 1 | 293 | 11 (10) | 00:00:01 |
| 9 | NESTED LOOPS OUTER |
| 1 | 216 | 7 (0) | 00:00:01 |
| 10 | NESTED LOOPS OUTER |
| 1 | 192 | 4 (0) | 00:00:01 |
| 11 | NESTED LOOPS OUTER |
| 1 | 183 | 3 (0) | 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | T_POLICY | 1 | 175 | 2 (0) | 00:00:01 |
| 13 | INDEX UNIQUE SCAN | T_POLICY_POLICY_SEQ_ID_PK | 1 |
| 1 (0) | 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | T_AGENCY | 4 | 32 | 1 (0) | 00:00:01 |
| 15 | INDEX UNIQUE SCAN | T_AGENCY_AGENCY_ID_FK | 1 |
| 0 (0) |
|
| 16 | TABLE ACCESS BY INDEX ROWID | T_INSURANCE_ORG | 2 | 18 | 1 (0) | 00:00:01 |
| 17 | INDEX UNIQUE SCAN | PK_T_INSURANCE_ORG | 1 |
| 0 (0) |
|
| 18 | TABLE ACCESS FULL | T_UW_CASE_INFO | 1 | 24 | 3 (0) | 00:00:01 |
| 19 | TABLE ACCESS FULL | T_HOLDER_ORG | 165 | 12705 | 3 (0) | 00:00:01 |
| 20 | TABLE ACCESS FULL | T_LIFE_ASSURED | 1 | 35 | 56 (2) | 00:00:01 |
| 21 | INDEX UNIQUE SCAN | PK_T_ID_TYPE | 1 | 3 | 0 (0) |
|
| 22 | TABLE ACCESS FULL | T_CHANNEL_GIMS_CIRC | 6 | 60 | 3 (0) | 00:00:01 |
| 23 | TABLE ACCESS BY INDEX ROWID | T_ADDRESS | 1 | 11 | 1 (0) | 00:00:01 |
| 24 | INDEX UNIQUE SCAN | PK_T_ADDRESS | 1 |
| 0 (0) |
|
| 25 | TABLE ACCESS FULL | T_CERTIFICATE | 42 | 378 | 84 (2) | 00:00:02 |
| 26 | TABLE ACCESS FULL | T_BENEFICIARY | 17938 | 227K | 41 (0) | 00:00:01 |
粗略的看一看此执行计划很多全表扫描，这是用问题的，然后分析其步骤也可以得出相应的结论，他会扫描过多的行，然后决定添加索引如下：
t_holder_org(party_id) (b-tree索引)
t_life_assured(policy_id) (反键索引)
t_uw_case_info(proposal_code) (b-tree索引)
t_certificate(policy_id) (BITMAP索引)
t_beneficiary(certificate_id) (b-tree索引)
建立什么养的索引要根据索引的特点和数据的特点来确定。
建立索引后也就是现在的团险生产库执行计划如下：
如果看总的消耗COST 以前为198 而现在仅仅为66刚好为以前的1/3,然后测试应用程序速度也提高了3倍左右，当然不是说他们成正比，但是确实提高了不好，另外此条语句是会循环进行的，我所做的优化会被循环次数所放大（自我感觉），就此语句其实还可以进一部缩减COST但是由于目的已经达到，所以优化可以停止了。
clip_image002.jpg来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7728585/viewspace-592060/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/7728585/viewspace-592060/
363
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
