研发人员ORACLE培训材料.docx

研发人员ORACLE培训材料.docx

《研发人员ORACLE培训材料.docx》由会员分享，可在线阅读，更多相关《研发人员ORACLE培训材料.docx（25页珍藏版）》请在冰豆网上搜索。

研发人员ORACLE培训材料

研发人员ORACLE培训材料

综合资源产品线

2010-12-29

修订历史记录

版本/状态

作者

参与者

起止日期

备注

目录

研发人员ORACLE培训材料1

1简介5

1.1编写目的5

1.2范围5

1.3定义、首字母缩写词和缩略语5

1.4参考资料5

2案例分析6

2.1综合资源项目的案例6

3SQL执行过程及执行计划9

3.1SQL执行过程9

3.2执行计划9

3.2.1执行计划相关参数10

3.2.2执行计划的类型10

4索引基础知识11

4.1类型类型11

4.1.1B-tree索引11

4.1.2位图索引12

4.2不会使用索引的情况12

4.3组合索引13

5数据扫描15

5.1全表扫描（FullTableScans,FTS）15

5.2通过rowid存取15

5.3索引扫描15

5.3.1索引唯一扫描（indexuniquescan）16

5.3.2索引范围扫描（indexrangescan）16

5.3.3索引全扫描（indexfullscan）17

5.3.4索引快速扫描（indexfastfullscan）17

6表连接18

6.1数据库表连接类型18

6.2嵌套循环连接（NESTEDLOOPJOIN）19

6.3排序合并连接（SORTMERGEJOIN）20

6.4哈希连接（HASHJOIN）21

6.5索引连接（INDEXJOIN）22

6.6主要连接方式比较23

7其他知识24

7.1IN和EXISTS的区别24

7.2子查询24

7.3视图合并25

7.4递归展开执行计划（connectby）25

8使用企业管理器进行性能监控27

8.1打开EM27

8.2进入性能页28

8.3进入顶级活动29

8.4查询耗资源的SQL语句30

1简介

编写目的

范围

定义、首字母缩写词和缩略语

参考资料

[1]文档编号（如果有的话）文档名称，作者，资料来源/出版单位，发表日期

表11表格标题

表头

表体

图11图形标题

2案例分析

综合资源项目的案例

典型的SQL语句

SELECT/*+ordereduse_nl（TPSDTPRDTPSCTPRCTPP1TLP1）*/

TPF1.SEQUENCEASSHELF_NUM,

TPSD.SEQUENCEASSLOT_NUM,

TPSC.SEQUENCEASCARD_NUM,

TPP1.SEQUENCEASPORT_NUM,

TLP1.CUID,

TLP1.LABEL_CN,

TLP1.RELATED_BMCLASSTYPE_CUID,

TLP1.RELATED_CARD_CUID,

TLP1.RELATED_LOGIC_DEVICE_CUID,

TLP1.RELATED_BANDWIDTH_CUID

FROMT_PHY_SHELFTPF1,

T_PHY_SLOTTPSD,

T_PHY_SLOT2CARDTPRD,

T_PHY_SLOTTPSC,

T_PHY_SLOT2CARDTPRC,

T_PHY_PORTTPP1,

T_LOGIC_PORTTLP1

WHERETPF1.CUID=TPSD.RELATED_SHELF_CUID

ANDTPSD.CUID=TPRD.RELATED_SLOT_CUID

ANDTPRD.RELATED_CARD_CUID=TPSC.RELATED_CARD_CUID

ANDTPSC.CUID=TPRC.RELATED_SLOT_CUID

ANDTPRC.RELATED_CARD_CUID=TPP1.RELATED_CARD_CUID

ANDTPP1.RELATED_LOGIC_PORT_CUID=TLP1.CUID

ANDTPF1.RELATED_DEVICE_CUID='T_PHY_HW_NE_80-8a03be2c2b2460ff012b246f570f0051';

SQL改造案例

改造前

SELECTDS.SEQUENCEASSLOT_NUM,CS.SEQUENCEASCARD_NUM,C.*

