oracle学习笔记.docx

oracle学习笔记.docx

《oracle学习笔记.docx》由会员分享，可在线阅读，更多相关《oracle学习笔记.docx（11页珍藏版）》请在冰豆网上搜索。

oracle学习笔记

1、sqlload导入数据

1.1、sqlloader的特点

oracle自己带了很多的工具可以用来进行数据的迁移、备份和恢复等工作。

但是每个工具都有自己的特点。

比如说exp和imp可以对数据库中的数据进行导出和导出的工作，是一种很好的数据库备份和恢复的工具，因此主要用在数据库的热备份和恢复方面。

有着速度快，使用简单，快捷的优点；同时也有一些缺点，比如在不同版本数据库之间的导出、导入的过程之中，总会出现这样或者那样的问题，这个也许是oracle公司自己产品的兼容性的问题吧。

sqlloader工具却没有这方面的问题，它可以把一些以文本格式存放的数据顺利的导入到oracle数据库中，是一种在不同数据库之间进行数据迁移的非常方便而且通用的工具。

缺点就速度比较慢，另外对blob等类型的数据就有点麻烦了。

1.2、sqlloader使用例子

创建一个假日表

createtableSOURCE_JRB

（

JRDATE,

DQDHVARCHAR2

（2）,

HBZLVARCHAR2

（2）,

SFJRVARCHAR2

（1）,

JLZTVARCHAR2

（1）,

BUSI_DATA_DATEDATE

）

1.3、控件文件jrb.ctl

UNRECOVERABLE--关闭日志，不可恢复

Loaddata--控制文件标识

INFILE*--要输入的数据文件名为这里在命令中指定，所以用*代替

"fix15"--指定一行记录的字符数，包括换行符

INTOTABLEsource_jrb--指定表名

APPEND--向表中追加记录

（

JRposition（1:

8）DATE'YYYY-MM-DD',--定长指定1-8位字符为列jr,类型为date

DQDHposition（9:

10）,

HBZLposition（11:

12）,

SFJRposition（13:

13）,

JLZTposition（14:

14）,

BUSI_DATA_DATEposition（1:

2）"to_date（‘20110428’）,'YYYY-MM-DD'"）

a、insert，为缺省方式，在数据装载开始时要求表为空

b、append，在表中追加新记录

c、replace，删除旧记录，替换成新装载的记录

d、truncate，同上

Data文件:

jrb.dat

20030101000011

20080207000011

20080208000011

20080211000011

20080212000011

20080404000011

20080504000001

20080501000011

20080502000011

20080609000011

20080915000011

20080927000001

1.4、执行命令

将jrb.ctl文件和jrb0.dat文件放在e:

\tem目录下

sqlldrarms/arms@armsdbcontrol=e:

\tem\jrb.ctldata=e:

\tem\JRB0.datlog=e:

\tem\JRB.logbad=e:

\tem\JRB.baddirect=true

arms/arms@armsdb这里数据库用户名：

arms；密码:

arms；数据库：

armsdb；

2、oracle对应用程序性能分析

有的时候我们发现一个应用系统运行很慢，运行很慢主要是由于跟数据库有关，致使数据很长时间没有返回数据，这个时候我们可能去查oracle到底哪里有问题，是什么原因导致他运行非常慢。

其实很多时间都是由于程序人员写的sql执行效率非常低或者某个时间段访问量忽然猛增或者其他原因，那么我们如何定位到是哪一个SQL或是哪一个时间段有问题呢？

因为如果我们定位到某个时间段比其他时间段cpu，内存都相当耗时，我们就可以查看我们应用系统在这个时间段做什么，从而分析出系统运行慢的原因；我们也可以分析某个sql的运行时间，有时一段sql跑上几个小时都没跑出来。

如果我们能定位到这些sql，对我们改善系统性能也是有很大帮助。

我们这里通个分析addmrt和awrrpt来粗略分析数据库性能。

在awrrpt这个里面，我们可以得到耗时前五的事件。

调取addmrt和awrrpt文件

登录oracle服务器进行以下目录：

cd$ORACLE_HOME

cdrdbms/admin

找到awrrpt.sql

lsawrrpt.sql

以数据库管理员进入sqlplus

sqlplus/assysdba

执行wrrpt.sql

