oracle学习笔记.doc

1、 sqlload导入数据1.1、sql loader 的特点oracle自己带了很多的工具可以用来进行数据的迁移、备份和恢复等工作。但是每个工具都有自己的特点。比如说exp和imp可以对数据库中的数据进行导出和导出的工作，是一种很好的数据库备份和恢复的工具，因此主要用在数据库的热备份和恢复方面。有着速度快，使用简单，快捷的优点；同时也有一些缺点，比如在不同版本数据库之间的导出、导入的过程之中，总会出现这样或者那样的问题，这个也许是oracle公司自己产品的兼容性的问题吧。sql loader 工具却没有这方面的问题，它可以把一些以文本格式存放的数据顺利的导入到oracle数据库中，是一种在不同数据库之间进行数据迁移的非常方便而且通用的工具。缺点就速度比较慢，另外对blob等类型的数据就有点麻烦了。1.2、 sql loader使用例子创建一个假日表create table SOURCE_JRB( JR DATE, DQDH VARCHAR2(2), HBZL VARCHAR2(2), SFJR VARCHAR2(1), JLZT VARCHAR2(1), BUSI_DATA_DATE DATE)1.3、控件文件jrb.ctlUNRECOVERABLE -关闭日志，不可恢复Load data -控制文件标识INFILE * -要输入的数据文件名为这里在命令中指定，所以用*代替fix 15 -指定一行记录的字符数，包括换行符INTO TABLE source_jrb -指定表名APPEND -向表中追加记录(JR position(1:8) DATE YYYY-MM-DD , -定长指定1-8位字符为列jr,类型为dateDQDH position(9:10),HBZL position(11:12),SFJR position(13:13),JLZT position(14:14),BUSI_DATA_DATE position(1:2) to_date(20110428) ,YYYY-MM-DD)a、insert，为缺省方式，在数据装载开始时要求表为空 b、append，在表中追加新记录 c、replace，删除旧记录，替换成新装载的记录 d、truncate，同上Data文件:jrb.dat2003010100001120080207000011200802080000112008021100001120080212000011200804040000112008050400000120080501000011200805020000112008060900001120080915000011200809270000011.4、执行命令将jrb.ctl文件和jrb0.dat文件放在e:tem目录下sqlldr arms/armsarmsdb control=e:temjrb.ctl data=e:temJRB0.dat log=e:temJRB.log bad=e:temJRB.bad direct=truearms/armsarmsdb这里数据库用户名：arms；密码:arms；数据库：armsdb；2、oracle对应用程序性能分析有的时候我们发现一个应用系统运行很慢，运行很慢主要是由于跟数据库有关，致使数据很长时间没有返回数据，这个时候我们可能去查oracle到底哪里有问题，是什么原因导致他运行非常慢。其实很多时间都是由于程序人员写的sql执行效率非常低或者某个时间段访问量忽然猛增或者其他原因，那么我们如何定位到是哪一个SQL或是哪一个时间段有问题呢？因为如果我们定位到某个时间段比其他时间段cpu，内存都相当耗时，我们就可以查看我们应用系统在这个时间段做什么，从而分析出系统运行慢的原因；我们也可以分析某个sql的运行时间，有时一段sql跑上几个小时都没跑出来。如果我们能定位到这些sql，对我们改善系统性能也是有很大帮助。我们这里通个分析addmrt和awrrpt来粗略分析数据库性能。在awrrpt这个里面，我们可以得到耗时前五的事件。调取addmrt和awrrpt文件登录oracle服务器进行以下目录：cd $ORACLE_HOMEcd rdbms/admin找到awrrpt.sqlls awrrpt.sql以数据库管理员进入sqlplussqlplus / as sysdba执行wrrpt.sql/database/product/10gR2/db_1/rdbms/admin/awrrpt.sql在上图输入格式类型，我们就把这些报告保存为txt格式的，默认保存为html格式。输完后回车输入报告天数，我们输入1，输入1表示他将把最近1天的报告展示出来，如果是2那就是近两天的。这里输入开始的一个snap id，在这里我们可以看到他是每隔一小时记一次快照，我们可以修改这个快照间隔。回车后，文件名我们这里不输，默认就可以了。回车后，在当前执行sql的目录中就生成了一个awrrpt_1_14078-14079.txt的文件，我们通过ftp把文件下载到本地(这里oracle服务器在AIX机器上)选择要下载到本地的目录(我这里e:tem)连接FTPftp 90输入用户名密码。登到文件所在路径：cd /database/product/10gR2/db_1/rdbms/admin下载文件get awrrpt_1_14078_14079.txt awrrpt_1_14078_14079.txt3、oracle执行计划通过oracle执行计划我们可以查询oracle数据库在执行一个sql时所花费的代价，它详细地列出了一个sql执行各部分时所耗费的代价，根据这些信息，可以为我们优化sql提供帮助。3.1、相关的概念Driving Table驱动表：又称为外层表OUTER TABLE。这个概念用于嵌套与HASH连接中。如果该row source返回较多的行数据，则对所有的后续操作有负面影响。如果一个大表在WHERE条件有限制条件，则该大表作为驱动表也是合适的，所以并不是只有小表可以作为驱动表，正确说法应该为应用查询的限制条件后，返回较少行源的表作为驱动表。在执行计划中，应该为靠上的那个row source，在后面描述中，一般将该表称为连接操作的row source 1。Probed Table(被探查表)：该表又称为内层表INNER TABLE。在我们从驱动表中得到具体一行的数据后，在该表中寻找符合连接条件的行。所以该表应当为大表实际上应该为返回较大row source的表 且相应列上应该有索引。在后面的描述中，一般将该表称为连接操作的row source 2.组合索引(concatenated index)：由多个列构成的索引，如create index idx_emp on emp(col1,col2)。在组合索引中有一个重要的概念：引导列(leading column)，在上面的例子中，col1列为引导列。当我们进行查询时可以使用where col1 = ?，也可以使用where col1 = ? and col2 = ?，都会使用索引，但是where col2 = ?查询就不会使用该索引。所以限制条件中包含引导列时，该限制条件才会使用该组合索引。可选择性(selectivity)：比较一下列中唯一键的数量和表中的行数，就可以判断该列的可选择性。如果该列的“唯一键的数量/表中的行数”的比值越接近1，则该列的可选择性越高，该列就越适合创建索引，同样索引的可选择性也越高。在可选择性高的列上进行查询时，返回的数据就较少，比较适合使用索引查询。3.2、oracle访问数据的存取方法1）全表扫描（Full Table Scans， FTS）为实现全表扫描，Oracle读取表中所有的行，并检查每一行是否满足语句的WHERE限制条件.一个多块读操作可以使一次I/O能读取多块数据块，而不是只读取一个数据块，这极大的减少了I/O总次数，提高了系统的吞吐量，所以利用多块读的方法可以十分高效地实现全表扫描，而且只有在全表扫描的情况下才能使用多块读操作。在这种访问模式下，每个数据块只被读一次。使用FTS的前提条件：在较大的表上不建议使用全表扫描，除非取出数据的比较多，超过总量的5%-10%，或你想使用并行查询功能时。3.3、索引扫描（Index Scan）我们先通过index查找到数据对应的rowid值(对于非唯一索引可能返回多个rowid值)，然后根据rowid直接从表中得到具体的数据，这种查找方式称为索引扫描或索引查找(index lookup)。一个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、嵌套循环(Nested Loops)这个连接方法有驱动表(外部表)的概念。其实，该连接过程就是一个2层嵌套循环，所以外层循环的次数越少越好，这也就是我们为什么将小表或返回较小 row source的表作为驱动表(用于外层循环)的理论依据。如果使用这种方法，决定使用哪个表作为驱动表很重要。有时如果驱动表选择不正确，将会导致语句的性能很差、很差。从内部连接过程来看，需要用row source1中的每一行，去匹配row source2中的所有行，所以此时保持row source1尽可能的小与高效的访问row source2(一般通过索引实现)是影响这个连接效率的关键问题。这只是理论指导原则，目的是使整个连接操作产生最少的物理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，多个硬盘控制器)，所以要具体问题具体对待。5、如何查看oracle执行计划我们一般都用plsql工具来查看oracle的执行计划，在pl/sql的SQL窗口中写上我们要分析的SQL语句，按F5可以查看他的执行计划。SQL语句：select a.dqdh | a.jgdh,a.khrq ,a.khrq, a.dqdh, a.jgdh, a.zhye, a.xzxh, a.zhdh from SOURCE_DXZDJB a join ARMS_CFG_GZTS b on a.dqdh = b.dqdh and b.tyrq=to_Date(3000/12/31,yyyy/mm/dd) join ARMS_ORGAN_ENTRYID e on a.dqdh=an_no and e.entry_id=10033 where a.jlzt = 1 and a.zhye 0 and a.xzrq =to_date(2010-01-01,yyyy-mm-dd)解释计划：这里为了好解释，后面的红色字体加了一个行号。对于这个解释计划如何看，哪一步先执行哪一步后执行，原则是最右边最上面的先执行。上面这个图最右边是第8行，对象名称是idx_dqdh_dxzdjb，描述是index range scan。意思是索引扫描，再看他的上面7行，对象名称是source_dxzdjb，这两行合起就是查source_dxzdjb这张