FROMT_PHY_SLOTDS

LEFTJOINT_PHY_SLOT2CARDDS2C

ONDS2C.RELATED_SLOT_CUID=DS.CUID

LEFTJOINT_PHY_SLOTCS

ONCS.RELATED_CARD_CUID=DS2C.RELATED_CARD_CUID

LEFTJOINT_PHY_SLOT2CARDR

ONR.RELATED_SLOT_CUID=DS.CUID

ORR.RELATED_SLOT_CUID=CS.CUID

LEFTJOINT_PHY_CARDC

ONC.CUID=R.RELATED_CARD_CUID

LEFTJOINT_MD_CARDTYPECT

ONCT.BM_CLASSID=C.RELATED_BMCLASSTYPE_CUID,T_PHY_SHELFF

WHEREDS.RELATED_SHELF_CUID=F.CUID

ANDF.RELATED_DEVICE_CUID='T_PHY_DEVICE_ERICSSON_SE800-8a03be2c2bb3b541012bb3f508102c75'

ANDDS.RELATED_CARD_CUIDISNULL

AND（CT.IS_SUBCARDISNULLORCT.IS_SUBCARD<>1）;

改造后

SELECT/*+ordereduse_nl（dsds2ccct）*/

DS.SEQUENCEASSLOT_NUM,''ASCARD_NUM,C.*

fromT_PHY_SHELFF,

T_PHY_SLOTDS,

T_PHY_SLOT2CARDDS2C,

T_PHY_CARDC,

T_MD_CARDTYPECT

whereF.CUID=DS.RELATED_SHELF_CUID

andf.cuid=ds.related_shelf_cuid（+）

andds.cuid=ds2c.related_slot_cuid（+）

andds2c.related_card_cuid=c.cuid（+）

andc.related_bmclasstype_cuid=ct.bm_classid（+）

andds.related_card_cuidisnull

and（ct.is_subcardisnullorct.is_subcard<>1）

andf.related_device_cuid='T_PHY_DEVICE_ERICSSON_SE800-8a03be2c2bb3b541012bb3f508102c75'

unionall

SELECT/*+ordereduse_nl（dsds2ccsrcct）*/

DS.SEQUENCEASSLOT_NUM,CS.SEQUENCEASCARD_NUM,C.*

fromT_PHY_SHELFf,

T_PHY_SLOTDS,

T_PHY_SLOT2CARDDS2C,

T_PHY_SLOTCS,

T_PHY_SLOT2CARDR,

T_PHY_CARDC,

T_MD_CARDTYPECT

wheref.cuid=ds.related_shelf_cuid

andds.cuid=ds2c.related_slot_cuid（+）

andds2c.related_card_cuid=cs.related_card_cuid（+）

andcs.cuid=r.related_slot_cuid（+）

andr.related_card_cuid=c.cuid（+）

andc.related_bmclasstype_cuid=ct.bm_classid（+）

andds.related_card_cuidisnotnull

and（ct.is_subcardisnullorct.is_subcard<>1）

andf.related_device_cuid='T_PHY_DEVICE_ERICSSON_SE800-8a03be2c2bb3b541012bb3f508102c75';

调整方法

（1）调整表的顺序

（2）调整表的连接条件

（3）检查各连接条件上是否有索引

（4）使用提示，固定执行计划

3SQL执行过程及执行计划

SQL执行过程

执行计划

查看SQL语句的执行计划，在PL/SQLDeveloper中F5键，查看语句对应的执行计划。

执行计划相关参数

基数（Card）：

指计划中这一步所处理的行数。

耗费（Cost）：

指cbo中这一步耗费的资源，这个值是相对值。

字节（bytes）：

指cbo中这一步所处理的所有记录的字节数，是估算出来的一组值。

执行计划的类型

4索引基础知识

类型类型

B-tree索引

我们使用的大部分都是这种索引

（1）适合于大量的增、删、改操作

（2）适合高基数的列（唯一值多）

（3）典型的树状结构

（4）每个节点都是数据块

（5）大多都在物理上一层、两层或三层不定。