@/database/product/10gR2/db_1/rdbms/admin/awrrpt.sql

在上图输入格式类型，我们就把这些报告保存为txt格式的，默认保存为html格式。

输完后回车

输入报告天数，我们输入1，输入1表示他将把最近1天的报告展示出来，如果是2那就是近两天的。

这里输入开始的一个snapid，在这里我们可以看到他是每隔一小时记一次快照，我们可以修改这个快照间隔。

回车后，文件名我们这里不输，默认就可以了。

回车后，在当前执行sql的目录中就生成了一个awrrpt_1_14078-14079.txt的文件，我们通过ftp把文件下载到本地（这里oracle服务器在AIX机器上）

选择要下载到本地的目录（我这里e:

\tem）

连接FTP

ftp168.3.63.190

输入用户名密码。

登到文件所在路径：

cd/database/product/10gR2/db_1/rdbms/admin

下载文件

getawrrpt_1_14078_14079.txtawrrpt_1_14078_14079.txt

3、oracle执行计划

通过oracle执行计划我们可以查询oracle数据库在执行一个sql时所花费的代价，它详细地列出了一个sql执行各部分时所耗费的代价，根据这些信息，可以为我们优化sql提供帮助。

3.1、相关的概念

DrivingTable驱动表：

又称为外层表OUTERTABLE。

这个概念用于嵌套与HASH连接中。

如果该rowsource返回较多的行数据，则对所有的后续操作有负面影响。

如果一个大表在WHERE条件有限制条件，则该大表作为驱动表也是合适的，所以并不是只有小表可以作为驱动表，正确说法应该为应用查询的限制条件后，返回较少行源的表作为驱动表。

在执行计划中，应该为靠上的那个rowsource，在后面描述中，一般将该表称为连接操作的rowsource1。

ProbedTable（被探查表）：

该表又称为内层表INNERTABLE。

在我们从驱动表中得到具体一行的数据后，在该表中寻找符合连接条件的行。

所以该表应当为大表[实际上应该为返回较大rowsource的表]且相应列上应该有索引。

在后面的描述中，一般将该表称为连接操作的rowsource2.

组合索引（concatenatedindex）：

由多个列构成的索引，如createindexidx_emponemp（col1,col2）。

在组合索引中有一个重要的概念：

引导列（leadingcolumn），在上面的例子中，col1列为引导列。

当我们进行查询时可以使用"wherecol1=?

"，也可以使用"wherecol1=?

andcol2=?

"，都会使用索引，但是"wherecol2=?

"查询就不会使用该索引。

所以限制条件中包含引导列时，该限制条件才会使用该组合索引。

可选择性（selectivity）：

比较一下列中唯一键的数量和表中的行数，就可以判断该列的可选择性。

如果该列的“唯一键的数量/表中的行数”的比值越接近1，则该列的可选择性越高，该列就越适合创建索引，同样索引的可选择性也越高。

在可选择性高的列上进行查询时，返回的数据就较少，比较适合使用索引查询。

3.2、oracle访问数据的存取方法

1）全表扫描（FullTableScans，FTS）

为实现全表扫描，Oracle读取表中所有的行，并检查每一行是否满足语句的WHERE限制条件.一个多块读操作可以使一次I/O能读取多块数据块，而不是只读取一个数据块，这极大的减少了I/O总次数，提高了系统的吞吐量，所以利用多块读的方法可以十分高效地实现全表扫描，而且只有在全表扫描的情况下才能使用多块读操作。

在这种访问模式下，每个数据块只被读一次。

使用FTS的前提条件：

在较大的表上不建议使用全表扫描，除非取出数据的比较多，超过总量的5%-10%，或你想使用并行查询功能时。

3.3、索引扫描（IndexScan）

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

一个rowid唯一的表示一行数据，该行对应的数据块是通过一次i/o得到的，在此情况下该次i/o只会读取一个数据库块。

索引扫描由2步组成：

[1]扫描索引得到对应的rowid值。

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

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

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

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

4、表之间的连接

4.1、嵌套循环（NestedLoops）

这个连接方法有驱动表（外部表）的概念。

其实，该连接过程就是一个2层嵌套循环，所以外层循环的次数越少越好，这也就是我们为什么将小表或返回较小rowsource的表作为驱动表（用于外层循环）的理论依据。