（6）叶子块数据是排序的，从左到右递增

（7）在分支块或根块中放的是索引的范围。

位图索引

（1）适合于决策支持系统（OLAP）；

（2）做update操作时代价非常高；

（3）基数比较少的时候才适合建位图索引。

不会使用索引的情况

⏹存在数据类型隐形转换的

select*fromempwhereemp_id=‘123’;--emp_id是number型

⏹列上有数学运算的

select*fromempwheresalary*2<10000；

⏹使用不等于（<>）运算的（包括NOT运算）

select*fromempwheredept_no<>2001；--可以改写成wheredep_no<2001ordep_no>2001

注：

在Oracle的高版本中如9i，数据库引擎将自动进行类似转换。

所以程序中可以考虑使用<>运算以简化程序代码。

⏹使用substr字符串函数的，如：

select*fromempwheresubstr（last_name，1，4）=‘FRED’；

⏹‘%’通配符在第一个字符的，如:

select*fromstaff_memberwherefirst_namelike‘%DON’；

然而当通配符出现在字符串其他位置时，优化器就能利用索引。

⏹字符串连接（||）的，如：

select*fromempwherefirst_name||’’=‘DONALD’;

⏹ISNULL与ISNOTNULL

不能用null作索引，任何包含null值的列都将不会被包含在索引中。

即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。

也就是说如果某列存在空值，即使对该列建索引也不会提高性能。

任何在where子句中使用isnull或isnotnull的语句优化器是不允许使用索引的。

⏹函数的索引

日期类型也是很容易用到的，而且在SQL语句中会使用to_char函数以查询具体的的范围日期。

如：

select*fromempwhereTO_CHAR（birth_day，‘YYYY’）=‘2003’；

如果频繁使用类似语句，可以建立基于此函数的索引如：

CREATEINDEXInd_emp_birthONemp（to_char（（birth_day，‘YYYY’））；

组合索引

在oracle中可以创建组合索引，即同时包含两个或两个以上的列的索引。

在组合索引的使用方面，oracle有以下特点：

●当使用基于规则的优化器（RBO）时，只有组合索引的前导列出现在SQL语句的where子句时，才会使用到该索引；A、B、C

●在使用oracle9i之前基于成本的优化器（CBO）时，只有组合索引的前导列出现在SQL语句的where子句时，才会使用到该索引，这取决于优化器计算的使用索引的成本和使用全表扫描的成本，Oracle会自动选择成本低的访问路径；

●从Oracle9i起，Oracle引入了一种新的索引扫描方式——索引跳跃扫描（indexskipscan），这种扫描方式只有基于成本的优化器（CBO）才能使用。

这样，当SQL语句的where子句中即使没有组合索引的前导列，并且索引跳跃扫描的成本低于其他扫描方式的成本时，Oracle就会使用该方式扫描组合索引；

Oracle优化器有时会做出错误的选择，因为它再“聪明”，也不如我们SQL语句编写人员更清楚表中数据的分布，在这种情况下，通过使用提示（hint），我们可以帮助Oracle优化器作出更好的选择。

5数据扫描

全表扫描（FullTableScans,FTS）

在全表扫描时，oracle读取表中的所有行，并检查每一行是否满足语句的WHERE限制条件。

一个多块读操作可以一次IO读取多块数据（db_block_multiblock_read_count参数设定），而不是一次只读取一个数据块，这极大的减少了IO总次数，提供系统的吞吐量，所以多块读的方法可以十分高效地实现全表扫描，而且只有在全表扫描的情况下才能使用多块读操作。

使用FTS的前提条件：

在较大的表上不建议使用全表扫描，除非取出数据量比较多，超过总量的5%~10%。

通过rowid存取

行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID来存取数据可以快速定位到目标数据上，是oracle存取单行数据的最快方法。

这种存取方法不会用到多块读操作，一次IO只能读取一个数据块。

我们会经常在执行计划中看到该存取方法，如通过索引查询数据。

索引扫描

先通过index查询到数据对应的rowid（对于非唯一索引可能返回多个rowid值），然后根据rowid直接从表中得到具体的数据，这种查找方式称为索引扫描。

一个rowid唯一表示一行数据，该行对应的数据块是通过一次IO得到的，在这种情况下oracle只会读取一个数据库块。

在索引中，存储索引值和索引值对应的行rowid值。

索引扫描由两步组成：

（1）扫描索引得到对应的rowid。

（2）通过找到的rowid从表中读出具体的数据。

每步都是单独的一次IO，对于索引，由于经常使用，绝大多数已经cache到内存中，所以第一步的IO经常是逻辑IO，即数据可以从内存中得到。

但是对于第二步，如果表比较大，其数据不可能全在内存中，所以其IO很有可能是物理IO，这是一个机械操作，相对于逻辑IO来说，是机器费时间的。

所以如果大表进行索引扫描，取出的数据如果大于总量的5%~10%，使用索引扫描效率会下降。

如果查询的数据全在索引中找到，就可以避免第二步操作，避免了不必要的IO，此时即使通过索引扫描取出的数据比较多，效率还是很高的。

如果sql语句中对索引列进行排序，因为索引已经预先排好序了，那么在执行计划中不需要再对索引列进行排序。

根据索引的类型以及where限制条件的不同，有4种类型的索引扫描：

（1）索引唯一扫描（indexuniquescan）;

（2）索引范围扫描（indexrangescan）；

（3）索引全扫描（indexfullscan）

（4）索引快速扫描（indexfastfullscan）

索引唯一扫描（indexuniquescan）

通过唯一索引查找一个数值返回单个rowid，如果存在unique或primarykey约束，oracle经常使用唯一性扫描。

索引范围扫描（indexrangescan）

使用indexrangescan的3种情况：

（1）在唯一所列列上使用了range操作符（>、<、<>、>=、<=、between）。

（2）在组合索引上只使用部分列进行查询，导致查询出多行。

（3）对非唯一索引列上进行的任何查询。

索引全扫描（indexfullscan）

与全表扫描对应，也有相应的全索引扫描。

索引快速扫描（indexfastfullscan）

扫描索引中所有的数据块，与indexfullscan很类似，但是一个显著的区别就是它不对查询出的数据进行排序，即数据不是以排序顺序被返回。

在这种存取方法中，可以使用多块读功能，也可以进行并行读入，以便获得最大吞吐量，缩短执行时间。

6表连接

假定给出下面的两个表，我们有几种方式来手工检索出员工的姓名和工资。

第一种方式：

从表1按顺序读取每一个员工编号，针对每一个记录到表2中查询对应员工编号的工资。

第二种方式：

把表1和表2对员工编号进行排序，然后对表1和表2按从上到下的顺序依次查询。

第三种方式：

对表1的全部员工编号进行hash计算，将计算结果和行的位置保持下来。

然后开始读取表2的数据，对每一条记录的员工编号，进行hash计算，算出对应表1的行位置。

进行匹配。

◆表的连接是指在一个SQL语句中通过表与表之间的关联，从一个表或多个表检索出相关的数据。

◆连接是通过SQL语句中FROM从句的多个表名，以及WHERE从句定义的表之间的连接条件来实现的。

◆如果SQL语句的关联表超过两个，ORACLE会首先连接其中的两个表，产生一个结果集；然后将产生的结果集与下一个表再进行关联；继续这个过程，直到所有的表都连接完成；最后产生所需的数据。

数据库表连接类型

数据库表的连接有以下种类型：

（1）嵌套循环连接（NESTEDLOOPJOIN）

（2）排序合并连接（SORTMERGEJOIN）

（3）哈希连接（HASHJOIN）

（4）索引连接（INDEXJOIN）

（5）群集连接（CLUSTERJOIN）

（6）笛卡尔连接（CARTESIANJOIN）

按照连接符的不同还可以将表连接分为等值连接、非等值连接和外连接。

驱动表：

在进行数据库连接时，首先进行存取的表称为驱动表。

一般将限制性条件加在驱动表上。

嵌套循环连接（NESTEDLOOPJOIN）

嵌套循环连接过程