如果使用这种方法，决定使用哪个表作为驱动表很重要。

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

从内部连接过程来看，需要用rowsource1中的每一行，去匹配rowsource2中的所有行，所以此时保持rowsource1尽可能的小与高效的访问rowsource2（一般通过索引实现）是影响这个连接效率的关键问题。

这只是理论指导原则，目的是使整个连接操作产生最少的物理I/O次数。

在上面的连接过程中，我们称Rowsource1为驱动表或外部表。

RowSource2被称为被探查表或内部表。

在NESTEDLOOPS连接中，Oracle读取rowsource1中的每一行，然后在rowsourc2中检查是否有匹配的行，所有被匹配的行都被放到结果集中，然后处理rowsource1中的下一行。

这个过程一直继续，直到rowsource1中的所有行都被处理。

这是从连接操作中可以得到第一个匹配行的最快的方法之一，这种类型的连接可以用在需要快速响应的语句中，以响应速度为主要目标。

如果drivingrowsource（外部表）比较小，并且在innerrowsource（内部表）上有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。

NESTEDLOOPS有其它连接方法没有的的一个优点是：

可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。

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

对于并行查询，我们经常选择大表作为驱动表，因为大表可以充分利用并行功能。

当然，有时对查询使用并行操作并不一定会比查询不使用并行操作效率高，因为最后可能每个表只有很少的行符合限制条件，而且还要看你的硬件配置是否可以支持并行（如是否有多个CPU，多个硬盘控制器），所以要具体问题具体对待。

5、如何查看oracle执行计划

我们一般都用plsql工具来查看oracle的执行计划，在pl/sql的SQL窗口中写上我们要分析的SQL语句，按F5可以查看他的执行计划。

SQL语句：

selecta.dqdh||a.jgdh,a.khrq,a.khrq,a.dqdh,a.jgdh,a.zhye,a.xzxh,a.zhdh

fromSOURCE_DXZDJBa

joinARMS_CFG_GZTSbona.dqdh=b.dqdh

andb.tyrq=to_Date（'3000/12/31','yyyy/mm/dd'）

joinARMS_ORGAN_ENTRYIDe

ona.dqdh=e.organ_no

ande.entry_id='10033'

wherea.jlzt='1'

anda.zhye<>0

anda.xzrq

andwork_date（a.khrq,get_date）=b.gzts

andwork_date（sysdate,sysdate）=1

anda.khrq>=to_date（'2010-01-01','yyyy-mm-dd'）

解释计划：

这里为了好解释，后面的红色字体加了一个行号。

对于这个解释计划如何看，哪一步先执行哪一步后执行，原则是最右边最上面的先执行。

上面这个图最右边是第8行，对象名称是idx_dqdh_dxzdjb，描述是indexrangescan。

意思是索引扫描，再看他的上面7行，对象名称是source_dxzdjb，这两行合起就是查source_dxzdjb这张表，查询方式是索引查询。

然后然后再看第6行是一个全表访问，表名是arms_cfg_gzts，再看第5行，是一个嵌套循环，也就是把source_dxzdjb与arms_cfg_gzts表作连接，source_dxzdjb表作为驱动表。

因为oracle每次都只能是两个表进行连接，所以他把上面的两张表连接后作为一个新的表（暂时这么说吧）再与arms_organ_entryid这个表连接。

我们可以看到目前这个sql的耗费是很少的，也就是说这个SQL执行效率还可以。

如果耗费非常大的话就需要考虑进行优化。

比如一个tableaccessfull的耗费值非常大，我们可以考滤给他建索引。

多表的话我们可以看看他选的驱动表是否合理，驱动表的选择是结果集越少越好（并不是大表不能作驱动表，如果大表有where条件限制查询出来的结果集少也可以作驱动表）。

5、oracle会话

Oracle会话也叫session，我们可以通pl/sql以sysdba身份登录在工具→会话中来查看，如下图所示

有时我们需要对数据库进行还原，把原来的数据库及用户删掉，有时会发现删不掉，因为还有用户连着数据库，我们就可以在这里把这些连接数据库的用户会话kill掉，选中一个会话右键kill；还有如果有用户把一个表锁定一直不释放，我们可以通过这里选中某个会话看是哪一个会话锁定了表，如果长时间不释放，我们也可以强制结束会话来释放该表的锁。