（1）Oracle优化器根据基于规则RBO或基于成本CBO的原则，选择两个表中的一个作为驱动表，并指定其为外部表。

（2）Oracle优化器再将另外一个表指定为内部表。

（3）Oracle从外部表读取第一行，然后和内部表中的数据逐一进行对比，所有匹配的记录放在结果集中。

（4）Oracle读取外部表的第二行，在和内部表的数据注意进行对比，所有匹配的记录添加到结果集中。

（5）重复上述步骤，直到外部表中所有记录全部处理完。

（6）最后产生满足要求的结果集。

嵌套循环连接分析

通过查询语句的执行计划，可以看出哪个表是外部表，哪个表是外部表。

上面的表（TABLE_A）是外部表（驱动表），下面的表（TABLE_B）是内部表。

SELECTSTATEMENTOptimizer=CHOOSE

　　NESTEDLOOPS

　　TABLEACCESS（FULL）OF'TABLE_A'

　　TABLEACCESS（FULL）OF'TABLE_B'

优点：

使用嵌套循环连接是一种从结果集中提取第一批记录最快速的方法。

在驱动表较小或内部表已连接的列有唯一索引或者高度可选的非唯一索引时，嵌套循环连接效果是比较理想的。

嵌套循环连接比其他连接方法有优势，它可以快速地从结果集中提取第一批记录，而不用等待整个结果集完全确定下来。

这样，在理想的情况下，终端用户就可以通过屏幕查看第一批记录，而在同时读取其他记录。

不管如何定义连接的条件或者模式，任何两表都可以使用嵌套循环连接，所以嵌套循环连接是非常灵活的。

缺点：

如果内部表连接列不包含索引，或者索引不是高度可选时，嵌套循环连接效率是很低的。

如果驱动表的记录非常庞大时，其他的链接方法可能更加有效。

可以通过在SQL语句中添加HINTS，强制ORACLE优化器产生嵌套循环连接的执行计划。

select/*+ordereduse_nl（b）*/a.user_name,b.dev_no

fromuser_infoa,dev_infob

wherea.user_id=b.user_id;

排序合并连接（SORTMERGEJOIN）

排序合并连接过程

（1）首先提取表A需要的数据，然后对这些数据按照连接操作关联列进行排序。

（2）然后提取表B需要的数据，然后对这些数据按照连接操作关联列进行排序。

（3）最后两边已排序的行被放在一起执行合并操作，即将2个表按照连接条件连接起来。

排序合并连接分析

在缺乏数据的选择性或者可用的索引时，或者两个表都过于庞大（所选的数据超过表记录的5%）时，排序合并连接将比嵌套循环连接更加有效。

排序合并连接需要比较大的临时内存块，用于排序，这将导致在临时表空间占用更多的内存和磁盘IO。

可以使用HINTS强制ORACLE优化器产生排序合并连接的执行计划。

select/*+use_merge（ab）*/a.user_name,b.dev_no

fromuser_infoa,dev_infob

wherea.user_id=b.user_id;

哈希连接（HASHJOIN）

　　当内存能够提供足够的空间时，哈希（HASH）连接是Oracle优化器通常的选择。

哈希连接中，优化器根据统计信息，首先选择两个表中的小表，在内存中建立这张表的基于连接键的哈希表；优化器再扫描表连接中的大表，将大表中的数据与哈希表进行比较，如果有相关联的数据，则将数据添加到结果集中。

　　当表连接中的小表能够完全cache到可用内存的时候，哈希连接的效果最佳。

哈希连接的成本只是两个表从硬盘读入到内存的成本。

　　但是，如果哈希表过大而不能全部cache到可用内存时，优化器将会把哈希表分成多个分区，再将分区逐一cache到内存中。

当表的分区超过了可用内存时，分区的部分数据就会临时地写到磁盘上的临时表空间上。

因此，分区的数据写磁盘时，比较大的区间（EXTENT）会提高I/O性能。

ORACLE推荐的临时表空间的区间是1MB。

临时表空间的区间大小由UNIFORMSIZE指定。